Trabajar con hojas de trabajo usando Excel VBA (explicado con ejemplos)

Además de las celdas y los rangos, trabajar con hojas de trabajo es otra área que debe conocer para usar VBA de manera eficiente en Excel.

Al igual que cualquier objeto en VBA, las hojas de trabajo tienen diferentes propiedades y métodos asociados que puede usar mientras automatiza su trabajo con VBA en Excel.

En este tutorial, cubriré "Hojas de trabajo" en detalle y también le mostraré algunos ejemplos prácticos.

Entonces empecemos.

Todos los códigos que menciono en este tutorial deben colocarse en el Editor de VB. Vaya a la sección "Dónde colocar el código VBA" para saber cómo funciona.

Si está interesado en aprender VBA de forma sencilla, consulte mi Capacitación en línea de Excel VBA.

Diferencia entre hojas de trabajo y hojas en VBA

En VBA, tiene dos colecciones que a veces pueden ser un poco confusas.

En un libro de trabajo, puede tener hojas de trabajo y hojas de gráficos. El siguiente ejemplo tiene tres hojas de trabajo y una hoja de gráfico.

En Excel VBA:

• La colección "Hojas de trabajo" se referiría a la colección de todos los objetos de la hoja de trabajo en un libro de trabajo. En el ejemplo anterior, la colección de hojas de trabajo constaría de tres hojas de trabajo.
• La colección "Hojas" se referiría a todas las hojas de trabajo, así como a las hojas de gráficos del libro de trabajo. En el ejemplo anterior, tendría cuatro elementos: 3 hojas de trabajo + 1 hoja de gráfico.

Si tiene un libro de trabajo que solo tiene hojas de trabajo y no hojas de gráficos, entonces la colección de "Hojas de trabajo" y "Hojas de cálculo" es la misma.

Pero cuando tiene una o más hojas de gráficos, la colección de "Hojas de cálculo" sería más grande que la colección de "Hojas de trabajo"

Hojas = Hojas de trabajo + Hojas de gráficos

Ahora, con esta distinción, recomiendo ser lo más específico posible al escribir un código VBA.

Por lo tanto, si tiene que consultar únicamente las hojas de trabajo, utilice la colección "Hojas de trabajo" y, si tiene que consultar todas las hojas (incluidas las hojas de gráficos), utilice la colección "Hojas de trabajo".

En este tutorial, solo usaré la colección "Hojas de trabajo".

Hacer referencia a una hoja de trabajo en VBA

Hay muchas formas diferentes que puede utilizar para hacer referencia a una hoja de trabajo en VBA.

Comprender cómo hacer referencia a las hojas de trabajo lo ayudaría a escribir un mejor código, especialmente cuando usa bucles en su código VBA.

Usando el nombre de la hoja de trabajo

La forma más sencilla de hacer referencia a una hoja de trabajo es utilizar su nombre.

Por ejemplo, suponga que tiene un libro de trabajo con tres hojas de trabajo: Hoja 1, Hoja 2, Hoja 3.

Y desea activar la Hoja 2.

Puede hacerlo usando el siguiente código: Sub ActivateSheet () Worksheets ("Sheet2"). Activar End Sub

El código anterior le pide a VBA que se refiera a Sheet2 en la colección Worksheets y lo active.

Dado que estamos usando el nombre exacto de la hoja, también puede usar la colección Sheets aquí. Entonces, el siguiente código también haría lo mismo.

Sub ActivateSheet () Sheets ("Sheet2"). Activar End Sub

Uso del número de índice

Si bien usar el nombre de la hoja es una manera fácil de hacer referencia a una hoja de trabajo, a veces, es posible que no sepa el nombre exacto de la hoja de trabajo.

Por ejemplo, si está usando un código VBA para agregar una nueva hoja de trabajo al libro de trabajo, y no sabe cuántas hojas de trabajo ya están allí, no sabría el nombre de la nueva hoja de trabajo.

En este caso, puede utilizar el número de índice de las hojas de trabajo.

Suponga que tiene las siguientes hojas en un libro de trabajo:

El siguiente código activaría Sheet2:

Sub ActivateSheet () Hojas de trabajo (2) .Activate End Sub

Tenga en cuenta que hemos utilizado el nmero de ndice 2 en Hojas de trabajo (2). Esto se referiría al segundo objeto de la colección de hojas de trabajo.

Ahora bien, ¿qué sucede cuando usa 3 como número de índice?

Seleccionará Sheet3.

Si se pregunta por qué seleccionó Sheet3, ya que claramente es el cuarto objeto.

Esto sucede porque una hoja de gráfico no forma parte de la colección de hojas de trabajo.

Entonces, cuando usamos los números de índice en la colección de Hojas de trabajo, solo se referirá a las hojas de trabajo en el libro de trabajo (e ignorará las hojas de gráficos).

Por el contrario, si usa Hojas de cálculo, Hojas de cálculo (1) se referirá a Hojas de cálculo1, Hojas de cálculo (2) se referirá a Hoja2, Hojas de cálculo (3) se referirá a Gráfico1 y Hojas de cálculo (4) se referirá a Hoja3.

Esta técnica de usar el número de índice es útil cuando desea recorrer todas las hojas de trabajo en un libro de trabajo. Puede contar el número de hojas de trabajo y luego recorrerlas usando este recuento (veremos cómo hacerlo más adelante en este tutorial).

Nota: El número de índice va de izquierda a derecha. Entonces, si desplaza Hoja2 a la izquierda de Hoja1, las Hojas de trabajo (1) se referirán a Hoja2.

Uso del nombre del código de la hoja de trabajo

Uno de los inconvenientes de usar el nombre de la hoja (como vimos en la sección anterior) es que un usuario puede cambiarlo.

Y si se ha cambiado el nombre de la hoja, su código no funcionará hasta que también cambie el nombre de la hoja de trabajo en el código VBA.

Para abordar este problema, puede usar el nombre en código de la hoja de trabajo (en lugar del nombre normal que hemos estado usando hasta ahora). Se puede asignar un nombre de código en el Editor de VB y no cambia cuando cambia el nombre de la hoja desde el área de la hoja de trabajo.

Para darle un nombre en código a su hoja de trabajo, siga los pasos a continuación:

1. Haga clic en la pestaña Desarrollador.
2. Haga clic en el botón de Visual Basic. Esto abrirá el VB Editor.
3. Haga clic en la opción Ver en el menú y haga clic en Ventana de proyecto. Esto hará visible el panel Propiedades. Si el panel Propiedades ya está visible, omita este paso.
4. Haga clic en el nombre de la hoja en el explorador del proyecto al que desea cambiarle el nombre.
5. En el panel Propiedades, cambie el nombre en el campo delante de (Nombre). Tenga en cuenta que no puede tener espacios en el nombre.

Los pasos anteriores cambiarían el nombre de su hoja de trabajo en el backend de VBA. En la vista de la hoja de cálculo de Excel, puede nombrar la hoja de trabajo como desee, pero en el backend, responderá a ambos nombres: el nombre de la hoja y el nombre del código.

En la imagen de arriba, el nombre de la hoja es "SheetName" y el nombre del código es "CodeName". Incluso si cambia el nombre de la hoja en la hoja de trabajo, el nombre del código sigue siendo el mismo.

Ahora, puede usar la colección de Hojas de trabajo para hacer referencia a la hoja de trabajo o usar el nombre en clave.

Por ejemplo, ambas líneas activarán la hoja de trabajo.

Hojas de trabajo ("Nombre de hoja"). Activar CodeName.Activar

La diferencia entre estos dos es que si cambia el nombre de la hoja de trabajo, el primero no funcionaría. Pero la segunda línea continuaría funcionando incluso con el nombre cambiado. La segunda línea (que usa CodeName) también es más corta y más fácil de usar.

Referencia a una hoja de trabajo en un libro de trabajo diferente

Si desea hacer referencia a una hoja de trabajo en un libro de trabajo diferente, ese libro de trabajo debe estar abierto mientras se ejecuta el código, y debe especificar el nombre del libro de trabajo y la hoja de trabajo a la que desea hacer referencia.

Por ejemplo, si tiene un libro de trabajo con el nombre Examples y desea activar Sheet1 en el libro de trabajo de ejemplo, debe usar el siguiente código:

Sub SheetActivate () Workbooks ("Examples.xlsx"). Worksheets ("Sheet1"). Activar End Sub

Tenga en cuenta que si el libro de trabajo se ha guardado, debe usar el nombre del archivo junto con la extensión. Si no está seguro de qué nombre usar, obtenga la ayuda del Explorador de proyectos.

En caso de que el libro de trabajo no se haya guardado, no es necesario que use la extensión de archivo.

Agregar una hoja de trabajo

El siguiente código agregaría una hoja de trabajo (como la primera hoja de trabajo, es decir, como la hoja más a la izquierda en la pestaña de la hoja).

Sub AddSheet () Worksheets.Añadir End Sub

Toma el nombre predeterminado Hoja2 (o cualquier otro número basado en la cantidad de hojas que ya existen).

Si desea que se agregue una hoja de trabajo antes de una hoja de trabajo específica (por ejemplo, Hoja2), puede usar el siguiente código.

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

El código anterior le dice a VBA que agregue una hoja y luego usa la declaración "Antes" para especificar la hoja de trabajo antes de la cual se debe insertar la nueva hoja de trabajo.

Del mismo modo, también puede agregar una hoja después de una hoja de trabajo (por ejemplo, Hoja2), usando el siguiente código:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

Si desea que la nueva hoja se agregue al final de las hojas, primero debe saber cuántas hojas hay. El siguiente código primero cuenta el número de hojas y luego agrega la nueva hoja después de la última hoja (a la que nos referimos usando el número de índice).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Eliminar una hoja de trabajo

El siguiente código eliminaría la hoja activa del libro de trabajo.

Sub DeleteSheet () ActiveSheet.Delete End Sub

El código anterior mostraría un mensaje de advertencia antes de eliminar la hoja de trabajo.

Si no desea ver el mensaje de advertencia, use el siguiente código:

Sub DeleteSheet () Application.DisplayAlerts = Falso ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Cuando Application.DisplayAlerts se establece en False, no le mostrará el mensaje de advertencia. Si lo usa, recuerde volver a establecerlo en True al final del código.

Recuerde que no puede deshacer esta eliminación, así que use el código anterior cuando esté absolutamente seguro.

Si desea eliminar una hoja específica, puede hacerlo usando el siguiente código:

Sub DeleteSheet () Hojas de trabajo ("Sheet2"). Eliminar End Sub

También puede utilizar el nombre en clave de la hoja para eliminarla.

Sub DeleteSheet () Sheet5.Eliminar End Sub

Cambiar el nombre de las hojas de trabajo

Puede modificar la propiedad de nombre de la hoja de trabajo para cambiar su nombre.

El siguiente código cambiará el nombre de Sheet1 a "Resumen".

Sub RenameSheet () Worksheets ("Sheet1"). Name = "Summary" End Sub

Puede combinar esto con el método de agregar hojas para tener un conjunto de hojas con nombres específicos.

Por ejemplo, si desea insertar cuatro hojas con el nombre 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 y 2021-2022 Q4, puede usar el siguiente código.

Sub RenameSheet () Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Add after: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i) .Name = "2018 Q" & i Next i End Sub

En el código anterior, primero contamos el número de hojas y luego usamos un bucle For Next para insertar nuevas hojas al final. A medida que se agrega la hoja, el código también la renombra.

Asignar un objeto de hoja de trabajo a una variable

Al trabajar con hojas de trabajo, puede asignar una hoja de trabajo a una variable de objeto y luego usar la variable en lugar de las referencias de la hoja de trabajo.

Por ejemplo, si desea agregar un prefijo de año a todas las hojas de trabajo, en lugar de contar las hojas y ejecutar el ciclo tantas veces, puede usar la variable de objeto.

Aquí está el código que agregará 2021-2022 como prefijo a todos los nombres de la hoja de trabajo.

Sub RenameSheet () Dim Ws como hoja de trabajo para cada W en las hojas de trabajo Ws.Name = "2018 -" & Ws.Name Siguiente Ws End Sub

El código anterior declara una variable Ws como el tipo de hoja de trabajo (usando la línea "Dim Ws As Worksheet").

Ahora, no necesitamos contar la cantidad de hojas para recorrerlas. En su lugar, podemos usar el ciclo "Para cada W en las hojas de trabajo". Esto nos permitirá revisar todas las hojas de la colección de hojas de trabajo. No importa si hay 2 hojas o 20 hojas.

Si bien el código anterior nos permite recorrer todas las hojas, también puede asignar una hoja específica a una variable.

En el siguiente código, asignamos la variable Ws a Sheet2 y la usamos para acceder a todas las propiedades de Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Una vez que establezca una referencia de hoja de trabajo a una variable de objeto (usando la instrucción SET), ese objeto se puede usar en lugar de la referencia de la hoja de trabajo. Esto puede resultar útil cuando tiene un código largo y complicado y desea cambiar la referencia. En lugar de realizar el cambio en todas partes, simplemente puede realizar el cambio en la instrucción SET.

Tenga en cuenta que el código declara el objeto Ws como la variable de tipo Hoja de trabajo (usando la línea Dim Ws como Hoja de trabajo).

Ocultar hojas de trabajo usando VBA (oculto + muy oculto)

Ocultar y mostrar hojas de trabajo en Excel es una tarea sencilla.

Puede ocultar una hoja de trabajo y el usuario no la verá cuando abra el libro. Sin embargo, pueden mostrar fácilmente la hoja de trabajo haciendo clic con el botón derecho en cualquier pestaña de la hoja.

Pero, ¿qué sucede si no desea que puedan mostrar la (s) hoja (s) de trabajo?

Puede hacer esto usando VBA.

El siguiente código ocultaría todas las hojas de trabajo en el libro de trabajo (excepto la hoja activa), de modo que no puede mostrarla haciendo clic con el botón derecho en el nombre de la hoja.

Sub HideAllExcetActiveSheet () Dim Ws As Worksheet para cada Ws en 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 en xlSheetVisible, la hoja es visible en el área de la hoja de trabajo (como pestañas de la hoja de trabajo).
• Cuando la propiedad Visible se establece en xlSheetHidden, la hoja está oculta pero el usuario puede mostrarla haciendo clic con el botón derecho en cualquier pestaña de la hoja.
• Cuando la propiedad Visible se establece en xlSheetVeryHidden, la hoja está oculta y no se puede mostrar del área de la hoja de trabajo. Debe usar un código VBA o la ventana de propiedades para mostrarlo.

Si simplemente desea ocultar hojas, que se pueden mostrar fácilmente, use el siguiente código:

Sub HideAllExceptActiveSheet () Dim Ws como hoja de trabajo para cada W en este libro de trabajo. Hojas de trabajo Si Ws.Name ActiveSheet.Name Entonces Ws.Visible = xlSheetHidden Siguiente Ws End Sub

El siguiente código mostraría todas las hojas de trabajo (tanto ocultas como muy ocultas).

Sub UnhideAllWoksheets () Dim Ws As Worksheet for each Ws in ThisWoksheets.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Artículo relacionado: Mostrar todas las hojas en Excel (de una vez)

Ocultar hojas según el texto que contiene

Suponga que tiene varias hojas con el nombre de diferentes departamentos o años y desea ocultar todas las hojas excepto las que tienen el año 2021-2022.

Puede hacer esto usando una función VBA INSTR.

El siguiente código ocultaría todas las hojas excepto las que tienen el texto 2021-2022 en él.

Sub HideWithMatchingText () Dim Ws como hoja de trabajo para cada Ws en hojas de trabajo Si InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Entonces Ws.Visible = xlSheetHidden End If Next Ws End Sub

En el código anterior, la función INSTR devuelve la posición del carácter donde encuentra la cadena coincidente. Si no encuentra la cadena coincidente, devuelve 0.

El código anterior verifica si el nombre tiene el texto 2021-2022 en él. Si lo hace, no pasa nada, de lo contrario, la hoja de trabajo está oculta.

Puede llevar esto un paso más allá si tiene el texto en una celda y usa esa celda en el código. Esto le permitirá tener un valor en la celda y luego, cuando ejecute la macro, todas las hojas, excepto la que tiene el texto coincidente, permanecerán visibles (junto con las hojas donde está ingresando el valor en el celda).

Clasificación de las hojas de trabajo en orden alfabético

Con VBA, puede ordenar rápidamente las hojas de trabajo según sus nombres.

Por ejemplo, si tiene un libro de trabajo que tiene hojas para diferentes departamentos o años, puede usar el siguiente código para ordenar rápidamente estas hojas en orden ascendente.

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount como entero, i como entero, j como entero ShCount = Sheets.Count Para i = 1 Para ShCount - 1 Para j = i + 1 Para ShCount Si Hojas (j) .Name < Sheets (i) .Name Then Sheets (j) .Move before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Tenga en cuenta que este código funciona bien con nombres de texto y, en la mayoría de los casos, también con años y números. Pero puede darle resultados incorrectos en caso de que los nombres de las hojas sean 1,2,11. Ordenará y le dará la secuencia 1, 11, 2. Esto se debe a que hace la comparación como texto y considera 2 más grande que 11.

Proteja / desproteja todas las hojas de una sola vez

Si tiene muchas hojas de trabajo en un libro de trabajo y desea proteger todas las hojas, puede usar el código VBA a continuación.

Le permite especificar la contraseña dentro del código. Necesitará esta contraseña para desproteger la hoja de trabajo.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'reemplace Test123 con la contraseña que desee Para cada ws en las hojas de trabajo ws.Protect contraseña: = contraseña Siguiente ws End Sub

El siguiente código desprotegería todas las hojas de una sola vez.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'reemplace Test123 con la contraseña que usó mientras protegía For Each ws In Worksheets ws.Unprotect contraseña: = contraseña Siguiente ws End Sub

Creación de una tabla de contenido de todas las hojas de trabajo (con hipervínculos)

Si tiene un conjunto de hojas de trabajo en el libro de trabajo y desea insertar rápidamente una hoja de resumen que tenga los enlaces a todas las hojas, puede usar el código a continuación.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Cuente ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Name & "! A1", _ TextToDisplay: = Hojas de trabajo (i) .Name Siguiente i End Sub

El código anterior inserta una nueva hoja de trabajo y la nombra Index.

Luego recorre todas las hojas de trabajo y crea un hipervínculo para todas las hojas de trabajo en la hoja de índice.

Dónde poner el código VBA

¿Se pregunta dónde va el código VBA en su libro de Excel?

Excel tiene un backend de VBA llamado editor de VBA. Debe copiar y pegar el código en la ventana de código del módulo VB Editor.

Estos son los pasos para hacer esto:

1. Vaya a la pestaña Desarrollador.
2. Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
3. En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea insertar el código. Si no ve el Explorador de proyectos, vaya a la pestaña Ver y haga clic en Explorador de proyectos.
4. Vaya a Insertar y haga clic en Módulo. Esto insertará un objeto de módulo para su libro de trabajo.
5. Copie y pegue el código en la ventana del módulo.

También le pueden gustar los siguientes tutoriales de Excel VBA:

• Trabajar con libros de trabajo usando VBA.
• Uso de declaraciones IF Then Else en VBA.
• Para el siguiente bucle en VBA.
• Creación de una función definida por el usuario en Excel.
• Cómo grabar una macro en Excel.
• Cómo ejecutar una macro en Excel.
• Eventos de Excel VBA: una guía fácil (y completa).
• Cómo crear un complemento en Excel.
• Cómo guardar y reutilizar macros usando el libro de macros personal de Excel.