Ya Microsoft nos invita a comprar su nuevo Office 2016, pero su Office 2013 ya está bien cargado de bugs. Y como en internet aplica el dicho "nunca actualices algo que funciona", lo esperable es que al actualizar tengas sorpresas adicionales. ¿Qué significa eso de que VBA tiene bugs? Veamos bugs horripilantes en VBA para Office 2013.
Issues o bugs
Una cosa es tener "issues" (asuntos) y otra es tener "bugs" (errores, defectos). Los issues son cosas solucionables, o donde no se ha demostrado la existencia de un bug. Desafortunadamente Excel VBA no está lleno de issues, sino bugs. Lo digo con cierto dolor, porque era algo que tenía el potencial de funcionar.
Los programadores "hardcore" que programan en C++ y plataformas basadas en Unix, miran a Microsoft de reojo por encima del hombro. Miran a Visual Basic con desprecio. Y desafortunadamente los bugs de Excel VBA (que usa algunos comandos de Visual Basic) no ayudan mucho a desmentir, sino más bien a fortalecer la idea de los programadores acerca del desprecio que podrían sentir hacia el software de Microsoft. Para estos programadores, dignidad es no programar para Windows o al menos no en Visual Basic, porque consideran que es un producto casero. Yo me negaba a pensar así, hasta que me entré en colisión con los bugs de Microsoft.
Como herramienta casera funciona, como por ejemplo para programar los registros de notas de un maestro de escuela. Para uso casero es una muy buena herramienta.
Tablas pivote
Si viste mi post anterior Trucos y tretas en Excel VBA para programadores (Tablas pivote) verás que el código muchas veces se cae en la parte de crear tablas pivote. Como usuario es facilísimo crear tablas pivote, pero en código te hace la vida miserable.
Si grabas la creación de una tabla pivote tendrás código que parece muy claro, y si tratas de parametrizar para adaptar ese código literal a tus necesidades, y tratas de reutilizarlo vas a tener errores debido a bugs de Microsoft.
Cuando tenías Sheet1 creas una hoja, la siguiente será Sheet2 y luego Sheet3 y luego Sheet4. Si habías creado Sheet4 pero borraste todas las hojas excepto Sheet1, supondrías que al crear una hoja obtendrías Sheet2, pero en su lugar obtienes Sheet5 que es la que sigue de Sheet4 que es la última que fue creada. Esto no debería ser un problema, excepto por el hecho de que si tratas de usar un nombre que no sea el del siguiente Sheet al crear un pivot table, vas a tener un error. Es una conducta absurda.
Lo mismo sucede para las tablas pivote. Y si no escoges el nombre de la siguiente tabla pivote en la secuencia, tendrás un error. Entonces creas el código para detectar nombres de tabla pivote y encontrar el siguiente nombre entre las pivotes existentes en el libro de Excel. Pero si tomas el código para crear la tabla pivote y reemplazas el valor literal (valor de texto entre comillas) y lo reemplazas por una variable tipo String, aunque tengas el nombre correcto de tabla pivote, vas a obtener un error.
De esta manera tus sueños de usar código genérico para crear tablas pivote se va por el drenaje. Este código no va a funcionar porque al cambiar el valor literal de la tabla pivote por una variable String llamada PivotName, no sirve. Este código no sirve.
Sub CreatePivot2013()
Dim SourceSheet As String
Dim DestinationSheet As String
Dim PivotName As String
SourceSheet = ActiveSheet.Name
Sheets.Add
DestinationSheet = ActiveSheet.Name
PivotName = NextPivotTableName
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
SourceSheet & "!R1C1:R1048576C4", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=DestinationSheet & "!R3C1", TableName:=PivotName, DefaultVersion _
:=xlPivotTableVersion15
End Sub
Eso significa que la creación de tablas pivote está "hardcoded" y no puede realizarse por medio de un programa. El código que grabaste sirve sólo una vez, para la ocasión en que grabaste el código de macro, y luego nunca más.
Variables tipo Byte
Con las variables byte deberias tener 8 bits, que te alcanza para valores de 0 a 255. Si pensaste que ibas a ahorrar memoria porque los valores que ibas a usar eran enteros que estaban en rango de 0 a 255, te encontrarás una sorpresa. vas a tener errores con valores entre 0 y 255.
De esta manera el código que habrías usado para encontrar el siguiente valor de tabla pivote, usando variables tipo byte, va a verse con errores debido a bugs de Microsoft. Vas a ver error de overflow con un valor de 255 en la variable i. Este código no sirve por eso
Private Function NextPivotTableName() As String
Dim i As Byte
Dim PrevExistingPivotName As Byte
For i = 1 To 255
If PivotNameExists("PivotTable" & CStr(i)) Then
PrevExistingPivotName = i
Else
If i = PrevExistingPivotName + 1 Then
NextPivotTableName = "PivotTable" & CStr(i)
End If
End If
Next i
End Function
De esta manera te despides del sueño de ahorrar memoria, y te despides del sueño de crear tablas pivote, porque VBA está lleno de bugs.
Otros bugs
Sharepoint queries
Cuando creas queries en Sharepoint (archivos con extensión .iqy), y grabas la operación de abrir el query en Excel, el código que obtienes es "hardcoded". Si quieres convertir ese código en código genérico donde puedes parametrizar la operación, va a fracasar miserablemente. De este modo cada vez que tengas un sharepoint dado deberás transcribir el código literal para ese sharepoint especifico. Al menos el código literal funciona, pero no así con tablas pivote.
Esconder blancos en tablas pivote
Si tienes (blank) en las filas de una tabla pivote, y grabas macro para su eliminación, Excel grabará código. Pero si tienes (blank) en las filas y columnas de la tabla pivote, al eliminar este item no grabará nada. Si tratas de usar el código que grabaste cuando sólo había un (blank) en las filas, obtendrás un error.
Como puedes ver, si querías hacer algo profesional en Excel VBA y creiste que las cosas eran fáciles, y habías prometido a tu jefe que ibas a terminar tu proyecto, vas a llevarte sorpresas desagradables.
Dando la vuelta al problema
Yo sé que esperabas una solución, un truco para saltarse el bug.
Bug de tipo Byte
Para el bug del tipo Byte lo mejor es que uses variables tipo Long.
Bug de blancos en tablas pivote
Convierte el rango en una tabla.
Bug al crear tablas pivote
Para la creación de tablas pivote convierte el rango a formato de tabla, y borra todas las tablas pivote del Workbook antes de crear las tablas.
La solución a los bugs
Al final tu programa principal dirá esto (UltimaFila y UltimaColumna son variables que debes calcular):
ConvertRangeToTable 1, 1, UltimaFila, UltimaColumna, "Table1"
DeleteAllPivotsInWorkbook
CreatePivot2013 "Table1"
Y tendrás que agregar este código para usar tablas pivote.
Sub DeleteAllPivotsInWorkbook()
Dim wks As Worksheet
Dim pt As PivotTable
For Each wks In Worksheets
For Each pt In wks.PivotTables
wks.Range(pt.TableRange2.Address).Delete Shift:=xlUp
Next pt
Next wks
Set pt = Nothing
Set wks = Nothing
End Sub
Sub CreatePivot2013(TableName As String) 'Use DeleteAllPivotsInWorkbook before using this sub for the fort time
Dim SourceSheet As String
Dim DestinationSheet As String
Dim MyPivotName As String
SourceSheet = ActiveSheet.Name
GotoSheet SourceSheet
Sheets.Add
DestinationSheet = ActiveSheet.Name
MyPivotName = NextPivotTableName
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
TableName, Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
:=DestinationSheet & "!R3C1", TableName:=NextPivotTableName, DefaultVersion:= _
xlPivotTableVersion15
End Sub
Private Function NextPivotTableName() As String
Dim i As Long
Dim PrevExistingPivotName As Long
For i = 1 To 255
If PivotNameExists("PivotTable" & CStr(i)) Then
PrevExistingPivotName = i
Else
If i = PrevExistingPivotName + 1 Then
NextPivotTableName = "PivotTable" & CStr(i)
End If
End If
Next i
End Function
Sub ConvertRangeToTable(Row1 As Long, Column1 As Long, Row2 As Long, Column2 As Long, TableName As String)
Dim tbl As ListObject
Dim rng As Range
If Row1 > 0 And Column1 > 0 And Row2 > 0 And Column2 > 0 Then
Set rng = Range(Cells(Row1, Column1), Cells(Row2, Column2))
If TableExistsInSheet(TableName) Then
'Update
ActiveSheet.ListObjects(TableName).Resize rng
Else
'Create
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
tbl.Name = TableName
End If
End If
End Sub
Como que la solución era intuitivamente obvia. ¿O no? Pues no. Es bastante desagradable gastar mucho tiempo lidiando con bugs. Realmente desagradable. Es improductivo. Pero he aquí la solución para que no sufras tú.
Reflexiones finales
Imagno que los bugs vienen de programadores baratos que hacen todo "harcoded", lo que hace que tratar de hacer cambios empuje el suirgimiento de bugs donde no había problemas. Y encima no documentan bien, no simplifican el código mediante encapsulamiento, ni usan procedimientos genéricos reutilizables.
El problema es que en la idiosincrasia de estas gentes, la consistencia no es uno de sus atributos culturales. Y el resultado natural es esto que vemos.
VBA quizás no sea tan profesional como C++, pero si no tuviera bugs, sería herramienta de elección para muchos propósitos. Pero ya hay proyectos en grandes empresas que se están migrando a otros lenguajes debido a este problema. El mayor problema viene de que actualmente los bancos basan una buena parte de su eficiencia en el uso de macros en VBA, y con los bugs sorpresivos que vemos, los bancos podrían ver una fuente de riesgo en el uso de VBA.
Ya con Internet Explorer (IE) hay fuga de usuarios, grandes compañías que están prefiriendo usar Chrome de Google, que ofrece más velocidad, y una buena seguridad. Es qie IE empezó como una pila de ladrillos, y cada versión era el resultado de amontonar pilas de ladrillos sobre pilas de ladrillos para terminar creando un mamarracho ineficiente sin arquitectura. No es de extrañar que Google haya tomado su lugar.
Que no puedas crear tablas pivote con código es una monstruosidad. Que no puedas usar variables tipo Byte como se ha usado desde eras inmemoriales, es el fin del mundo. Te deja atónito y estupefacto.
¿Acaso esto presenta una oportunidad para otras empresas o entidades que hagan bien las cosas desplazar las macros de Microsoft Office? ¿Acaso será buena idea comprar Microsoft Office 2016 si te gusta tener programas cosas que funcionan o es mejor buscar otras opciones? Bill Gates dejó un monopolio imbatible. Hoy no estoy seguro de saber lo que estoy mirando...
