sábado, febrero 24, 2007

Gráficos Dinámicos con Autofiltro

Una característica interesante de Autofiltro es que si creamos una gráfico a partir de una lista, al aplicar Autofiltro, el gráfico se irá adaptando a los datos visibles (filtrados).

El archivo con el ejemplo se puede descargar aqui graficos dinamicos


Por ejemplo, a partir de esta lista



creamos este gráfico (que no tiene mucho sentido, en esta etapa del ejemplo)



Si filtramos la lista para mostrar sólo las ventas de José, el gráfico se adaptará



En cuanto empezamos a trabajar con este gráfico descubrimos que hay un problema: el gráfico se "encoge" cuando navega sobre líneas ocultas. Por ejemplo, nuestro gráfico puede llegar a verse así



Podemos sobreponernos a este problema y, mejor aún, hacer que en el título de nuestro gráfico aparezca el nombre del agente cuyas ventas estamos mostrando. Todo esto puede hacerse sin macros y usando las técnicas que mostramos en la nota anterior sobre cómo ocultar gráficos en Excel.

Como en ese ejemplo, usaremos dos hojas. En la Hoja1 tendremos la tabla de datos y mostraremos el gráfico (de hecho, una imagen del gráfico).
En la hoja dos reside el "motor" del modelo: el gráfico y la lista de los agentes. En la celda contigua al agente ponemos una fórmula que totaliza las ventas de cada agente. Esto nos servirá para identificar el nombre del agente cuyo gráfico queremos exhibir en la Hoja1.

Empezamos por cortar y pegar el gráfico que acabamos de crear a otra hoja (en nuestro ejemplo a la Hoja2).

Seleccionamos el rango "sobre" el cual se encuentra el gráfico y lo definimos con un nombre. En nuestro caso definimos el nombre "grafico"

grafico: =Hoja2!$A$2:$G$17

En la Hoja2 elegimos una celda vacía, por ejemplo $I$1 y la llamamos sin_graf

sin_graf =Hoja2!$I$2

En el rango A20:A24 ponemos la lista de los agentes; en la celda B20 ponemos la fórmula =SUMAR.SI(agente,A20,ventas) y la copiamos al rango B20:B24.

Los nombres usados en la fórmula se refieren a rangos en la Hoja1

agente: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

ventas: =DESREF(Hoja1!$C$2,0,0,CONTARA(Hoja1!$C:$C)-1,1)

En la Hoja1 ponemos en la celda E1 la siguiente fórmula

=INDICE(Hoja2!A20:A24,COINCIDIR(Hoja1!F1,Hoja2!B20:B24,0))

En la celda F1 ponemos la fórmula

=SUBTOTALES(9,ventas)

Esta fórmula nos da el total de ventas del agente elegido con Autofiltro. La fórmula en la celda E1 nos da el nombre del agente, basado en el resultado de la celda F1.

Volvemos por un instante a la Hoja2 y ligamos el cuadro de texto del título del gráfico a la celda E1 en la Hoja1



Ahora tenemos que crear una imagen vinculada al gráfico en la Hoja3.
Elegimos una celda vacía, y la copiamos (Ctrl+C). Seleccionamos la celda E3, abrimos el menú Edición pulsando Mayúsculas (Shift) y elegimos Pegar--Vínculos Imagen. El resultado es una imagen vacía del tamaño de la celda.

Ahora creamos el nombre

mostrar_graf =SI(ESNOD(Hoja1!$E$1),sin_graf,grafico)

Esta fórmula nos permite mostrar u ocultar el gráfico basado en su resultado. Para qu esto sucede ligamos la imagen a la fórmula de la siguiente manera:

Seleccionamos la imagen que creamos con Pegar Vínculos—Imagen y en la barra de fórmulas reemplazamos la referencia por "=mostrar_graf"



Ahora podemos filtrarla lista con Autofiltro y veremos aparecer el gráfico. Por ejemplo, si elegimos Miguel



Este modelo tiene un serio problema potencial: si dos agentes tienen exactamente el mismo volumen de ventas, no se mostrará el gráfico.
Las soluciones posibles son:

1 – usar macros para determinar el nombre de la primera fila visible en la columna A a partir de la celda A2; ver el ejemplo desarrollado en el sitio Contextures;

2 – agregar una columna auxiliar que agregue a cada total de ventas del mes de cada agente un número identificatorio suficientemente pequeño como para no alterar los resultados. Por ejemplo, a Roberto le damos el número 1; a las ventas de Roberto le sumamos 1/1000000. Lo mismo con los restantes (Pedro 2/1000000, etc). Luego basamos las fórmulas y el gráfico en la columna auxiliar.

Technorati Tags:

Graficos Excel con listas desplegables – Segunda nota

En el pasado mostramos como generar un gráfico dinámico en Excel agregándole una lista desplegable. Para esto usamos el control Cuadro Combinado de la barra de herramientas Formulario.

La tabla de datos es la siguiente




Y el gráfico el siguiente




Uno de mis lectores me pregunta cómo hacer si queremos mostrar el gráfico inverso. Es decir, los meses de ventas por línea de producto.
La lista desplegable deberá mostrar las líneas de productos y el gráfico mostrar doce columnas, una por cada mes de ventas



La técnica es muy similar a la que mostramos en la nota anterior, pero con algunas pequeñas variantes.

Los pasos son los siguientes:

1 – Ponemos la lista en una hoja a la que llamaremos "Columnas"



Para este ejemplo hemos ubicado la tabla de datos en el rango A3:D15

2 – En una nueva hoja, a la que llamaremos "Control", ponemos:

en la celda A1 la fórmula =INDICE(columnas!B3:D3;A2)

en el rango A4:A15 la lista de los meses

en el rango B4:B15 la siguiente fórmula =INDICE(columnas!$B$4:$D$15;COINCIDIR(A4;columnas!$A$4:$A$15;0);$A$2)

en el rango D4:D6 la lista de las líneas de productos

La hoja "Control" se verá así



El error #¡VALOR! En la celda A1 desaparecerá más adelante.

3 – En la hoja "Columnas" creamos el gráfico, pero basándonos en las tabla de la hoja Control



Activamos la barra de herramientas Formularios, elegimos el control Cuadro combinado



y lo ponemos sobre el gráfico, tal como mostramos en la nota anterior sobre el tema.

Abrimos el menú del control y le damos las siguientes definiciones



Cerramos el menú y elegimos una de las líneas de la lista desplegable.



Como el control esta ligado a la celda A2 de la hoja Control, el error desaparece y el gráfico empieza a funcionar como gráfico dinámico.

La diferencia en la técnica se debe a que no podemos usar un rango horizontal como referencia en el control Cuadro Combinado.



Technorati Tags:

viernes, febrero 23, 2007

Ocultar gráficos en Excel

En las últimas notas hemos hablado sobre cómo ocultar y mostrar hojas en un cuaderno Excel. Siguiendo con esta onda del "ocultismo", veamos hoy cómo ocultar un gráfico que hemos insertado en una hoja Excel.
Supongamos esta tabla de datos con la cual construimos un gráfico



Una forma de ocultar el gráfico sería ocultar las filas de la hoja sobre las cuales "flota" el gráfico. Pero si queremos ubicar el gráfico a la izquierda de la tabla, no podremos hacerlo sin ocultar, al mismo tiempo, la tabla misma.

La solución consiste en ligar el gráfico a una imagen y crear un nombre que contenga una fórmula con una condición para mostrar u ocultar la imagen. Veamos la solución por partes.

Empezamos por mudar el gráfico cortándolo y pegándolo en una nueva hoja. Nos aseguramos que en la nueva hoja, la ubicación del gráfico coincida con un rango determinado. En nuestro ejemplo, el gráfico está ubicado sobre el rango A1:F16.



Seleccionamos el rango sobre el cual se encuentra el gráfico y le damos un nombre. En nuestro caso lo llamaremos "grafico"



Esto puede hacerse usando el cuado de nombres (como muestro en la imagen) o con el menú Insertar—Nombres—Definir

Ahora seleccionamos una celda en blanco (por ejemplo, H1) y la damos el nombre "ocultar".

Volvemos a la Hoja1, copiamos (Ctrl+C) una celda en blanco cualquiera, por ejemplo J1. Seleccionamos la celda que será el extremo superior izquierdo del gráfico, en nuestro caso D1. Mientras apretamos la tecla Mayúsculas (Shift) abrimos el menú Edición—Pegar vínculo de imagen



Esta opción sólo aparece si apretamos la tecla Mayúsculas (Shift) al abrir el menú Edición. Hemos creado una imagen que está vinculada a la celda J1.



Reemplazamos el vínculo de la imagen, $J$1, por el nombre "grafico" que contiene el rango sobre el cual se encuentra el gráfico



Inmediatamente aparece el gráfico de la Hoja2 que hemos vinculado a la imagen.

Nuestro próximo paso consiste en crear un nombre con una fórmula condicional que muestre u oculte el gráfico y una celda con un valor que sirva de parámetro a esta fórmula.

Creamos una lista desplegable con dos valores, si y no, en la celda C1. Para esto usamos Validación de Datos



Ahora creamos un nombre, mostrar_grafico, conteniendo la siguiente fórmula:

=SI(Hoja1!$C$1="si",grafico,ocultar)

Seleccionamos la imagen en la Hoja1 y reemplazamos el vínculo al nombre "grafico" en la barra de fórmulas por el nombre "mostrar_grafico"



A partir de este momento, cuando seleccionamos "si" en la celda C1, veremos el gráfico. Si seleccionamos "no", el gráfico desaparecerá.

Aquí pueden grafico_ocultodescargar el archivo con el ejemplo.

Technorati Tags:

jueves, febrero 22, 2007

Excel – Mostrar hojas ocultas, segunda nota

En la nota anterior sobre hojas ocultas en Excel, mostramos una pequeña macro que nos permite mostrar de un golpe todas las hojas ocultas de un cuaderno Excel.
El próximo paso es desarrollar una macro que nos permite escoger qué hojas del cuaderno descubrir y cuales ocultar, sin necesidad de abrir y cerrar cada vez el menú Formato.

En realidad no hace falta desarrollar la macro porque la gente de GR Bussines Process ya lo han hecho y lo ofrecen, gratis, en su página.
Una vez instalado el complemento con la macro, podemos ocultar y mostrar las hojas de un cuaderno a través de un formulario.
Por ejemplo, en este cuaderno he ocultado las hojas Hoja2 y Hoja3. Al activar la macro se abre este formulario




La ventana de la izquierda muestra las hojas visibles; la ventana de la derecha las ocultas. Los botones ubicados entre las ventanas permiten pasar cada hoja de un estado al otro con facilidad.

En el sitio hay un enlace que permite descargar la documentación de la macro.

Recomendado!

Technorati Tags:

domingo, febrero 18, 2007

Excel – Mostrar hojas ocultas

Excel permite ocultar hojas de un cuaderno usando el menú Formato—Hojas—Ocultar. Si queremos ocultar varias hojas de una vez, sencillamente seleccionamos las hojas (seleccionado la pestaña de la hoja junto con la tecla Ctrl).

Pero si queremos mostrar hojas ocultas, solo podemos hacerlo de una a la vez. Cada vez que abrimos el menú Formato—Hojas—Mostrar, Excel nos presentará una lista de hojas ocultas, pero sólo podremos elegir de una a la vez.

Para mostrar todas las hojas ocultas de un golpe podemos escribir una macro sencilla. Abrimos el editor de Vb (Alt+F11). Si existe el Personal.xls, le agregamos un módulo y en él escribimos

Sub mostrar_hojas()
  Dim sh As Worksheet

    For Each sh In Worksheets
     sh .Visible = True
    Next

End Sub

Luego podemos agregarle una combinación de teclas, por ejemplo Ctrl+Mayúsculas+M.

Si el Personal.xls no existe, podemos crearlo fácilmente. Abrimos la grabadora de macros, seleccionamos la opción libro Personal para guardarlo y grabamos algo.



Al hacer esto, Excel crea el cuaderno Personal.xls.




Technorati Tags:

Redondear por múltiplos en Excel

Excel tiene varias funciones para redondear, como ya vimos en notas anteriores. Pero a veces queremos redondear por múltiplos. Es decir, por saltos de un número determinado. Por ejemplo, redondear números por múltiplos de 0.25.

Si tenemos instalado el
Análisis Toolpak, podemos usar la función MROUND de la siguiente manera:
si N el número que queremos redondear y m el factor de redondeo (en nuestro caso, 0.25)

La fórmula es =MROUND(N;m)

Si no tenemos instalado el Análisis ToolPak, podemos usar esta fórmula

=REDONDEAR(N/m;0)*m

El truco consiste en dividir el número a redondear por el múltiplo, el resultado redondearlo a 0 decimales y finalmente multiplicar este resultado por el múltiplo.

En esta tabla usamos ambas funciones para redondear números aleatorios con dos decimales




La fórmula en la celda B2 es =REDONDEAR(A2/m,0)*m (m es nombre que contiene el valor del múltiplo, en nuestro caso 0.25)
La fórmula en la celda C2 es =MROUND(A2,m)

La forma de la fórmula =REDONDEAR(N/m;0)*m podemos usarla con otras funciones de redondeo y obtener resultados interesantes.

Hace unos días preguntaban en uno de los foros en castellano qué fórmula permite redondear un número por múltiplos de 0.05, de la siguiente manera:

dado el número 123.4x, donde 4x es la parte decimal, si x es menor que 5, redondear a 0; si x es igual o mayor a 5, redondear a 5. Por ejemplo,:

123.42 redondear a 123.40
123.46 redondear a 123.45

La solución es usar la fórmula =REDONDEAR.MENOS(N/m,0)*m. En esta tabla vemos los resultados (m = 0.05)


De la misma manera podemos usar =REDONDEAR.MAS, =MULTIPLO.SUPERIOR y =MULTIPLO.INFERIOR.

Los resultados pueden compararse en esta tabla (m = 0.05)


El archivo con el ejemplo se puede descargar aqui
mult_frac
mult_frac.xls
Hosted by eSnips


El sitio XLDynamics tiene una
excelente nota (en inglés) sobre el tema.


Technorati Tags:

jueves, febrero 15, 2007

Macros Excel – Cómo crear un menú

En las últimas dos notas vimos cómo automatizar una tarea con macros en Excel, como hacer la macro disponible para todos los cuadernos en la sesión y como convertirla en un complemento (Add-in). Esto último nos permite usar y distribuir la macro eficientemente.
Para activar la macro del complemento mostramos como crear una barra de herramientas cada vez que instalamos el complemento.
Otro método, que personalmente prefiero, es agregar un menú en la barra de menús.
Normalmente la barra de menús se ve así




En esta nota veremos una técnica relativamente sencilla para agregar un nuevo elemento en la barra de menús, desde donde podremos activar nuestras macros



Como se puede ver, hemos agregado un nuevo menú, "Mis Macros", entre los menús "Ventana" y "Ayuda". Si pulsamos el menú veremos dos entradas



Cada entrada activa una macro.

La técnica que hemos usado es la propuesta por John Walkenbach. La técnica consiste en almacenar todos los datos del menú en una hoja Excel; luego en un módulo de Vba escribir una rutina para crear el menú de acuerdo a los datos de la hoja y otra rutina para borrar el menú cuando cerramos la aplicación.
Una vez creadas la hoja y la rutina, agregamos eventos para correr las macros de creación y borrado del menú. Finalmente convertimos el archivo en complemento, que luego podremos instalar-

Veamos el ejemplo. Hemos creado una macro, N_Gris, para poner negrita y fondo gris a un rango. Una segunda macro, Saludar, nos saludará con la fórmula adecuada de acuerdo a la hora del día.

Una vez creadas las macros, creamos la hoja "Menu" que guardará los datos del menú.



La hoja Menu usa cinco columnas

Level (Nivel): determina la ubicación de la entrada en el menú en forma jerárquica.
Caption (Título/Subtítulo): es el texto que aparece en la entrada del menú.
Position/Macro (Posición/Macro); en el nivel 1 debe ser un número entero que determina la posición en la barra de menús.
Divider (Divisor): Si queremos que aparezca una línea divisora entre dos entradas del menú ponemos TRUE.
FaceID: es el número que representa la imagen/icono que queremos que aparezca al lado del texto.

La macro que crea el menú, es la siguiente

Sub CreateMenu()

   Dim Menu As Worksheet
   Dim MenuObject As CommandBarPopup

   Dim MenuItem As Object
   Dim SubMenuItem As CommandBarButton
   Dim Row As Integer
   Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set Menu = ThisWorkbook.Sheets("Menu")
''''''''''''''''''''''''''''''''''''''''''''''''''''

  ' Make sure the menus aren't duplicated
   Call DeleteMenu

  ' Initialize the row counter
     Row = 2

  ' Add the menus, menu items and submenu items using
  ' data stored on Menu

     Do Until IsEmpty(Menu.Cells(Row, 1))
     With Menu
     MenuLevel = .Cells(Row, 1)
     Caption = .Cells(Row, 2)
     PositionOrMacro = .Cells(Row, 3)
     Divider = .Cells(Row, 4)
     FaceId = .Cells(Row, 5)
     NextLevel = .Cells(Row + 1, 1)
    End With

     Select Case MenuLevel
       Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
   Set MenuObject = Application.CommandBars(1). _
   Controls.Add(Type:=msoControlPopup, _
   Before:=PositionOrMacro, _
   Temporary:=True)
   MenuObject.Caption = Caption

     Case 2 ' A Menu Item
     If NextLevel = 3 Then
   Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
   Else
   Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
   MenuItem.OnAction = PositionOrMacro
   End If
   MenuItem.Caption = Caption
'If FaceId <> "" Then MenuItem.FaceId = FaceId
   If Divider Then MenuItem.BeginGroup = True

   Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Para borrar el menú usamos esta macro

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus
Dim Menu As Worksheet
Dim Row As Integer
Dim Caption As String

On Error Resume Next
Set Menu = ThisWorkbook.Sheets("Menu")
Row = 2
Do Until IsEmpty(Menu.Cells(Row, 1))
If Menu.Cells(Row, 1) = 1 Then
Caption = Menu.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub

En el objeto ThisWorkbook ponemos estos dos eventos

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
End Sub

Private Sub Workbook_Open()
    Call CreateMenu

    MsgBox "Se ha agregado el menú Mis Macros delante del menu de Ayuda", vbInformation

End Sub

Finalmente, guardamos el cuaderno como complemento, pasamos al editor de Vba, cambiamos la propiedad IsAddin del objeto ThisWorkbook a TRUE y guardamos el proyecto con el menú Archivo del editor. Todo este proceso está explicado en la nota anterior.

Para instalar el complemento, usamos el menú Herramientas—Complementos. Si el complemento no aparece en la lista, usamos el botón Examinar para instalarlo. Una vez instalado veremos este mensaje



y el menú "Mis Macros" aparecerá en el lugar planeado




Technorati Tags:

lunes, febrero 12, 2007

Macros Excel – distribuyéndolas a otros usuarios

En la nota anterior vimos qué fácil es automatizar tareas con macros en Excel.
También vimos como hacer que las macros que desarrollamos estén disponibles en toda sesión de Excel, guardándolas en el cuaderno Personal.xls.

Si queremos distribuir nuestra macro a otros usuarios podemos, simplemente, darles una copia del cuaderno que contiene la macro.

Una forma más eficiente de hacerlo es guardando el cuaderno como Complemento (Add-in). Los complementos son cuadernos "invisibles", con extensión ".xla", que agregan a Excel cierta funcionalidad. En nuestro caso, la macro que desarrollamos.

Los complementos no se abren, como los cuadernos regulares de Excel (.xls), sino que se instalan con el menú Herramientas—Complementos. Más adelante mostraremos cómo hacerlo.

Otro detalle importante es que las macros contenidas en cuadernos de tipo complementos, no son visibles con el menú Herramientas—Macros. Debido a esto, debemos incluir en el complemento un método para activar la macro.

En esta nota mostraremos como agregar una barra de herramientas con un botón que activa la macro cada vez que instalamos el complemento.

Una vez desarrollada la macro, y después de comprobar que funciona, guardamos el cuaderno como Complemento con el menú Archivo—Guardar como





Excel propone la carpeta Addins de Office (su ubicación depende la versión de Office). No es obligatorio guardarlo en esa carpeta.

El próximo paso es pasar al editor de Visual Basic (Alt+F11). En la ventanilla de proyectos ubicamos el complemento (N_Gris.xla en nuestro caso) y volvemos visibles los objetos pulsando la señal "+"




Seleccionamos el objeto ThisWorkbook de N_Gris.xla. En la ventanilla de las propiedades del objeto, cambiamos la propiedad de IsAddin de False a True.



Al hacer esto, el cuaderno que contiene la macro (la instancia .xls del complemento) se vuelve "invisible".
Ahora guardamos el proyecto con el menú Archivo—Guardar del editor de Vba.
El siguiente paso es agregar un método para activar la macro. Existen varias posibilidades. En esta nota mostraremos como agregar una barra de herramientas. En una próxima nota veremos como agregar un item en la barra del menú de Excel.

En la ventanilla de Proyectos del editor de Vba, hacemos un doble clic al objeto ThisWorkBook. Esto provoca que se abra un módulo especial al que podemos agregar eventos al objeto. Eventos son macros especiales que se activan cuando ocurre algo en particular (algún evento, de allí el nombre) relacionado con el objeto.

En la ventana del editor de Vba, vamos a la ventanilla donde aparece la palabra "General" y elegimos "Workbook"




Al hacerlo Excel agrega al módulo el evento Workbook_Open. Este evento activa la macro que crea la barra con el botón, cada vez que instalemos el complemento.
De la misma manera, necesitamos una rutina que borre la barra de herramientas cada vez que desinstalemos el complemento. Esto lo hacemos con un evento BeforeClose





Las rutinas son las siguientes:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Call cerrar_barra
End Sub

Private Sub Workbook_Open()
     Call crear_barra
End Sub


Como se ve, son dos rutinas muy sencillas. Cada una llama a otra rutina que crea o borra la barra.

Las rutinas para crear y borrar la barra de herramientas las escribimos en un módulo normal de Vba. Creamos un nuevo módulo y por, comodidad y claridad, le cambiamos el nombre a "Barra"



También hemos cambiado el nombre del módulo que contiene la macro a "Aplicación". Esto se hace seleccionando el módulo y cambiando el valor de la propiedad Name.

En el módulo "Barra" escribimos las rutinas que aparecen en los eventos de ThisWorkBook:


Sub crear_barra()
      Dim Boton As CommandBarButton

      CommandBars.Add(Name:="N_gris").Visible = True
      Set Boton = CommandBars("N_Gris").Controls.Add _(Type:=msoControlButton)
      With Boton
            .Caption = "Aplicar negrita y fondo gris"
            .FaceId = 200
            .OnAction = "N_gris"
      End With
End Sub

Sub cerrar_barra()
      On Error Resume Next
      CommandBars("N_Gris").Delete
End Sub




Guardamos nuevamente el complemento usando el menú Archivo—Guardar del editor de Vba.

Para instalar el complemento usamos el menú Herramientas—Complementos de Excel



Apretamos el botón Examinar y vamos a la carpeta dónde hemos guardado el complemento (el icono de los complementos es distinto del de los archivos Excel comunes).
Apretamos Aceptar y nos aseguramos que el complemento este marcado con una "V"



Apretamos "Aceptar" y la barra de herramientas aparecerá sobre el cuaderno Excel. Para desinstalar el complemento quitamos la señal "V" en el diálogo Complementos del menú Herramientas.

Para agregar el comentario que aparece en la parte inferior del diálogo, tenemos que cambiar la propiedad IsAddin del complemento a False, para hacerlo visible. Luego en el menú Archivo—Propiedades, vamos a la pestaña Resumen y agregamos el comentario



Luego, volvemos al editor de Vba y cambiamos la propiedad IsAddin de ThisWorkbook a True, y, naturalmente, guardamos el proyecto.
Para distribuir el complemento podemos mandar el archivo con instrucciones de cómo instalarlo. También podemos ubicarlo en un carpeta accesible a los otros usuarios, que podrán instalar el complemento guardado allí.
La ventaja de este sistema consiste en que nos permite actualizar las macros del complemento fácilmente (no es necesario mandar nuevas copias a los usuarios).


Technorati Tags:

lunes, febrero 05, 2007

Macros Excel – haciéndolas disponibles para todos los cuadernos

Con Excel es relativamente fácil crear macros. Uno de los usos más corrientes es automatizar tareas. No hace falta ser un profesional del Vba (Visual Basic for Applications) para automatizar tareas sencillas con macros.
Así que supongamos que hemos desarrollado una macro para formar la fuente de la selección a negrita (bold) y el fondo de las celdas a gris.
La forma más fácil es utilizar la grabadora de macros.




El resultado es el siguiente



Cambiamos el nombre de la macro a N_gris, y eliminamos la primer línea de la macro, para que podamos aplicarla a cualquier rango que elijamos en la hoja



Sub N_gris()
'
' Macro1 Macro
' Macro grabada el 05/02/2007 por JLD
'

'
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub


Para poder usarla con facilidad asociamos una combinación de teclas a la macro (Herramientas—Macros—Opciones)




Ahora tenemos una macro que nos permite aplicar los formatos pulsando Ctrl+Mayúsculas+N a cualquier rango que hayamos seleccionado.

Para usar esta macro en hojas de otros cuadernos podemos:

1 – Abrir el cuaderno donde grabamos la macro. De esta manera estará disponible para todos los cuadernos abiertos en la sesión.

2 – Guardar la macro en el cuaderno Personal.xls. Excel crea este cuaderno automáticamente la primera vez que grabamos una macro en él. Normalmente este cuaderno está oculto, pero las macros que contiene están disponibles en cada sesión de Excel.

Para crear Personal.xls, simplemente grabamos una macro en él (basta con activar la grabadora e inmediatamente terminar la grabación)




Una vez hecho esto, podemos ver en la ventanilla Proyectos el Personal.xls




Copiamos la macro a un módulo de Personal, y la macro estará disponible cada vez que iniciemos una sesión de Excel.


Supongamos ahora que queremos distribuir nuestra macro a otros usuarios. Una posibilidad es darles una copia del cuaderno con la macro. Estos usuarios podrán luego copiar la macro a su propio Personal.

Otra posibilidad, más eficiente, en guardar la macro como complemento (Add in) y guardarlo en una carpeta pública, de donde cada usuario autorizado podrá instalarlo.
Mostraremos esta técnica en la próxima nota.

Technorati Tags: