Introducción a la programación en VBA para Excel

¿Qué es VBA Excel?

Microsoft Visual Basic para aplicaciones, llamado para acortar mediante las iniciales VBA, es un lenguaje de programación basado en macros para desarrollar aplicaciones en el sistema operativo Windows. Las macros se pueden utilizar para varios programas de la suite de Microsoft Office, ampliando así las funcionalidades que tienen por defecto estas aplicaciones.

Una macro programada en VBA es muy útil cuando se necesita automatizar una tarea repetitiva que hay que realizar varias veces al día. De este modo se ahorra muchísimo tiempo y permite al usuario concentrarse en otras tareas más importantes.

VBA, retrospectiva de sus orígenes y potencial

En la época de los ochenta y los noventa los lenguajes de programación no tenían un entorno gráfico que permitiese agilizar el trabajo, incluso el programa BASIC, que se corresponde con las iniciales de Beginner’s All-purpose Symbolic Instruction Code, no poseía un interface gráfico.

El siguiente paso fue la creación de Visual Basic de la mano de Alan Cooper, que ofrecía la posibilidad de diseñar la apariencia de las aplicaciones programadas. En el año 1993 Microsoft incluyó este lenguaje en el Excel y, por extensión, en toda la suite de Office, consiguiendo unificar todos los lenguajes anteriores que ampliaban las funcionalidades de estos programas en uno solo. Esto significó conseguir más consistencia, fiabilidad y transversalidad.

 

Estructura

Al ser un lenguaje de programación completo permite la creación de aplicaciones a medida o soluciones con el límite que ponga la imaginación. Además, cuenta con una amplia galería de métodos ya desarrollados para hacer pequeñas acciones que suelen ser necesarias. Por ejemplo, encontrar un símbolo entre dos textos, invertir el orden, separar contenido y un amplio etcétera.

VBA para aplicaciones es una poderosa herramienta para añadir versatilidad al Excel, convirtiendo las hojas de cálculo en una solución a medida que se adapta como un guante a las necesidades de cada caso. Por eso, es interesante recopilar los principales métodos y objetos existentes, las limitaciones de la memoria y las restricciones que tienen las hojas de cálculo así como los libros.

Repaso de los principales objetos y sus métodos en el entorno Excel VBA
Como en gran cantidad de lenguajes de programación, en Excel existen muchos objetos. Están organizados con el denominado modelo de objetos de Excel, que jerarquiza todo el ecosistema existente.

 

Los objetos en Excel

En un primer nivel se sitúa la aplicación en sí, de la que cuelgan los libros, los gráficos y las hojas de cálculo. De esta última dependen 4 posibles objetos, las columnas, las filas, las celdas y los rangos.

Todos estos objetos tienen sus métodos y sus propiedades. La definición de ambas sería:

Propiedades: Características que posee el objeto.

Métodos: Las acciones que puede desempeñar.

– Las propiedades de los objetos

Al igual que un objeto cotidiano como un smartphone tiene sus propiedades, como podrían ser el color, el material, el peso o la forma, en Excel tienen sus propiedades. Por ejemplo, una celda puede tener la propiedad address para indicar que es una dirección el texto que contiene o value, que indicaría su valor.

Las propiedades describen a los objetos, como los adjetivos a los nombres (que serían los objetos en este símil) en el lenguaje, y les atribuyen:

  • Cualidades.
  • Características.
  • Atributos.

Las propiedades que se van aplicando determinan que el objeto:

  • Se pueda ver o que no esté visible.
  • En el caso de estar visible cómo se vería.
  • Definen el comportamiento del mismo.

Se puede complicar un poco la compresión de un comportamiento en función de la complejidad de cada caso, puesto que cada objeto puede tener varias propiedades que moldearán su forma de actuar.

Las acciones que se pueden realizar sobre las propiedades son las siguientes:

1. Leerlas

Al realizar esta operación se consigue leer la configuración actual del objeto. Aunque también hay que tener en cuenta que las propiedades se pueden usar para devolver objetos, puesto que a la gran mayoría de los objetos se accede a través de las propiedades.

2. Modificarlas

Consistirá en cambiar la configuración en curso por otra diferente.

Los métodos de los objetos

Siguiendo con el ejemplo del teléfono móvil, sus métodos serían su capacidad de procesamiento, el tipo de aplicaciones que puede ejecutar, el nivel de seguridad que ofrece, las características de su cámara, etcétera.

Es muy importante conocer cómo se conforman las declaraciones que llaman a los diferentes métodos del objeto. Hay 3 elementos que se deben utilizar:

  1. El nombre del objeto VBA
  2. Un punto
  3. El nombre del método

De tal modo que la instrucción quedaría de la siguiente manera:

My_Worksheet.Delete

También hay que tener en cuenta que un mismo método puede comportase de forma distintita en función del objeto con el que esté relacionado. Se puede ilustrar este caso siguiendo con la instrucción delete. En ambas situaciones el resultado final será que se elimina ese objeto, la diferencia está en el proceso para llevarlo a cabo.

  • Supuesto sobre un objeto range.

Cuando está relacionada con el objeto range hay un argumento adicional en la instrucción que determina cómo se desplazarán todas las celdas que sustituirán a las eliminadas.

  • Supuesto sobre una hoja de cálculo.

En este caso aparecerá una ventana que solicitará la confirmación de la acción de borrar una hoja entera.

En el Excel, una celda tiene métodos como activate (activar), calculate (calcular), clear (borrar) y otros muchos.

Los parámetros o argumentos de los métodos

En este momento es interesante introducir el concepto de argumento, también llamado parámetro para los métodos. Son utilizados para definir con más detalle la acción que realizará el método sobre el objeto. No todos los objetos admiten parámetros y estos pueden ser opcionales u obligatorios.

Un ejemplo de uso sería en con el método Worksheet.Copy que tiene 2 parámetros excluyentes entre sí:

  1. After (antes), para especificar antes de qué punto se copiará la hoja seleccionada.
  2. Before (después), que en este caso tomará la referencia indicada en el código para insertar después la hoja deseada.

La sintaxis para especificar los parámetros de un objeto se puede escribir usando diferentes métodos:

1. Sintaxis básica

Tras comprobar el método del objeto hay que añadir un espacio tras el nombre del objeto e indicar a continuación los valores que se necesiten.

Esta forma de programar no hace honor al nombre de básica, puesto que la legibilidad del código es bastante confusa y, en los casos de programas extensos, puede ser imposible intentar comprenderlos de forma global.

2. Argumentos con nombre

Para resolver el problema anterior, se utiliza la referencia al objeto seguido de un espacio, hasta ahora la forma es igual que antes, se añaden dos puntos y el símbolo de igual (:=) y a continuación se añaden los parámetros.

– Cómo acceder a los métodos de un objeto concreto

Un objeto puede tener más de un método vinculado a él, es común que sean bastantes. El objeto range admite hasta 80 métodos distintos. Lo normal es que sean necesarios una relativa pequeña cantidad de parámetros y estos se asocien a múltiples objetos. Pero debido a las propias vicisitudes de las necesidades de programación es muy posible que sea necesaria la búsqueda de parámetros no habituales, en ese caso hay varias tácticas de búsqueda:

  • Mostrar la lista de métodos

La vasta abundancia de propiedades y métodos de los objetos hace muy complicada la memorización de todos ellos. Los que más uso tienen en cada caso serán los más fáciles de recordar, para el resto, el programa abre una lista de sugerencias a medida que se va escribiendo el programa. De tal modo que se pueden ir completando líneas de código de una manera más sencilla y, además, se pueden evitar errores al escribirlos. Posee también una ayuda visual mediante el uso de 2 tipos diferentes de iconos, que representan métodos y propiedades, para evitar confusiones.

Es la más sencilla de utilizar, sin embargo es necesario habilitar la «configuración de miembros de la lista automática».

  • Utilizar el navegador de objetos

Este navegador es una pantalla que contiene las propiedades y métodos disponibles para un objeto determinado. Su uso es sencillo y se puede explicar en 3 pasos:

  1. Lo primero es abrir el navegador de objetos, para ello existen varios caminos:

o Uno de ellos es haciendo clic en el botón del navegador de objetos en la barra de herramientas.

o La segunda forma de acceder es a través del menú «Ver» (view en inglés) y hacer clic en el texto «navegador de objetos».

o El último es presionando la tecla «F2».

  1. Seleccionar la biblioteca para el Excel.

En la parte superior izquierda está situado un desplegable con todas las librerías disponibles. De todas las opciones se ha de hacer clic en «Excel».

  1. Elegir el objeto deseado.

Ahora aparecerá una ventana divida verticalmente en dos partes. La de la izquierda contiene una relación de todos los objetos disponibles y la de la derecha se va actualizando dinámicamente para mostrar los métodos disponibles según se vayan eligiendo objetos de la parte izquierda.

  • Usar la red de desarrolladores de Microsoft

Este centro de desarrollo tiene disponible una gran cantidad de información, entre todo este contenido se encuentran los objetos y los métodos. Para acceder a la red de desarrolladores hay que seguir estos 4 pasos:

  1. Escribir en el editor el nombre del objeto.
  2. Colocar el cursor en el texto escrito.
  3. Presionar la tecla «F1».
  4. En la página que se abre, del centro de desarrollo, elegir el objeto deseado.

Es común que haya que navegar un poco para encontrarlo porque a los objetos se accede a través de la propiedad. Sin embargo, la navegación durante la búsqueda es sencilla y rápida.

Limitaciones de las hojas de cálculo y los libros

El lenguaje VBA es muy potente y versátil, capaz de adaptarse a prácticamente todas las necesidades y circunstancias, sin embargo, las versiones de Excel tienen ciertas limitaciones que están relacionadas con los libros y con las hojas de cálculo que se pueden crear y gestionar.

Dependiendo de la versión de Excel de la que se trate las limitaciones son unas u otras. Por eso se puede hacer una relación de limitaciones distinguiendo entre las versiones más recientes, correspondientes a los años 2013, 2016 y 2019 y las versiones más antiguas, concretamente las del año 2010.

A continuación se expone una tabla con los principales parámetros, los mismos en ambas comparaciones:

  • Versiones más recientes (Excel 2019, Excel 2016 y Excel 2013)

o Libros abiertos.

Esta características depende exclusivamente de los recursos disponibles del sistema y de la cantidad de memoria que se pueda utilizar.

o Número total de filas y de columnas de una hoja de cálculo.

La cantidad establecida es de 1.048.576 filas por 16.384 columnas.

o Ancho de columna.

La dimensión horizontal de la columna está establecida en un máximo de 255 caracteres.

o Alto de fila.

La medida vertical tiene como límite 409 puntos.

o Saltos de página.

En total, para los horizontales y los verticales hay un máximo de 1026.

o Número total de caracteres que puede contener una celda.

El tope son 32.767 caracteres.

o Hojas en un libro.

El valor por defecto es 1 hoja. El máximo vuelve a estar limitado por la cantidad de memoria disponible en el sistema.

o Formatos o estilos de celdas distintos.

64 000 opciones distintas es capaz de gestionar.

o Hipervínculos en una hoja de cálculo.

En este caso pueden incluirse un total de 66 530 enlaces.

o Niveles de deshacer.

«Recuerda» un máximo de 100 niveles.

  • Versión más antigua: Excel 2010

o Libros abiertos.

Al igual que en las versiones más modernas la limitación depende de los recursos del sistema.

o Número total de filas y de columnas de una hoja de cálculo.

  1. 576 filas por 16.384 columnas en total.

o Ancho de columna.

255 caracteres.

o Alto de fila.

409 puntos.

o Saltos de página.

1026 entre los verticales y horizontales.

o Número total de caracteres que puede contener una celda.

Como mucho 32 767 caracteres.

o Hojas en un libro.

De forma predeterminada se crean 3 hojas, el máximo dependerá de la memoria disponible en el ordenador.

o Formatos o estilos de celdas distintos.

64 000 opciones.

o Hipervínculos en una hoja de cálculo.

66 530 hipervínculos.

o Niveles de deshacer.

100 niveles para poder ir hacia atrás.

Limitaciones de memoria en Excel

Al igual que en las hojas de cálculo y los libros, la cantidad de memoria que son capaces de gestionar depende según la antigüedad de las versiones de Excel y también de si son de 32 o de 64 bits.

Versiones antiguas de Excel

Para las versiones anteriores al año 2002 la limitación de memoria es de 64 MB. La memoria disponible se amplió al doble en el Excel que se lanzó en el año 2002, pudiendo trabajar con hasta 128 MB. El Microsoft Office 2003, puesto en el mercado en ese mismo año, tiene una limitación de 1 GB, se multiplicó por 3 la capacidad de gestión de memoria dando un salto significativo con respecto a sus predecesores.

Las versiones de 32 bits: capacidades, limitaciones y ampliaciones

Esta versión tiene una limitación de memoria virtual de 2 GB, muy poco comparado con la versión de 64 bits. A pesar de ello muchas personas se decantan por la versión de 32 bits porque hay muchos add-on y controladores que no están disponibles en la otra versión.

– Razones por las que utilizar la versión de 32 bits

A pesar de las limitaciones de memoria de esta versión, este motivo no es siempre el que determina la decisión. Los desarrolladores y las personas que integran los equipos de tecnología de las empresas deberían considerar instalarla por los siguientes motivos:

  • Tiene complementos COM de 32 bits, sin posibilidad de usar los de 64.

Es posible ejecutar estos complementos COM de 32 bits en un Office de 32 bits instalado en un Windows de 64 bits. Mediante un proveedor de complementos COM se podría intentar instalar la versión de 64 bits.

  • Utiliza controles de 32 bits, sin una alternativa de 64.

Se pueden ejecutar los controles estándar de Microsoft Windows, como por ejemplo comctl.ocx o Mscomctl.ocx, e incluso cualquier control de un tercero.

  • Su código utiliza declaraciones Declare.

La mayoría del código VBA no necesita adaptarse si se está usando en una versión o en otra, pero en el caso de este tipo de declaraciones usadas para llamar a la API de Windows. Se podría compatibilizar su uso agregando PtrSafe a Declare y reemplazarlo por LongPtr, de este modo se podría usar en ambos modos (32 y 64).

Sin embargo, pueden darse casos inusuales en los que no exista una API de 64 bits para declarar.

  • Activar un objeto OLE de 32 bits

Es posible seguir ejecutando la aplicación de servidor OLE con una versión de Office 32 bits.

  • El sistema «large address aware» (LAA)

Las versiones de 32 bits de Excel 2013 y 2016 tienen habilitado el LAA, con lo que se logra minimizar los errores provocados por la falta de memoria.

Existen además los LAA dobles, incrementando de 2 GB a 4 GB en las versiones de 64 bits de Windows. En los Windows de 32 bits la memoria se aumenta de 2 GB a 3 GB.

Hay que tener en cuenta que Excel 2013 y 2016 podrán utilizar el LAA después de instalar las últimas versiones.

Las versiones de 64 bits: capacidades
Estas versiones pueden acceder a bastante más memoria que las de 32 bits, hasta a 18,4 millones de petabytes (1 petabyte son 1000 TB). En el caso de prever la necesidad de generar archivos muy grandes de Excel o si la previsión de trabajo exige almacenar gran cantidad de datos la versión de 64 bits, que funcionará instalada en un Windows de 64 bits, es la mejor opción.

Los usuarios expertos de Excel, que necesitan trabajar con hojas muy complejas se decantan por la versión de 64 bits, tanto de Office 2013 como 2016 debido a la falta de las fuertes restricciones de tamaño de archivo que establecen las versiones de 32 bits.

– Razones para usar la versión de 64 bits:

  • Para trabajos que precisan gran cantidad de datos.

Como las hojas de cálculo que necesitan grandes empresas para realizar cálculos complejos, tablas dinámicas o conexiones externas.

  • Para insertar gran cantidad de imágenes, gadget o elementos gráficos.

En estos casos es necesario que no haya una limitación tan estricta de tamaño de archivo ni de memoria, por eso se aconseja el uso de la versión de 64 bits.

  • No es necesario el uso de extensiones

En la otra versión es necesario instalar ciertos complementos, controles (como los activeX) y extensiones. La verdad es que ambas versiones son apenas indistinguibles a primera vista, esta versión de 64 bits es más fácil de configurar porque no necesita todos estos complementos extra y actualizaciones para rebajar un poco sus limitaciones en cuanto a memoria.

  • Trabaja con archivos de más de 2 GB en Project.

Es muy útil en los casos en los que de un mismo proyecto cuelgan una gran cantidad de subproyectos.

  • Desarrollo de soluciones de Office

Con la programación de complementos o personalización de estos documentos los desarrolladores pueden ofrecer tanto la versión de 64 como la de 32 bits de estas soluciones creadas. Además, durante la programación se puede acceder a la versión de 64 bits para hacer test de funcionamiento o para actualizarlas.

 

Conclusiones

El lenguaje VBA Excel es un lenguaje potente que permite potenciar extraordinariamente las fuciones de excel, creando nuevas funcionalidades totalmente personalizadas a nuestras necesidades. Por este motivo, es una opción a tner muy en cuenta tanto en entorno de usuario avanzado como en entorno empresarial.

¡ Ya sólo nos queda desearte una feliz programación en Visual Basic para Excel !.