ALGUNAS RECOMENDACIONES DE MICROSOFT PARA VBA CON EXCEL
En algunos detalles el VBA para Excel no es muy obvio que digamos. Buscando en el help de Microsoft me encontré con estos datos que me fueron bastante útiles en mi trabajo. En algunos adjunto un comentario de con mi aplicación práctica:
- Algunas funciones de hoja de cálculo no tienen utilidad en Visual Basic. Por ejemplo, la función Concatenar no se necesita, ya que en Visual Basic puede usar el operador & para unir varios valores de texto.
- Llamar a una función de hoja de cálculo desde Visual Basic:
En Visual Basic, las funciones de hoja de calculo de Microsoft Excel pueden ejecutarse mediante el objeto WorksheetFunction.
El siguiente procedimiento Sub usa la función Mín para obtener el valor más pequeño de un rango de celdas. En primer lugar, se declara la variable miRango como un objeto Range y, a continuación, se establece como el rango A1:C10 de la Hoja1. Otra variable, respuesta, se asigna al resultado de aplicar la función Mín a miRango. Por último, el valor de respuesta se muestra en un cuadro de mensaje.
Sub UseFunction()
Dim miRango As Range
Set miRango = Worksheets("Hoja1").Range("A1:C10")
respuesta = Application.WorksheetFunction.Min(miRango)
MsgBox respuesta
End Sub
Comentario: el sentido común nos diría que podemos incluír la función de Excel en nuestro programa VBA sin más pero no es así, hay que definir primero el rango (Set miRango en este ejemplo) y luego colocar la función usando Application.WorksheetFunction.
Por ejemplo con:
Set miRango = Worksheets("Hoja2").Range("B11:B21")
Hoja2.Cells(22, 2) = Application.WorksheetFunction.Sum(miRango)
Set miRango = Worksheets("Hoja2").Range("C11:C21")
Hoja2.Cells(22, 3) = Application.WorksheetFunction.Sum(miRango)
Se suman las celdas C11 a la C21 y el resultado se coloca en la celda(22,3)
- Si usa una función de hoja de cálculo que requiere como argumento una referencia de rango, deberá especificar un objeto Range. Por ejemplo, puede usar la función de hoja de cálculo Coincidir para efectuar una búsqueda en un rango de celdas. En una celda de hoja de cálculo, podría introducir una fórmula como =COINCIDIR(9;A1:A10;0). No obstante, en un procedimiento de Visual Basic, para obtener el mismo resultado debe especificar un objeto Range.
Sub FindFirst()
miVar = Application.WorksheetFunction _
.Match(9, Worksheets(1).Range("A1:A10"), 0)
MsgBox miVar
End Sub
Nota Las funciones de Visual Basic no usan el calificador WorksheetFunction. Una función puede tener el mismo nombre que una función de Microsoft Excel y, sin embargo, dar otros resultados. Por ejemplo, Application.WorksheetFunction.Log y Log dan resultados diferentes.
- Insertar una función de hoja de cálculo en una celda
Para insertar una función de hoja de cálculo en una celda, especifique la función como el valor de la propiedad Formula del objeto Range correspondiente. En el siguiente ejemplo, la función ALEATORIO (que genera un número aleatorio) se asigna a la propiedad Formula del rango A1:B3 de la Hoja1 del libro activo.
Sub InsertFormula()
Worksheets("Hoja1").Range("A1:B3").Formula = "=ALEATORIO()"
End Sub
- Puede identificar las hojas por su nombre, utilizando las propiedades Worksheets y Charts. Las siguientes instrucciones activan varias hojas del libro activo.
Worksheets("Hoja1").Activate
Charts("Gráfico1").Activate
DialogSheets("Diálogo1").Activate
- Puede utilizar la propiedad Sheets para devolver una hoja de cálculo, de gráficos, de módulo o de cuadro de diálogo, incluidos todos en el conjunto Sheets. El siguiente ejemplo activa la hoja denominada "Gráfico1" del libro activo.
Sub ActivarGráfico()
Sheets("Gráfico1").Activate
End Sub
Nota Los gráficos incrustados en una hoja de cálculo son miembros del grupo ChartObjects, aunque los gráficos que poseen sus propias hojas pertenecen al conjunto Charts.
- Puede hacer referencia a una celda o rango de celdas del estilo de referencia A1 utilizando el método Range. El siguiente procedimiento Sub cambia el formato de las celdas A1:D5 a negrita.
Sub FormatoRango()
Workbooks("Libro1").Sheets("Hoja1").Range("A1:D5") _
.Font.Bold = True
End Sub
- La siguiente tabla muestra algunas referencias de estilo A1 utilizando el método Range.
Referencia Significado
Range("A1") Celda A1
Range("A1:B5") Celdas de la A1 a la B5
Range("C5:D9,G9:H16") Selección de varias áreas
Range("A:A") Columna A
Range("1:1") Fila uno
Range("A:C") Columnas de la A a la C
Range("1:5") Filas de la uno a la cinco
Range("1:1,3:3,8:8") Filas uno, tres y ocho
Range("A:A,C:C,F:F") Columnas A, C y F
- Al utilizar Visual Basic, con frecuencia necesitará ejecutar el mismo bloque de instrucciones en cada una de las celdas de un rango. Para ello, combine una instrucción de repetición y uno o más métodos para identificar cada celda, una a la vez, y ejecutar la operación.
Una manera de ejecutar un bucle en un rango es utilizar el bucle For...Next con la propiedad Cells. Al utilizar la propiedad Cells, puede sustituir el contador del bucle, u otras variables o expresiones, por el número de índice de las celdas. En el siguiente ejemplo se sustituye la variable contador por el índice de fila. El procedimiento ejecuta un bucle en el rango C1:C20, estableciendo en 0 (cero) cualquier número cuyo valor absoluto sea menor que 0.01.
Sub RedondeoACero1()
For contador = 1 To 20
Set Celda_a = Worksheets("Hoja1").Cells(contador, 3)
If Abs(Celda_a.Value) < 0.01 Then Celda_a.Value = 0
Next contador
End Sub
Otra manera sencilla de ejecutar un bucle en un rango es utilizar el bucle For Each...Next en el conjunto de celdas devuelto por el método Range. Visual Basic establece automáticamente una variable de objeto para la siguiente celda cada vez que se ejecuta el bucle. El siguiente procedimiento realiza un bucle en el rango A1:D20, estableciendo en 0 (cero) cualquier número cuyo valor absoluto sea menor que 0.01.
Sub RedondeoACero2()
For Each c In Worksheets("Hoja1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
End Sub
Comentario: esto sirve, por ejemplo para programar la suma de dos columnas (B y C, desde la fila 11 a la 22 en este ejemplo) así
Dim miRango As Range
For contador = 11 To 22
Set celda_a = Worksheets("Hoja2").Cells(contador, 2)
Set celda_b = Worksheets("Hoja2").Cells(contador, 3)
Hoja2.Cells(contador, 4) = celda_a +celda_b
Next contador
- Si no conoce los límites del rango en que desea ejecutar el bucle, puede utilizar la propiedad CurrentRegion para devolver el rango que rodea la celda activa. Por ejemplo, el siguiente procedimiento, cuando se ejecuta desde una hoja de cálculo, ejecuta un bucle en el rango que rodea la celda activa, estableciendo en 0 (cero) todos los números cuyo valor absoluto sea menor que 0.01.
Sub RedondeoACero3()
For Each c In ActiveCell.CurrentRegion.Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
End Sub
- Al aplicar la propiedad Cells a una hoja de cálculo sin especificar un número de índice, el método devuelve un objeto Range que representa todas las celdas de la hoja de cálculo. El siguiente procedimiento Sub borra el contenido de todas las celdas de la Hoja1 del libro activo.
Sub BorrarHoja()
Worksheets("Hoja1").Cells.ClearContents
End Sub
- Una manera de trabajar con una celda relacionada con otra es utilizar la propiedad Offset. El siguiente ejemplo asigna un formato de doble subrayado al contenido de la celda situada una fila más abajo y a tres columnas de la hoja de cálculo activa.
Sub Subrayar()
ActiveCell.Offset(1, 3).Font.Underline = xlDouble
End Sub
Nota Puede grabar macros que utilicen la propiedad Offset en lugar en referencias absolutas. En el menú Herramientas, señale Grabar macro y, a continuación, haga clic en Usar referencias relativas.
- Para ejecutar un bucle en un rango de celdas, utilice en el rango una variable con la propiedad Cells. El siguiente ejemplo rellena las primeras 20 celdas de la tercera columna con valores entre 5 y 100, en incrementos de 5. La variable contador se utiliza como índice de fila para la propiedad Cells.
Sub BucleAtravés()
Dim contador As Integer
For contador = 1 To 20
Worksheets("Hoja1").Cells(contador, 3).Value = contador * 5
Next contador
End Sub
- Si establece una variable de objeto para un objeto Range, puede manipular fácilmente el rango utilizando el nombre de la variable.
El siguiente procedimiento crea la variable de objeto miRango y, a continuación, asigna la variable al rango A1:D5 de la Hoja1 del libro activo. Las instrucciones posteriores modifican las propiedades del rango, sustituyendo el nombre de la variable por el objeto del rango.
Sub Aleatorio()
Dim miRango As Range
Set miRango = Worksheets("Hoja1").Range("A1:D5")
miRango.Formula = "=ALEATORIO()"
miRango.Font.Bold = True
End Sub
- Utilice la función Array para identificar un grupo de hojas. El siguiente ejemplo selecciona tres hojas del libro activo.
Sub Varias()
Worksheets(Array("Hoja1", "Hoja2","Hoja4")).Select
End Sub
- Utilice la propiedad Rows o Columns para trabajar con filas o columnas enteras. Estas propiedades devuelven un objeto Range que representa un rango de celdas. En el siguiente ejemplo, Rows(1) devuelve la fila uno de la Hoja1. A continuación, la propiedad Bold del objeto Font del rango se establece en True.
Sub FilaNegrita()
Worksheets("Hoja1").Rows(1).Font.Bold = True
End Sub
- La siguiente tabla muestra algunas referencias de fila y columna, utilizando las propiedades Rows y Columns.
Referencia Significado
Rows(1) Fila uno
Rows Todas las filas de la hoja de cálculo
Columns(1) Columna uno
Columns("A") Columna uno
Columns Todas las columnas de la hoja de cálculo
- Para trabajar con varias filas o columnas al mismo tiempo, cree una variable de objeto y utilice el método Union, combinando varias llamadas a la propiedad Rows o Columns. El siguiente ejemplo cambia a negrita el formato de las filas uno, tres y cinco de la hoja de cálculo uno del libro activo.
Sub VariasFilas()
Worksheets("Hoja1").Activate
Dim miUnión As Range
Set miUnión = Union(Rows(1), Rows(3), Rows(5))
miUnión.Font.Bold = True
End Sub
Bueno, creo que con eso hay para entretenerse durante un tiempo, espero que practiquen y que les sirva, ¡Suerte!
Entrega 1 - Entrega 2 - Entrega 3 - Entrega 4 - Entrega 5 - Entrega 6 - Entrega 7 - Entrega 8 - Entrega 9
Descargar