Como muchos ya sabéis, Excel es un programa bastante potente y versátil. Forma parte de la suite Office de Microsoft.
En esta entrada voy a comentar algunos aspectos sobre la automatización de tareas empleado el lenguaje de programación que lleva incorporado. Excel posee un editor para realizar tareas, además de proporcionarnos muchas herramientas para administrar información, mediante las macros podremos crear aplicaciones a medida en el lenguaje de programación Visual Basic.

También os comentaré aspectos relacionados con la seguridad y centros de confianza.

Para ello, voy a basarme en la hoja de cálculo Excel versión 2013.

No obstante  desde la versión 2013, existe en el ribbon o cinta de opciones un nuevo apartado denominado DESARROLLADOR (oculto por defecto, ver imagen) que nos va a permitir realizar las aplicaciones a medida.

Esta barra se llamaba PROGRAMADOR en las versiones 2007 y 2010. Por defecto no se encuentra visible OJO.

Lo primero que tenemos que realizar para trabajar con ella es activarla. En Excel 2013, desde el menú archivo, opciones, personalizar cinta de opciones, pestañas principales, es preciso activar la casilla ficha Desarrollador  y pulsar aceptar.

Visual Basic para Aplicaciones (VBA) es un lenguaje de programación que incorpora la suite Office.

En las primeras versiones, las macros en Excel se escribían en hojas separadas para este propósito. La extensión de los archivos que se generaban eran .XLM. En la versión 5.0 de 1993, la empresa de Redmond incorporó VBA. Aunque en sus inicios las macros se escribían en módulos, se comenzó a usar el editor de programación desde la versión 97. El editor es muy parecido al IDE Visual Studio.

Un dato bastante relevante es que a partir de la versión 2007, los libros que contienen código VBA se almacenan en un formato de archivo diferente .XLSM. La M significa Macros. El formato estándar es XLSX, ya quedo en el pasado el xls, desde la incorporación del XML como estructura interna de archivo. Los beneficios del XLSX son en aras de una mayor compatibilidad, recuperación mejorada de archivos dañados, y espacio (ancho de banda).

Me gustaría diferenciar un matiz entre lo que es VBA y VB. Visual Basic es un lenguaje de programación de objetos (OOP) y VBA es un lenguaje embebido dentro de cada programa que forma la suite. La sintaxis es prácticamente idéntica, pero el conjunto de objetos que podemos usar dentro de VBA es diferente.

¿Pero esto en que me afecta a mi?, yo sólo uso macros.

La respuesta es bastante, puesto que aunque no toquemos una línea de código con las macros generadas, estas se encuentran desarrolladas en VB. De hecho podemos ver el código de una macro y modificarlo, en el editor. VBA no es un lenguaje aparentemente complicado, pero si es necesario disponer de una serie de conocimientos de programación básicos.

Una macro no es más que una serie de instrucciones escritas en VBA que se almacenan en un módulo. Este aspecto es importante. Cuando se crea una macro y se pulsa sobre el botón Visual Basic, se puede ver un procedimiento sub sobre la carpeta módulos.

Recuerda que con las macros podemos agilizar todas nuestras tareas frecuentes, ampliando las capacidades de Excel. Para grabar una macro tendremos que emplear la utilidad grabar macros.

Un gran consejo que os puedo dar es el siguiente. Si no conoces el código de algo o no te has aprendido el DOM, puedes usar el examinador de objetos. Pero lo mejor de todo es estudiar el código generado automáticamente por una macro que tengamos almacenada. De esta forma podremos conocer muchos comportamientos y métodos que posee VBA.

Dentro del editor, tenemos la posibilidad de incorporar breakpoints y realizar depuración paso a paso. Usando la ventana inmediato mostrar resultados. Podremos ver el estado de las variables en la ventaja de inspección.

Todas las macros poseen un nombre. Podremos grabar una determinada macro con el botón de grabar macro. Nos pedirá un nombre, tecla abreviada de funcionamiento y una descripción. Para detenerla es necesario pulsar stop. Un nombre de la macro no puede ser mayor de 255 caracteres, es necesario que comience por una letra.

Dentro del editor VBA podemos usar un conjunto de objetos y llamar a sus métodos. En POO un objeto es una instancia real que queda identificado por sus estado (propiedades) y las  acciones que se pueden realizar. Y digo esto, porque en Excel, cada elemento se puede considerar un objeto.

El libro (workbook), las hojas de cada libro (worksheet), los rangos (range), las tablas dinámicas (PivotTable), gráficos (charts), etc… son todo ellos tratados como objetos.

Curioso, pero actualmente en numerosos programas informáticos todo se trata en objetos. Y el sentido no es mas que la búsqueda de la reutilización del código y la adaptación del mundo programático a la realidad visible. Que usamos. Al abstraernos y pensar en objetos, todo es más sencillo. Por otro lado el código se encapsula y se protege para su reutilización. Bueno, esto es otra historia y no quiero extenderme demasiado. Pero existen cientos de libros que hablan sobre la filosofía POO.

Continuemos…

Los objetos en Excel se agrupan y clasifican en colecciones o categorías. En Excel, la clase sheet contiene todas las celdas de esa hoja, así como el objeto workbook todas las hojas.

Existe pues una jerarquía de 192 objetos en Excel. ¿Muchos no?, no me he puesto a contarles pero que lo que figura en la documentación.

Pero esto se complica, puesto que los objetos pueden a su vez contener otros objetos y sucesivamente formando una jerarquía con 1 solo padre para cada clase.

El objeto aplicación (Application) es el más anciano por decirlo de alguna manera, de él cuelgan los libros, hojas y celdas. Todos estos objetos forman lo que se conoce como DOM o modelo de objetos de documento. Seguro que en otros lenguajes también lo has oído o leído.

La interfaz de documento en Excel (el tipo de ventanas) es SDI, lo que supone que cada libro posee su propia ventaja de aplicación con su correspondiente ribbon o cinta de opciones. Una pequeña aclaración, también existe el MDI. La principal desventaja de MDI es la escasez de información sobre las ventanas abiertas: Para ver una lista de ventanas abiertas de una aplicación MDI, el usuario normalmente tiene que seleccionar un menú específico («lista de ventanas» o algo parecido), si esta opción está disponible.

Dentro del explorador de objetos pueden existir clases y miembros. Dentro de las clases (se diferencian por los iconos), propiedades y métodos.

Esto es importante. Para hacer referencia o usar un determinado objeto (que en muchas ocasiones ya esta creado y no es necesario instanciarle puesto que Excel lo crea por nosotros automáticamente y luego se encarga de destruirlo cuando se cierra el programa), se emplea la notación:

TipoDeObjeto.Metodo(parámetros)

Por ejemplo para referenciar un libro:

Application.workbooks(“El-Archivo.xlsm”)

Para acceder a una celda:

Application.Workbooks(“El-Archivo.xlsm”).Worksheets(“hoja-de-datos”).Range(“B1”).Select

Que se puede resumir en:

Worksheets(“hoja-de-datos”).Range(“B1”).Select

Puesto que si estamos con el libro abierto se puede acceder directamente. Y más aún, si nos encontramos sobre la hoja “Hoja-de-datos”, simplemente con: Range(“B1”).Select

Recuerda que todos los objetos y métodos son en inglés.

Algunos objetos si son del mismo tipo se pueden agrupar en forma de colecciones. Gracias a las colecciones podremos referenciar un conjunto de objetos. Por ejemplo, la colección Worksheets en plural contiene todas las hojas o worksheet. Por lo que podremos automatizar tareas mediante alguna estructura de control para realizar ciertas acciones tediosas o que nos llevarían bastante tiempo realizarlas.

Para hacer referencia a un objeto determinado de una colección existen diversas técnicas.

Coleccion![Objeto]

Colección!Objeto

Coleccion(objeto)

Coleccion(variable)

Coleccion (índice)

Algunos ejemplos:

Workwooks(1).worsheets(1)

Respecto a los objetos, estos poseen propiedades, métodos y eventos que definen su comportamiento y características. Por ejemplo, algunas propiedades de una hoja pueden ser range, name, value, column, etc.

Muchas propiedades las podemos cambiar a pelo, pero recuerda que si no sabes como realizar algo, puedes grabar una macro y estudiar su código.

Por ejemplo, me gustaría cambiar el color del contenido de una celda activa a negrita, fuente 14 y color rojo. Como no se las propiedades, creo una macro llamada temporal.

Este es el código:

Sub Temporal2()

' Temporal2 Macro

Selection.Font.Bold = True

With Selection.Font

.Name = "Calibri"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

End Sub

Ahora estudio el código y trato de entender los objetos y sus propiedades.

Recuerda que con with nos simplifica tener que repetir la misma sintaxis para cada línea. Por eso se emplea.

With Selection.Font

.Color = -16776961

es lo mismo que:

Selection.Font .Color=-16776961

Es muy importante conocer la sintaxis VBA, y nos servirá de gran ayuda conocer Visual Basic. Mas que de ayuda, lo considero casi esencial, para sacar más partido y potencia a nuestras hojas.

Recuerda que algunos objetos también poseen eventos. Existen eventos diferentes en función del objeto de donde se apliquen.

Otro truco. Para acceder a la ventaja macros de forma rápida pulsa la tecla Alt-F8. Para acceder al editor VB las teclas Alt-f11.

Podemos crear también formularios dentro de Excel y que sean llamados como si fuesen “aplicaciones nativas” Windows o con su look. Esto es realmente potente y la imagen permite hacernos una idea de lo que hablo. Posibilita crear aplicaciones realmente potentes en nuestras hojas.

Seguridad en archivos VBA de Excel.

Debido a la posibilidad de crear aplicaciones realmente potentes que permitan acceder al sistema mediante VB, fue preciso para evitar ataques informáticos producidos por virus o código malicioso, crear una serie de medidas relacionadas con la seguridad.

El principal motivo para almacenar un archivo con extensión diferente es para diferenciar y ofrecer mayor seguridad. Además Microsoft ha incluido varios niveles de protección.

Las extensiones de los archivos (véase un buscador de extensiones que he desarrollado) nos brindan información importante sobre las características que definen el tipo de archivo, el formato, el tipo de datos que contiene y el programa necesario para abrirlo o visualizarlo.

Excel 2007, 2010 y 2013 almacena la información en formato Office Open XML y Excel en concreto asigna a sus archivos la extensión .XLSX. Este formato es seguro, ya que no almacena código VBA, no admite grabación de macros ni de controles ActiveX.

Para almacenar las macros, repito, es preciso utilizar el formato .XLSM.

Esto es interesante, puesto que para evitar tener que seleccionar el tipo de archivo tipo macros cada vez que guardemos un libro con macros, podemos predeterminar el formato .XSLM realizando las siguientes acciones:

Ficha Archivo, Opciones de Excel, Guardar, sección guardar libros, menú desplegable, seleccionar Guardar archivos en formato *.xlsm, aceptar.

En las versiones anteriores a Excel 2007, la presencia de macros en la hoja era imperceptible a menos que tuviéramos habilitados los niveles de seguridad referentes a las macros.

Siempre que abramos un archivo con código VBA Excel nos mostrará un mensaje debajo del ribbon indicándonos  que se ha deshabilitado parte del contenido activo.

El motivo de esto es que hace cierto tiempo se pusieron de moda los virus de Macro.

Excel toma en cuenta que los con macros pueden no ser de confianza. Excel considera que el fichero que estamos abriendo es poco seguro debido a que no se encuentra almacenado en una de las ubicaciones que la hoja califica de confianza.

Una ubicación de confianza es una carpeta de nuestro disco cuyo contenido consideramos se encuentra libre de amenazas. Cualquier archivo con código VBA y almacenado en esa ubicación abrirá con las macros habilitadas automáticamente, sin mostrar advertencias.

Para crear una ubicación de confianza tendremos que hacer los siguientes pasos:

En el botón de seguridad de Macros dentro de la ficha Desarrollador, cuadro de diálogo centro de confianza, opción ubicaciones de confianza. Pulsar sobre agregar nueva ubicación…, y en la ventana ubicación de confianza de Microsoft Office, pulsar examinar. Tendremos que aceptar los cambios. Recuerda que las modificaciones de seguridad de macros realizadas en Excel no afectan a otras aplicaciones.

Podemos observar la configuración de seguridad de Macros en Excel 2013 desde el botón Seguridad de Macros del grupo Código de la ficha desarrollador. Existen diversas opciones para distintas configuraciones.

Otra forma de ofrecer seguridad en hojas es mediante el empleo de certificados digitales. Mediante la firma del libro (precisa certificado). Para firmar el código si estamos realizando desarrollos para empresa es necesario hacer clic en el botón de Visual Basic de la ficha desarrollador. Luego dirigirnos al menú herramientas, seleccionamos firma digital, y escogemos una firma, y luego el certificado.

Volviendo al tema del desarrollo, es importante conocer las estructuras de control necesarias en VB para automatizar las tareas.

En el enlace inferior aparecerán todas las necesarias para realizar cosas interesantes y sus respectivos ejemplos. Contiene extensa documentación al respecto.

http://www.excel-avanzado.com/estructuras-control-vba