Trucos y tretas en Excel VBA para programadores (usando clases y objetos)

Este post no está hecho para novatos que no sepan nada del macros de Excel, sino aquellos que saben algo de programación de Visual Basic. Si eres neófito en macros de Excel en Visual Basic, deberías ver mi post Excel VBA y macros: Una herramienta muy útil para trabajar hojas de cálculo (1).

En este post asumo que ya conoces las bases sobre cómo declarar variables a nivel de módulo y a nivel de procedimiento, y que dominas los conceptos sobre construcción de procedimientos (también llamados métodos o subrutinas).  Si no los conoces, puedes preguntar aquí.  Empecemos...

¿Se puede usar objetos en Excel VBA?

El lenguaje Visual Basic es una versión para Windows del BASIC.  Visual Basic incorpora programación estructurada y objetos, un lenguaje que se deriva del Fortran. 

Como hemos visto en post anteriores, en Visual Basic para Aplicaciones en Excel (Excel VBA) es posible programar usando programación estructurada, pero llega un momento en que manejar demasiadas cosas a la vez resulta extremadamente complicado (y además permite muchos errores).  Para simplificar, existe la programación basada en objetos (OOP, Object Oriented Programming). 

No es tan fácil crear un objeto en Excel VBA como en C++, pues tendrás que escribir mucho más, y el manejo de objetos no te permite administrar memoria con el control absoluto que el uso de punteros da en C++.  Es como tener un coche automático, donde no tienes tanto control como en los coches no automáticos.  Hay un precio que pagar.  Sin embargo, es lo que tienes en Excel y con ello tendrás que jugar.

Si hacemos un programa para establecer ajustes para impresión para una hoja de Excel, con programación estructurada basta, sin necesidad de usar OOP.  Usar OOP implica un esfuerzo de diseño y la construcción de clases, antes de que siquiera puedas probar una porción de programa.  Con Excel VBA puedes programar a la antigua para cosas pequeñas, lo cual ofrece ventajas de tiempo de programación si tu programa es pequeño y no maneja cosas muy complicadas.

Ahora bien, hay situaciones mucho más complejas donde eso no basta.  Imaginemos por un momento que vamos a programar un juego de ajedrez en Excel. Para hacerlo con programación estructurada, necesitarías almacenar toda la información de cada una de las piezas y empezar a hacer un recorrido por cada pieza, analizando las posibles actuaciones o la situación táctica de cada una, y haciendo que el código administre las conductas de todas las piezas. 

Compliquemos más el cuadro.  Si fueras a hacer un juego de carreras de coches, donde no tienes una idea clara de la cantidad de coches, calcular las estrategias y maniobras de cada coche resulta sumamente complicado, y debes reservar todo el espacio en memoria que necesitarás para esos coches.  Imagina que además de coches tienes obstáculos móviles que habrán de interactuar con los coches, y cada uno de esos obstáculos tiene su conducta propia.  Al final terminas haciendo malabarismos de alta complejidad con el código, y terminas enredándote sobremanera y las probabilidades de cometer errores se disparan. 

Lo mejor es usar OOP.  Con OOP sólo debes programar las características funcionales genéricas de un sólo coche u obstáculo (clase), y luego crear muchos coches y obstáculos (objetos) en memoria, y le darás cierta información a cada coche y obstáculo para que el cada uno analice su propia situación y decida que hacer y cómo hacerlo de manera independiente.  Entonces la cantidad de código se reduce significativamente, lo cual presenta muchas ventajas.

No voy a entrar a analizar las características y principios del OOP en detalle (pues ya hay suficientes sitios web que lo indican), sino explicarte cómo se implementa el código para objetos en VBA.  Cuando se usa OOP es evidente que tenemos que diseñar el objeto de forma suficientemente genérica para ajustarse a todos los tipos de objetos y tareas que debamos efectuar, para diseñar una sola clase y no muchas para usos particulares, y debemos prediseñar la interfaz que tendrá el objeto para comunicarse desde y hacia afuera.

Un objeto en VBA tiene lo siguiente:

• Propiedades: Son características que se pueden parametrizar.  Por ejemplo, en un coche tienes el color, la cantidad de asientos, el tipo de motor, nivel de inteligencia del chofer (para fijar la dificultad), etc.
• Métodos: Se refiere a las conductas o acciones que puede realizar.  En un coche, podrías tener virar, acelerar y frenar.
• Eventos:  Se refiere a eventos externos que desencadenan acciones internamente.  No voy a cubrir este tema.

TYPE: EL ANCESTRO DE LOS OBJETOS

Si revisamos la historia antigua del Basic, el ancestro de los objetos que conocemos hoy incorporaba sólo propiedades, y le creábamos mediante una declaración Type. 

Antes de que existiera el Type en los tiempos primitivos, si íbamos a crear un arreglo de datos para empleados, debíamos crear un arreglo de texto para nombre, otro de texto para dirección y otro numérico para el salario. Nombre, dirección y salario son características o propiedades del empleado.

Empezamos declarando los arreglos.

	Dim Nombre(100) as String
	Dim Direccion(100) as String
	Dim Salario(100) as Single

Y para usarlos, lo haríamos como se muestra abajo. Vamos a agregar los datos del quinto empleado de la lista de 100 empleados.

 
	Nombre(5) = "Juan Pérez"
	Direccion(5) = "Calle 5, Avenida 10, casa npumero 20"
	Salario(5) = 2500

Con el Type hacemos una declaración genérica de un "tipo" que contiene varias propiedades y luego asignamos el tipo al arreglo.

 
	Private Type TipoEmpleado
	    Nombre as String
	    Direccion as String
	    Salario as String
	End Type
	Dim Empleado(100) as TipoEmpleado

para usar el arreglo Type haríamos esto:

 
	Empleado(5).Nombre = "Juan Pérez"
	Empleado(5).Direccion = "Calle 5, Avenida 10, casa npumero 20"
	Empleado(5).Salario = 2500

¿Verdad que se mira más intuitivo? ¿Problemas con el Type? 

• Tiene una funcionalidad limitada.
• Todo se hace a nivel de declaración en tiempo de diseño.  Para hacer cambios en tiempo de ejecución tendrías que usar el comando Redim Preserve que es una tarea que consume mucho tiempo-máquina. 
• No puedes validar la información que ingresa en el arreglo, pues debes validar los datos ingresados en el código de programa, pues  Type no incorpora validación de información.

USANDO CLASES

¿Recuerdas cuando dije que debíamos crear el código para un objeto, como un coche o una pieza de ajedrez?  La "clase" se puede entender como "los planos para construir un objeto".  No es el objeto en sí mismo, pero con estos planos podremos construir muchos objetos en tiempo de ejecución.

Un módulo de clase es el lugar para poner el código que dará funcionalidad a los objetos. En lugar de crear un módulo normal, creamos un módulo de clase en Excel.

En ese módulo pondremos todo el código para un objeto genérico, y cuando estamos en tiempo de ejecución creamos o destruimos los objetos. Para una clase necesitarás propiedades, métodos y eventos para el objeto en cuestión.  

CREANDO LAS PROPIEDADES

Como sabemos las propiedades son datos que nos describe una cualidad del objeto, y como tal, tendremos que declarar variables a nivel de módulo.  Estas variables serán la que almacenarán la información de propiedades internamente dentro del módulo.  Son variables privadas, pues desde afuera no podrán ser accedidas.

 
	Private pNombre As String
	Private pDireccion As String
	Private pSalario As Double

Si desde afuera no podemos acceder a estas variables privadas, ¿cómo vamos a leer o asignar valores al objeto?  Usamos un tipo de procedimiento especial para extraer (Property Get) o para insertar valores (Property Let) dentro del objeto. 

• Programa principal -> Property Let -> Variable interna del objeto
• Variable interna del objeto -> Property Get -> Programa principal

Y estos procedimientos son públicos, lo cual significa que pueden accederse desde afuera.  Así, los procedimientos Property Let y Property Get tienen la única función de insertar o extraer datos en las variables privadas que se manejan dentro del objeto.  ¿Por qué no simplemente hacer públicas las variables internas?  Porque estos procedimientos permiten incorporar funciones adicionales de validación de información, o entregar sólo valores determinados a partir del contenido de las variables internas.  A estas variables internas le he agregado una "p", de modo que pNombre es la variable interna para la propiedad Nombre.

 
	''''''''''''''''''''''
	' Propiedad Nombre
	''''''''''''''''''''''
	Public Property Get Nombre() As String
	    Nombre = pNombre
	End Property
	Public Property Let Nombre(Valor As String)
	    pNombre = Valor
	End Property

	''''''''''''''''''''''
	' Propiedad Direccion
	''''''''''''''''''''''
	Public Property Get Direccion() As String
	    Direccion= pDireccion
	End Property
	Public Property Let Direccion(Valor As String)
	    pDireccion = Valor
	End Property

	''''''''''''''''''''''
	' Propiedad Salario
	''''''''''''''''''''''
	Public Property Get Salario() As Double
	    Salario = pSalario
	End Property
	Public Property Let Salario(Valor As Double)
	    pSalario = Valor
	End Property 

Ahora bien, imaginemos que el usuario ingresa un número negativo.  Tendrías que validar que el salario sea un número positivo.

 
	Public Property Let Salario(Valor As Double)
	    pSalario = Abs(Valor)
	End Property 

Como vemos, el objeto acepta un valor numérico, de modo que si le envían un valor de texto, tendrás un error que Excel ubicará en la clase, cuando en realidad viene del código del programa principal. Entonces podrías hacer que el objeto reciba cualquier valor, mediante una variable de tipo Variant, y si el valor ingresado es texto, entonces activar el código que maneja errores.

 
	Public Property Let Salario(Valor As Variant)
	    If IsNumeric(Valor) Then
	        pSalario = Abs(Valor)
	    Else
	        'Agregar aquí el código para manejo de error
	    End If
	End Property 

Como ves, puedes incorporar código para validación y manejo de errores en los procedimientos que reciben valores o envían valores desde el objeto que has creado.  Además, si creas sólo el código para el procedimiento Property Get (omitiendo el de Property Let) entonces tienes una propiedad que es sólo para lectura.  Pensemos que al salario se le aplicará un 9% de deducciones salariales, un valor que realmente no debería ser insertado en el objeto Empleado desde afuera, sino calculado a partir del salario.

 
	Public Property Get Deducciones()
	    Deducciones = pSalario * 0.09
	End Property 

CREANDO LOS MÉTODOS

Una vez que ya tienes claras las propiedades, es tiempo de agregar los métodos.  Los métodos son procedimientos ordinarios, pero trabajarán con los datos del objeto.  En un coche, virar a la derecha o a la izquierda, es un método, una conducta, una acción que tiene lugar en el objeto, y como podrás imaginar, si creas múltiples coches, cada uno conducirá por cuenta propia.  Si tenemos un objeto llamado empleado, al imprimir el comprobante, en realidad estás imprimiendo el comprobante para el empleado que está cubierto por el objeto que creaste.

 
	Public Sub ImprimirComprobanteDePago() 
	    'Agregar código para imprimir comprobante aquí
	End Sub 

PONER NOMBRE A LA CLASE

Aún no hemos terminado.  Todavía falta ponerle nombre a la clase, para que pueda ser llamada desde afuera, para crear objetos usando esta clase. En la propiedad (Name) del editor, pondré el nombre cEmpleado.

Estamos listos para usar la clase.

USANDO LA CLASE

En el programa principal usaremos la clase para crear objetos en tiempo de ejecución.  Lo primero es declarar la variable que contendrá el objeto.

 
	Dim Empleado As cEmpleado

Dentro del procedimiento del programa principal tendrías que crear el objeto.  Como puedes ver, se usa el compando Set, y la palabra New indica que estás creando un nuevo objeto en memoria.

 
	Set Empleado = New CEmployee

Y luego puedes usar el objeto.

 
	Empleado.Nombre = "Juan Pérez"
	Empleado.Direccion = "Calle 5, Avenida 10, casa npumero 20"
	Empleado.Salario = 2500
        DeduccionesDelEmpleado = Empleado.Deducciones
	ImprimirComprobanteDePago

Cuando ya hayas dejado de usar el objeto, y antes de terminar el programa, sería bueno que destruyas el objeto para que no ocupe memoria. 

 
	Set Empleado = Nothing

Dejar objetos sin destruir en memoria al terminar el programa, se conoce como "memory leak", y es una buena manera de desperdiciar memoria RAM que seguramente ocuparás luego.

USAR OBJETOS DE EXCEL

No sólo puedes crear objetos para manejar clases hechas por tí, sino que también puedes crear objetos usando objetos existentes en Excel o usando clases que ya vienen dadas por Excel.  Veamos por ejemplo este código que te permite enviar un correo electrónico.

Sub SendEmail(sTo As String, sSubject As String, sBody As String, sAttachmentFilename As String)
    On Error GoTo EmailError
    Dim oLook As Object
    Dim oMail As Object
    
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    With oMail
        .To = sTo
        .body = sBody
        .Subject = sSubject
        .Attachments.Add (sAttachmentFilename)
        .Send
    End With
   
    Set oMail = Nothing
    Set oLook = Nothing
    Exit Sub
EmailError:
    On Error Resume Next
    AddErrorMessage "Unable to send email"
End Sub

Miramos que el objeto oLook creado como Object, y que en lugar de usar New como se usaría con una clase creada por tí, usa CreateObject, que crea un objeto "manejador de Outlook", que tiene un método llamado createitem con el cual se crea el objeto oMail que tiene las propiedades To, body, Subject y permite usar los métodos Attachments.Add y Send.  Parece un poco complicado al inicio, pero en realidad sucede que un objeto puede crear otros objetos dentro de él, y así tienes el manejador de Outlook y el manejador de correos individuales.

Miramos el código del manejador de portapapeles para Excel.

 Dim doClip As DataObject

    Sub CrearClipboard()
        Set doClip = New DataObject
    End Sub

    Sub DestruirClipboard()
        Set doClip = Nothing
    End Sub

    Sub CopiarAlClipboard(sTexto As String)
        doClip.Clear
        doClip.SetText sTexto
        doClip.PutInClipboard
    End Sub

    Function ClipboardTexto() As String
        doClip.GetFromClipboard
        ClipboardTexto = doClip.GetText
    End Function

Como podemos observar, existe la clase DataObject que te permite crear un manejador de portapapeles.  Cuando creas o destruyes el manejador del portapapeles, no estás creando o destruyendo el portapapeles, sino que estás creando o destruyendo un objeto que te permite comunicarte con el portapapeles.

CLASES PARA CREAR MANEJADORES DE HOJAS DE EXCEL

El concepto de "manejador" también puede servirte cuando tienes que manejar múltiples hojas de Excel.  Si cada objeto maneja un libro de Excel, y al usar el objeto, te mueves al libro de Excel correspondiente, puedes saber exactamente en qué libro estás trabajando.  Y también tiene la ventaja de que puedes trasladar valores entre libros de Excel de manera más efectiva y sin mucho papeleo.

    Set Reporte = New clsExcelFile
    Set HojaDeMacros = New clsExcelFile
    Set Transacciones = New clsExcelFile

    HojaDeMacros.NombreDeLibro = HojaDeMacros.NombreDeLibroActual
    Transacciones.NombreDeLibro = "Transacciones.xlsx"
    Reporte.NombreDeLibro = "Reporte_Anual.xlsx"

    HojaDeMacros CargarArchivoTransacciones
    Transacciones.CrearReporte Reporte.NombreDeLibro
    Reporte.ImpuestoDeRenta = HojaDeMacros.ParámetroImpuestos
    Reporte.ImprimirReporte
    Transacciones.CerrarArchivo
    HojaDeMacros.CerrarArchivo

    Set HojaDeMacros = Nothing
    Set Transacciones = Nothing

En el ejemplo anterior tenemos una clase que es un manejador de libros de Excel, con las siguientes propiedades:

• NombreDeLibro
• ImpuestoDeRenta

Y tiene los siguientes métodos:

• ParámetroImpuestos: Busca el parámetro de la tasa de impuesto a pagar en la hoja de Excel.
• NombreDeLibroActual: Busca el nombre del libro de Excel que está activo.
• CargarArchivoTransacciones: Busca y carga el libro de Excel de transacciones en una ubicación determinada
• CerrarArchivo: Cierra el libro.
• CrearReporte: Crea un libro nuevo de Excel y crea la plantilla de reporte con datos de transaciones.
• ImprimirReporte: Hace los cálculos usando el parámetro de impuestos e imprime.

Entonces, con una sola clase, manejamos tres libros de Excel, y nunca tenemos confusión acerca qué archivos estamos manejando.  El programa principal es muy corto y usa 3 objetos, donde cada uno maneja un libro de Excel particular.  Y como puedes ver, sólo cargamos el archivo con las macros, las transacciones se cargan automáticamente, y el reporte se genera automáticamente.

OTROS USOS PARA LAS CLASES

Imagina que tienes una lista de productos, y una lista de tipos de cliente.  Determinados productos se ofrecen a determinados tipos de cliente, porque de otro modo los productos podrían no satisfacer las necesidades de los clientes.  Tienes una lista de los productos que se vendió a determinados clientes cuyo tipo ya conoces.  Quieres determinar si hubo alguna venta de producto que no satisface a clientes.  Entonces tienes que sacar cada combinación de producto con el tipo de cliente y comparar para ver si hay alguna combinación que calce con las ventas realizadas.  Normalmente este proceso sería muy complicado y engorroso de programar con programación estructurada, de modo que puedes crear un manejador de listas, ya sea de productos o de tipos de cliente, para hacerte más fácil el trabajo.

En general el uso de objetos se usa cuando tienes que hacer malabares con datos y donde hay muchos entes similares que deben ser manejados a la vez o cuya interacción necesita ser evaluada o simulada.

Espero que te sirva.  No estoy seguro de si logré explicar con claridad, pero si no es así, puedes anotar tus consultas y con gusto buscaré una manera de hacer la explicación más clara.