Objetos VBA Excel

En VBA trabajamos con entidades llamadas objetos que representan cada uno de los elementos de los que está compuesto un libro de trabajo, como puede ser el libro, la hoja, la celda o el rango.

01-Oct-2016

En VBA trabajamos con entidades llamadas objetos que representan cada uno de los elementos de los que está compuesto un libro de trabajo, como puede ser el libro, la hoja, la celda o el rango.

Sobre estos objetos se ejecutan métodos (instrucciones) para realizar operaciones con ellos como puede ser insertar, eliminar, seleccionar, copiar, etc. y se modifican sus propiedades para cambiar sus características como pueden ser su contenido o su formato.

Objetos más importantes de trabajo en VBA Excel:

Elemento Colecciones Objeto Objeto Activo
Aplicación   Application  
Libro WorkBooks WorkBook ActiveWorkBook
Hoja WorkSheets o Sheets Sheet o WorkSheet ActiveWorkSheet
Celda Cells   ActiveCell
Rango   Range  
Selección   Selection  

Los objetos de niveles superiores contienen o están formados por colecciones de elementos de los niveles inferiores, es decir, un objeto de tipo WorkBook que representa el libro de hojas de cálculo, está formado por una colección de objetos WorkSheets o Sheets  que son las hojas del libro y a su vez cada objeto WorkSheet de esta colección, está formado por una colección de objetos Cells o Range que representan las celdas o rangos de la hoja.

Jerarquia de objetos en Excel

Siempre que hagamos referencia a un objeto sin especificar el objeto de su nivel superior, se asume que se está trabajando con el grupo de objetos activos (ActiveWorkBook, ActiveWorkSheet, ActiveCell).

Por ejemplo si escribimos una instrucción que modifica el valor de una celda sin especificar la hoja que lo contiene, Excel tomará como elementos del nivel superior (ActiveWorkSheet y ActiveWorkBook)  la hoja y el libro activo.

Programa Excel

Application

El objeto Application, es el objeto de mayor nivel dentro de la jerarquía de objetos VBA de Excel y representa la instancia de Excel actual.

Este objeto contiene los valores y opciones de toda la aplicación y todos los métodos necesarios para obtener objetos de nivel superior como Libros, Hojas y Celdas.

Por defecto no se suele especificar al hacer referencia a los objetos de trabajo, pero si lo utilizamos para manipular las opciones y comandos de Excel.

  • Ejecutar la orden Abrir de la ficha Archivo (método FindFile)
Application.FindFile
  • Recalcular todas las fórmulas de los libros abiertos (método Calculate)
Application.Calculate
  • Activar/Desactivar las actualizaciones de pantalla (propiedad ScreenUpdating)
Application.ScreenUpdating = true | false

Activar/Desactivar los mensajes de alerta que se puedan producir durante la ejecución de la macro (propiedad DisplayAlerts)

 Application.DisplayAlerts = true | false
  • Cerrar el programa Excel (método Quit)
Application.Quit
  • Establecer una pausa en la ejecución de una macro (paraliza todos los procesos excepto la impresión y actualizaciones en segundo plano) (método Wait)
Establece una pausa desde ahora hasta las 19:30
Application.Wait "19:30:00"
Establece una pausa durante 5 segundos aproximadamente
Application.Wait Hour(Now()) & “:” & Minute(Now()) & “:” & Second(Now()) + 5
  

Colección Libros

WorkBooks

La colección más importante de objetos que nos ofrece Excel es WorkBooks que representa todos los libros de trabajo actualmente abiertos en la sesión actual de trabajo.

Por medio de esta colección podemos crear o abrir libros de trabajo, o hacer referencia a algún elemento de cualquier libro abierto.

Colección libros
  • Crear un nuevo libro de trabajo: (método Add)
Workbooks.Add

Crea un nuevo libro y lo añade a la colección de libros abiertos.

  • Conocer el total de libros abiertos actualmente. (propiedad Count)
Workbooks.Count

Si tenemos 3 libros abiertos actualmente esta propiedad contendrá el valor 3.

  • Abrir un libro existente: (método Open)
Workbooks.Open Filename:="Z:\DocumentosTrabajo\DatosTrabajo.xlsx"

Esta instrucción abrirá el archivo ubicado en “Z:\DocumentosTrabajo\DatosTrabajo.xlsx” y lo agregará a la colección de objetos Workbooks. De esta manera podemos abrir tantos archivos como sean necesarios y para cada uno de ellos se creará un objeto Workbook el cual será almacenado dentro de Workbooks.

Filename -> indica donde y qué nombre tiene el libro que deseamos abrir.

Una vez abierto un libro, podremos hacer referencia a él por medio de su nombre si lo conocemos o del índice que ocupa dentro de la colección de libros abiertos.

  • Activar un libro para trabajar con su contenido: (método Activate)

Para el ejemplo anterior si deseamos activar el libro para trabajar sobre él escribiríamos la siguiente orden:

   WorkBooks("DatosTrabajo.xlsx").Activate
   O 
   WorkBooks(2).Activate “en el caso de qué el índice de la colección fuera 2”
  • Cerrar un libro abierto: (método Close)
   WorkBooks("DatosTrabajo.xlsx").Close
   O
   WorkBooks(2).Close “en el caso de qué el índice de la colección fuera 2”

ActiveWorkBook (Libro Actual)

Una vez que se ha activado un libro de la colección de libros abiertos, este se convierte en el libro activo y para hacer referencia se  utiliza el objeto ActiveWorkbook.

Objeto Libro activo

Por medio de este objeto podremos realizar cambios que afecten al libro de trabajo actual, como guardar, guardar como o cerrar.

  • Guardar el libro actual: (método Save)
ActiveWorkbook.Save
  • Guardar el libro actual como: (método SaveAs)
ActiveWorkbook.SaveAs Filename:="W:\Documentos\Libro1.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Filename -> indica donde y con qué nombre tenemos que guardar el libro. La extensión del archivo no es necesario indicarla, ya que Excel se la asigna en función del Formato de Fichero indicado en FileFormat.
FileFormat -> Indica el formato con el que se guardará. Algunos de los valores admitidos son:

  • xlOpenXMLWorkbook (Libro de Open XML xlsx versiones 2007/2013)
  • xlOpenXMLWorkbookMacroEnabled (Libro habilitado para macros xlsm)
  • xlWorkbookDefault (equivale a xlOpenXMLWorkbook)
  • xlExcel8 (Libro de Excel 97/2003)
  • xlText (Archivo de texto txt)

CreateBackup -> Indica si se ha de realizar una copia del libro original en el caso de que ya exista.

  • Cerrar el libro actual: (método Close)
ActiveWorkbook.Close

Colección Hojas

WorkSheets

Para hacer referencia a una hoja de cálculo del libro abierto, se utiliza la colección WorkSheets que representa las hojas de cálculo actuales del libro. Opcionalmente también podemos trabajar con las hojas del libro con la colección Sheets.

Colección Hojas

WorkSheets(IndiceHoja) -> Hacer referencia a una hoja por su índice, siendo la 1ª hoja del libro el índice 1, la segunda hoja el índice 2 y así sucesivamente.

WorkSheets(“NombreHoja”) -> Hacer referencia a una hoja por su nombre.

  • Teniendo un libro de 3 hojas, donde tenemos una hoja de nombre ‘Listado’. Crear un duplicado de la hoja ‘Listado’ moviendo la copia a continuación de la hoja 2: (método Copy)
WorkSheets("listado").Copy After:=Sheets(2)
  • Teniendo un libro de X hojas, donde tenemos una hoja de nombre ‘Listado’. Crear un duplicado de la hoja ‘Listado’ moviendo la copia a continuación de la última hoja:
WorkSheets("listado").Copy After:= ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
  • Eliminar la hoja de nombre ‘Listado’ del libro actual:  (método Delete)
Application.DisplayAlerts = False  
WorkSheets("listado").Delete
Application.DisplayAlerts = True

Para evitar que se muestren mensajes de aviso pidiendo confirmación a la hora de realizar la eliminación de la hoja, desactivamos la propiedad DisplayAlerts del objeto Application, para volverla a activar después de la eliminación.

  • Activar una hoja de cálculo de nombre ‘Listado’ del libro actual: (método Activate)
WorkSheets("listado").Activate

Colección Celdas

Cells

Para hacer referencia a una celda de la hoja, se utiliza la colección Cells que contiene todas las celdas de la hoja de cálculo, indicando el número de fila y columna en la que se localiza. Si omitimos estos valores, entonces estaremos haciendo referencia a todas las celdas de la hoja.

Colección Celdas
  • Hacer referencia a todas las celdas de la hoja.
Cells.Select  (Seleccionar todas las celdas de la hoja activa)
  • Hacer referencia  a una celda concreta de la hoja.
Cells(NFila, NColumna)
  • Establecer el valor de la celda C2 de la hoja activa.
Cells(2,3).Value=”Curso Macros Excel

ActiveCell (Celda Activa)

Para hacer referencia a la celda activa se utiliza el objeto ActiveCell.

El término ActiveCell sirve para referirse a la celda actualmente seleccionada en una hoja de cálculo. Siempre que se haga click en cualquier celda, esta automáticamente se convertirá en la ActiveCell. Una vez que la celda es seleccionada se pueden introducir funciones o valores a la celda o modificar el formato de los datos contenidos en ella.

  • Seleccionar la hoja ‘Listados’ del libro, marcar la celda B5 para convertirla en celda activa, introducir la fórmula A3*10% y a continuación establecer su formato a negrita y cursiva. Adicionalmente desactivamos la actualización de pantalla mientras realizamos operaciones sobre la hoja.
Application.ScreenUpdating = False 
Sheets("Listados").Activate
Cells(5,2).Select
ActiveCell.Formula = "=A3*10%"
ActiveCell.Font.Bold = True
ActiveCell.Font.Italic = True
Application.ScreenUpdating = True


Application.ScreenUpdating = False
Sheets("Listados").Activate
Range("B5").Select
With ActiveCell
   .Formula = "=A3*10%"
   .Font.Bold = True
   .Font.Italic = True
End With
Application.ScreenUpdating = True

Cuando ejecutamos métodos o realizamos cambios en las propiedades de un objeto de forma continua, podremos omitir el nombre del objeto en cada línea encerrando las instrucciones entre el bloque With NombreObjeto y End With de tal manera que nos ahorramos ir escribiendo en cada nueva línea el nombre del objeto.
Si deseamos desactivar las molestas actualizaciones de pantalla que se producen a medida que la macro va ejecutando las instrucciones, estableceremos al valor false la propiedad ScreenUpdating del objeto Application.

Trabajar con las colecciones

Ejemplos

Cuando tenemos que realizar algún proceso con todos y cada uno de los elementos de una colección (WorkBooks, WorkSheets, Cells), necesitamos ir recorriendo la colección elemento tras elemento. Para realizar estas iteraciones VBA dispone del bucle For Each pensado para ir procesando cada uno de los elementos de una colección.

Veamos algunos ejemplos.

  • Deseamos cerrar todos los libros abiertos, excepto el que tiene de nombre ‘Resumen’, guardando los cambios realizados en cada libro.

For Each Libro In Workbooks
   If (Libro.name <> “Resumen”) Then
       Libro.Save
       Libro.Close
   End If 
Next

For Each Libro In Workbooks
   With Libro 
   If (.name <> “Resumen”) Then
      .Save
      .Close
   End If 
   End Width
Next
  • Establecer el ancho y alto de celda a 20 de todas las celdas de todas las hojas del libro actual.
Application.ScreenUpdating = False 
For Each Hoja In WorkSheets
  Hoja.Select
  Cells.Select
  Selection.ColumnWidth = 20
  Selection.RowHeight = 20
  Range(“A1”).Select
Next
Application.ScreenUpdating = True
  • Comprobar si existe una hoja de cálculo en el libro actual.
' Declaramos algunas variables de trabajo
Dim Nombre as String
Dim Encontrada as Boolean
  
Nombre = ”CálculosFor Each Hoja In WorkSheets
  If(Hoja.Name = Nombre) then
     Encontrada=true
     Exit for
  End if
Next
  
If(Encontrada) then
  MsgBoxLa hoja: “ & Nombre & “ se encuentra en el libro actual.Else
  MsgBox La hoja: “ & Nombre & “ NO se encuentra en el libro actual.End If