Excel 2003 vs 2007: Programacin VBA
Excel 2003 vs 2007: Programacin VBA
Snchez Garca, Juan Francisco [Link]@[Link] Bernal Garca, Juan Jess [Link]@[Link] Martnez Mara Dolores, Soledad Mara [Link]@[Link] Departamento Mtodos Cuantitativos e Informticos Universidad Politcnica de Cartagena
RESUMEN
Office 2007 ha supuesto un gran cambio en la interfaz grfica de todas las aplicaciones que forman parte de esta suite: Access, Excel, PowerPoint, Word. En el presente trabajo se analizan cmo dichos cambios han afectado a la programacin VBA en la hoja de clculo Excel y se profundiza en los nuevos mtodos introducidos con esta versin.
ABSTRACT
Office 2007 has been a big challenge in the UI (User Interface) of all applications that are part of this suite: Access, Excel, PowerPoint, Word. This paper analyzes how these changes have affected the VBA programming in Excel and deepened in the new methods introduced in this version.
Palabras claves: Programacin; hoja de clculo; Visual Basic para Aplicaciones. Keywords: Programming; spreadsheet; Visual Basic for Applications Clasificacin JEL (Journal Economic Literature): C15; C88 rea temtica: Informtica aplicada a los mtodos cuantitativos.
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
1. INTRODUCCIN
La aparicin en el ao 2007 de la versin 12 del paquete informtico Office de Microsoft introdujo una serie de cambios en Excel, la mayor parte de los cuales son visuales. As, a modo de resumen y siguiendo a Walkenbach (2007a) se pueden citar las siguientes: Nuevo interfaz de usuario. Aumento del nmero de filas por hoja pasando de 65.536 (216) filas a 1.048.576 (220). Aumento del nmero de columnas pasando de 256 (28) columnas a 16.384 (214). Aumento de la capacidad de clculo: cantidad de memoria, niveles de ordenacin, operaciones deshacer, elementos en autofiltros, caracteres por frmula, argumentos por frmula, etc. Nuevos formatos de ficheros. Tablas. Estilos y temas. Mejores opciones de grficos. Nueva vista de diseo de pgina. Formato condicional mejorado. Caractersticas de colaboracin. Comprobacin de compatibilidad. Tablas dinmicas mejoradas. Nuevas funciones.
Uno de los cambios ms visibles es la desaparicin de la tradicional barra de mens habitual en toda aplicacin informtica y su sustitucin por una cinta de opciones (ribbon) que, adems de ser mucho ms visual al estar integrada por iconos en lugar de los tradicionales comandos, supone una reorganizacin de las opciones existentes en Excel hasta la versin anterior.
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Excel 2003 vs 2007: Programacin VBA
2. EXCEL 2007 Y VBA
A priori, los cambios sufridos por Excel 2007 parecen no tener ninguna implicacin en la programacin de la hoja de clculo mediante el uso de Visual Basic para Aplicaciones (VBA), como se vena efectuado hasta ahora. Aunque, en lneas generales esta afirmacin es cierta, existen dos aspectos que han cambiado y que analizaremos a continuacin:
2.1. Formato de ficheros Desde las primeras versiones de Excel el tipo de archivo utilizado para almacenar las hojas de clculo ha sido un formato propietario identificado con la extensin XLS (Excel Spreadsheet). Este formato almacena tanto simples hojas de clculo como hojas de clculo con mdulos en VBA. Junto a este formato se utilizaba la extensin XLA para los complementos (Excel Add-in) que proporcionan nuevas funcionalidades a Excel a travs de la programacin de mens, herramientas y nuevas funciones y el formato XLT para las plantillas (Excel Template).
Con Excel 2007, Microsoft pasa a usar un nuevo conjunto de formatos de archivos denominados genricamente Office Open XML, basados en el estndar XML (Extensible Markup Language). Estos archivos, realmente son ficheros ZIP que contienen el documento guardado utilizando el formato XML y pueden ser ledos y escritos por cualquier aplicacin, al ser formatos abiertos y por tanto ser pblicas su estructura y caractersticas. Este formato, en cierta medida, fue la respuesta de Microsoft al formato OASIS (Open Document Format for Office Applications) utilizado por la suite informtica [Link] (OOo) y a las exigencias realizadas por diversas administraciones pblicas de dejar de usar formatos propietarios.
El cambio de codificacin realizado por Microsoft ha conllevado as mismo la creacin y separacin de diversos formatos: XLSX: Libro que no contiene VBA XLSM: Libro que contiene VBA XLTX: Plantilla que no contiene VBA
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
XLTM: Plantilla que contiene VBA XLSA: Archivo de complementos XLSB: Archivo binario similar al anterior XLS adaptado a las novedades de Office 2007 XLSK: Archivo de copia de seguridad
Resulta evidente, que Microsoft ha decidido separar drsticamente los archivos que contienen programacin en VBA (genricamente llamada Macros) de los que no la contienen. Este hecho, posibilita as mismo que sea ms sencillo limitar la programacin de virus de scripts almacenados en archivos de Excel, ya que ahora el tipo de archivo identificar fcilmente a los archivos potencialmente peligrosos.
2.2. Cinta de opciones La sustitucin de la barra de mens por la cinta de opciones ha provocado que toda la programacin que hasta ahora se efectuaba para la creacin de barras de mens personalizadas sufra las consecuencias de este cambio de filosofa en las aplicaciones existentes en la suite Office 2007.
La cinta de opciones es un elemento ms visual que el anterior sistema de mens y barras de herramientas, y ocupa algo ms de espacio del que utilizaban dichas barras. Sin embargo, para aprovechar ms el rea de trabajo puede ser minimizada utilizando un comando de men contextual o pulsando la combinacin de teclas Ctrl+F1. Junto con la cinta de opciones, para dar cabida a las operaciones ms habituales, existe en la misma barra de ttulo de la ventana en la que se ejecuta Excel aparece un nuevo elemento denominado barra de herramientas de acceso rpido que puede ser fcilmente personalizado por el usuario.
Hasta la versin 2003 era posible crear una barra de herramientas nueva utilizando el comando Personalizar dentro del men Herramientas:
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Excel 2003 vs 2007: Programacin VBA
As, mismo tambin en ese mismo cuadro de dilogo era posible crear un nuevo men utilizando para ello la pestaa Comandos:
Este procedimiento tena un pequeo defecto y era que la barra de herramientas o men creado permaneca automticamente en la aplicacin Excel con lo que no estaba asociado a un fichero Excel determinado. De esta forma, si en un libro Excel se inclua una programacin VBA no era posible asignarla a los comandos de men o barras de herramientas salvo que se crearan de forma manual en la instalacin de Excel donde se fuera a utilizar. La solucin pasaba por que fuese el propio cdigo VBA el que creara el correspondiente men y lo eliminara al finalizar.
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
2.3. Caso prctico
Como ejemplo de lo expuesto, a continuacin se recoge la creacin y eliminacin de un men denominado Simulacin que incluye 6 comandos llamados Sesiones quirrgicas, Simulacin sin prioridades, Simulacin con prioridades, Tabla tiradas aleatorias Tabla tiradas con prioridades y Calcular hoja (dicha programacin forma parte del trabajo emprico recogido en el trabajo de Bernal Garca, Martnez Mara Dolores y Snchez Garca).
Sub AadirMiMenu() Dim Dim Dim Dim HelpMenu As CommandBarControl MiMenu As CommandBarPopup MenuItem As CommandBarControl SubmenuItem As CommandBarButton
Set HelpMenu = CommandBars(1).FindControl(ID:=30010) Set MiMenu = CommandBars(1).[Link](Type:=msoControlPopup, before:=[Link], temporary:=True) [Link] = "&Simulacin" Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "Sesiones &quirrgicas..." .OnAction = "FormularioSesionesQuirurgicas" End With Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "Simulacin &sin prioridades" .OnAction = "CalcularTablas" End With Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "Simulacin &con prioridades" .OnAction = "CalcularTablasPrioridades" End With Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "&Tabla tiradas aleatorias" .OnAction = "TablaTiradas" .BeginGroup = True End With Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "Tabla tiradas con &prioridades" .OnAction = "TablaTiradasPrioridades" End With Set MenuItem = [Link](Type:=msoControlButton) With MenuItem .Caption = "Calcular &Hoja" .OnAction = "CalcularHoja" .BeginGroup = True End With End Sub Sub QuitarMiMenu() On Error Resume Next CommandBars(1).Controls("Simulacin").Delete
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Excel 2003 vs 2007: Programacin VBA
End Sub
En la siguiente pantalla se muestra el efecto de dicha programacin VBA en la versin 2003 de Excel:
Sin embargo, cuando abrimos el mismo fichero con Excel 2007 el resultado final es muy diferente, ya que el men creado pasa a incluirse dentro de la cinta Complementos y no es tan intuitiva su localizacin:
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
De una forma similar, es posible recuperar la antigua barra de mens con toda su funcionalidad, utilizando para ello la tcnica descrita en The Spreadsheet Page ([Link] y realizando la correspondiente adaptacin al idioma espaol para asegurar su correcto funcionamiento:
Sub MakeOldMenus() Dim cb As CommandBar Dim cbc As CommandBarControl Dim OldMenu As CommandBar ' Delete it, if it exists On Error Resume Next [Link]("Old Menus").Delete On Error GoTo 0 Create an old-style toolbar Set the last argument to False for a more compact menu Set OldMenu = [Link]("Old Menus", , True) Copy the controls from Excel's "Built-in Menus" shortcut menu With CommandBars("Built-in Menus") .Controls("&Archivo").Copy OldMenu .Controls("&Edicin").Copy OldMenu .Controls("&Ver").Copy OldMenu .Controls("&Insertar").Copy OldMenu .Controls("&Formato").Copy OldMenu .Controls("&Herramientas").Copy OldMenu
' '
'
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Excel 2003 vs 2007: Programacin VBA
.Controls("&Datos").Copy OldMenu .Controls("Ve&ntana").Copy OldMenu .Controls("&?").Copy OldMenu End With ' Make it visible. It appears in the Add-Ins tab [Link]("Old Menus").Visible = True End Sub
Lo ideal sera conseguir que se pudieran crear estos mens dentro de la cinta de opciones propia de Excel, al igual que los comandos que incorpora de forma directa Excel 2007, proporcionando una apariencia ms profesional. Esta labor que era relativamente sencilla con Excel 2003, y anteriores, se hace bastante ms complicada en esta nueva versin.
Lo primero que debe quedar claro es que no es posible modificar la cinta de opciones desde VBA (Walkenbach, 2007b). En su lugar, es necesario escribir instrucciones RibbonX e insertar las mismas en el fichero de Excel, cosa que se debe hacer desde fuera de Excel. En este punto, cabe recordar que los nuevos ficheros de Excel son en realidad ficheros ZIP, con lo que es posible agregar ficheros en su interior utilizando programas como WinZip, WinRAR o 7zip. El uso de VBA en 2007 queda limitado a programar las acciones que realizarn las nuevas opciones de la cinta de opciones. Por tanto, lo primero que tenemos que hacer es conocer qu es RibbonX. RibbonX es cdigo XML que describe los controles, dnde se colocan, cul es su aspecto y qu pasa al activarlos. Por ejemplo, el siguiente cdigo RibbonX aade un nuevo grupo llamado Mi grupo al final de la cinta Inicio e incluye en l un control llamado Plsame que al pulsarlo llama a la accin Macro1 que tendr que ser programada en el editor de VBA.
<customUI xmlns="[Link] <ribbon> <tabs> <tab idMso="TabHome" > <group id="customGroup1" label="Mi grupo" insertAfterMso="GroupEditingExcel"> <button id="customButton1" imageMso="HappyFace" /> </group> </tab> </tabs> </ribbon> </customUI> label="Plsame" size="normal" onAction="Macro1"
El cdigo anterior tiene que ubicarse en un fichero llamado en [Link] que se almacena en la carpeta customUI, quedando de la siguiente forma al ser abierto con 7zip:
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
El resultado final al abrir el archivo en Excel 2010 es el siguiente:
Como ha quedado demostrado, el procedimiento no es sencillo y, por esta razn, han aparecido diversas soluciones para simplificar al menos la inclusin del archivo XML con el cdigo RibbonX dentro del archivo de Excel, evitando tener que crear el archivo de forma manual con un editor y su posterior inclusin con un compresor en la carpeta correspondiente del archivo Excel. Entre las soluciones ms utilizadas se encuentra Custom UI Editor, software realizado por Microsoft y que puede ser descargado de forma gratuita de la red ([Link]
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
10
Excel 2003 vs 2007: Programacin VBA
Volviendo a nuestro ejemplo, ser necesario por tanto crear una pestaa que podemos denominar Simulacin y dentro de la cual incluiremos los 6 comandos que hasta ahora tenamos programados como comandos de men. Ya que inicialmente los tenamos separados en 3 grupos, lo que haremos ser crear estos grupos dentro de dicha pestaa.
El cdigo XML con el que podemos conseguir el efecto deseado es el que se recoge a continuacin. Para simplificar, se le ha asignado el mismo icono (QuerySelectQueryType) a todos los comandos, aunque tambin se podran haber dejado sin imagen. Adems, para completar mejor la cinta, el tamao del comando se ha establecido en large.
<customUI xmlns="[Link] <ribbon> <tabs> <tab id="CustomTab" label="Simulacin"> <group id="customGroup1" label="Simulacin"> <button id="customButton1" label="Sesiones quirrgicas" size="large" onAction="FormularioSesionesQuirurgicas" imageMso="QuerySelectQueryType" /> <button id="customButton2" label="Simulacin sin prioridades" size="large" onAction="CalcularTablas" imageMso="QuerySelectQueryType" /> <button id="customButton3" label="Simulacin con prioridades" size="large" onAction="CalcularTablasPrioridades" imageMso="QuerySelectQueryType" /> </group> <group id="customGroup2" label="Tablas"> <button id="customButton4" label="Tabla tiradas aleatorias" size="large" onAction="TablaTiradas" imageMso="QuerySelectQueryType" /> <button id="customButton5" label="Tabla tiradas con prioridades" size="large" onAction="TablaTiradasPrioridades" imageMso="QuerySelectQueryType" /> </group> <group id="customGroup3" label="Calcular"> <button id="customButton6" label="Calcular onAction="CalcularHoja" imageMso="QuerySelectQueryType" /> </group> </tab> </tabs> </ribbon> </customUI> hoja" size="large"
El resultado final de este cdigo RibbonX es el que a continuacin se reproduce.
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
11
Snchez Garca, J. F.; Bernal Garca, J. J.; Martnez Mara Dolores, S. M.
3. CONCLUSIONES
En el presente trabajo hemos abordado cmo afectan los cambios que Microsoft introdujo con Office 2007 a la programacin en VBA de la hoja de clculo, as como a la creacin y mantenimiento de barras de herramientas y barras de mens. En anteriores trabajos de los autores qued evidente la utilidad de la programacin en VBA dentro de Excel para la creacin y posterior desactivacin de mens especficos para cada herramienta desarrollada, haciendo que fuera independiente de la versin de Excel utilizada, hecho este que no es posible aplicarlo de forma directa a Excel 2007.
Si bien, por razones de compatibilidad, la anterior programacin realizada pensando en el enfoque de Office 2003 y anteriores sigue siendo vlida, es cierto que pierde parte de su funcionalidad y no aprovecha al mximo las nuevas posibilidades que Office 2007 brinda como la cinta de opciones en sustitucin de las antiguas barras de mens.
Por ltimo, y a ttulo de resumen, podemos citar los cambios que han supuesto Office 2007 y Office 2010 en cuanto a la creacin de mens personalizados y la utilizacin de VBA:
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
12
Excel 2003 vs 2007: Programacin VBA
Office 2003 Archivos de datos Archivos con macros Creacin de mens Forma de creacin de mens Automatizacin de mens XLS XLS En la aplicacin Manual y VBA S, con VBA
Office 2007 XLSX XLSM En el archivo de datos RibbonX No
4. REFERENCIAS BIBLIOGRFICAS
BERNAL GARCA, Juan Jess; MARTNEZ MARA DOLORES, Soledad Mara; SNCHEZ GARCA, Juan Francisco (2009). La hoja de clculo como apoyo a la simulacin de los fenmenos de espera con prioridades. Una aplicacin a la sanidad, Recta, nm. 10, pp. 77-104. GETZ, Ken. (2007). Uso de RibbonX desde Visual Basic.
[Link] WALKENBACH, John (2007a). Excel 2007 Bible. Indianapolis, Indiana: Wiley Publishing, Inc. WALKENBACH, John (2007b). Excel 2007 Power Programming with VBA. Indianapolis, Indiana: Wiley Publishing, Inc.
XVIII Jornadas ASEPUMA VI Encuentro Internacional Rect@ Vol Actas_18 Issue 1: 902
13
