VBA – Sheets – La guía definitiva

Esta es la guía definitiva para trabajar con Sheets / Worksheets en VBA.

Al final de esta guía, hemos creado una hoja de trucos de comandos comunes para trabajar con Sheets.

Sheets vs. Worksheets

Hay dos maneras de hacer referencia a las hojas usando VBA. La primera es con el objeto Sheets:

Sheets("Hoja1").Activate

La otra es con el objeto Worksheets:

Worksheets("Hoja1").Activate

el 99% de las veces, estos dos objetos son idénticos. De hecho, si ha buscado en Internet ejemplos de código VBA, probablemente haya visto el uso de ambos objetos. Esta es la diferencia:

La Colección de Sheets contiene Worksheets y Chart Sheets.

 

Por lo tanto, utilice Sheets si desea incluir las Hojas de Trabajo normales y las Hojas de Gráficos. Utilice Worksheets si desea excluir las Hojas de gráficos. En el resto de esta guía utilizaremos indistintamente Sheets y Worksheets

Referenciando Hojas

Hay varias formas de referenciar las hojas:

• ActiveSheet
• Nombre de la pestaña de la hoja
• Número de índice de la hoja
• Nombre del Código de la Hoja

ActiveSheet

La ActiveSheet es la Hoja que está actualmente activa. En otras palabras, si pausas tu código y miras a Excel, es la hoja que está visible. El siguiente ejemplo de código mostrará un MessageBox con el nombre de la ActiveSheet.

MsgBox ActiveSheet.Name

Nombre de la hoja

Probablemente esté más familiarizado con la referencia a las hojas por su nombre de pestaña:

Sheets("NombreDePestaña").Activate

Este es el nombre de la hoja que es visible para los usuarios de Excel. Introdúzcalo en el objeto Sheets, como una cadena de texto, rodeada de comillas.

Número de índice de la hoja

El número de índice de la hoja es la posición de la hoja en el libro. 1 es la primera hoja. 2 es la segunda hoja, etc:

Sheets(1).Activate

Número de Índice de la Hoja – Última Hoja del Libro de Trabajo

Para referirse a la última hoja del libro de trabajo, utilice Sheets.Count para obtener el último número de índice y active esa hoja:

Sheets(Sheets.Count).Activate

Hoja ‘Nombre Código’

El nombre de código de la hoja es su nombre de objeto en VBA:

NombreCódigo.Activate

Referenciando Hojas en Otros Libros de Trabajo

También es fácil referenciar Hojas en otros Libros de Trabajo. Para hacerlo, necesita usar el objeto Workbooks:

Workbooks("Ejemplos_VBA.xlsm").Worksheets("Hoja1").Activate

Importante: El Libro de Trabajo debe estar abierto antes de poder hacer referencia a sus Hojas.

Activar vs. Seleccionar Hoja

En otro artículo hablamos de todo lo relacionado con la activación y la selección de hojas. La versión corta es esta:

Cuando activa una hoja, ésta se convierte en la hoja activa. Esta es la hoja que verá si mira su programa Excel. Sólo se puede activar una hoja a la vez.

Activar una hoja

Sheets("Hoja1").Activate

Cuando se selecciona una Hoja, ésta también se convierte en la Hoja Activa. Sin embargo, puede seleccionar varias hojas a la vez. Cuando se seleccionan varias hojas a la vez, la hoja «superior» es la Hoja Activa. Sin embargo, puede cambiar la Hoja Activa dentro de las hojas seleccionadas.

Seleccione una hoja

Sheets("Hoja1").Select

Seleccionar varias hojas

Utilice una Array para seleccionar varias hojas a la vez:

Worksheets(Array("Hoja2", "Hoja3")).Select

Variable de la hoja de trabajo

Asignar una hoja de trabajo a una variable de objeto le permite referenciar la hoja de trabajo por su nombre de variable. Esto puede ahorrar mucho tiempo de escritura y hacer que su código sea más fácil de leer. También hay muchas otras razones por las que puede querer usar variables. Para declarar una variable de hoja de trabajo:

Dim ws as worksheet

Asignar una hoja de trabajo a una variable:

Set ws = Sheets("Hoja1")

Ahora puedes hacer referencia a la variable de la hoja de trabajo en tu código:

ws.Activate

Recorrer todas las hojas del libro de trabajo

Las variables de hoja de trabajo son útiles cuando se quiere hacer un bucle a través de todas las hojas de trabajo de un libro. La forma más sencilla de hacerlo es

Dim hoja as Worksheet
 
For Each hoja in Worksheets
  MsgBox hoja.name
Next hoja

Este código recorrerá todas las hojas de trabajo del libro, mostrando el nombre de cada hoja de trabajo en un cuadro de mensaje. Recorrer en bucle todas las hojas de un libro de trabajo es muy útil cuando se bloquean/desbloquean u ocultan/muestran varias hojas de trabajo a la vez.

Protección de las hojas de trabajo

Protección del libro de trabajo

La protección del libro de trabajo  bloquea el libro de trabajo para evitar cambios estructurales como añadir, eliminar, mover u ocultar hojas de trabajo. Puede activar la protección del libro de trabajo utilizando VBA:

ActiveWorkbook.Protect Password:="Contraseña"

o desactivar la protección del libro de trabajo:

ActiveWorkbook.UnProtect Password:="Contraseña"

Nota: También puede proteger / desproteger sin contraseña omitiendo el argumento Contraseña:

ActiveWorkbook.Protect

Protección de la hoja de trabajo

La protección a nivel de hoja de trabajo impide que se realicen cambios en hojas de trabajo individuales.

Proteger Hoja de Trabajo

Worksheets("Hoja1").Protect "Contraseña"

Desproteger hoja de trabajo

Worksheets("Hoja1").Unprotect "Contraseña"

Hay una variedad de opciones a la hora de proteger las hojas de trabajo (permitir cambios de formato, permitir que el usuario inserte filas, etc.) Recomendamos utilizar la grabadora de macros para registrar la configuración deseada.

Aquí discutimos la protección de las hojas de trabajo con más detalle.

Propiedad de visibilidad de la hoja de trabajo

Es posible que ya sepa que las hojas de trabajo se pueden ocultar:

En realidad hay tres configuraciones de visibilidad de la hoja de trabajo: Visible, Oculta y MuyOculta. Las hojas ocultas pueden ser desocultadas por cualquier usuario normal de Excel, haciendo clic con el botón derecho del ratón en el área de la pestaña de la hoja de trabajo (mostrada arriba). Las hojas MuyOcultas sólo pueden desocultarse con código VBA o desde el Editor VBA. Utilice los siguientes ejemplos de código para ocultar/desocultar hojas de trabajo:

Desocultar hoja de trabajo

Worksheets("Hoja1").Visible = xlSheetVisible

Ocultar la hoja de trabajo

Worksheets("Hoja1").visible = xlSheetHidden

Hoja de trabajo Muy Oculta

Worksheets("Hoja1").Visible = xlSheetVeryHidden

Eventos a nivel de hoja de cálculo

Los eventos son disparadores que pueden hacer que se ejecuten «Procedimientos de Eventos». Por ejemplo, puede hacer que el código se ejecute cada vez que se cambie cualquier celda de una hoja de trabajo o cuando se active una hoja de trabajo. Los procedimientos de eventos a nivel de hoja de trabajo deben colocarse en un módulo de hoja de trabajo:

 

Existen numerosos eventos de hoja de trabajo. Para ver una lista completa, vaya a un módulo de hoja de trabajo, seleccione «Worksheet» en el primer desplegable. A continuación, seleccione un procedimiento de evento en el segundo desplegable para insertarlo en el módulo.

Evento de activación de la hoja de trabajo

Los eventos de activación de la hoja de trabajo se ejecutan cada vez que se abre la hoja de trabajo.

Private Sub Worksheet_Activate()
  Range("A1").Select
End Sub

Este código seleccionará la celda A1 (restableciendo el área de vista a la parte superior izquierda de la hoja de trabajo) cada vez que se abra la hoja de trabajo.

Evento de Cambio de Hoja de Trabajo

Los eventos de cambio de hoja de trabajo se ejecutan cada vez que se cambia el valor de una celda en la hoja de trabajo. Lee nuestro tutorial sobre Eventos de Cambio de Hoja de Trabajo para más información.

Hoja de trucos de la hoja de trabajo

A continuación encontrará una hoja de trucos que contiene ejemplos de código común para trabajar con hojas en VBA

VBA Worksheets Cheatsheet

VBA worksheets Cheatsheet

Description	Code Example
Referencing and Activating Sheets
Tab Name	Sheets("Input").Activate
VBA Code Name	Sheet1.Activate
Index Position	Sheets(1).Activate
Select Sheet
Select Sheet	Sheets("Input").Select
Set to Variable	Dim ws as Worksheet Set ws = ActiveSheet
Name / Rename	ActiveSheet.Name = "NewName"
Next Sheet	ActiveSheet.Next.Activate
Loop Through all Sheets	Dim ws as Worksheet For each ws in Worksheets Msgbox ws.name Next ws
Loop Through Selected Sheets	Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets MsgBox ws.Name Next ws
Get ActiveSheet	MsgBox ActiveSheet.Name
Add Sheet	Sheets.Add
Add Sheet and Name	Sheets.Add.Name = "NewSheet"
Add Sheet With Name From Cell	Sheets.Add.Name = range("a3").value
Add Sheet After Another	Sheets.Add After:=Sheets("Input")
Add Sheet After and Name	Sheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Add Sheet Before and Name	Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
Add Sheet to End of Workbook	Sheets.Add After:=Sheets(Sheets.Count)
Add Sheet to Beginning of Workbook	Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
Add Sheet to Variable	Dim ws As Worksheet Set ws = Sheets.Add
Copy Worksheets
Move Sheet to End of Workbook	Sheets("Sheet1").Move After:=Sheets(Sheets.Count)
To New Workbook	Sheets("Sheet1").Copy
Selected Sheets To New Workbook	ActiveWindow.SelectedSheets.Copy
Before Another Sheet	Sheets("Sheet1").Copy Before:=Sheets("Sheet2")
Before First Sheet	Sheets("Sheet1").Copy Before:=Sheets(1)
After Last Sheet	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Copy and Name	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "LastSheet"
Copy and Name From Cell Value	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Range("A1").Value
To Another Workbook	Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
Hide / Unhide Sheets
Hide Sheet	Sheets("Sheet1").visible = False or Sheets("Sheet1").visible = xlSheetHidden
Unhide Sheet	Sheets("Sheet1").Visible = True or Sheets("Sheet1").Visible = xlSheetVisible
Very Hide Sheet	Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete or Clear Sheets
Delete Sheet	Sheets("Sheet1").Delete
Delete Sheet (Error Handling)	On Error Resume Next Sheets("Sheet1").Delete On Error GoTo 0
Delete Sheet (No Prompt)	Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True
Clear Sheet	Sheets("Sheet1").Cells.Clear
Clear Sheet Contents Only	Sheets("Sheet1").Cells.ClearContents
Clear Sheet UsedRange	Sheets("Sheet1").UsedRange.Clear
Protect or Unprotect Sheets
Unprotect (No Password)	Sheets("Sheet1").Unprotect
Unprotect (Password)	Sheets("Sheet1").Unprotect "Password"
Protect (No Password)	Sheets("Sheet1").Protect
Protect (Password)	Sheets("Sheet1").Protect "Password"
Protect but Allow VBA Access	Sheets("Sheet1").Protect UserInterfaceOnly:=True
Unprotect All Sheets	Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect "password" Next ws