Trucos y tretas en Excel VBA 2 para programadores (parte 2)
Excel
,Visual Basic para Aplicaciones
Publicado el 15 de Julio del 2019 por Gnomi
2.079 visualizaciones desde el 15 de Julio del 2019
Articulo original en: https://www.gnomi.club/trucos-y-tretas-en-excel-vba-2/
En Trucos y tretas en Excel VBA 2 continuamos viendo algunas soluciones a situaciones comunes cuando se programan Macros de Excel en VBA.
Cuando programamos código para macros de Microsoft Excel VBA a menudo nos encontramos con que los detalles de cómo invocar tal o cual cosa de Excel termina siendo muy crítica y debes navegar por foros, tutoriales o incontables páginas ininteligibles de Microsoft que no te responden lo que andas buscando.
Obtener los nombres de libros de Excel abiertos.
En ocasiones ocupas una lista de los libros de Excel que están abiertos. Para ello te vales del objeto Workbooks de Excel.
El arreglo NombreDeArchivo almacenará los nombres de los archivos de Excel que están abiertos en un momento dado.
Obtener la lista de páginas del libro de Excel
Puedes usar el siguiente código.
Los nombres se almacenan en el arreglo NombreDePagina.
Obtener los nombres de las gráficas (charts) que hay en una página de Excel
Hay ocasiones en que ocupas buscar todas las gráficas contenidas en una página de Excel. para ello te vales de ActiveSheet.ChartObjects
El arreglo NombreDeChart contendrá los nombres de las gráficas de la hoja de Excel en que nos encontramos.
Obtener el nombre del libro de Excel en que te encuentras actualmente
Existen algunas situaciones donde ocupas saber el nombre del libro de Excel en que te encuentras actualmente.
El nombre de la hoja actual será guardado en la variable LibroActual.
Cambiar de libro o de hoja
Si deseas pasarte a otro libro abierto u otra hoja, puedes usar el siguiente código.
Este código hará que te pases al libro de Excel MiLibro.xls y a la página Sheet1 de ese libro. Como ves, el valor entre comillas puede ser reemplazado por una variable tipo String si lo deseas.
Cómo lidiar con libros de Excel protegidos por password
Este código se encargará de eliminar la protección contra password. En este caso el password es XYZ. Cuando trabajas con hojas protegidas con password, obtendrás errores al tratar de acceder a material protegido y por eso debes desproteger la hoja para trabajar en ella.
Enviar un correo con archivo adjunto
Si quieres generar un correo y pegarle un archivo adjunto, puedes usar el siguiente código:
Este código buscará el archivo MiArchivo.xlsx ubicado en C:\, construirá el mensaje y lo enviará a la dirección [email protected]. La lógica de este código usa un poco de programación orientada a objetos. oLook es un objeto que se encarga de manejar la aplicación Outlook, y oMail es un objeto que se encarga de manejar el correo.
Ocultar una hoja e impedir que el usuario la vea
Si quieres ocultar una hoja de Excel para que el usuario no la pueda ver, y no pueda mostrar su contenido usa este código:
El código lo que hace es ocultar todas las columnas de la hoja Sheet1 y luego oculta la hoja. Cabe notar que Sheet1 no es el nombre de la hoja visible para el usuario, sino el nombre del objeto que aparece en el editor de código de Excel VBA fuera de los paréntesis.
Crear un botón en una hoja de cálculo
Si quieres crear un botón:
El código crea un botón sobre la celda en la que estás ubicado actualmente.
Cargar, refrescar, salvar y cerrar una hoja de cálculo
Si quieres efectuar esas tareas:
El código efectúa las cuatro tareas descritas en ese orden.
Cómo usar el portapapeles
El portapapeles no viene incluido en la versión básica de Excel VBA, así que primero tenemos que configurar algunas cosas.
- Ve al editor de Visual Basic (Alt F11).
- Selecciona Tools > References
Activa Microsoft Forms Object Library y presiona Ok.
Si no aparece Microsoft Forms 2.0 Object Library en la lista, quizás quieras usar el botón Browse para buscar el archivo FM20.DLL que se encuentra en la ubicación en disco duro que se muestra en la imagen.
Ahora que ya configuramos todo, veamos el código.
Primero deberías declarar el objeto de portapapeles (en este caso le llamaremos doClip)
Este sería el método para crear el portapapeles en memoria.
Con este método puedes destruir el objeto portapapeles en memoria para liberar espacio.
Con este método copias un texto al portapapeles.
Con esta función extraes el texto del portapapeles.
Cómo configurar área de impresión
Para configurar el área de impresión ocupas la orientación portrait o landscape. He puesto las muestras de las líneas para ambos casos, aunque tú sólo ocuparías una. Si vas a ajustar a una o más páginas horizontal o verticamente, primero necesitas poner el modo Zoom en apagado y luego puedes configurar los ajustes. Y también debes delimitar el área de impresión que en este ejemplo es desde A1 hasta H25.
Cómo crear una lista de archivos y directorios
- Para obtener una lista de archivos, ve al siguiente sitio: List all files in a directory.
- Para listar archivos y subdirectorios ve al sitio List folders and subdirectories.
Otros trucos
Existen trucos para optimizar el desempeño de Visual Basic, tales como:
Evitar al máximo el uso de propiedades de controles. Las celdas de Excel son controles. Los forms (formulario, ventana) y los controles sobre un form son controles. Usar propiedades de controles agrega mucha carga de procesamiento innecesario. Por ejemplo, el valor de una celda es una propiedad del control llamado celda. El texto de un control de texto es una propiedad. Hay que minimizar la referencia a controles hasta donde sea posible, pasando los valores de los controles a una variable, se trabaja todo en la variable, y al final se actualiza la propiedad del control con el valor final de la variable.
Apaga la actualización de video, porque el tiempo de actualizar la pantalla innecesariamente consume enormes cantidades de tiempo de procesamiento.
Evitar el uso de variables tipo Variant. Estas variables generalmente aparecen cuando no se declaran las variables. Para hacer que el Visual Basic muestre un error cuando vea variables no declaradas, puedes poner esto al inicio de cada módulo.
No olvides de compartir en tus redes sociales y de comentar!!!
Bendiciones!!!
En Trucos y tretas en Excel VBA 2 continuamos viendo algunas soluciones a situaciones comunes cuando se programan Macros de Excel en VBA.
Cuando programamos código para macros de Microsoft Excel VBA a menudo nos encontramos con que los detalles de cómo invocar tal o cual cosa de Excel termina siendo muy crítica y debes navegar por foros, tutoriales o incontables páginas ininteligibles de Microsoft que no te responden lo que andas buscando.
Trucos y tretas en Excel VBA 2: Manejo de LIbros y Hojas
Obtener los nombres de libros de Excel abiertos.
En ocasiones ocupas una lista de los libros de Excel que están abiertos. Para ello te vales del objeto Workbooks de Excel.
1
2
3
4
5
6
7
Dim NombreDeArchivo() as String
Redim NombreDeArchivo(Workbooks.Count-1)
For i = 1 To Workbooks.Count
NombreDeArchivo(i-1) = Workbooks.Item(i).Name
Next i
El arreglo NombreDeArchivo almacenará los nombres de los archivos de Excel que están abiertos en un momento dado.
Obtener la lista de páginas del libro de Excel
Puedes usar el siguiente código.
1
2
3
4
5
6
7
8
9
10
Dim NombreDePagina()
Dim Cantidad as Long
Cantidad = Sheets.Count
redim NombreDePagina(n)
For i = 1 To CantidadNombreDePagina(i) = Sheets(i).Name
Next i
Los nombres se almacenan en el arreglo NombreDePagina.
Obtener los nombres de las gráficas (charts) que hay en una página de Excel
Hay ocasiones en que ocupas buscar todas las gráficas contenidas en una página de Excel. para ello te vales de ActiveSheet.ChartObjects
1
2
3
4
5
6
7
Dim NombreDeChart()
Redim NombreDeChart(ActiveSheet.ChartObjects.Count-1)
For i = 1 To ActiveSheet.ChartObjects.Count
NombreDeChart(i-1) = ActiveSheet.ChartObjects(i).Name
Next i
El arreglo NombreDeChart contendrá los nombres de las gráficas de la hoja de Excel en que nos encontramos.
Obtener el nombre del libro de Excel en que te encuentras actualmente
Existen algunas situaciones donde ocupas saber el nombre del libro de Excel en que te encuentras actualmente.
1
LibroActual = ActiveWorkbook.Name
Cambiar de libro o de hoja
Si deseas pasarte a otro libro abierto u otra hoja, puedes usar el siguiente código.
1
2
3
Workbooks("MiLibro.xls").Activate
Sheets("Sheet1").Select
Cómo lidiar con libros de Excel protegidos por password
1
2
3
If ActiveSheet.ProtectContents ThenActiveSheet.Unprotect Password:="XYZ"
End If
Enviar un correo con archivo adjunto
Si quieres generar un correo y pegarle un archivo adjunto, puedes usar el siguiente código:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.To = "mipersona@dominio.com"
.body = "Este es el cuerpo del mensaje. Ver archivo adjunto"
.Subject = "Asunto. Envio de archivo adjunto"
.Attachments.Add ("C:\MiArchivo.xlsx")
.SendEnd With
Set oMail = NothingSet oLook = NothingEste código buscará el archivo MiArchivo.xlsx ubicado en C:\, construirá el mensaje y lo enviará a la dirección [email protected]. La lógica de este código usa un poco de programación orientada a objetos. oLook es un objeto que se encarga de manejar la aplicación Outlook, y oMail es un objeto que se encarga de manejar el correo.
Ocultar una hoja e impedir que el usuario la vea
Si quieres ocultar una hoja de Excel para que el usuario no la pueda ver, y no pueda mostrar su contenido usa este código:
1
2
3
4
Set hide_sheet = Sheet1hide_sheet.Columns.Hidden = True
hide_sheet.Visible = False
Crear un botón en una hoja de cálculo
Si quieres crear un botón:
1
ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
Cargar, refrescar, salvar y cerrar una hoja de cálculo
Si quieres efectuar esas tareas:
1
2
3
4
5
Workbooks.Open Filename:="C:\MiArchivo.xlsx", UpdateLinks:=3
Workbooks("MiArchivo.xlsx").RefreshAll
ActiveWorkbook.SaveActiveWindow.CloseCómo usar el portapapeles
El portapapeles no viene incluido en la versión básica de Excel VBA, así que primero tenemos que configurar algunas cosas.
- Ve al editor de Visual Basic (Alt F11).
- Selecciona Tools > References
Activa Microsoft Forms Object Library y presiona Ok.
Si no aparece Microsoft Forms 2.0 Object Library en la lista, quizás quieras usar el botón Browse para buscar el archivo FM20.DLL que se encuentra en la ubicación en disco duro que se muestra en la imagen.
Ahora que ya configuramos todo, veamos el código.
Primero deberías declarar el objeto de portapapeles (en este caso le llamaremos doClip)
1
Dim doClip As DataObject
1
2
3
Sub CrearClipboard()
Set doClip = New DataObjectEnd Sub
Con este método puedes destruir el objeto portapapeles en memoria para liberar espacio.
1
2
3
Sub DestruirClipboard()
Set doClip = NothingEnd Sub
Con este método copias un texto al portapapeles.
1
2
3
4
5
Sub CopiarAlClipboard(sTexto As String)
doClip.Clear doClip.SetText sTexto doClip.PutInClipboardEnd Sub
Con esta función extraes el texto del portapapeles.
1
2
3
4
Function ClipboardTexto() As String
doClip.GetFromClipboardClipboardTexto = doClip.GetText
End Function
Cómo configurar área de impresión
Para configurar el área de impresión ocupas la orientación portrait o landscape. He puesto las muestras de las líneas para ambos casos, aunque tú sólo ocuparías una. Si vas a ajustar a una o más páginas horizontal o verticamente, primero necesitas poner el modo Zoom en apagado y luego puedes configurar los ajustes. Y también debes delimitar el área de impresión que en este ejemplo es desde A1 hasta H25.
1
2
3
4
5
6
ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.PrintArea = "A1:H25"
Cómo crear una lista de archivos y directorios
- Para obtener una lista de archivos, ve al siguiente sitio: List all files in a directory.
- Para listar archivos y subdirectorios ve al sitio List folders and subdirectories.
Otros trucos
Existen trucos para optimizar el desempeño de Visual Basic, tales como:
Evitar al máximo el uso de propiedades de controles. Las celdas de Excel son controles. Los forms (formulario, ventana) y los controles sobre un form son controles. Usar propiedades de controles agrega mucha carga de procesamiento innecesario. Por ejemplo, el valor de una celda es una propiedad del control llamado celda. El texto de un control de texto es una propiedad. Hay que minimizar la referencia a controles hasta donde sea posible, pasando los valores de los controles a una variable, se trabaja todo en la variable, y al final se actualiza la propiedad del control con el valor final de la variable.
Apaga la actualización de video, porque el tiempo de actualizar la pantalla innecesariamente consume enormes cantidades de tiempo de procesamiento.
Evitar el uso de variables tipo Variant. Estas variables generalmente aparecen cuando no se declaran las variables. Para hacer que el Visual Basic muestre un error cuando vea variables no declaradas, puedes poner esto al inicio de cada módulo.
1
Option Explicit
No olvides de compartir en tus redes sociales y de comentar!!!
Bendiciones!!!
Comentarios... (0)
No hay comentarios