Cómo generar una lista de nombres de hojas en Excel usando VBA

Ya sea que tu libro de Microsoft Excel tenga tres hojas o 50, saber qué tienes es importante. Algunas aplicaciones complejas requieren una documentación detallada que incluya los nombres de las hojas. Para aquellos momentos en los que una simple mirada no es suficiente, y necesitas tener una lista, tienes un par de opciones: puedes hacerlo de forma manual, lo cual sería tedioso y requeriría actualización cada vez que realices un cambio. O puedes ejecutar un procedimiento de VBA rápido. En este artículo, te mostraré un código fácilmente modificado que genera una lista de nombres de hojas y enlaces. Hay otras formas de generar una lista, pero prefiero el método de VBA porque es automatizado y fácil de modificar según los requisitos individuales.

Cómo ingresar y ejecutar código en VBA

Si eres nuevo en el código de VBA, es posible que te preguntes acerca de los términos procedimiento y macro. Los verás usar de forma intercambiable cuando se usa VBA como lenguaje. Esto no es cierto para todos los lenguajes. En VBA, tanto un procedimiento como una macro son un conjunto de instrucciones con nombre que se ejecutan cuando son llamados. Algunos desarrolladores se refieren a un procedimiento sub como una macro y a un procedimiento de función como un procedimiento porque un procedimiento puede aceptar argumentos. Muchos utilizan el término macro para todo. Algunos, como yo, tienden a utilizar el término procedimiento para todo. Además, Access tiene una característica de macro que es independiente de cualquier código VBA. No te preocupes demasiado por los términos.

Para ingresar código VBA, presiona Alt + F11 para abrir el Editor de Visual Basic. En el Explorador de proyectos a la izquierda, elige ThisWorkbook e ingresa el código. Si estás usando una versión de la cinta de opciones, debes guardar el archivo como un archivo habilitado para macros para poder usar macros. También puedes importar el archivo .cls descargable que contiene el código. O puedes trabajar con cualquiera de los libros de Excel descargables. Si vas a ingresar el código tú mismo, por favor no lo copies desde esta página web. En su lugar, ingrésalo manualmente o cópialo en un editor de texto y luego pega ese código en un módulo.

Cuando estés en VBE, presiona F5 para ejecutar un procedimiento, pero asegúrate de hacer clic dentro del procedimiento que deseas ejecutar. Cuando estés en una hoja de Excel, haz clic en la pestaña Desarrollador, haz clic en Macros en el grupo Código, elige el procedimiento en el cuadro de diálogo resultante que se muestra en Figura A y luego haz clic en Ejecutar.

Figura A

El código VBA básico

Generar una simple lista de nombres de hojas es fácil usando VBA gracias a la colección Worksheets. Listado A muestra un simple bucle For Each que recorre esta colección. Para cada hoja, el código utiliza la propiedad Name para ingresar ese nombre en la columna A, comenzando en A1, en la hoja 1.

Sub ListarNombresHojas()
    'Listar todos los nombres de hojas en la columna A de la hoja 1.
    'Actualiza para cambiar la ubicación de la lista.
    
    Sheets("Hoja1").Activate
    ActiveSheet.Cells(1, 1).Select

    'Generar una lista de enlaces a cada hoja en el libro en la hoja 1.
    For Each hoja In Worksheets
        ActiveCell = hoja.Name
        ActiveCell.Offset(1, 0).Select 'Moverse hacia abajo una fila.
    Next

End Sub

Cuando adaptes este código, es posible que desees cambiar la ubicación de la lista; hazlo cambiando las dos primeras líneas adecuadamente. El bucle For Each también ofrece posibilidades de modificación. Es posible que desees agregar nombres de encabezado o agregar valores para enumerar los nombres de las hojas.

Este código listará las hojas ocultas y muy ocultas, lo cual es posible que no desees. En ese caso, deberás verificar las propiedades Visibles xlSheetVisible y xlSheetVeryHidden. Además, debido a que estamos seleccionando activamente A1 en la hoja 1, el cursor se mueve a esa ubicación. Si no deseas que la celda activa cambie, utiliza declaraciones de selección implícitas. Para obtener más información sobre referencias implícitas y explícitas, lee Consejos de Excel: Cómo seleccionar celdas y rangos eficientemente usando VBA.

Existen muchas modificaciones que es posible que desees hacer. Por ejemplo, en lugar de una simple lista de texto, es posible que desees una lista de enlaces.

Cómo generar una lista de enlaces en Excel

No es infrecuente que un libro de trabajo complejo incluya una lista de enlaces a cada hoja en el libro. El procedimiento que utilizarás, mostrado en Listado B, es similar al Listado A, pero este código utiliza la propiedad Name para crear un enlace.

Sub ListarNombresHojasComoEnlaces()
    'Generar una lista de enlaces a cada hoja en el libro en la hoja 1, A1.
    
    Sheets("Hoja1").Activate
    ActiveSheet.Cells(1, 1).Select

    'Generar una lista de enlaces a cada hoja en el libro en la hoja 1.
    For Each hoja In Worksheets
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
        Address:="", SubAddress:="'" & hoja.Name & "'!A1", _
        TextToDisplay:=hoja.Name
        ActiveCell.Offset(1, 0).Select 'Moverse hacia abajo una fila
    Next

End Sub

Las dos primeras líneas seleccionan la celda A1 en la hoja 1. Actualiza estas dos declaraciones para cambiar la ubicación de la lista. El bucle For Each recorre todas las hojas, utilizando la propiedad Name para crear un enlace para cada hoja.

La propiedad Hyperlinks.Add en el bucle For Each utiliza la forma

.Add Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay]

La información de los parámetros se muestra a continuación:

• Anchor: Un objeto Range o Shape.
• Address: La dirección del enlace.
• SubAddress: La subdirección del enlace.
• ScreenTip: Información que se muestra cuando el puntero del mouse se detiene sobre el enlace.
• TextToDisplay: El texto del enlace.

El argumento SubAddress de nuestro procedimiento

SubAddress:="'" & hoja.Name & "'!A1"

construye una cadena utilizando el nombre de la hoja actual y la referencia de celda A1. Por ejemplo, si la hoja actual es Hoja1, esto se evalúa como 'Hoja1'!A1. Posteriormente, cuando haces clic en este enlace, te lleva a la celda A1 en Hoja1. Como antes, puedes modificar fácilmente este procedimiento para reflejar cómo deseas usar esta lista.

Ambos procedimientos son fáciles de usar. Ambos se pueden modificar fácilmente para cambiar la posición de la lista o para agregar más información a la lista simple.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo generar una lista de nombres de hojas en Excel usando VBA , tenemos lo ultimo en tecnología 2023.