- Hojas vs. Hojas de trabajo
- Hojas de referencia
- Activar vs. Seleccionar hoja
- Variable de la hoja de trabajo
- Recorrer todas las hojas del libro de trabajo
- Protección de la hoja de trabajo
- Propiedad visible de la hoja de trabajo
- Eventos a nivel de hoja de trabajo
- Hoja de trabajo Hoja de referencia
- Hoja de referencia de hojas de trabajo de VBA
Esta es la guía definitiva para trabajar con hojas / hojas de trabajo en Excel.
En la parte inferior de esta guía, hemos creado una hoja de referencia de comandos comunes para trabajar con hojas.
Hojas vs. Hojas de trabajo
Hay dos formas de hacer referencia a Sheets usando VBA. El primero es con el objeto Sheets:
| 1 | Hojas de cálculo ("Hoja1"). Activar |
El otro es con el objeto Worksheets:
| 1 | Hojas de trabajo ("Hoja1"). Activar |
El 99% de las veces, estos dos objetos son idénticos. De hecho, si buscó en línea ejemplos de código VBA, probablemente haya visto ambos objetos utilizados. Esta es la diferencia:
La colección de hojas contiene hojas de trabajo Y hojas de gráficos.
Por lo tanto, use Hojas de cálculo si desea incluir hojas de trabajo Y hojas de gráficos regulares. Utilice Hojas de trabajo si desea excluir las hojas de gráficos. Para el resto de esta guía, usaremos Hojas y Hojas de trabajo indistintamente.
Hojas de referencia
Hay varias formas diferentes de hacer referencia a Hojas de cálculo:
- Hoja activa
- Nombre de la pestaña de hoja
- Número de índice de hoja
- Nombre de código de hoja
Hoja activa
ActiveSheet es la hoja que está actualmente activa. En otras palabras, si detuvo su código y miró Excel, es la hoja la que está visible. El siguiente ejemplo de código mostrará un cuadro de mensaje con el nombre de ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Nombre de la hoja
Probablemente esté más familiarizado con hacer referencia a las hojas por su nombre de pestaña:
| 1 | Hojas de cálculo ("TabName"). Activar |
Número de índice de hoja
El número de índice de hoja es la posición de la hoja en el libro de trabajo. 1 es la primera hoja. 2 es la segunda hoja, etc .:
| 1 | Hojas (1) .Activar |
Número de índice de hoja: última hoja del libro de trabajo
Para hacer referencia a la última hoja del libro de trabajo, use Sheets.Count para obtener el último número de índice:
| 1 | Hojas (Sheets.Count) .Activar |
Hoja "Nombre de código"
El nombre del código de la hoja es el nombre del objeto en VBA:
| 1 | CodeName.Activate |
Hacer referencia a hojas en otros libros de trabajo
También es fácil hacer referencia a Hojas de cálculo en otros libros de trabajo. Para hacerlo, debe utilizar el objeto Workbooks:
| 1 | Libros de trabajo ("VBA_Examples.xlsm"). Hojas de trabajo ("Hoja1"). Activar |
Importante: El Libro de trabajo debe estar abierto antes de que pueda hacer referencia a sus Hojas de cálculo.
Activar vs. Seleccionar hoja
En otro artículo discutimos todo sobre la activación y selección de hojas. La versión corta es esta:
Cuando activa una hoja, se convierte en la hoja activa. Esta es la hoja que vería si mirara su programa de Excel. Solo se puede activar una hoja a la vez.
Activar una hoja
| 1 | Hojas de cálculo ("Hoja1"). Activar |
Cuando selecciona una hoja, también se convierte en ActiveSheet. Sin embargo, puede seleccionar varias hojas a la vez. Cuando se seleccionan varias hojas a la vez, la hoja "superior" es ActiveSheet. Sin embargo, puede alternar ActiveSheet dentro de las hojas seleccionadas.
Seleccione una hoja
| 1 | Hojas ("Hoja1"). Seleccione |
Seleccionar varias hojas
Use una matriz para seleccionar varias hojas a la vez:
| 1 | Hojas de trabajo (Matriz ("Hoja2", "Hoja3")). Seleccione |
Variable de la hoja de trabajo
Asignar una hoja de trabajo a una variable le permite hacer referencia a la hoja de trabajo por su nombre de variable. Esto puede ahorrarle mucho escribir y hacer que su código sea más fácil de leer. También hay muchas otras razones por las que es posible que desee utilizar variables.
Para declarar una variable de hoja de trabajo:
| 1 | Dim ws como hoja de trabajo |
Asignar una hoja de trabajo a una variable:
| 1 | Establecer ws = Hojas ("Hoja1") |
Ahora puede hacer referencia a la variable de la hoja de trabajo en su código:
| 1 | ws.Activate |
Recorrer todas las hojas del libro de trabajo
Las variables de la hoja de trabajo son esenciales cuando desea recorrer todas las hojas de trabajo en un libro de trabajo. La forma más sencilla de hacerlo es:
| 12345 | Dim ws como hoja de trabajoPara cada ws en hojas de trabajoMsgBox ws.nameSiguiente ws |
Este código recorrerá todas las hojas de trabajo en el libro de trabajo, mostrando cada nombre de hoja de trabajo en un cuadro de mensaje. Recorrer todas las hojas de un libro de trabajo es muy útil al bloquear / desbloquear u ocultar / mostrar varias hojas de trabajo a la vez.
Protección de la hoja de trabajo
Protección del libro de trabajo
La protección del libro bloquea el libro de cambios estructurales como agregar, eliminar, mover u ocultar hojas de trabajo.
Puede activar la protección del libro de trabajo usando VBA:
| 1 | ActiveWorkbook.Protect Contraseña: = "Contraseña" |
o deshabilite la protección del libro de trabajo:
| 1 | ActiveWorkbook.UnProtect Contraseña: = "Contraseña" |
Nota: También puede proteger / desproteger sin una contraseña omitiendo el argumento Contraseña:
| 1 | ActiveWorkbook.Protect |
Protección de la hoja de trabajo
La protección a nivel de hoja de trabajo evita cambios en hojas de trabajo individuales.
Proteger la hoja de trabajo
| 1 | Hojas de trabajo ("Hoja1"). Proteger "Contraseña" |
Desproteger hoja de trabajo
| 1 | Hojas de trabajo ("Hoja1"). Desproteger "Contraseña" |
Hay una variedad de opciones para proteger las hojas de trabajo (permitir cambios de formato, permitir que el usuario inserte filas, etc.) Recomendamos usar la Grabadora de macros para registrar la configuración deseada.
Analizamos la protección de la hoja de trabajo con más detalle aquí.
Propiedad visible 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, Oculto y VeryHidden.Cualquier usuario normal de Excel puede mostrar las hojas ocultas, haciendo clic con el botón derecho en el área de la pestaña de la hoja de trabajo (que se muestra arriba). Las hojas VeryHidden solo se pueden mostrar con código VBA o desde el Editor de VBA. Utilice los siguientes ejemplos de código para ocultar / mostrar hojas de trabajo:
Mostrar hoja de trabajo
| 1 | Hojas de trabajo ("Hoja1"). Visible = xlSheetVisible |
Ocultar hoja de trabajo
| 1 | Hojas de trabajo ("Hoja1"). Visible = xlSheetHidden |
Hoja de trabajo muy oculta
| 1 | Hojas de trabajo ("Hoja1"). Visible = xlSheetVeryHidden |
Eventos a nivel de hoja de trabajo
Los eventos son desencadenantes que pueden hacer que se ejecuten "Procedimientos de eventos". Por ejemplo, puede hacer que el código se ejecute cada vez que se cambia cualquier celda de una hoja de trabajo o cuando se activa una hoja de trabajo.
Los procedimientos de eventos de la hoja de trabajo deben colocarse en un módulo de la hoja de trabajo:
Hay numerosos eventos de hojas de trabajo. Para ver una lista completa, vaya a un módulo de hoja de trabajo, seleccione "Hoja de trabajo" en el primer menú desplegable. Luego, seleccione un procedimiento de evento del segundo menú desplegable para insertarlo en el módulo.
Evento de activación de hoja de trabajo
Los eventos de activación de la hoja de trabajo se ejecutan cada vez que se abre la hoja de trabajo.
| 123 | Private Sub Worksheet_Activate ()Rango ("A1"). SeleccionarEnd Sub |
Este código seleccionará la celda A1 (restableciendo el área de vista en 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 siempre que se cambia un valor de celda en la hoja de trabajo. Lea nuestro tutorial sobre Eventos de cambio de hoja de trabajo para obtener más información.
Hoja de trabajo Hoja de referencia
A continuación, encontrará una hoja de trucos que contiene ejemplos de códigos comunes para trabajar con hojas en VBA
Hoja de referencia de hojas de trabajo de VBA
Hojas de trabajo de VBA Cheatsheet| Descripción | Ejemplo de código |
|---|---|
| Hacer referencia y activar hojas | |
| Nombre de la pestaña | Hojas de cálculo ("Entrada"). Activar |
| Nombre de código de VBA | Sheet1.Activate |
| Posición del índice | Hojas (1) .Activar |
| Seleccionar hoja | |
| Seleccionar hoja | Hojas ("Entrada"). Seleccione |
| Establecer como variable | Dim ws como hoja de trabajo Establecer ws = ActiveSheet |
| Nombre / Cambiar nombre | ActiveSheet.Name = "NewName" |
| Hoja siguiente | ActiveSheet.Next.Activate |
| Recorrer todas las hojas | Dim ws como hoja de trabajo Para cada ws en hojas de trabajo Msgbox ws.name Siguiente ws |
| Recorrer las hojas seleccionadas | Dim ws como hoja de trabajo Para cada ws en ActiveWindow.SelectedSheets MsgBox ws.Name Siguiente ws |
| Obtener ActiveSheet | MsgBox ActiveSheet.Name |
| Agregar hoja | Sábanas.Añadir |
| Agregar hoja y nombre | Sheets.Add.Name = "Nueva hoja" |
| Agregar hoja con el nombre de la celda | Sheets.Add.Name = rango ("a3"). Valor |
| Agregar hoja tras otra | Hojas.Añadir después: = Hojas ("Entrada") |
| Agregar hoja después y nombre | Sheets.Add (After: = Sheets ("Entrada")). Name = "NewSheet" |
| Agregar hoja antes y nombre | Sheets.Add (Before: = Sheets ("Entrada")). Name = "NewSheet" |
| Agregar hoja al final del libro de trabajo | Sheets.Add After: = Hojas (Sheets.Count) |
| Agregar hoja al comienzo del libro de trabajo | Hojas.Añadir (Antes: = Hojas (1)). Nombre = "Primera hoja" |
| Agregar hoja a variable | Dim ws como hoja de trabajo Establecer ws = Sheets.Add |
| Copiar hojas de trabajo | |
| Mover hoja al final del libro de trabajo | Hojas ("Hoja1"). Mover después: = Hojas (Hojas.Cuenta) |
| Al libro de trabajo nuevo | Hojas ("Hoja1"). Copiar |
| Hojas seleccionadas al libro de trabajo nuevo | ActiveWindow.SelectedSheets.Copy |
| Antes de otra hoja | Hojas ("Hoja1"). Copiar antes: = Hojas ("Hoja2") |
| Antes de la primera hoja | Hojas ("Hoja1"). Copiar antes: = Hojas (1) |
| Después de la última hoja | Hojas ("Hoja1"). Copiar después: = Hojas (Hojas.Cuenta) |
| Copia y nombre | Hojas ("Hoja1"). Copiar después: = Hojas (Hojas.Cuenta) ActiveSheet.Name = "Última hoja" |
| Copiar y nombrar del valor de la celda | Hojas ("Hoja1"). Copiar después: = Hojas (Hojas.Cuenta) ActiveSheet.Name = Rango ("A1"). Valor |
| A otro libro de trabajo | Hojas ("Hoja1"). Copiar antes: = Libros de trabajo ("Ejemplo.xlsm"). Hojas (1) |
| Ocultar / mostrar hojas | |
| Ocultar hoja | Hojas ("Hoja1"). Visible = Falso o Hojas ("Hoja1"). Visible = xlSheetHidden |
| Mostrar hoja | Hojas de cálculo ("Hoja1"). Visible = Verdadero o Hojas ("Hoja1"). Visible = xlSheetVisible |
| Hoja muy oculta | Hojas ("Hoja1"). Visible = xlSheetVeryHidden |
| Eliminar o borrar hojas | |
| Eliminar hoja | Hojas de cálculo ("Hoja1"). Eliminar |
| Eliminar hoja (manejo de errores) | En caso de error, reanudar siguiente Hojas de cálculo ("Hoja1"). Eliminar En caso de error, vaya a 0 |
| Eliminar hoja (sin aviso) | Application.DisplayAlerts = Falso Hojas de cálculo ("Hoja1"). Eliminar Application.DisplayAlerts = True |
| Hoja clara | Hojas ("Hoja1"). Celdas Borrar |
| Borrar solo el contenido de la hoja | Hojas ("Hoja1"). Cells.ClearContents |
| Hoja transparente utilizada | Hojas ("Hoja1"). Rango usado.Borrar |
| Proteger o desproteger hojas | |
| Desproteger (sin contraseña) | Hojas ("Hoja1"). Desproteger |
| Desproteger (contraseña) | Hojas de cálculo ("Hoja1"). Desproteger "Contraseña" |
| Proteger (sin contraseña) | Hojas ("Hoja1"). Proteger |
| Proteger (contraseña) | Hojas de cálculo ("Hoja1"). Proteger "Contraseña" |
| Proteger pero permitir el acceso a VBA | Hojas ("Hoja1"). Proteger UserInterfaceOnly: = True |
| Desproteger todas las hojas | Dim ws como hoja de trabajo Para cada ws en hojas de trabajo ws. Desproteger "contraseña" Siguiente ws |
