Cómo encargar una macro Excel a medida sin definir mal el proyecto

Introducción

Encargar una macro Excel a medida puede ahorrar muchas horas de trabajo, reducir errores repetitivos y convertir un procedimiento manual en una herramienta estable para la empresa. Sin embargo, el resultado no depende únicamente de que el programador conozca VBA. Una parte decisiva del proyecto consiste en definir correctamente los datos de entrada, las reglas del proceso, los resultados esperados y las posibles ampliaciones futuras.

Muchos proyectos se complican porque la macro se encarga a partir de una descripción demasiado general: «hay que combinar estos archivos», «necesitamos preparar un informe» o «queremos automatizar esta plantilla». Estas frases explican la intención, pero no definen con precisión el funcionamiento que debe implementarse.

El problema aparece cuando la estructura real de los datos no coincide con la estructura explicada inicialmente, no existe un campo que identifique de forma única cada registro, aparecen duplicidades inesperadas o las tablas crecen mucho más de lo previsto. En estas circunstancias, una macro aparentemente sencilla puede requerir numerosas modificaciones, controles adicionales y cambios de arquitectura.

Este artículo explica cómo preparar correctamente el encargo de una macro Excel a medida, qué información debe facilitarse al programador y qué errores conviene evitar antes de comenzar el desarrollo.

Índice

Por qué se definen mal algunos proyectos de macros Excel

En muchas pequeñas empresas, el procedimiento que se desea automatizar nunca ha sido documentado formalmente. Una persona conoce los archivos, aplica filtros, corrige datos, copia información entre hojas y prepara el resultado siguiendo una combinación de instrucciones escritas, experiencia y decisiones tomadas sobre la marcha.

Mientras el trabajo permanece en manos de esa persona, las ambigüedades pueden resolverse mediante criterio humano. Una macro, en cambio, necesita reglas explícitas. El programa debe saber qué archivo utilizar, qué columnas leer, cómo reconocer cada registro, qué hacer ante un dato incompleto y cuál es el resultado correcto.

También es frecuente definir el proyecto tomando como referencia un único archivo que parece representativo, pero que no contiene todas las situaciones posibles. Cuando la macro comienza a utilizarse con archivos de otros meses, clientes, departamentos o proveedores, aparecen estructuras y valores que no habían sido contemplados.

Antes de solicitar el desarrollo resulta útil revisar qué información necesita un programador para crear una macro Excel. Cuanto más completa sea la documentación inicial, menos decisiones tendrán que improvisarse durante la programación.

Definir el objetivo antes de explicar la solución

El encargo debería comenzar explicando qué problema empresarial se pretende resolver, no únicamente qué botones, hojas o formularios se desea añadir al libro. El programador necesita conocer la finalidad del proceso para poder proponer una solución adecuada y detectar contradicciones en los requisitos.

Una definición útil del objetivo puede responder a las siguientes preguntas:

• ¿Qué trabajo se realiza actualmente de forma manual?
• ¿Quién ejecuta el proceso y con qué frecuencia?
• ¿Cuánto tiempo requiere cada ejecución?
• ¿Qué errores se producen habitualmente?
• ¿Qué documentos o decisiones dependen del resultado?
• ¿Qué parte debe automatizarse y qué parte seguirá siendo manual?
• ¿Cómo se comprobará que el resultado es correcto?

Por ejemplo, «crear una macro para consolidar archivos» es una descripción insuficiente. Una definición más precisa sería: «leer los archivos mensuales enviados por doce delegaciones, validar que contienen las columnas obligatorias, eliminar registros repetidos según el identificador de operación, incorporar el nombre de la delegación y generar una tabla consolidada para preparar el informe de dirección».

La segunda formulación permite comprender la finalidad, el origen de los datos, el tratamiento necesario y el resultado esperado. Además, ayuda a distinguir entre una simple unión de archivos y un proceso de validación, normalización y consolidación.

Documentar correctamente los datos de entrada

Uno de los errores más graves consiste en proporcionar una descripción incorrecta o incompleta de la estructura de los datos. Una pequeña diferencia puede cambiar por completo el funcionamiento de la macro.

No basta con indicar que los datos se encuentran en una hoja llamada «Ventas». Conviene documentar, como mínimo:

• Nombre y tipo de cada archivo de entrada.
• Ubicación habitual de los archivos.
• Nombre de las hojas que deben procesarse.
• Fila en la que comienzan los encabezados.
• Columnas obligatorias y opcionales.
• Tipo de dato esperado en cada columna.
• Formatos posibles de fechas, importes, códigos y porcentajes.
• Existencia de fórmulas, celdas combinadas, filtros o filas ocultas.
• Posibilidad de que cambie el orden de las columnas.
• Número aproximado y máximo de registros.

También debe aclararse si la fuente es un rango convencional, una tabla estructurada de Excel, una tabla dinámica, un archivo CSV, una exportación de un programa de gestión o una combinación de varios formatos. Desde el punto de vista de VBA, estas estructuras no se tratan de la misma forma.

No confundir el ejemplo con la especificación

Un archivo de muestra ayuda a comprender el encargo, pero no sustituye a una especificación. El ejemplo puede contener diez columnas aunque el archivo real llegue algunas veces con doce, o puede mostrar códigos completos aunque en determinadas ocasiones existan valores vacíos.

Es recomendable entregar varios archivos representativos:

• Un archivo normal y completo.
• Un archivo con muchos registros.
• Un archivo con campos vacíos.
• Un archivo con duplicidades.
• Un archivo procedente de cada origen diferente.
• Un archivo antiguo si todavía debe ser compatible.

Indicar qué cambios están permitidos

Debe especificarse si la estructura de entrada es estable o si puede modificarse. Una macro que depende de posiciones fijas, como «el importe siempre está en la columna G», será frágil si un usuario inserta una columna nueva.

Cuando sea posible, conviene localizar las columnas mediante el nombre de sus encabezados, validar que existen antes de procesar los datos y detener la ejecución con un mensaje comprensible cuando falte información esencial.

Identificar el campo clave de valor único

En una tabla de datos, el campo clave es el valor que permite identificar de forma inequívoca cada registro. Puede ser un número de operación, un código de cliente, un identificador de pedido, un número de factura o una combinación de varios campos.

No especificar esta clave puede provocar errores importantes al cruzar, actualizar, consolidar o eliminar registros. La macro necesita saber cuándo dos filas representan el mismo elemento y cuándo corresponden a elementos distintos.

Una clave debe ser realmente única

Que una columna parezca única en un archivo de ejemplo no significa que lo sea en todo el sistema. Un número de factura podría repetirse entre proveedores; un código de producto podría reutilizarse en distintas empresas; y un número de pedido podría comenzar nuevamente cada año.

En estos casos, la clave real podría estar formada por varios campos:

• Proveedor más número de factura.
• Empresa más código de cliente.
• Año más número de pedido.
• Delegación más identificador local.
• Proyecto más código de tarea.

Qué debe definirse sobre la clave

• Qué columna o combinación de columnas identifica el registro.
• Si se permiten valores vacíos.
• Si existen ceros a la izquierda que deban conservarse.
• Si las mayúsculas y minúsculas se consideran diferentes.
• Si deben ignorarse espacios iniciales o finales.
• Si el identificador puede cambiar con el tiempo.
• Qué debe hacer la macro cuando encuentra una clave repetida.

Los códigos deben tratarse con especial cuidado. Excel puede convertir automáticamente determinados identificadores en números, fechas o notación científica. Un código como «000145» no debería perder sus ceros iniciales si estos forman parte de su valor real.

Analizar las duplicidades posibles

Otro error frecuente consiste en afirmar que «no hay duplicados» sin haber comprobado todos los orígenes y periodos históricos. Las duplicidades pueden aparecer por errores humanos, reenvíos de archivos, importaciones repetidas, diferencias de formato o falta de coordinación entre departamentos.

Antes de programar, es necesario definir qué se considera un duplicado. Dos filas pueden compartir el mismo código y, sin embargo, corresponder a versiones distintas de una operación. También pueden parecer diferentes porque una contiene espacios, símbolos o formatos de fecha distintos.

Tipos habituales de duplicidad

• Duplicado exacto: todos los campos relevantes contienen el mismo valor.
• Duplicado por clave: se repite el identificador único, aunque otros campos sean diferentes.
• Duplicado parcial: coinciden algunos datos, como cliente, fecha e importe.
• Duplicado de importación: el mismo archivo se procesa dos veces.
• Duplicado aparente: dos registros parecen iguales, pero representan operaciones diferentes.
• Versión actualizada: un registro sustituye a otro anterior y debe conservarse únicamente el más reciente.

Decisiones que debe tomar la empresa

El programador no debería decidir por su cuenta qué registro conservar. La empresa debe indicar la regla aplicable:

• Conservar el primer registro.
• Conservar el último.
• Conservar el registro con la fecha de actualización más reciente.
• Sumar determinados importes.
• Combinar información de varias filas.
• Marcar el conflicto para revisión manual.
• Detener el proceso hasta que se corrija el origen.

Una buena solución puede generar una hoja de incidencias donde se recojan las claves repetidas, el archivo de procedencia, el número de fila y el motivo del rechazo. De esta forma, la automatización no oculta los problemas de calidad de los datos.

Prever el crecimiento de las tablas

Una macro puede funcionar correctamente con quinientas filas y volverse lenta o inestable cuando debe procesar cien mil. Por ello, no basta con informar del volumen actual; también debe estimarse cómo crecerán los datos durante la vida útil prevista de la solución.

El tamaño afecta a decisiones técnicas como:

• El uso de arrays en memoria en lugar de recorrer celda por celda.
• La utilización de diccionarios para buscar claves.
• La conveniencia de dividir los resultados en varias hojas o archivos.
• La necesidad de evitar fórmulas volátiles.
• El tiempo máximo aceptable de ejecución.
• La gestión de memoria en equipos con recursos limitados.
• La posible migración futura a una base de datos u otra plataforma.

Preguntas necesarias sobre el crecimiento

• ¿Cuántas filas se procesan actualmente?
• ¿Cuántas se añadirán cada mes o cada año?
• ¿Se conserva todo el histórico?
• ¿Cuántos archivos pueden recibirse en una ejecución?
• ¿Aumentará el número de columnas?
• ¿Habrá nuevos departamentos, clientes o delegaciones?
• ¿Se ejecutará la macro en un equipo local o en una máquina virtual?

Una previsión no tiene que ser exacta, pero sí razonable. No es lo mismo diseñar una solución para cinco mil registros anuales que para cinco millones acumulados. Si el volumen esperado se acerca a los límites prácticos de Excel, conviene plantear desde el principio una arquitectura diferente.

Anticipar ampliaciones a corto plazo

Las necesidades futuras no siempre pueden conocerse con detalle, pero normalmente sí es posible identificar ampliaciones probables. Ignorarlas puede obligar a rehacer partes importantes de la macro pocos meses después de su entrega.

Algunas ampliaciones habituales son:

• Añadir nuevas columnas a los informes.
• Incorporar otro tipo de archivo de entrada.
• Procesar datos de más departamentos.
• Generar un fichero independiente por cliente.
• Crear informes en PDF además de libros Excel.
• Guardar los resultados en carpetas distintas.
• Aplicar reglas diferentes según la empresa o delegación.
• Incorporar nuevos gráficos o tablas dinámicas.
• Enviar automáticamente los resultados por correo.
• Registrar quién ejecutó el proceso y cuándo.

No se trata de programar desde el primer día todas las funciones imaginables. Eso aumentaría el coste y la complejidad sin una necesidad real. El objetivo es evitar decisiones rígidas que hagan innecesariamente difícil una ampliación previsible.

Diferenciar requisito actual y posibilidad futura

La documentación debería separar claramente tres categorías:

1. Obligatorio para la primera versión: funciones sin las cuales la macro no resulta útil.
2. Conveniente, pero aplazable: mejoras que pueden incorporarse en una segunda fase.
3. Posible evolución futura: ideas que deben tenerse presentes, pero todavía no están suficientemente definidas.

Esta clasificación ayuda a controlar el presupuesto y evita convertir la primera versión en un proyecto interminable.

Explicar las reglas de negocio sin dejar decisiones implícitas

Las reglas de negocio determinan cómo deben transformarse los datos. Son conocimientos que muchas veces parecen evidentes para la persona que realiza el trabajo, pero que no resultan visibles para un programador externo.

Algunos ejemplos son:

• Excluir operaciones anuladas.
• Asignar una categoría según un intervalo de importes.
• Calcular una fecha límite únicamente sobre días laborables.
• Aplicar un porcentaje distinto según el tipo de cliente.
• Considerar prioritaria una incidencia cuando concurren varias condiciones.
• Utilizar una tarifa histórica en lugar de la tarifa actual.
• Redondear importes de una forma concreta.
• Ignorar determinados registros de prueba.

Una regla debería redactarse de forma comprobable. «Destacar los importes altos» es ambiguo. «Marcar como importe alto cualquier operación superior a 10.000 euros, salvo las operaciones internas» permite programar y probar el comportamiento.

Crear una tabla de reglas

Cuando existen muchas condiciones, resulta útil preparar una tabla con las siguientes columnas:

• Número o nombre de la regla.
• Datos que intervienen.
• Condición que debe cumplirse.
• Resultado esperado.
• Excepciones.
• Ejemplo correcto.
• Responsable de validar la regla.

Esta tabla reduce discusiones posteriores y facilita la realización de pruebas. También permite distinguir los fallos de programación de las diferencias de interpretación.

Entregar ejemplos reales de entrada y salida

Una macro a medida debería desarrollarse utilizando archivos representativos y una muestra clara del resultado esperado. La plantilla de salida es tan importante como los datos de entrada.

El resultado debe definir:

• Nombre del archivo generado.
• Carpeta en la que debe guardarse.
• Hojas que debe contener.
• Orden y nombre de las columnas.
• Formatos de fechas, importes y porcentajes.
• Fórmulas que deben mantenerse.
• Filtros, tablas, gráficos o tablas dinámicas necesarios.
• Información que debe eliminarse antes de entregar el fichero.
• Protecciones que deben aplicarse.
• Comportamiento cuando ya existe un archivo con el mismo nombre.

Si la finalidad consiste en producir documentos separados, puede ser útil consultar cómo generar informes por cliente desde una plantilla de Excel o cómo crear un informe independiente por departamento. Estos procesos requieren definir nombres, rutas, permisos y criterios de separación con especial precisión.

Utilizar datos anonimizados cuando sea necesario

Los ejemplos deben conservar la estructura y las situaciones reales, pero no tienen por qué contener datos personales o confidenciales. Es posible sustituir nombres, documentos de identidad, correos, importes sensibles y referencias comerciales por valores ficticios, siempre que no se alteren los tipos de datos ni los casos que deben probarse.

Definir los errores y casos excepcionales

Una macro profesional no debe diseñarse únicamente para el escenario perfecto. También debe contemplar qué sucede cuando faltan archivos, cambian los encabezados, existen datos inválidos o no se dispone de permisos suficientes.

Conviene definir el comportamiento ante situaciones como:

• El archivo de entrada no existe.
• El archivo está abierto o bloqueado por otro usuario.
• Falta una hoja obligatoria.
• No se encuentra una columna requerida.
• Una fecha contiene texto no interpretable.
• Un importe incluye símbolos inesperados.
• La clave está vacía o repetida.
• El archivo no contiene registros.
• La carpeta de salida no existe.
• El usuario no tiene permiso para guardar el resultado.
• El nombre del archivo contiene caracteres no válidos.
• La ejecución se interrumpe antes de terminar.

Para cada caso debe decidirse si la macro debe corregir automáticamente, omitir el registro, advertir al usuario, registrar una incidencia o detener el proceso.

No ocultar los errores

Un mensaje genérico como «se ha producido un error» aporta poca información. Es preferible indicar qué archivo, hoja, fila o campo ha causado el problema y qué acción debe realizar el usuario.

También puede generarse un registro de ejecución con la fecha, duración, archivos tratados, número de filas procesadas, advertencias y errores encontrados. Este registro facilita el soporte y permite detectar problemas recurrentes en los datos de origen.

Establecer requisitos de volumen y rendimiento

La expresión «debe funcionar rápido» no constituye un requisito medible. Es necesario indicar qué duración resulta aceptable y en qué equipo se utilizará la solución.

La estimación debería considerar:

• Número habitual y máximo de archivos.
• Número habitual y máximo de filas.
• Tamaño aproximado de los libros.
• Versión de Excel y Windows.
• Arquitectura de Office de 32 o 64 bits.
• Memoria disponible en el equipo.
• Ubicación local, en red o sincronizada en la nube.
• Procesos que se ejecutan simultáneamente.
• Frecuencia de utilización.

Una macro mensual que tarda diez minutos puede ser aceptable si sustituye varias horas de trabajo. La misma duración puede resultar inadecuada para una tarea que debe repetirse cincuenta veces al día.

Cuando se desea valorar el beneficio económico, resulta útil calcular el ahorro de tiempo de una macro Excel, incluyendo no solo el tiempo de ejecución, sino también las correcciones, revisiones y errores evitados.

Considerar la seguridad y la protección de datos

Algunas macros procesan información de clientes, empleados, proveedores, candidatos o proyectos confidenciales. La definición del encargo debe incluir las restricciones de acceso y distribución de estos datos.

Es necesario aclarar:

• Quién puede ejecutar la macro.
• Quién puede consultar los archivos de entrada.
• Dónde deben guardarse los resultados.
• Si deben generarse informes distintos para cada destinatario.
• Qué columnas deben ocultarse o eliminarse.
• Si es necesario anonimizar determinados campos.
• Cuánto tiempo deben conservarse los archivos temporales.
• Si pueden utilizarse datos reales durante el desarrollo y las pruebas.

La protección mediante contraseña de una hoja puede evitar modificaciones accidentales, pero no debe confundirse con un sistema sólido de control de acceso. Cuando los requisitos de confidencialidad sean elevados, deben combinarse las medidas de Excel con permisos de carpetas, cifrado, políticas internas y una gestión adecuada de los usuarios.

Dividir el proyecto en fases verificables

Una forma eficaz de reducir errores consiste en dividir el encargo en entregas pequeñas. Así se comprueba primero la interpretación de los requisitos antes de desarrollar todas las funciones.

Fase 1: análisis del proceso

Se revisan los archivos, las reglas, los volúmenes y el resultado esperado. En esta fase deben detectarse las contradicciones y los datos que todavía no están definidos.

Fase 2: prototipo funcional

Se implementa el recorrido principal con un conjunto controlado de datos. El objetivo es validar la lógica, no completar todavía todos los formatos, mensajes y excepciones.

Fase 3: tratamiento de incidencias

Se incorporan controles de archivos, validaciones de columnas, detección de duplicidades, registros de errores y comportamientos alternativos.

Fase 4: presentación del resultado

Se ajustan formatos, nombres de hojas, anchos de columna, gráficos, tablas dinámicas, rutas de salida y demás elementos de presentación.

Fase 5: pruebas de aceptación

La empresa ejecuta la macro con casos reales y compara el resultado con ejemplos previamente validados. Las pruebas deben incluir tanto situaciones normales como errores y casos extremos.

Fase 6: entrega y documentación

La entrega debería incluir el libro o complemento, las instrucciones de uso, los requisitos técnicos, la relación de configuraciones y una descripción básica de las incidencias conocidas.

Este enfoque es especialmente útil al convertir un proceso manual de Excel en una solución automatizada, porque obliga a separar el conocimiento del usuario de las reglas que realmente puede ejecutar el programa.

Lista de comprobación antes de encargar la macro

Antes de solicitar presupuesto o autorizar el desarrollo, conviene comprobar que se dispone de la información siguiente.

Objetivo y alcance

• El problema que debe resolverse está explicado con claridad.
• Se ha indicado qué parte del proceso debe automatizarse.
• Se han separado las funciones obligatorias de las opcionales.
• Se ha definido quién utilizará la macro.
• Se conoce la frecuencia de ejecución.

Datos de entrada

• Se han entregado varios archivos representativos.
• Se han documentado hojas, columnas y tipos de datos.
• Se ha indicado qué elementos pueden cambiar.
• Se conocen los volúmenes actuales y máximos.
• Se han incluido ejemplos con errores y valores vacíos.

Identificación y duplicidades

• Se ha definido el campo clave o la clave compuesta.
• Se ha comprobado que la clave es realmente única.
• Se han identificado los tipos posibles de duplicidad.
• Se ha decidido qué hacer con cada tipo de duplicado.
• Se ha establecido cómo tratar los identificadores vacíos.

Reglas y resultados

• Las reglas de negocio están redactadas de forma comprobable.
• Se dispone de una plantilla de salida correcta.
• Se han indicado nombres, rutas y formatos.
• Se sabe qué información debe conservarse o eliminarse.
• Se han definido los criterios de aceptación.

Evolución y mantenimiento

• Se ha estimado el crecimiento de los datos.
• Se conocen las ampliaciones probables a corto plazo.
• Se ha indicado la versión de Excel utilizada.
• Se han previsto copias de seguridad y control de versiones.
• Se ha acordado cómo se gestionarán los cambios posteriores.

Errores frecuentes al solicitar una macro Excel a medida

Los problemas más habituales no suelen deberse a una única decisión equivocada, sino a la acumulación de pequeñas omisiones durante la definición del proyecto.

Dar una descripción errónea de la estructura de los datos

Cuando la estructura explicada no coincide con los archivos reales, la macro puede buscar hojas inexistentes, leer columnas equivocadas o interpretar incorrectamente los valores. La solución consiste en revisar varios archivos reales y documentar sus variaciones.

No especificar el campo clave

Sin una clave fiable, resulta difícil actualizar registros, cruzar tablas o controlar duplicados. El campo debe validarse sobre un conjunto amplio de datos, no únicamente sobre una pequeña muestra.

Desconocer las duplicidades

Negar la existencia de duplicados sin analizarlos traslada el problema a la fase de producción. Es preferible aceptar que pueden existir y definir cómo detectarlos y tratarlos.

No prever el crecimiento

Diseñar la solución únicamente para el volumen actual puede producir una macro lenta, un libro demasiado pesado o un archivo que se aproxima rápidamente a los límites prácticos de Excel.

No prever ampliaciones próximas

Una macro excesivamente rígida puede requerir una reprogramación importante cuando se incorpora un nuevo departamento, cambia la plantilla o se solicita un informe adicional.

Modificar el proceso durante el desarrollo sin controlar el alcance

Es normal descubrir mejoras mientras se construye la solución. Sin embargo, cada cambio debe analizarse, documentarse y clasificarse como corrección, requisito omitido o ampliación. De lo contrario, el proyecto puede crecer indefinidamente sin una fecha clara de finalización.

Probar solo con el archivo perfecto

Las pruebas deben incluir datos incompletos, duplicados, archivos vacíos, columnas desordenadas y valores con formatos inesperados. Una macro no está terminada únicamente porque funcione con el ejemplo utilizado durante la programación.

Conclusiones

Encargar una macro Excel a medida no consiste únicamente en describir una serie de clics que deben repetirse automáticamente. El proyecto debe definir el objetivo empresarial, la estructura de los datos, las claves de identificación, las duplicidades, las reglas de tratamiento, los resultados esperados y las condiciones excepcionales.

Los errores más costosos suelen aparecer cuando se proporciona una estructura de datos incorrecta, no se identifica un campo único, se desconocen las duplicidades o se ignoran el crecimiento y las ampliaciones próximas. Estas carencias obligan a modificar la lógica cuando el desarrollo ya está avanzado.

Una buena definición no necesita ser un documento técnico complejo. Puede prepararse mediante archivos de muestra, una tabla de columnas, una relación de reglas, ejemplos de resultados y una lista de situaciones excepcionales. Lo importante es convertir el conocimiento informal del proceso en criterios que puedan programarse y comprobarse.

Cuando el encargo se divide en fases y se valida con datos representativos, la empresa obtiene una solución más fiable, mantenible y adaptada a sus necesidades reales. También puede estimar mejor el coste, controlar las ampliaciones y evitar que una automatización útil termine convirtiéndose en una sucesión de correcciones imprevistas.

Preguntas frecuentes