Lo que debes saber al trabajar con hojas en VBA

Sergio 12/04/2021
hojas en vba Excel

En VBA existen algunos objetos con los cuales trabajarás más que con ningún otro: Celdas y rangos, hojas y libros. Solo has de observar tu trabajo diario para comprobarlo. Constantemente estás introduciendo información en celdas y organizándola en hojas y libros.

En este tutorial me enfocaré en las hojas, otra área que debes conocer para utilizar VBA de manera eficiente. Abordaré las propiedades y los métodos más utilizados y pondré algunos ejemplos prácticos.

Al igual que cualquier otro objeto en VBA, las hojas tienen unas propiedades personalizadas y unos métodos asociados. Las propiedades y los métodos te servirán para automatizar las hojas enVBA.

¿Estás listo/a?

Diferencia entre Worksheets y Sheets

En VBA existen dos colecciones que muchas veces suelen confundirse entre ellas: Worksheets y Sheets. Un libro de Excel puede tener hojas de trabajo (las que tienen celdas) y hojas de gráfico, entre otras. En la siente imagen se muestra un libro que contiene ambos tipos de hoja:

Varias hojas de Excel
  • La colección Worksheets se refiere a la colección de objetos de ‘hojas con celdas’ de un libro. En el ejemplo de la imagen, la colección tiene tres objetos.
  • La colección Sheets hace referencia a todas las hojas del libro, tanto hojas de trabajo como hojas de gráfico, de macro y de formulario (son otros tipos de hoja apenas utilizados).

Esto significa que si estás trabajando en un libro que tiene tres hojas de trabajo (sin hojas de gráfico), las colecciones Worksheets y Sheets serían la misma.

Sin embargo, si el libro tiene una o más hojas de gráfico, la colección Sheets será mayor que la colección Worksheets.

Ahora que conoces la diferencia, te recomiendo ser lo más específico/a posible al escribir código. Es decir, si tienes que hacer referencia únicamente a hojas de trabajo, utiliza la colección Worksheets y si tienes que referirte a todas las hojas del libro (incluyendo los otros tipos de hoja), hazlo con la colección Sheets.

Como las hojas de trabajo se usan con más frecuencia, a lo largo del tutorial utilizará solo la colección Worksheets.

Cómo hacer referencia a una hoja enVBA

Existen muchas formas de hacer referencia a una hoja en VBA. Entenderlas te ayudará a escribir un mejor código, especialmente cuando estés usando estructuras de bucle.

Con el nombre de la hoja

La forma más fácil de hacer referencia a una hoja es desde su nombre.

Por ejemplo, si tienes un libro con tres hojas (Hoja1, Hoja2 y Hoja3) y quieres activar la Hoja2, puedes utilizar el siguiente código:

Sub ActivarHoja()
    Worksheets("Hoja2").Activate
End Sub

El código anterior hace referencia a la Hoja2 y la activa. Al utilizar el nombre exacto de la hoja, también podrías usar la colección Sheets. Por tanto, el siguiente código tendría el mismo efecto que el anterior:

Sub ActivarHoja()
    Sheets("Hoja2").Activate
End Sub

Con el número de índice

Aunque el método anterior es la forma más sencilla de hacer referencia a una hoja, a veces es posible que no sepas el nombre exacto al cual referirte.

También es posible que quieras activar la segunda hoja del libro, sea cual sea su nombre

Por ejemplo, si en el ejemplo de la imagen anterior quieres hacer referencia a la segunda hoja, utiliza el siguiente procedimiento:

Sub ActivarHoja()
    Worksheets(2).Activate
End Sub

Al usar el número de índice le estás indicando a VBA que debe activar el objeto número 2 de la colección.

¿y qué ocurre cuando en este ejemplo introduces el 3 como número de índice?

La hoja activada sería la Hoja3. ¿Por qué se activa la Hoja3 si es el cuarto objeto? Sencillamente porque una hoja de gráfico no forma parte de la colección Worksheets.

Por el contrario, si usas la colección Sheets, el tercer objeto es claramente la hoja de gráfico.

Este método de usar números de índice es útil cuando quieres hacer un bucle únicamente por las hojas de trabajo (por ejemplo para introducir un valor en una celda de todas las hojas).

El número de índice siempre va de izquierda a derecha. Si en el ejemplo de la imagen anterior desplazas la Hoja2 a la izquierda de la Hoja1, cuando haces referencia al índice 2, estás haciendo referencia a Hoja1, pues se encuentra en segundo lugar.

Con el nombre de código

Uno de los inconvenientes que tiene usar alguno de los dos métodos anteriores es que una persona (incluso tú mismo/a) podría cambiar el nombre de la hoja o su posición. Y el resultado no puede ser peor: la macro deja de funcionar.

Para solucionar este problema, puedes utilizar el nombre de código VBA en lugar del nombre asignado en la pestaña. El nombre de código VBA se puede asignar en el editor de VBA y no cambia cuando se cambia el nombre de la hoja.

Para asignar un nombre de código a una hoja sigue estos pasos:

  1. Haz clic en la ficha Programador y, a continuación, en el botón Visual Basic.
Botón Visual Basic Excel
  1. Haz doble clic en el objeto de hoja que quieras cambiar de nombre.
  2. En la ventana Propiedades, modifica el nombre de la propiedad (Name), la que está entre paréntesis. Ten en cuenta que no puedes utilizar espacios.
Propiedad Name VBA

Los pasos anteriores cambian el nombre de la hoja para VBA. A partir de ahora podrás hacer referencia tanto al nombre de la hoja (el de la pestaña) como al nombre de código.

En el ejemplo de la imagen anterior he cambiado el nombre de código de la Hoja2 por ‘NombreCodigo’. Por tanto, desde ahora puedes utilizar cualquiera de las siguientes instrucciones para hacer referencia a la misma hoja:

Worksheets("Hoja2").Activate
NombreCodigo.Activate

La diferencia entre estas instrucciones es que, si alguien cambia el nombre de la hoja, la primera no funcionará.

Supongo que no hará falta que diga cuál de ellas prefiero…

Cuando se encuentra en otro libro

Para hacer referencia a una hoja de trabajo en un libro diferente, dicho libro debe encontrarse abierto mientras se ejecuta el código. Además de la hoja debes especificar la ruta al libro.

Imagina que tienen un libro llamado ‘Ejemplos’ y quieres activar la Hoja1. En el libro donde te encuentres en ese momento debes escribir el siguiente procedimiento:

Sub ActivarHoja()
    Workbooks("Ejemplos.xlsx").Worksheets("Hoja1").Activate
End Sub

Añadir una hoja al libro

Observa el siguiente código. Sirve para agregar una hoja de trabajo a la izquierda de la hoja activa:

Sub AgregaHoja()
    Worksheets.Add
End Sub

Si el libro en el cual se ejecuta el código anterior contiene una única hoja llamada Hoja1, la nueva hoja toma el nombre Hoja2 de forma predeterminada.

Si quieres que una hoja se añada antes de una hoja en concreto (por ejemplo, delante de Hoja2), puedes usar el siguiente código:

Sub AgregaHoja()
    Worksheets.Add Before:=Worksheets("Hoja2")
End Sub

De igual forma, si quieres que la hoja se añada a la derecha de una hoja (por ejemplo, Hoja2), usa este procedimiento:

Sub AgregaHoja()
    Worksheets.Add After:=Worksheets("Hoja2")
End Sub

Y si lo que buscas es que la hoja se agregue al final de todas las hojas, necesitas saber primero cuántas hojas tiene el libro.

El siguiente procedimiento cuenta el número de hojas y añade la hoja nueva después de la última hoja (a la que se hace referencia usando el número de índice):

Sub AgregarHoja()
    Dim CuentaHojas As Integer
    CuentaHojas = Worksheets.Count
    Worksheets.Add After:=Worksheets(CuentaHojas)
End Sub

Eliminar una hoja del libro

El siguiente procedimiento elimina del libro la hoja activa:

Sub EliminaHoja()
    ActiveSheet.Delete
End Sub

Al introducir el código anterior, se mostrará un cuadro de advertencia indicando que se eliminará la hoja.

Sé que muchas veces es molesto tener que ocultar este cuadro, por lo cual, también te propongo el siguiente código:

Sub EliminaHoja()
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

Cuando la propiedad DisplayAlerts se establece en False, nos te mostrará el mensaje de advertencia. Si lo usas te recomiendo volver a ponerlo en True al final del código.

Recuerda que la acción de eliminar una hoja no se puede deshacer, por tanto usa el código anterior solo cuando estés seguro/a. Para eliminar una hoja específica (por ejemplo Hoja2) puedes hacerlo usando el siguiente procedimiento:

Sub EliminaHoja()
    Worksheets("Hoja2").Delete
End Sub

O también puedes hacerlo desde el nombre de código de VBA:

Sub EliminaHoja()
    NombreCodigo.Delete
End Sub

Cómo renombrar una hoja en VBA

Para cambiar el nombre de una o varias hojas en VBA debes hacerlo modificando la propiedad Name (la que se muestra sin paréntesis en la ventana Propiedades). El siguiente código renombra la Hoja1 con ‘Índice’.

Sub RenombraHoja()
    Worksheets("Hoja1").Name = "Índice"
End Sub

Puedes combinar este procedimiento con el método Add de la sección anterior para añadir una hoja e inmediatamente renombrarla.

Por ejemplo, si quieres insertar cuatro hojas con los nombres ‘Trimestre 1’, ‘Trimestre 2’, ‘Trimestre 3’ y ‘Trimestre 4’, puedes utilizar el siguiente código:

Sub AgregaRenombraHoja()
    Dim CuentaHojas As Integer
    CuentaHojas = Worksheets.Count
    For i = 1 To 4
        Worksheets.Add after:=Worksheets(CuentaHojas + i - 1)
        Worksheets(CuentaHojas + i).Name = "Trimestre " & i
    Next i
End Sub

En el código anterior, primero se cuenta el número de hojas y luego se usa el bucle For-Next para agregar hojas nuevas al final. Según se van agregando, el código también las renombra.

Asignar un objeto Worksheets a una variable

Cuando se trabaja con hojas en VBA, se puede asignar una hoja de trabajo a una variable de objeto y luego utilizar dicha variable en lugar de la referencia a la hoja.

Por ejemplo, si quieres añadir el año tras el nombre de las hojas, en lugar de contar las hojas y ejecutar el bucle ese número de veces, puedes utilizar una variable de objeto.

En el siguiente código, se agrega el año ‘2021’ a todos los nombres de hojas del libro:

Sub RenombraHojas()
    Dim Ws As Worksheet
    For Each Ws In Worksheets
        Ws.Name = Ws.Name & " 2021"
    Next Ws
End Sub

Este código declara la variable Ws como tipo hoja (en la línea Dim Ws As Worksheet)

Aquí no necesitas contar el número de hojas para hacer un bucle por todas ellas. En su lugar puedes utilizar el bucle For Each-Next. Esto te permite recorrer todas las hojas de la colección Worksheets. Da igual si el libro tiene dos hojas o 50 hojas.

Aunque el código anterior permite recorrer todas las hojas del libro también es posible asignar una hoja específica a una variable de objeto:

Sub RenombrarHoja()
    Dim Ws As Worksheet
    Set Ws = Worksheets("Hoja2")
    Ws.Name = "Índice"
    Ws.Protect
End Sub

Una vez que has asignada la hoja a la variable de objeto (con la instrucción Set), puedes utilizar ese objeto en lugar de la referencia de la hoja. Esto es muy útil cuando tienes un código largo y quieres cambiar la referencia. En lugar de hacer el cambio en todas partes, puedes simplemente hacer el cambio en la instrucción Set.

Observa que el código anterior declara la variable Ws como de tipo Worksheet.

Dos formas de ocultar hojas enVBA

Ocultar y mostrar hojas en Excel es una tarea sencilla.

Puedes ocultar una hoja para que no se muestre al abrir el libro. Sin embargo cualquiera puede mostrar las hojas ocultas haciendo clic con el botón derecho del ratón en cualquier pestaña de hoja y seleccionando la opción Mostrar.

¿Pero qué ocurre si quieres que las hojas no se puedan mostrar?

La única forma de hacerlo es con VBA.

El siguiente código oculta todas las hojas de trabajo del libro excepto la hoja activa, de forma que no se puedan mostrar haciendo clic con el botón derecho del ratón en la pestaña de la hoja:

Sub OcultarExceptoActiva()
    Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden
    Next Ws
End Sub

En el código anterior la propiedad Ws.Visible se cambia a xlSheetVeryHidden.

  • Cuando la propiedad Visible se establece como xlSheetVisible, la hoja es visible en el libro de Excel (se muestra la pestaña de la hoja en la parte inferior de la ventana de Excel).
  • Si la propiedad Visible se establece como xlSheetHidden, la hoja se oculta pero el usuario puede mostrarla haciendo clic con el botón derecho en la pestaña de la hoja.
  • Si la propiedad Visible se establece como xlSheetVeryHidden, la hoja está oculta y no puede ser mostrada desde la ventana de Excel. Es necesario utilizar un código VBA o la ventana Propiedades para hacerlo.

Si simplemente quieres ocultar la hojas pero que estas se puedan mostrar desde la ventana de Excel, introduce el siguiente procedimiento:

Sub OcultarExceptoActiva()
    Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> ActiveSheet.Name Then Ws.Visible = xlSheetHidden
    Next Ws
End Sub

Para volver a mostrar todas las hojas, utiliza este código:

Sub MostrarTodasHojas()
    Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Visible = xlSheetVisible
    Next Ws
End Sub

Cómo ocultar hojas según su nombre

Supongamos que tienes varias hojas con datos de los últimos años. Si deseas ocultar todas las hojas cuyo nombre no corresponda al año 2021, utiliza el siguiente código.

Este código utiliza la función Instr de VBA.

Sub OcultarDiferentes2021()
    Dim Ws As Worksheet
    For Each Ws In Worksheets
        If InStr(1, Ws.Name, "2021", vbBinaryCompare) = 0 Then
            Ws.Visible = xlSheetHidden
        End If
    Next Ws
End Sub

La función Instr devuelve la posición del carácter donde se encuentra la cadena de texto coincidente (2021). Si no encuentra esta cadena devuelve 0.

El procedimiento comprueba si el nombre de la hoja contiene el texto ‘2021’. Si lo tiene no pasa nada pero si no lo encuentra, la hoja se oculta.

Podrías transformar esta macro haciendo que busque el texto en una celda y usando su referencia en el código. Esto te permitirá introducir un texto en una celda y cuando ejecutes la macro, todas las hojas excepto la que tiene el texto de la celda se pueden ocultar (o al revés, permanecer visibles y el resto ocultarse).

Ordenar las hojas del libro alfabéticamente

Existen varios métodos para ordenar las hojas en VBA de un libro de forma alfabética. Este es uno de ellos. ¿Para qué querrías hacer esto?

Por ejemplo, si tienes un libro con las ventas de varios años y el nombre de las pestañas son los años, puedes usar el siguiente código para ordenar rápidamente las hojas en VBA de forma ascendente:

Sub OrdenarHojas()
    Application.ScreenUpdating = False
    Dim ShCuenta As Integer, i As Integer, j As Integer
    ShCuenta = Sheets.Count
    For i = 1 To ShCuenta - 1
        For j = i + 1 To ShCuenta
            If Sheets(j).Name < Sheets(i).Name Then
                Sheets(j).Move before:=Sheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

Debes tener en cuenta que el código anterior funciona correctamente con nombres compuestos por textos, números o una combinación de ambos. Cuando los nombres de las hojas contienen puntos, comas u otros caracteres de este tipo, los resultados puede que no sean los que esperas. Atento/a a esto.

Proteger/desproteger todas las hojas a la vez

Estoy seguro de que alguna vez has querido proteger o desproteger todas las hojas de un libro a la vez, ¿me equivoco? Cuando tienes un archivo con una o dos hojas lo puedes hacer fácilmente de forma manual o haciendo referencia al nombre de la hoja. Pero cuando tienes más hojas… sería una enorme pérdida de tiempo hacerlo de forma manual. Afortunadamente puedes hacerlo con VBA:

Sub ProtegerHojas()
    Dim ws As Worksheet
    Dim Pass As String
    Pass = "abc123" 'Sustituye 'abc123' por tu propia contraseña
    For Each ws In Worksheets
        ws.Protect Pass:=Pass
    Next ws
End Sub

Cómo crear una tabla de contenidos del libro

Si tienes un gran número de hojas en el libro y quieres crear rápidamente una hoja que resuma los nombres de todas ellas y que, además, contenga enlaces a cada una de ellas, puedes utilizar el siguiente procedimiento:

Sub CreaIndice()
    Worksheets.Add
    ActiveSheet.Name = "Índice"
    For i = 2 To Worksheets.Count
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i - 1, 1), _
        Address:="", SubAddress:=Worksheets(i).Name & "!A1", _
        TextToDisplay:=Worksheets(i).Name
    Next i
End Sub

El código anterior comienza agregando una nueva hoja y le asigna el nombre ‘Índice’.

A continuación, recorre todas las hojas del libro y crea un hipervínculo para cada una de ellas.

Resumen

Abarcar todas las operaciones que se pueden realizar con hojas en VBA sería larguísimo… En este artículo te he mostrado una tarea imprescindible en VBA: hacer referencia a una hoja. También te he enseñado algunas operaciones básicas como agregar, eliminar o cambiar el nombre a una hoja y he finalizado con algunas muy habituales al trabajar con VBA.

Categorías: Macros y VBA