Veremos hoy como mostrar, eliminar, agregar y ordenar las hojas de un libro de excel mediante macros.
[+/-] Ver el resto / Ocultar
En mas de una ocasión deberemos interactuar con las hojas de nuestro libro. Este ejemplo que les brindo a continuación no "hace ningún trabajo en especial", si no que realiza tareas varias mediante un formulario, para brindar una idea en general de como recorrer la colección de hojas de un libro y poder eliminarlas, agregar u ordenarlas de menor a mayor. Así, cada cual tomará el código que necesite y podrá implementarlo en su trabajo, o bien sabrá como empezar a escribir el código para las utilidades de su proyecto.
Empecemos.
Vamos al editor de visual basic y agregamos un formulario.
Luego insertamos en el 5 controles:
1) un listbox
2) tres botones
3) un label
los colocamos con la siguiente disposicion:
ahora les muestro los dos procedimientos que invoco cuando se inicializa el formulario:
[+/-] Ver el resto / Ocultar
En mas de una ocasión deberemos interactuar con las hojas de nuestro libro. Este ejemplo que les brindo a continuación no "hace ningún trabajo en especial", si no que realiza tareas varias mediante un formulario, para brindar una idea en general de como recorrer la colección de hojas de un libro y poder eliminarlas, agregar u ordenarlas de menor a mayor. Así, cada cual tomará el código que necesite y podrá implementarlo en su trabajo, o bien sabrá como empezar a escribir el código para las utilidades de su proyecto.
Empecemos.
Vamos al editor de visual basic y agregamos un formulario.
Luego insertamos en el 5 controles:
1) un listbox
2) tres botones
3) un label
los colocamos con la siguiente disposicion:
a los botones los llamamos: cmdAgregar, cmdEliminar y cmdOrdenar, respectivamente.
al listbox le dejamos el nombre "de fábrica": ListBox1
Como habran adivinado, este formulario será la interfaz gráfica que nos permitirá interactuar con las hojas del libro. En el control ListBox1 cargaré todas las hojas.
Intentaré ir poniendo el código con cierto orden lógico y al final de este post pegaré la ventana de VBA tal cual la tengo en mi proyecto.
Bien, vamos x partes.
1) Mostrar las hojas al aparecer el formulario: esta "aparecer" del form tiene un evento, y se llama "initilize". Escribo el código necesario al "inicializarse", o sea, cuando aparece por primera vez en pantalla.
Private Sub UserForm_Initialize()
'ejecuto el código utilizando el evento Initialize, es decir,
'cuando el formulario se inicia.
CargarHojasDeLibro
'y muestro cuantas hojas hay
ContarHojas
End Sub
'ejecuto el código utilizando el evento Initialize, es decir,
'cuando el formulario se inicia.
CargarHojasDeLibro
'y muestro cuantas hojas hay
ContarHojas
End Sub
ahora les muestro los dos procedimientos que invoco cuando se inicializa el formulario:
Sub CargarHojasDeLibro()
'con for each recorro la colección de objetos Sheets (hojas) del libro:
For Each Hoja In ActiveWorkbook.Sheets
'y cargo en el list el nombre de la hoja
ListBox1.AddItem Hoja.Name
Next Hoja
'con for each recorro la colección de objetos Sheets (hojas) del libro:
For Each Hoja In ActiveWorkbook.Sheets
'y cargo en el list el nombre de la hoja
ListBox1.AddItem Hoja.Name
Next Hoja
'destruyo el objeto luego de utilizarlo, para liberar los recursos del sistema
Set Hoja = Nothing
End Sub
Set Hoja = Nothing
End Sub
con esta linea de código le muestro al usuario cuantas hojas hay en el libro:
Sub ContarHojas()
Label1.Caption = "El libro posee " & ActiveWorkbook.Sheets.Count & " hoja/s"
End Sub
Label1.Caption = "El libro posee " & ActiveWorkbook.Sheets.Count & " hoja/s"
End Sub
2) eliminar hojas:
el código correspondiente al boton cmdEliminar:
Private Sub cmdEliminar_Click()
'antes que nada, me aseguro que siempre quede una hoja, ya que el libro no se puede quedar sin ninguna de ellas. si no tomo esta precaución, la macro arrojará error. lo hago tomando la propiedad ListCount del objeto, que me devuelve la cantidad de items presentes. 1 item = 1 hoja
If ListBox1.ListCount = 1 Then
MsgBox "Queda una sola hoja, la cual no se puede eliminar", vbCritical, "Error"
'envío el foco al control list
ListBox1.SetFocus
'y produzco una salida del procedimiento:
Exit Sub
ElseIf ListBox1.Text <> "" Then
'antes que nada, me aseguro que siempre quede una hoja, ya que el libro no se puede quedar sin ninguna de ellas. si no tomo esta precaución, la macro arrojará error. lo hago tomando la propiedad ListCount del objeto, que me devuelve la cantidad de items presentes. 1 item = 1 hoja
If ListBox1.ListCount = 1 Then
MsgBox "Queda una sola hoja, la cual no se puede eliminar", vbCritical, "Error"
'envío el foco al control list
ListBox1.SetFocus
'y produzco una salida del procedimiento:
Exit Sub
ElseIf ListBox1.Text <> "" Then
'pregunto antes de eliminar:
If MsgBox("Confirma la eliminacion de la hoja " & ListBox1.Text & "?", vbQuestion + vbYesNo, "Atención") = vbYes Then
Application.DisplayAlerts = False
'tomo el nombre de la hoja y la borro
Sheets(ListBox1.Text).Delete
'debo quitar del list la hoja borrada. paso como argumento de RemoveItem el nro del item seleccionado
ListBox1.RemoveItem (ListBox1.ListIndex)
Application.DisplayAlerts = True
'y llamo al procedimiento de contar hojas, para mostrar la nueva cantidad
ContarHojas
End If
End If
End Sub
If MsgBox("Confirma la eliminacion de la hoja " & ListBox1.Text & "?", vbQuestion + vbYesNo, "Atención") = vbYes Then
Application.DisplayAlerts = False
'tomo el nombre de la hoja y la borro
Sheets(ListBox1.Text).Delete
'debo quitar del list la hoja borrada. paso como argumento de RemoveItem el nro del item seleccionado
ListBox1.RemoveItem (ListBox1.ListIndex)
Application.DisplayAlerts = True
'y llamo al procedimiento de contar hojas, para mostrar la nueva cantidad
ContarHojas
End If
End If
End Sub
3) agregar hojas:
obviamente, este es el código del botón cmdAgregar. Presten atención a todos los comentarios y a las funciones que implemento dentro de este Sub, para evitar problemas al insertar una nueva hoja:
Private Sub cmdAgregar_Click()
Dim Nombre As String
'utilizo a InputBox para que el usuario me brinde el nombre de la nueva hoja:
Nombre = InputBox("Ingrese el nombre de la nueva hoja: ", "Añadir Hoja")
'le elimino posibles espacios en blanco al comienzo y final:
Nombre = Trim(Nombre)
'primer recaudo a tomar: que haya ingresado algo.
If Nombre = "" Then
MsgBox "No ha introducido ningún nombre", vbExclamation, "Faltan datos"
Exit Sub
'ahora viene un tema mas complicado: los nombres de hoja no soportan ciertos caracteres. entonces debo 'crear una funcion que revise una x una las letras ingresadas y controle que estén bien:
ElseIf RevisarNombreDeHoja(Nombre) = False Then
MsgBox "Ha introducido caracteres NO validos", vbCritical, "Error"
Exit Sub
'otra cosa para revisar: que el nombre de hoja no exista, ya que eso provocaría un error:
ElseIf HojaDuplicada(Nombre) = True Then
MsgBox "Ya existe una hoja con el nombre " & Nombre, vbCritical, "Error"
Exit Sub
Else
'si todas las revisiones pasaron satisfactoriamente, sigo. ahora algo importante: confirmar si desea agregar
Dim Nombre As String
'utilizo a InputBox para que el usuario me brinde el nombre de la nueva hoja:
Nombre = InputBox("Ingrese el nombre de la nueva hoja: ", "Añadir Hoja")
'le elimino posibles espacios en blanco al comienzo y final:
Nombre = Trim(Nombre)
'primer recaudo a tomar: que haya ingresado algo.
If Nombre = "" Then
MsgBox "No ha introducido ningún nombre", vbExclamation, "Faltan datos"
Exit Sub
'ahora viene un tema mas complicado: los nombres de hoja no soportan ciertos caracteres. entonces debo 'crear una funcion que revise una x una las letras ingresadas y controle que estén bien:
ElseIf RevisarNombreDeHoja(Nombre) = False Then
MsgBox "Ha introducido caracteres NO validos", vbCritical, "Error"
Exit Sub
'otra cosa para revisar: que el nombre de hoja no exista, ya que eso provocaría un error:
ElseIf HojaDuplicada(Nombre) = True Then
MsgBox "Ya existe una hoja con el nombre " & Nombre, vbCritical, "Error"
Exit Sub
Else
'si todas las revisiones pasaron satisfactoriamente, sigo. ahora algo importante: confirmar si desea agregar
'la hoja
If MsgBox("Agrega la hoja " & Nombre & "?", vbYesNo + vbQuestion, "Atención") = vbYes Then
'agrego la hoja
ActiveWorkbook.Sheets.Add
'le pongo el Nombre
ActiveSheet.Name = Nombre
'lo agrego al listbox del formulario
ListBox1.AddItem Nombre
'y cuento nuevamente
ContarHojas
End If
End If
End Sub
If MsgBox("Agrega la hoja " & Nombre & "?", vbYesNo + vbQuestion, "Atención") = vbYes Then
'agrego la hoja
ActiveWorkbook.Sheets.Add
'le pongo el Nombre
ActiveSheet.Name = Nombre
'lo agrego al listbox del formulario
ListBox1.AddItem Nombre
'y cuento nuevamente
ContarHojas
End If
End If
End Sub
a continuación las dos funciones que llamo desde el Sub: RevisarNombreDeHoja y HojaDuplicada:
Function RevisarNombreDeHoja(Cadena As String) As Boolean
Dim Largo As Integer
Dim X As Integer
Dim Caracter As String
'pongo a la funcion en False, de arranque:
RevisarNombreDeHoja = False
'tomo la longitud del nombre de hoja, que me pasan a traves del parámetro Cadena:
Largo = Len(Cadena)
'convierto el nombre de hoja a mayúsculas, para "emparejar" los caracteres (recordemos que cada letra es un 'nro para el ordenador)
Cadena = UCase(Cadena)
'ahora con un bucle For.. recorro el nombre de la hoja y reviso
'cada caracter. solo permitiremos: letras y guiones bajos:
For X = 1 To Largo
'extraigo letra x letra, almacenandola en Caracter, utilizando Mid()
Caracter = Mid(Cadena, X, 1)
If Asc(Caracter) >= 65 And Asc(Caracter) <= 90 Then
RevisarNombreDeHoja = True
'el codigo Ascii nunca debe ser superior a 90 (letra Z)
ElseIf Asc(Caracter) > 90 Then
'una excepcion: el guion bajo, que tiene el nro 95
If Asc(Caracter) <> 95 Then
RevisarNombreDeHoja = False
'y salgo de la funcion, ya que al detectar un solo caracter NO válido, todo el nombre es inservible:
Exit Function
End If
ElseIf Asc(Caracter) < 65 Then 'si es menor a la A
'y no está comprendido entre los caracteres del 0 al 9
If Asc(Caracter) < 48 Or Asc(Caracter) > 57 Then
RevisarNombreDeHoja = False
Exit Function
Else
'aqui la activo en verdadero:
RevisarNombreDeHoja = True
End If
End If
Next X
End Function
Dim Largo As Integer
Dim X As Integer
Dim Caracter As String
'pongo a la funcion en False, de arranque:
RevisarNombreDeHoja = False
'tomo la longitud del nombre de hoja, que me pasan a traves del parámetro Cadena:
Largo = Len(Cadena)
'convierto el nombre de hoja a mayúsculas, para "emparejar" los caracteres (recordemos que cada letra es un 'nro para el ordenador)
Cadena = UCase(Cadena)
'ahora con un bucle For.. recorro el nombre de la hoja y reviso
'cada caracter. solo permitiremos: letras y guiones bajos:
For X = 1 To Largo
'extraigo letra x letra, almacenandola en Caracter, utilizando Mid()
Caracter = Mid(Cadena, X, 1)
If Asc(Caracter) >= 65 And Asc(Caracter) <= 90 Then
RevisarNombreDeHoja = True
'el codigo Ascii nunca debe ser superior a 90 (letra Z)
ElseIf Asc(Caracter) > 90 Then
'una excepcion: el guion bajo, que tiene el nro 95
If Asc(Caracter) <> 95 Then
RevisarNombreDeHoja = False
'y salgo de la funcion, ya que al detectar un solo caracter NO válido, todo el nombre es inservible:
Exit Function
End If
ElseIf Asc(Caracter) < 65 Then 'si es menor a la A
'y no está comprendido entre los caracteres del 0 al 9
If Asc(Caracter) < 48 Or Asc(Caracter) > 57 Then
RevisarNombreDeHoja = False
Exit Function
Else
'aqui la activo en verdadero:
RevisarNombreDeHoja = True
End If
End If
Next X
End Function
Function HojaDuplicada(Cadena As String) As Boolean
'recorro todas las hojas del libro, comparandolas con el nuevo nombre ingresado por el usuario:
For Each Hoja In ActiveWorkbook.Sheets
'si hay coincidencia, pongo en True la funcion y me voy. noten que dentro de esta funcion convierto el
'recorro todas las hojas del libro, comparandolas con el nuevo nombre ingresado por el usuario:
For Each Hoja In ActiveWorkbook.Sheets
'si hay coincidencia, pongo en True la funcion y me voy. noten que dentro de esta funcion convierto el
'nombre de la hoja a mayúsculas, para igualar a Cadena.
If UCase(Hoja.Name) = Cadena Then
HojaDuplicada = True
Exit Function
End If
Next Hoja
HojaDuplicada = False
Set Hoja = Nothing
End Function
If UCase(Hoja.Name) = Cadena Then
HojaDuplicada = True
Exit Function
End If
Next Hoja
HojaDuplicada = False
Set Hoja = Nothing
End Function
Probablemente el código de la segunda función sea "mucho...", ya que se podría hacer lo mismo "forzando" un error. Lo logro de la siguiente forma: le digo a VBA que seleccione la hoja que le paso como argumento. Si la hoja no existe, generará un error, ya que le estoy pidiendo un imposible. Entonces es allí donde intercepto el error y creo la hoja. Este Sub no está en el ejemplo, lo pongo para ampliar el concepto:
Sub AgregarHoja(Nombre)
On Error Resume Next
Sheets(Nombre).Select
If err.number <> 0 then
sheets.add
activesheet.name=nombre
end if
End Sub
4) Ordenar hojas: uno de mis preferidos y de los mas consultados. Como ordenar de mayor a menor las hojas de un libro. Si son dos o tres... arrastrando con el mouse, que es lo mas práctico. =)
Pero en determinadas ocasiones trabajaremos con decenas de hojas... y allí se complica. La idea es sencilla:
en una hoja coloco los nombres de todas las hojas del libro, por ej:
luego las ordeno de mayor a menor:
para finalmente colocar las hojas del libro en el mismo orden que el rango A1:A7. Todo esto, desde ya, a traves del siguiente código:
Private Sub cmdOrdenar_Click()
Dim Fila As Long
Dim UltimaFila As Long
'primero inserto la hoja en donde colocaré los nombres de todas las hojas del libro:
CrearHojaOrdenar
Fila = 1
'recorro todas las hojas del libro:
For Each Hoja In ActiveWorkbook.Sheets
'ecluyo a "ordenar" ya que es una hoja transitoriamente creada
'y no forma parte del libro del usuario:
If Hoja.Name <> "ordenar" Then
'cargo el nombre de la hoja en el rango que posteriormente ordenaré:
Sheets("ordenar").Cells(Fila, 1).Value = Hoja.Name
'sumo 1 a Fila, para colocar el siguiente registro justo debajo del último
Fila = Fila + 1
End If
Next Hoja
Set Hoja = Nothing
'ya tengo los datos en la hoja "ordenar". ahora los ordeno de forma ascendente:
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("a1").Select
'en la columna A de la hoja "ordenar", tengo todas las hojas del libro, ordenadas de menor a mayor.
'ahora recorro ese rango, tomo el nombre de cada hoja y la coloco en su nueva posición:
total = Sheets.Count
'almaceno en donde termina el rango de la columna A. Lo hago'"subiendo" desde la ultima fila de la planilla 'hasta encontrar la primer celda ocupada:
UltimaFila = Range("a" & Cells.Rows.Count).End(xlUp).Row
For X = 1 To UltimaFila
estahoja = Sheets("ordenar").Cells(X, 1).Value
Sheets(estahoja).Move After:=Sheets(total)
Next X
'y queda eliminar la hoja "ordenar"
Application.DisplayAlerts = False
Sheets("ordenar").Delete
Application.DisplayAlerts = True
'las hojas se ordenaron, pero debo reflejar ese cambio en el listbox1:
'primero quito los datos del list:
ListBox1.Clear
'y cargo nuevamente las hojas
CargarHojasDeLibro
End Sub
Dim Fila As Long
Dim UltimaFila As Long
'primero inserto la hoja en donde colocaré los nombres de todas las hojas del libro:
CrearHojaOrdenar
Fila = 1
'recorro todas las hojas del libro:
For Each Hoja In ActiveWorkbook.Sheets
'ecluyo a "ordenar" ya que es una hoja transitoriamente creada
'y no forma parte del libro del usuario:
If Hoja.Name <> "ordenar" Then
'cargo el nombre de la hoja en el rango que posteriormente ordenaré:
Sheets("ordenar").Cells(Fila, 1).Value = Hoja.Name
'sumo 1 a Fila, para colocar el siguiente registro justo debajo del último
Fila = Fila + 1
End If
Next Hoja
Set Hoja = Nothing
'ya tengo los datos en la hoja "ordenar". ahora los ordeno de forma ascendente:
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("a1").Select
'en la columna A de la hoja "ordenar", tengo todas las hojas del libro, ordenadas de menor a mayor.
'ahora recorro ese rango, tomo el nombre de cada hoja y la coloco en su nueva posición:
total = Sheets.Count
'almaceno en donde termina el rango de la columna A. Lo hago'"subiendo" desde la ultima fila de la planilla 'hasta encontrar la primer celda ocupada:
UltimaFila = Range("a" & Cells.Rows.Count).End(xlUp).Row
For X = 1 To UltimaFila
estahoja = Sheets("ordenar").Cells(X, 1).Value
Sheets(estahoja).Move After:=Sheets(total)
Next X
'y queda eliminar la hoja "ordenar"
Application.DisplayAlerts = False
Sheets("ordenar").Delete
Application.DisplayAlerts = True
'las hojas se ordenaron, pero debo reflejar ese cambio en el listbox1:
'primero quito los datos del list:
ListBox1.Clear
'y cargo nuevamente las hojas
CargarHojasDeLibro
End Sub
las hojas quedarán ordenadas tal cual lo están en el rango. Se podría implementar el algoritmo de la "burbuja mejorada", pero preferí mostrarlo con las propias herramientas de Excel.
5) Seleccionar una hoja:
el código mas corto y sencillo de todos, que se producirá al hacer dobleclick sobre el nombre de la hoja, dentro del listbox:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'si la propiedad text del list es distinta a nada (o sea que NO se encuentra vacía, utilizo el texto para definir la 'hoja e ir hasta ella:
If ListBox1.Text <> "" Then
Sheets(ListBox1.Text).Select
'y selecciono el rango A1
[a1].Select
End If
End Sub
'si la propiedad text del list es distinta a nada (o sea que NO se encuentra vacía, utilizo el texto para definir la 'hoja e ir hasta ella:
If ListBox1.Text <> "" Then
Sheets(ListBox1.Text).Select
'y selecciono el rango A1
[a1].Select
End If
End Sub
fin del show. mucho código para analizar, así que tienen trabajo.
desde ya les aclaro que en programación no existe una única forma de hacer las cosas, así que todo este código bien podría ser reemplazado por otro totalmente distinto. aquí traté de exponer distintas formas de trabajo, desde bucles for each a la creación de funciones personalizadas.
- Obtener enlace
- Correo electrónico
- Otras aplicaciones
Etiquetas
Macros
Etiquetas:
Macros
- Obtener enlace
- Correo electrónico
- Otras aplicaciones
Comentarios
Publicar un comentario