Categorías
Ofimática

Menús personalizados en Excel [VBA]

Un punto a favor de Excel es que sumando la potencia del propio programa de hoja de cálculo y la de Visual Basic podemos crear verdaderas maravillas. Cuadros de mando, facturas, estadísticas… se abre todo un abanico de posibilidades cuando empezamos picar código. Para poder darle un toque más elegante vamos a crear menús personalizados en Excel.

Existen un montón de maneras de personalizar el Ribbon (menú superior) de Excel pero muy pocas son fáciles y las que lo son no nos ofrecen la seguridad de que funcionen en todos los sistemas o (lo más común) que no perdamos los cambios una vez el archivo pase por varias manos, lo enviemos a otra persona, modificaciones… En este caso vamos a seguir un método que me ha funcionado muy bien durante los últimos años y que he probado hasta la saciedad con diferentes versiones de Office (2007, 2013…). No es un proceso extremadamente sencillo pero con un poquito de paciencia conseguiréis grandes resultados.

¿Para qué necesitamos un menú personalizado?

Realmente esta es una pregunta muy lógica teniendo en cuenta que, por defecto, Excel trae casi todo lo que se necesita de forma fácil y bien organizada. El problema surge cuando tenemos macros añadidas a nuestra hoja de cálculo y queremos llamar a esas funciones o subrutinas.

Seguramente os habréis encontrado con muchos archivos Excel que tienen sus propias funciones y que, para lanzarlas, utilizan botones embebidos en las propias hojas. Esta forma de hacerlo es perfectamente válida y en muchas ocasiones es la más recomendable. Si lo que estamos diseñando es algo muy complejo, con muchos botones y muchas opciones, lo mejor es dedicar una hoja completa al «cuadro de control» para disponer en ella todas las opciones necesarias.

Ejemplo Botones Excel

En cambio, la situación anterior no es la más usual, no habitual es necesitas unas pocas llamadas: «actualizar», «cargar datos», «crear informe», «exportar y enviar por correo», etc. En estos casos no merece la pena ensuciar los diseños y lo ideal es crear el «menú especial» para este Excel.

Las funciones y macros de Excel: El formato «.xlsm»

Como he comentado, la necesidad de disponer de un menú superior personalizado es poder llamar a nuestras funcionas VBA personalizadas. Para poder disponer de dichas funciones en nuestro archivo lo primero es crearlas y programarlas (Podemos tomar como ejemplo las últimas funciones que he publicado aquí como enviar un correo o cualquier otra que se nos ocurra) y posteriormente guardar el archivo en un formato compatible con ellas.

Guardar Excel Como Libro Para Macros

Para eso Excel ha creado el formato «.xlsm» (Libro de Excel habilitado para macros) así que, una vez tengamos todo a punto, necesitamos guardarlo con esta extensión. Este es nuestro punto de partida: un archivo Excel con funciones y macros guardado en formato «.xlsm«.

De XLSM a ZIP

Cuando las hojas de cálculo empezaron a rondar los escritorios de medio mundo los archivos ocupaban pocos KB (o incluso algún MB). Era la época del formato «.xls» y no era necesario preocuparse mucho por el espacio ni de como se las arreglaba el sistema para almacenar la información. Cuando esto cambió y las hojas empezaron a crecer (se superaban con facilidad las decenas y centenas de MB) nació el formato «.xlsx» y su hermano con macros «.xlsm«. Estos formatos no son más que archivos Excel comprimidos (Esto es un poco «inexacto» pero es una explicación simple).

Para poder modificar el menú de forma permanente necesitamos descomprimir el archivo «.xlsm» y cambiar un poco su estructura, así que cambiamos la extensión».xlsm» a».zip«. Para poder realizar este paso es necesario tener configurado el sistema para que nos muestre las extensiones de los archivos (una búsqueda rápida en Google) y seguramente Windows nos alertará de que este proceso es peligroso, aceptamos igualmente.

Cambiar extensión xlsm a zip

Una vez cambiada la extensión abrimos con nuestro gestor de archivos comprimidos favorito (o el propio de Windows) el archivo que acabamos de crear y veremos algunas carpetas y archivos. Esos son los que necesitamos modificar.

Archivo xlsm descomprimido

La carpeta «_rels«: El archivo .rels

En esta carpeta tenemos la configuración base del archivo. En este archivo se define qué se necesita para trabajar con él. Dentro de esa carpeta tenemos el archivo «.rels«: un XML con la relación de todos los componentes a cargar cuando abrimos nuestro archivo en Excel. Lo que vamos a hacer es extraer ese archivo (el escritorio por ejemplo) y modificarlo.

El archivo base tiene una forma similar a esta (Puede variar en vuestro archivo, seguramente no estará indentado y lo veréis de corrido pero no hay problema por cambiarlo):

Ahora vamos a añadir el menú personalizado. Para esto (veremos después cómo configurarlo) tenemos que añadir la siguiente línea:

Si lo explicamos por partes sólo necesitaríamos saber que el «id» tiene que ser único y que el «Target» es la ruta al archivo de configuración de la relación que vamos a cargar. Una vez modificado nos quedará con un archivo «.rels» parecido a este:

Guardamos nuestras modificaciones y cargamos de nuevo este archivo modificado a la carpeta «_rels» de nuestro zip sobreescribiendo el «.rels» que tiene. Lo que hemos conseguido con esta línea es indicarle a Excel que, además de los componentes necesarios para manejar nuestro archivo, añada una nueva extensión al Ribbon. Vamos a ver en el siguiente paso como configurar ese menú.

Configuración del menú personalizado

Una vez hemos actualizado el archivo de relaciones toca añadir una nueva carpeta con la configuración del nuevo menú. Para ello crearemos una con el nombre «customUI» y dentro meteremos un fichero con el nombre «customUI.xml». Es muy importante que los archivos y carpetas lleven ese nombre exacto (mayúsculas y minúsculas incluídas) ya que como hemos visto en el punto anterior, ésta es la ruta que hemos indicado en el archivo de relaciones y podremos encontrarnos con algún problema si no lo hacemos así.

El archivo «customUI.xml» es un XML con la configuración necesaria para nuestro menú personalizado. Los menús en el Ribbon se jerarquizan en:

Pestañas, grupos y botones en Excel

  • Tab: Cada una de las pestañas que vemos en la cinta superior de Excel. Así tenemos «INICIO», «INSERTAR», «FÓRMULAS»..
  • Group: Si nos fijamos, en cada pestaña, los botones están agrupados por «categorías» asociando así un conjunto de botones. Por ejemplo: En «INICIO» tenemos los grupos «Fuente», «Número» o «Celdas»
  • Button: Cada uno de los botones en sí.

El XML que necesitamos tendrá la siguiente «base»:

Con esto podríais crear ya un menú personalizado sin problema, sustituyendo los «label» de cada nombre pero vamos a definir algo más para que podáis llegar más al fondo. Como podréis ver cada tab, group y button tienen muchos valores comunes:

  • id: Tiene que ser único. Recomiendo que sigáis el mismo esquema numérico que he puesto yo ya que, si el menú se hace largo, evita confusiones.
  • label: El nombre que aparecerá en el botón, pestaña o grupo.
  • size: (Sólo para botones) Existen varios tipos en función del tamaño que queramos en nuestros botones. «large» para botones grandes y «small» para pequeños.
  • imageMSO: (Sólo para botones) Imagen que se verá en el botón. No podemos personalizar lo que queramos (o no es fácil) pero tenemos una gran variedad y podemos ver una galería completa online para decidirnos.
  • onAction: (Sólo para botones) Este es el parámetro más importante. Es el nombre de la función o subrutina de nuestro código VBA que llamaremos. Es importante que el nombre sea exacto.

Y con esto añadimos el archivo y la carpeta a nuestro archivo que tenemos abierto y listo. Cambiamos de nuevo nuestro .zip a xlsm y ya podemos ver nuestro nuevo menú.

Cambios en las funciones VBA para adaptarlas al menú personalizado

Nos encontraremos con un error si dejamos el menú sin más de esta forma.

Error macro Excel Ribbon argumentos

Esto sucede porque, para poder realizar las activaciones desde el Ribbon a nuestras funciones necesitamos pasarles el parámetro de control:

De esta forma, nuestra función sería algo así:

Y ya funcionaría sin problema.

Menu personalizado Excel funcionando

Archivos base en github

Como siempre, os dejo los archivos base en github y un pequeño resumen paso a paso de cómo hacerlo para que no tengáis problemas y podáis seguir este tutorial de forma rápida, ágil y a prueba de fallos. Sólo es descargarse los archivos y seguir los pasos.

Si os habéis quedado con dudas o necesitáis alguna aclaración extra (o incluso si queréis aprender a hacer algún menú más complejo) dejadme un comentario aquí, en Facebook o Twitter y estaré encantado de ayudaros.