Algunos trucos 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!

LECCION 9

tombrad@webhost.cl

http://fly.to/arica