Trucos y tretas en Excel VBA para programadores (parte 1)
Excel
Actualizado el 13 de Julio del 2019 por Gnomi (Publicado el 10 de Julio del 2019)
1.400 visualizaciones desde el 10 de Julio del 2019
articulo original en: https://www.gnomi.club/trucos-y-tretas-en-excel-vba/
Trucos y tretas en Excel VBA es un post que no está hecho para novatos que no sepan nada de macros de Excel, sino aquellos que saben algo de programación de Visual Basic. Especialmente en finanzas o en bolsa, pero también en negocios de todo tipo, programar macros de Excel suele ser sumemante útil y por eso he querido dedicar este post a transmitir algunos trucos para ahorrarte tiempo.
Cuando programamos código para macros de Microsoft Excel VBA a menudo nos encontramos con que los detalles de cómo invocar tal o cual cosa de Excel termina siendo muy críptica y debes navegar por foros, tutoriales o incontables páginas ininteligibles de Microsoft que no te responden lo que andas buscando.
Si sabes Visual Basic, lo más lógico es que para elaborar macros, trates de grabar una macro primero, y luego empiezas a ver el código de muestra para ver cómo hacer determinadas cosas. En este post se pretende proveer trucos de programación en Excel VBA para efectuar tareas que no se pueden derivar del código grabado en una macro en Excel. Estos trucos son el resultado de resolver una serie de problemas que en su momento significaron un dolor de cabeza.
Obtener valor de una celda
Este ejemplo inserta el valor 23 en la celda de la fila 4 y columna 5. Luego la variable x toma ese valor y le suma 1. Cabe añadir que cada vez que llamamos una celda, una cantidad muy grande de código de Excel es invocada, de modo que si piensas trabajar con muchas celdas o muchas veces con la misma celda, es mejor que pases los valores a variables, en lugar de llamar a la celda en sí misma.
En este ejemplo, la celda de la fila 2 columna 3 es seleccionada, y la selección luego es objeto de centrado vertical y horizontal usando Selection.
Usando rangos
Pasar de la notación de fila y columna a la de celdas suele ser un poco incómodo. Normalmente para seleccionar un rango usaríamos algo como esto:
Como has visto, lo que está procesando la función Range() entre comillas es una hilera de caracteres. Para efectos de escribir código, podemos usar esta expresión equivalente:
Convertir números en letras (pasar de la notación fila 5 columna 1 a “A5”) suele ser sumamente incómodo. Para ello he construido una función que hace el trabajo.
La función convierte el número en letras, en caracteres. Cabe anotar que sólo sirve para llegar hasta columna ZZ, de modo que si se pasa de esa columna, esta función no te servirá. Para llamar esta función con las mismas coordenadas (5,1) y (8,2) harías algo como esto para convertir en hilera:
Como habrás notado he usado la función Format() que evita que se agreguen espacios al convertir de número a hilera, como pasaría con la función Str().
Ventana emergente para que el usuario ingrese un rango
Si quieres que aparezca una ventana emergente con la cual puedas pedir al usuario que seleccione un rango de datos, puedes usar el código que se muestra a continuación. Lo que se hace es declarar un objeto de tipo Range llamado celdasSeleccionadas. A ese objeto se le asigna el resultado de la función Application.InputBox que despliega la ventana emergente que solicita la información al usuario. Si el usuario no ingresa ningún rango, el valor que tendrá el objeto es Nothing.
El comando On Error Resume Next indica que si ocurre un error, entonces ignore el error y proceda conla siguiente instrucción. El comando On Error Goto 0 indica que se cancela la detección de errores. El comando Msgbox despliega información en una ventana emergente.
He aquí el código:
Como habrás notado en este código lo que se hace si el usuario digita un código es desplegar el valor de la suma de todos los valores del rango usando la función Application.WorksheetFunction.Sum, pero tú puedes hacer que haga algo distinto.
Si quisieras obtener el valor contenido en el rango seleccionado puedes usar este código.
En este caso el contenido del rango se almacena en una variable llamada MiVariable.
Obtener posición de la última celda
Cuando tienes hojas de Excel de tamaño variable y necesitas ubicar la última celda ocupada (la que está más abajo y más a la derecha), puedes usar lo siguiente:
Si buscas en Internet acerca de cómo obtener posición de la última celda, encontrarás maneras muy diversas. Sin embargo, esta es la mejor que he encontrado. Si agregas valores más allá de la última celda, el valor se actualiza. El único problema es que si borras filas o columnas, este número no cambiará, sino hasta que abras de nuevo el archivo de Excel. Sin embargo eso es mejor que nada, y puedes verificar si encuentras celdas en blanco, así que eso no debería ser problema.
No olvides de compartir en tus redes sociales y de comentar!!!
Bendiciones!!!
Trucos y tretas en Excel VBA es un post que no está hecho para novatos que no sepan nada de macros de Excel, sino aquellos que saben algo de programación de Visual Basic. Especialmente en finanzas o en bolsa, pero también en negocios de todo tipo, programar macros de Excel suele ser sumemante útil y por eso he querido dedicar este post a transmitir algunos trucos para ahorrarte tiempo.
Cuando programamos código para macros de Microsoft Excel VBA a menudo nos encontramos con que los detalles de cómo invocar tal o cual cosa de Excel termina siendo muy críptica y debes navegar por foros, tutoriales o incontables páginas ininteligibles de Microsoft que no te responden lo que andas buscando.
Si sabes Visual Basic, lo más lógico es que para elaborar macros, trates de grabar una macro primero, y luego empiezas a ver el código de muestra para ver cómo hacer determinadas cosas. En este post se pretende proveer trucos de programación en Excel VBA para efectuar tareas que no se pueden derivar del código grabado en una macro en Excel. Estos trucos son el resultado de resolver una serie de problemas que en su momento significaron un dolor de cabeza.
Trucos y tretas en Excel VBA: Manejo de Celdas
Obtener valor de una celda
1
2
Cells(4,5)=23
x = Cells(4,5)+1
1
2
3
Cells(2,3).Select
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Usando rangos
Pasar de la notación de fila y columna a la de celdas suele ser un poco incómodo. Normalmente para seleccionar un rango usaríamos algo como esto:
1
Range("A5:B8").Select
1
Range(Cells(5,1),Cells(8,2)).Select
1
2
3
4
5
6
7
8
9
10
11
12
13
Private Function GetColumnLetter(index) As String
Dim FL, LL As LongGetColumnLetter = ""
LL = (index - 1) Mod 26 + 65
If LL > 63 Then GetColumnLetter = Chr(LL)
If index > 26 ThenFL = Int((index - 1) / 26) + 64
GetColumnLetter = Chr(FL) & GetColumnLetter
End If
End Function
1
Range(GetColumnLetter(5) & Format(1,"0") & ":" & GetColumnLetter(8) & Format(2,"0")).Select
Ventana emergente para que el usuario ingrese un rango
Si quieres que aparezca una ventana emergente con la cual puedas pedir al usuario que seleccione un rango de datos, puedes usar el código que se muestra a continuación. Lo que se hace es declarar un objeto de tipo Range llamado celdasSeleccionadas. A ese objeto se le asigna el resultado de la función Application.InputBox que despliega la ventana emergente que solicita la información al usuario. Si el usuario no ingresa ningún rango, el valor que tendrá el objeto es Nothing.
El comando On Error Resume Next indica que si ocurre un error, entonces ignore el error y proceda conla siguiente instrucción. El comando On Error Goto 0 indica que se cancela la detección de errores. El comando Msgbox despliega información en una ventana emergente.
He aquí el código:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Dim celdasSeleccionadas As Range
On Error Resume Next
Set celdasSeleccionadas = Application.InputBox(prompt:="Seleccione un rango de celdas", Type:=8)
On Error GoTo 0
If celdasSeleccionadas Is Nothing Then
'Aquí va el código que se ejecuta si no se selecciona ninguna celda.
'En este caso se despliega una ventana emergente con un mensaje.
MsgBox "No se ha seleccionado ninguna celda."Else
'Aquí va el código que se ejecuta si se se seleccionó celdas.
'En este caso se despliega una ventana emergente con el resultado de la suma de celdas.
MsgBox Application.WorksheetFunction.Sum(celdasSeleccionadas.Cells)
End If
Como habrás notado en este código lo que se hace si el usuario digita un código es desplegar el valor de la suma de todos los valores del rango usando la función Application.WorksheetFunction.Sum, pero tú puedes hacer que haga algo distinto.
Si quisieras obtener el valor contenido en el rango seleccionado puedes usar este código.
1
MiVariable = celdasSeleccionadas.Value
Obtener posición de la última celda
Cuando tienes hojas de Excel de tamaño variable y necesitas ubicar la última celda ocupada (la que está más abajo y más a la derecha), puedes usar lo siguiente:
1
2
3
ActiveCell.SpecialCells(xlLastCell).Select
UltimaFila = ActiveCell.Row
UltimaColumna = ActiveCell.Column
Si buscas en Internet acerca de cómo obtener posición de la última celda, encontrarás maneras muy diversas. Sin embargo, esta es la mejor que he encontrado. Si agregas valores más allá de la última celda, el valor se actualiza. El único problema es que si borras filas o columnas, este número no cambiará, sino hasta que abras de nuevo el archivo de Excel. Sin embargo eso es mejor que nada, y puedes verificar si encuentras celdas en blanco, así que eso no debería ser problema.
No olvides de compartir en tus redes sociales y de comentar!!!
Bendiciones!!!
Comentarios... (0)
No hay comentarios