jueves, 22 de febrero de 2018

Las colecciones "Worksheets" y "Sheets"

En innumerables ocasiones, cuando de manejar las hojas de Excel como objetos en VBA se trata, he visto que se hace de manera indiferente tanto usando “Sheets” como usando “Worksheets” pero ¿son ambas lo mismo?

Según las definiciones que hay en el “Microsoft Developer Network” (MSDN), “Sheets” hace referencia a “la colección de hojas que puede contener objetos gráfico y hojas de trabajo” y “Worksheets” es “la colección que contiene todas las hojas de trabajo en un libro”.

Entonces:

- Worsheets: Solo hojas de trabajo (u hojas de cálculo como normalmente las llamamos u hojas a secas)

- Sheets: Hojas de trabajo y hojas tipo gráfico (no confundir con los gráficos insertados en hojas)

 

Para que se entienda mejor, vamos a suponer que tenemos un archivo con tres hojas así:

 

Entonces usamos esta macro muy simple:

Sub ContarHojas()
MsgBox Worksheets.Count
End Sub   
Cuando ejecutamos la macro el “Msgbox” nos dará un resultado de “3”. Ahora hagamos un pequeño cambio y el resultado será el mismo:
Sub ContarHojas()
MsgBox Sheets.Count
End Sub 

La colección “Worksheets” nos da como resultado “3” y el resultado es el mismo cuando usamos “Sheets” porque dicha colección contiene, también, a las hojas en cuestión. Veamos ahora que ocurre en este otro caso:

 

Si aplicamos las macros del ejemplo anterior, con “Worksheets.Count” tenemos como resultado “2” pero en esta ocasión con “Sheets.Count” tendremos como resultado “4”. “Sheets” considera todas las hojas del libro (4 en total) y “Worksheets” solo las “hojas de trabajo” (hojas de cálculo).

Pero ¿son estos los únicos tipos de hojas que trae Excel? Vamos a recordar una imagen que vimos líneas arriba:

 

Hay tres objetos ahí que nos interesan: Macro de Microsoft Excel 4.0, Hoja internacional de macros y el Dialogo de Excel 5.0. Las dos primeras son herencia de las macros de Excel 4.0 y el tercero es herencia de Excel 5.0. En los tres casos las versiones posteriores de Excel las mantienen por una cuestión de compatibilidad (evidentemente no con todas las características de sus versiones iniciales).

En el MSDN nos dicen que hay cinco tipos de hojas:

Nombre

Constante

Descripción

xlChart

-4109

Hoja de Gráfico

xlDialogSheet

-4116

Hoja de Dialogo

xlExcel4IntlMacroSheet

4

Hoja Internacional de Macros

xlExcel4MacroSheet

3

Hoja Macro de Microsoft Excel 4.0

xlWorksheet

-4167

Hoja de trabajo

(Enumeración XlSheetType)

Entonces, si en nuestro archivo tenemos algo como esto:

Al usar “Worksheets.Count” el resultado será “2” pero con “Sheets.Count” será “6”, ya que la colección “Sheets” considera a todas las hojas sin excepción. Si bien en la definición del MSDN sobre la colección “Sheets” habla solo de dos tipos de hojas, al parecer podría deberse a lo ya mencionado: Que los otros tipos se mantienen solo por compatibilidad y por eso han obviado mencionarlo (O por lo menos eso es lo que yo, y otras personas, creemos).

Algo muy importante sobre todo a tomar en cuenta si trabajamos con los índices de las hojas, es que cada colección tiene su propio índice. Con el ejemplo anterior si colocamos “Worksheets(2).Name” nos devolverá “Hoja2” pero si colocamos “Sheets(2).Name” nos dará como resultado “Macro1”.

Hasta la próxima.

Abraham Valencia

domingo, 4 de febrero de 2018

Microsoft Outlook desde Excel (I)

Muchas veces deseamos que nuestros trabajos en Excel, o en otros programas de Office incluso, se puedan enviar a través de nuestro correo electrónico. Podríamos decir que si tenemos dicho correo configurado en nuestro Microsoft Outlook no será muy difícil lograrlo.

A través de VBA podemos manejar desde Excel nuestro Microsoft Outlook como un objeto y así enviar mensajes adjuntando incluso archivos o nuestra firma digital, si es que la tenemos.

Lo primero que recomiendo hacer es activar la referencia respectiva en el editor de VBA:

En mi caso dice 15.0 por la versión de Office que tengo en la respectiva PC pero se debe activar la que a cada uno le corresponda.

Para continuar, lo que vamos a hacer ahora es crear un objeto “Outlook” y también usaremos el método “CreateItem” con el parámetro del tipo “olMailItem” que creará un nuevo mensaje (objeto “MailItem”):

Dim OutlookApp As Outlook.Application
Dim objItem As MailItem 

Set OutlookApp = CreateObject("Outlook.Application") 
Set objItem = OutlookApp.CreateItem(olMailItem)  
Tal y como cuando enviamos un mensaje desde nuestro correo o desde el propio Microsfot Outlook, el objeto creado tiene propiedades que representan los campos “Para”, “Asunto”, “Cuerpo”, etc:
With objItem 
 .To = "destinatario@ejemplo.com" 'Para
 .CC = "segundodestinatario@ejemplo.com" 'Con copia
 .Subject = "Prueba" 'Asunto
 .Body = "Este es un mensaje de prueba" 'Cuerpo 
End With 

Lo único que faltaría es que usemos el método “Send” para enviar el mensaje:

.Send 

Toda nuestra macro junta podría quedar así:

Sub EnviarMensajeOutlook()
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem

Set OutlookApp = CreateObject("Outlook.Application") 
Set objItem = OutlookApp.CreateItem(olMailItem)  

With objItem 
 .To = "destinatario@ejemplo.com" 'Para
 .CC = "segundodestinatario@ejemplo.com" 'Con copia
 .Subject = "Prueba" 'Asunto
 .Body = "Este es un mensaje de prueba" 'Cuerpo 
 .Send
End With

Set OutlookApp = Nothing 
Set objItem = Nothing 

End Sub 

Si deseamos poder ir cambiando el correo de destino o el texto del cuerpo del mensaje o etc., sin tener que modificar la macro, podemos colocar los datos en celdas o variables:

With objItem
 .To = Range("A1").Value     
 .CC = Range("A2").Value     
 .Subject = Range("A3").Value     
 .Body = Range("A4").Value     
 .Send 
End With 

En este momento ya se deben de haber dado cuenta (o ya sabían) que el cuerpo del mensaje se envía sin formato y quizá lo que queremos es más bien que dicho mensaje tenga formato como ya estamos, casi todos, acostumbrados hoy en día cuando usamos el correo electronico. Para lograr eso ya no debemos usar la propiedad “Body” sino que ahora usaremos la propiedad “HTMLBody” pero tendremos que aplicar algo de “HTML” en nuestro VBA:

.HTMLBody = "<html>" & _ 
"<body><font color=""#FF0000"" size=""6"" face=""Comic Sans MS, cursive""><strong>Hola mi querido y estimado amigo</strong></font>" & _ 
"</body></html>" 
En este caso el mensaje se envía en letra cursiva de color rojo usando “Comic Sans” como fuente. Para ayudarnos con el código "HTML", podemos usar un programa como el “DreamWeaver” o un editor de “HTML” en línea, solo hay que adaptar lo que obtengamos de él para agregarlo a nuestra macro.

Si lo que queremos, además, es adjuntar algún archivo a nuestro mensaje, debemos usar la propiedad “Attachments” y su método “Add”. Podemos agregar más de un archivo del siguiente modo:

.Attachments.Add "D:\Miarchivouno.xlsm" 
.Attachments.Add "D:\MiArchivoNuevo.xlsx" 

Es bueno recordar que también podemos usar un bucle y/o variables para agregar más archivos.

Siguiendo con los adjuntos, probablemente tengamos nuestra firma en formato “JPG” y queremos incluirla en el cuerpo del mensaje; de ser así, lo primero que debemos hacer es adjuntar el archivo:

.Attachments.Add “D:\MiFirma.jpg"

Después incluiremos dicha imagen en el cuerpo del mensaje del siguiente modo y/o similar:

.HTMLBody = "<html>" & _
        "<body>" & _
           "<p>Aqui tu mensaje</p>" & _
            "<br>" & _ 
            "<br>" & _
            "<img src='cid:'" & .Attachments.Item(1).Filename & "'' height=100 width=75>" & _
            "</body>" & _
            "</html>" 
Los archivos adjuntos tienen un índice, si se tiene más archivos adjuntos, considerar el índice adecuado. En este caso he usado el uno (1).

Hasta la próxima.

Abraham Valencia

PD: En realidad cuando se usa el objeto "Outlook", y sobre todo se nota cuando se hacen envíos masivos, lo que ocurre es que se van colocando los mensajes en la "Bandeja de salida" así que es recomendable que se tenga el Microsoft Outlook configurado para el envío automático al abrir/cerrar para que el proceso continúe el envío así se cierre el Excel. Igual si se desea que se haga casi al instante el envío desde la macro, se puede añadir un Application.Wait después del "Send" o tener el Outlook abierto.

Extras:

En ocasiones deseamos enviar un rango de nuestra hoja como parte del cuerpo del mensaje, hay algunas alternativas como convertir el rango en imagen y colocarla en el cuerpo del mensaje, similar a lo que se hace con las firmas pero la mejor alternativa que he visto es usar una “Función Definida por el Usuario” (UDF) que convierte el rango en lenguaje “HTML” y permite incluirlo en el cuerpo del mensaje a través de la propiedad “HTMLBody”. EL autor es Ron de Bruin y la UDF podemos encontrarla aquí:

http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Cuando tenemos más de un correo configurado en el Outlook, recomiendo usar una UDF (Autor: Antoni - Galicia, España) para poder elegir el correo que queremos usar:

https://ayudaexcel.com/foro/topic/32737-elegir-cuenta-de-correo-de-outlook/

De no elegir alguno de los correos con la UDF, se usará el correo configurado como predeterminado.

 

martes, 23 de enero de 2018

Las variables de objetos

En el VBA, al igual que en otros lenguajes de programación, un objeto representa un elemento de una aplicación; en el caso del Excel estos son las hojas, las celdas, los gráficos, los formularios de VBA (UserForm), etc. Los objetos de Excel tienen métodos, eventos y propiedades.

En muchas ocasiones es necesario usar objetos en nuestros procedimientos, para ello debemos declarar los objetos como variables. Las variables de objeto se declaran como cualquier otra solo que haciendo referencia al objeto. De este modo se puede establecer y/o cambiar sus propiedades o usar cualquiera de sus métodos:

'Objeto rango
 Dim MiCelda As Range
'Objeto hoja
Dim MiHoja As WorkSheet
'Objeto libro
Dim MiLibro As WorkBook 

En ocasiones quizá tengamos que declarar una variable de objeto con el tipo de datos “Object” pues quizá el tipo específico del objeto no se conoce. Este tipo hace referencia a cualquier objeto pero consume más recursos que un tipo específico:

Dim MiObjeto As Object

Para asignar valor (objetos) a este tipo de variable usaremos la instrucción “Set”:

Set MiCelda = Range("A1:A10") 
Set MiHoja = Worksheets("Hoja1") 

Una vez asignado el objeto a la variable, podemos hacer referencia y/o cambiar, por ejemplo, sus propiedades o valores:

MiCelda.Value = “Hola a todos” 
MiHoja.Name = "Nueva Hoja" 

En el primer caso asignamos la frase “Hola a todos” a todas las celdas del rango asignado al objeto “MiCelda” y en el segundo caso cambiamos la propiedad “Name” (el nombre) del objeto “MiHoja”.

Después de trabajar con nuestras variables de objeto, una buena costumbre es limpiar la variable y de ese modo dejar libre memoria (aunque al terminar la rutina las variables igual quedan libres y por ende la memoria). Para ello utilizaremos la instrucción “Nothing”:

Set MiCelda = Nothing 
Set MiHoja = Nothing 

Y esos son algunos consejos e indicaciones para que podamos trabajar con variables de objetos.

Hasta la próxima.

Abraham Valencia

miércoles, 10 de enero de 2018

El gran problema de los archivos lentos y "pesados"

Pasan los años, se crean versiones más modernas y potentes de Excel, pero entre los usuarios(as) sigue existiendo el gran problema de que se generan archivos que ocupan muchos megas y que se vuelven cada vez más lentos.

Excel es una hoja de cálculo parte del paquete de Office y, dependiendo del tipo de producto Office, viene acompañada de Access que, a pesar de las críticas, es un buen sistema gestor de base de datos (SGBD). Me imagino que algunas personas ya se estarán preguntando “¿y qué tiene que ver eso con los archivos “pesados” y lentos? Pues mucho.

Excel es una hoja de cálculo pero por su masivo uso y aparente mayor facilidad de aprenderlo que Access, es usada como SGBD ¿es necesariamente un error eso? No, pero a diferencia de los SGBD, no almacena los datos de forma “plana”  (como las tablas de los SGBD) sino que los datos almacenados en las hojas están acompañados de diversos formatos (de tipo o color de letra, de color de fondo, de color de bordes, etc.), de fórmulas, de imágenes y un largo etcétera de cosas. Eso, a la larga, genera que los archivos crezcan de modo muchas veces desmesurado y hasta se pongan lentos al cargar.  Esto, finalmente, no es un problema del Excel sino de los propios usuarios(as).

He aquí algunas de las causas, y soluciones,  que ocasionan esos problemas:

Uno:

Por una cuestión visual muchas veces le damos formatos que usan color a nuestros datos, ya sea al color de relleno de las celdas, al color de la fuente o algún otro que nos permita Excel. El problema con ello es que en lugar de dar el formato al rango de celdas que usamos, le damos formato a toda la columna o a toda la fila, o incluso en muchos casos a varias filas o columnas e incluso a todas las celdas de las hojas:

Un ejemplo de que no hacer:

Un segundo ejemplo de que no hacer:

Un ejemplo de que como se debe aplicar formatos:

Evitemos eso, demos el formato deseado solo al rango de celdas que estamos usando y sí vamos a ingresar más datos, esperemos a ello antes de aplicar los formatos que deseemos.

Dos:

En ocasiones se usa Excel prácticamente como catálogo de imágenes, no solo decenas sino hasta cientos de imágenes se insertan en sus hojas haciendo que los megas del archivo crezcan y crezcan. Lo primero es recomendar no usar Excel para mostrar imágenes y de insistir en ello al menos comprimirlas:

Eso ayudará en algo a evitar que su archivo sea demasiado “pesado”.


Tres:

Muchas veces se aplica fórmulas sobre columnas o filas completas y no solo sobre los rangos que tienes datos. No es raro ver fórmulas de estos tipos:
=SUMA(A:A)
Las fórmulas así, tienen que analizar cada una de las celdas de dicha columna, en este caso más de un millón de celdas cuando lo ideal es solo aplicarla sobre el rango con datos:
=SUMA(A2:A1232)
Cuatro:
Se usan fórmulas en celdas en las que no se esperan resultados porque se supone que a futuro dichas celda tendrán datos. A pesar de estar vacías, Excel igual analiza dichas celdas causando que se use más memoria y, como ya saben, poniendo lento el archivo además de incrementar su “peso”.  He visto archivos con 100 filas ocupadas y fórmulas en al menos diez mil filas.  Lo ideal es solo tener fórmulas en aquellas celdas que esperamos resultados inmediatos, asimismo en aquellas celdas en las que ya tenemos resultados y aquellos no van a cambiar, lo ideal es cambiar las fórmulas por los valores obtenidos, lo que se puede hacer fácil y rápidamente con “Pegado Especial”  y la opción de “Pegar Valores”.
De ser necesario que tengas muchas fórmulas, y de aquellas sí se espera resultado inmediato, lo ideal es cambiar el “Cálculo” a “Manual”.

 

Cinco:
Aunque hay varias cosas más he tratado de colocar algunas de las que considero son las principales pero igual, como para terminar, podríamos mencionar que es bueno, también, no usar muchas funciones volátiles como “HOY”, no hay que abusar de los “Formatos Condicionales” así como tampoco de las fórmulas matriciales.

Hasta la próxima.

Abraham Valencia