La última actualización de Excel deshabilita los controles ActiveX

A los usuarios de Excel que hayan instalado la actualización del Office del 09/dec/2014 les espera una desagradable sorpresa: Excel no permite incrustar controles ActiveX en las hojas

Al intentarlo recibimos este aviso: No se puede insertar el objeto

Para quien, como yo, use estos controles en sus modelos (por ejemplo, en gráficos animados o en dashboards), esta situación es un verdadero dolor de cabeza.

Para nuestra fortuna el MVP RoryA publicó esta solución en su sitio :

• Cerrar todos los programas del Office;
• usando el Windows Explorer o cualquier otra aplicación (personalmente prefiero el Total Commander) buscar todos los archivos *.exd (no confundir con *.exe) y borrarlos o, preferentemente, cambiarles el nombre.

• Volver a abrir Excel y probar si todo funciona ahora normalmente. También se puede realizar reboot del computador.

En mi caso, ésto solucionó el problema, pero de acuerdo a las conversaciones el foro Technet en ciertos casos el problema persiste. De acuerdo a Rory, los técnicos de Microsoft conocen el problema y es de esperar una corrección en breve.

Señalemos que el problema no se limita a la incapacidad de incrustar controles ActiveX en una hoja de Excel. Controles existentes dejar de funcionar y son convertidos en imágenes.

Agregar controles en una hoja de Excel usando Vba

Los controles (casillas de verificación, cuadros combinados, botones de opción, etc.) dan un "toque profesional" a la hoja pero no siempre son la mejor opción. En  general podemos encontrar soluciones más prácticas usando, por ejemplo, validación de datos y/o funciones SI.

Lo más corriente es agregar controles en la hoja en forma manual, usando el menú Desarrollador-Controles-Insertar. Existen dos colecciones de controles: Formulario y ActiveX.
En esta nota veremos como insertar controles ActiveX usando Vba.

Supongamos esta tabla de facturas con sus fechas de vencmientos

En el campo "Pagada" (columna E) anotamos "SI" cuando la factura ha sido pagada. Esto nos permite crear el informe que nos muestra los totales de facturas atrasadas, pagadas y a vencer.
Las fórmulas en el informe son:

celda H4:

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]<H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

celda H5:

=SUMAR.SI(tblFacturas[Pagada],"SI",tblFacturas[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]>=H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

la Tabla "tblFacturas" se refiere al rango B2:E16 (supongo que la mayoría de mis lectores ya hayan adoptado la sana costumbre de usar Tablas para organizar matrices de datos).

Y ahora vayamos a la cuestión de los controles incrustados en hojas de Excel. En nuestro ejemplo queremos usar casillas de verificación para señalar que una factura ha sido pagada  en lugar de un "plebeyo" SI.

Queremos que nuestro informe se vea así:

Si nuestra tabla tiene pocas filas podemos simplemente agregar los controles manualmente. Las casillas están definidas sin texto, ligadas a ka celda sobre la cual están ubicadas y el valor es FALSO. Todo esto tenemos que definirlo cambiando las propiedades por defecto de la casilla. Para ahorrarnos el trabajo de hacerlo cada vez que queremos agregar una casilla podemos usar esta macro:

Sub insert_one()
   
    With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
         Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
         Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
         .Object.Caption = ""
         .LinkedCell = ActiveCell.Address
         .Object.Value = False
         .Object.BackStyle = fmBackStyleTransparent
     End With
    
End Sub

El código agrega la casilla de verificación en la celda (con el método Add); luego definimos algunas propiedades:
Caption = "" para que la casilla no contenga ningún texto;

LinkedCell = Activecell.Address para ligar la casilla a la celda; esto es necesario para poder luego utilizar el valor de la casilla en nuestras fórmulas.

Value = False para que éste sea el valor por defecto en la celda vinculada a la casilla de verificación.

En la tabla de las facturas cambiamos el color de la fuente en las celdas de la columna Pagadas a blanco, para que el valor de la casilla en la celda vinculada no sea visible.

Dado que en el campo Pagada tenemos ahora valores FALSO o VERDADERO (cuando la casilla a sido marcada), tenemos que modificar nuestras fórmulas

celda H4:

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]<H3)* (tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

celda H5:

=SUMAR.SI(tblFacturas3[Pagada],"VERDADERO",tblFacturas3[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]>=H3)*(tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

Si tenemos una tabla con muchas filas podemos y queremos agregar las casillas en un única operación podemos usar esta macro (igual a la anterior a la que le hemos agregado un loop):

Sub insert_check()
    Dim rngCell As Range
 
    Application.ScreenUpdating = False
 
    For Each rngCell In Selection
        rngCell.Select
        With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
             Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
             Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
             .Object.Caption = ""
             .LinkedCell = ActiveCell.Address
             .Object.Value = False
         End With
     Next rngCell
    
     Application.ScreenUpdating = True
    
End Sub
Sub del_all_cb()

Uso de controles en hojas de Excel - Spin Button con valores no enteros

En la nota anterior vimos como superar la limitación del uso de números negativos en los controles Spin Button y Scroll Bar. En esta nota veremos un rodeo para usar números no enteros en estos controles.

Supongamos que queremos crear un Control de Número que vaya de 0 a 5, pero en saltos de 0.5 (en algunos países se usa la coma en lugar del punto para separar la parte decimal del número).

Excel nos deja ingresar un número no entero en la casilla Incremento,

pero al apretar Aceptar cambia la definición dejando sólo la parte entera del número. En  nuestro ejemplo, la casilla Incremento mostrará 0.

Como en la nota anterior, el rodeo consiste en usar una celda auxiliar. Siguiendo con nuestro ejemplo, el valor mínimo es 0; el máximo será 10 (resulta de dividir 5 por 0.5) y el incremento 1. En nuestro ejemplo la celda vinculada es A3 y en la celda A4 ponemos la fórmula =A3/2 o su equivalente =A3*0.5

En resumen, la norma es:

• mínimo: 0
• máximo: máximo deseado dividido por el incremento deseado
• incremento: 1
• en la celda auxiliar: celda vinculada multiplicada por el  incremento deseado (o dividida por el inverso del incremento deseado)

Asi que si quisiéramos ir de 0 a 10 con incrementos de 1/3, ponemos 30 en la casilla del máximo y en la celda auxiliar (A4) =A3/3

Si usamos el Control de Número (Spin Button) de la colección ActiveX podemos dar una solución sin usar una celda auxiliar programando eventos para definir el control dinámicamente de acuerdo a valores que ingresemos en celdas de la hoja.

Siguiendo con nuestro ejemplo, ponemos el valor máximo deseado (10) en la celda E3 y el valor de incremento enla celda E4. En las propiedades del control dejamos el valor de la propiedad LinkedCell (celda vinculada) en blanco

Tal como indicamos en la nota anerior, abrimos el editor de Vb en el módulo de la hoja que contiene el control seleccionando el control y apreando Ver Código. En el módulo de la hoja ponemos estos dos eventos:

El evento GotFocus define el valor máximo del control de acuerdo a los valores que ingresamos en las celda E3 y E4 y el evento Change pasa el valor calculado a la celda vinculada con cada cambio del control

Uso de controles en hojas de Excel - Spin Button con valores negativos

Desde casi los primeros días de este blog he escrito sobre el uso de controles en hojas de cálculos. Los usos son casi ilimitados: dashboards, gráficos dinámicos, listas desplegables, etc.
Excel cuenta con dos colecciones de controles: Formulario y Activex. Ya hemos escrito sobre las ventajas y desventajas de cada una de estas colecciones. En esta nota veremos como sobreponerse a las limitaciónes de los controles Control de Número (Spin Button) y Barra de Desplazamiento (Scroll Bar)

• aceptan sólo números enteros
• no aceptan números negativos

En esta nota veremos un rodeo para poder usar números negativos en estos controles.

Por ejemplo, si queremos usar el control de números (spin button) de la colección Formlarios con valores que vayan de -10 a 10, al tratar de definir el valor mínimo veremos lo siguiente

Para superar esta limitación, siguiendo con nuestro ejemplo,usamos las siguientes definiciones:

Definimos 0 en el valor mínimo y el doble del máximo deseado para el Valor Máximo. En nuestro ejemplo, el valor del control está ligado a la celda A3; en la celda A4 ponemos la fórmula =A3-10. La celda A4 mostrará los valores deseado al accionar el control

Si usamos el control Activex la situación es diferente. Aparentemente Excel acepta el número negativo en la definición del mínimo

Pero al tratar de usar el control veremos lo siguiente:

al descender de 0, en lugar de -1 Excel poner en la celda ligada 65535!! (suena familiar, no es cierto? 65536 es el número máximo de filas en las versiones anteriores a Excel 2007).

En el caso del control ActiveX, la solución consiste en programar un evento para el objeto. En el menú Desarrollador apretamos el botón Modo de Diseñño, seleccionamos el control y activamos la opción Ver Código

Al apretar Ver Código, el editor de Vb se abre en el módulo de la hoja; allí ponemos este código

Con este código el control pasa los números deseados, también los negativos. La ventaja de usar el control ActiveX es que no necesitamos agregar una celda auxiliar.

En la próxima nota veremos como usar estos controles con valores no enteros.

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Lista desplegable con combobox dinámico

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana

En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX

Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).

De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")

4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así

Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Listas desplegables dependientes con combobox

Algunos de mis memoriosos lectores recordarán seguramente las notas sobre listas desplegables dependientes y listas desplegables dependientes múltiples que publiqué hace ya más de seis años la primera y casi cuatro años atrás la segunda.

Ambas fueron muy populares, con más de 120000 vistas y 250 comentarios. Hasta hoy en día sigo recibiendo comentarios y consultas relacionados con el tema de las notas. Una de las consultas más corrientes es como construir el modelo pero usando el control combobox en lugar de validación de datos, que es la técnica que muestro en esas notas.

Hay varias razones para usar el control combobox en lugar de listas desplegables de validación de datos. Una de ellas es la posibilidad de usar la propiedad de autocompletar del combobox. Otra es el hecho de que con validación de datos, la opción se hace evidente sólo cuando se elige la celda que la contiene. Sólo cuando seleccionamos la celda que contiene la lista veremos la flecha que nos permite desplegar la lista de opciones. En cambio el cuadro combinado (combobox) es un objeto visible permanentemente.

Empecemos por recordar que Excel cuenta con dos colecciones de objetos que pueden ser incluidos en una hoja: controles de formulario y controles ActiveX

Los controles de Formulario son más fáciles de implementar, pero no nos sirven para nuestro modelo ya que no aceptan nombres que se refieren a rangos y tampoco se pueden programar.

El primer paso es crear los nombres definidos que alimentan las listas desplegables. A los efectos de este ejemplo, los rangos serán estáticos. En un modelo más avanzado crearíamos nombres definidos con rango dinámicos.

La forma más práctica de crear nombres definidos con rangos estáticos es usar la funcionalidad “Crear desde la selección”

En la hoja “Ciudades” los rangos tienen tamaños distintos por lo que usaremos un pequeño truco para no tener que definir cada nombre por separado. En la hoja “Ciudades” tenemos de hecho una tabla donde los nombres de los países figuran en la primer fila. El proceso es el siguiente:

1. Elegimos una de las celdas de la tabla (en nuestro ejemplo A1)
2. Apretamos Ctrl + * para seleccionar todo el rango de la tabla
3. Accionamos F5 (Ir A), apretamos el botón Especial y seleccionamos la opción Constantes-Texto. Esto hace que sólo las celdas que contienen los nombres de las ciudades sean seleccionadas.
4. Finalmente usamos “Crear desde la selección-Fila superior”

Este video muestra el proceso



Ahora tenemos que insertar los controles en la hoja. Elegimos el control cuadro combinado (combobox) de la colección ActiveX y lo insertamos en la hoja

Al insertar el objeto en la hoja se activa al modo Diseño; en este estado, con el sontrol seleccionado, abrimos el menú Propiedades para definir dos propiedades el control: Linked Cell y ListFIllRange

LinkedCell es la celda donde aparecerá el resultado de la elección; en este control ponemos A2. ListFillRange es el rango que contiene los valores de la lista desplegable (en nuestro caso el nombre definido Continentes)

Una vez definidas las propiedades apretamos el icono Diseño para pasar a la situación normal de uso del control. Ahora podemos elegir un contienente y éste se registrara en la celda A2

Como explicamos en las notas anteriores, los nombres definidos no aceptan espacios entre las palabras. Por lo tanto usamos la función SUSTITUIR para transformar el resultado del control al nombre definido. En la celda A4 ponemos

=SUSTITUIR(A2," ","_")

Antes de agregar un nuevo control para la lista desplegable de los países definimos el nombre “continente elegido” que se refiere a esta fórmula:

=INDIRECTO(eleccion!$A$4)

En las propiedades del control definimos:

LinkedCell: A6
ListFillRange: continente_elegido

De esta manera la lista de las ciudades depende del continente elegido y el país elegido se registra en la celda A6

Nos resta insertar el control para elegir las ciudades. Empezamos por poner la fórmula

=SUSTITUIR(A6," ","_")

en la celda A8 para transformar la elección del cuadro combinado en el nombre definido que contiene las ciudades del país elegido.
Luego creamos el nombre definido “país_elegido” que se refiere a la fórmula:

=INDIRECTO(eleccion!$A$8)

Ahora definimos las propiedades del control:

LinkedCell: A10 (o cualquier otra celda donde queramos que aparezca la ciudad)
ListFillRange: pais_elegido

Un último toque es programar un evento que limpie el contenido de las combobox cuando se cambia la elección del continente. En el módulo de la hoja del editor Vbe ponemos este código

Private Sub ComboBox1_Change()
    ComboBox2.Value = ""
    ComboBox3.Value = ""
End Sub

Este evento hace que cuando se cambia el valor en la Combobox1 (continents), se borran los valores de las dos restantes combobox.

El archivo del ejemplo se puede descargar aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Gráficos animados con Excel

En este blog ya hemos mostrado cómo crear gráficos interactivos (pueden ver las notas apretando el enlace Gráficos en la nube de etiquetas). Entendemos por gráficos interactivos aquellos donde el usuario puede cambiar el aspecto, las series y/o puntos de las series sin necesidad de acceder a la base de datos que alimentan el gráfico.

Podemos dar otro paso adelante y crear un gráfico animado, como éste



En este gráfico mostramos los cambios en la relación entre el real brasileño (BRL) y el dólar estadounidense (USD).

No me entusiasman particularmente los gráficos animados, pero en casos como éste, nos ayudan a ver o descubrir tendencias.

En esta nota vamos a mostrar cómo construirlo (el modelo se puede descargar aquí).

EL primer paso, por supuesto, es obtener los datos. En este ejemplo he obtenido los datos del sitio OANDA (datos históricos)

El segundo paso es construir el gráfico que en esta etapa será estático.

El próximo paso es agregar una barra de desplazamiento que nos permita ir cambiando los datos de la serie en el gráfico. En este caso usamos la barra de desplazamiento (scrollbar) de la colección de comandos ActiveX

En el cuadro de propiedades del control, definimos la celda B9 de la hoja “dinamico” como la celda ligada

A la celda B9 le hemos asignado el nombre “chrtCounter”.

En la celda B6 de la hoja “dinamico” ponemos el número de puntos que queremos que aparezcan en el gráfico. En nuestro caso hemos definido 30

A la celda le hemos asignado el nombre “chrtStep”.

Un último parámetro a definir para la barra de desplazamiento es el valor máximo. Nuestra serie tiene 366 puntos (valores); 30 aparecen en el gráfico así que el valor máximo de la barra de deslazamiento será 336 (para evitar que aparezcan puntos sin valor). Si la cantidad de puntos de la serie y el número de puntos a exhibir en el gráfico no varían, podemos poder una constante en la celda B7 (a la que le hemos asignado el nombre definido “chrtMaxScrollBar”).
Pero si queremos tener más control de las definiciones, usamos una fórmula para determinar dinámicamente el valor máximo. En la celda B7 ponemos:

=CONTAR(datos!$B$2:$B$367)-chrtStep

Excel no nos permite usar una referencia a la celda para definir el valor máximo de la barra de desplazamiento. Para hacerlo programamos el evento ScrollBar1_Change()

Private Sub ScrollBar1_Change()
    ScrollBar1.Max = Range("chrtMaxScrollBar")
End Sub

Ahora tenemos que definir nombres que se refieran dinámicamente a los puntos de la serie de datos y a la categoría (los valores del eje de las X). Creamos dos nombres:

- Para los puntos de la serie de datos

chrtSeries =DESREF(datos!$C$2,chrtCounter,0,12+chrtStep,1)

- Para los puntos del eje de las X

chrtCategory =DESREF(datos!$B$2,chrtCounter,0,1+chrtStep,1)

Ambos nombres se refieren a fórmulas que usan la función DESREF para determinar el rango de valores a mostrar de acuerdo a los valores que el usuario haya asignado a la barra de desplazamiento.
Cada vez que el usuario pulsa la barra de desplazamiento, el valor de la celda ligada (chrtCounter) cambia.



Para que estos cambios se reflejen en el gráfico remplazamos los rangos de la función SERIES del gráfico con los nombres definidos. Seleccionamos el gráfico y usamos el menú “seleccionar origen de datos-editar” para remplazar la referencia fija al rango por el nombre que se refiere al rango dinámico

A esta altura de los acontecimientos podemos lograr la animación sencillamente arrastrando el marcador de la barra de desplazamiento (como se ve en el video). Pero en nuestro caso usamos una macro para disparar la animación y otra para volver el gráfico al punto de partida. Estas macros están ligadas a los botones con los símbolos “>>” y “<<” grafAnim07 Todo lo que hace la macro para animar el gráfico es cambiar el valor de “chrtCounter”


Sub animate_Chart()
    Dim iX As Integer, Delay As Single, Start As Single
   
    'fijar valores del eje Y del grafico
    Call fix_Y_Values
   
    Range("chrtCounter") = 0
   
    For iX = 0 To Range("chrtMaxScrollBar")
        Range("chrtCounter") = Range("chrtCounter") + 1
            Delay = 0.1
            Start = Timer
            Do While Timer < Start + Delay
                DoEvents
            Loop
    Next iX
   
End Sub


Para volver el gráfico a la situación inicial sencillamente fijamos el valor de “chrtCounter” a 0

Sub backTo0()
    Range("chrtCounter") = 0
End Sub


El ultimo detalle es fijar el valor mínimo y máximo del eje de las Y. Podemos usar constantes, pero mejor es determinar estos valores en forma dinámica usando estas fórmulas

Para el valor mínimo (chrtXmin): =MULTIPLO.INFERIOR(MIN(datos!$C$2:$C$367),0.1)

Para el valor máximo(chrtXmax): =MULTIPLO.SUPERIOR(MAX(datos!$C$2:$C$367),0.1)

Estos valores se fijan al empezar la macro de la animación con la rutina Call fix_Y_Values

Private Sub fix_Y_Values()
   
    ActiveSheet.ChartObjects("chrtUSDBRL").Activate
    With ActiveChart
        .Axes(xlValue).MinimumScale = Range("chrtXmin")
        .Axes(xlValue).MaximumScale = Range("chrtXmax")
    End With
   
    Range("A1").Select
   
End Sub

El cuaderno con el ejemplo se puede descargar aquí.

Gráfico Big Mac dinámico en Excel

La publicación británica The Economist publica desde hace varios años el índice Big Mac. El Big Mac Index (o Índice Big Mac, en español) es un índice elaborado a partir de una investigación no científica, que permite comparar el poder adquisitivo de distintos países donde se vende la hamburguesa Big Mac de McDonald's (citado de Wikipedia).

El diario argentino La Nación publicó este gráfico que muestra la diferencia del precio del Big Mac en relación al valor en los Estados Unidos

Este gráfico fue construido con la aplicación Tableau.

Como ya habrán intuido, la pregunta es: ¿se puede hacer con Excel?

Veamos qué elementos incluye:

• Gráfico de barras por país
• Controles que permiten visualizar los países por continente
• Los colores de las barras representan el valor (diferencias positivas en rojo, diferencias negativas en verde)

La tabla de datos es la siguiente

Empezamos por ordenar la tabla en orden ascendente según el campo Porcentaje; seleccionamos los campos “País” y “Porcentaje” para construir este gráfico de barras

Este es el gráfico de barras estándar de Excel con unas pocas modificaciones: quitamos las líneas de cuadrícula, fijamos las etiquetas del eje vertical en “bajo” y en formato de series de datos—relleno marcamos la opción “variar colores entre puntos”.

Este gráfico es una primera aproximación. Para poder agregar los elementos dinámicos y los calores del gráfico original tendremos que hacer algunas transformaciones.

Una segunda aproximación es usar una tabla dinámica para generar un gráfico dinámico que muestre sólo los países de los continentes elegidos



Lo que hemos hecho es generar una tabla dinámica y un gráfico dinámico basada en ella; la tabla está en la filas 3 a 27 que hemos ocultado, dejando visible sólo el campo de filtro del informe dinámico. Además hemos agregado una segmentación de datos para mostrar cuáles son los continentes elegidos

Pero para crear un gráfico como el publicado en La Nación tendremos que usar Vba (macros) y controles.
Esto es lo que queremos crear:



La anatomía del modelo es la siguiente:

1 – Controles: insertamos 7 casillas de verificación (de la colección de controles de hoja, no ActiveX) y los ligamos a las celdas en el rango B3:B9. Cuando se señala el control, la celda correspondiente muestra VERDADERO; en caso contrario mostrará FALSO. Usaremos estos valores en las macros que controlan los puntos mostrados en el gráfico.

2 – Celda de control: la celda B11 (el nombre Dimension_del_array se refiere a esta celda), cuenta cuantos controles han sido señalados, es decir, cuantos continente queremos mostrar en el gráfico. Cuando el valor es 6, significa que hemos elegido todos los continentes. También esta celda la usaremos en nuestras macros.

3 – Macros: programamos dos macros para ocultar o mostrar datos con Autofiltro en la tabla delos datos, una que responde a las elecciones de los controles excepto el control “Todos”, la segunda para el caso que el usuario señale la casilla “Todos”

El código de la primer macro es

Sub select_series()

    Dim strContinentes() As String
    Dim iR As Integer
    Dim iCounter As Integer
  
    If Range("Dimension_del_array") = 6 Then
        Range("Todos") = True
    Else
        Range("Todos") = False
    End If

    'redimensionar el array
    ReDim strContinentes(Range("Dimension_del_array"))
  
    iCounter = 0
  
    With Sheets("grafico dinamico")
    For iR = 4 To 9
        If .Cells(iR, 2) Then
                strContinentes(iCounter) = .Cells(iR, 1)
                iCounter = iCounter + 1
        End If
    Next iR
  
    Sheets("datos").Range("$A$1:$C$24").AutoFilter Field:=1, _
                Criteria1:=strContinentes, Operator:=xlFilterValues
  
    End With
    
End Sub


La segunda macro, para el caso que se haya elegido la casilla “Todos” es

Sub all_Continents()
    Dim iR As Integer
  
    'si se elige Todos
    With Sheets("grafico dinamico")
    If Range("Todos") Then
        For iR = 4 To 9
            .Cells(iR, 2).Formula = True
        Next iR
    Else
        For iR = 4 To 9
            .Cells(iR, 2).Formula = False
        Next iR
    End If
    End With

    Call select_series
        
End Sub


En este modelo usamos una única serie de valores, por eso para ocultar algunos de los puntos de la serie con Autofiltro (los países de los continentes que no hemos elegido) nos aprovechamos de la propiedad de los gráficos de no mostrar los valores de celdas ocultas.

El control “Todos” está asociado a la macro “all_Continents”

A los demás controles les hemos asignado la macro “select_series”.

Este modelo puede adaptarse a muchos escenarios.

El cuaderno puede descargarse, sin cargo, aquí (usar el enlace Descargar en la parte inferior de la página). Quien esté interesado en una explicación detallada del modelo y la contraseña para acceder a los códigos, puede descargar el manual (tiene un costo de 5 Euros).

Validación de datos - Tamaño de la fuente de la lista desplegable

En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.

El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:

1 – Algunos de los valores de la lista desplegable aparecen “cortados”

2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles

En esta nota mostraré dos estrategias para solucionar estos problemas:

1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;

2 – usar una combobox de la colección de controles ActiveX.

Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%

Solución con un evento simple.


La estrategia es programar un evento de manera que cuando el usuario seleccione la celda que contiene la lista (E2), el ancho de la columna E pase a ser 30 (antes del evento es 15) y el zoom será del 120%. Al seleccionar cualquier celda fuera de E2, el zoom vuelve a ser del 80% y el ancho de la columna 15.
En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Range("E2").ColumnWidth = 30
    Else
        Range("E2").ColumnWidth = 15
        ActiveWindow.Zoom = 80
    End If

End Sub

Solución con evento complejo

La estrategia es la misma que en la solución anterior, pero el ancho de la columna será fijado dinámicamente de acuerdo al valor de la lista más largo. Para esto creamos una función UDF (definida por el usuario) para determinar este valor y lo pasamos al código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim iColWidth As Double
'
    iColWidth = max_len_in_range(Range("lstDepartamentos"))
      
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Target.ColumnWidth = iColWidth
    Else
        ActiveWindow.Zoom = 80
        Range("E2").ColumnWidth = 15
    End If

End Sub

Private Function max_len_in_range(rngList As Range)
    Dim iR As Long
    Dim tmpLen As Integer
  
    For iR = 2 To rngList.Count
        If Len(rngList(iR)) > Len(rngList(iR - 1)) Then
            tmpLen = Len(rngList(iR))
        End If
    Next iR
  
    max_len_in_range = tmpLen
  
End Function
Solución con ComboBox de la colección ActiveX

La ventaja de los controles ActiveX es que sus propiedades y sus eventos pueden ser programados. Esto nos permite “detectar” cuando el usuario activa el control y también cuando lo desactiva. Programamos un evento para cada una de las situaciones:

Private Sub ComboBox1_GotFocus()
    ActiveWindow.Zoom = 120
    Application.EnableEvents = False
    Me.Select
    Application.EnableEvents = True
End Sub

Private Sub ComboBox1_LostFocus()
ActiveWindow.Zoom = 80
End Sub

Los códigos hay que ponerlos en el módulo de la hoja correspondiente.

El archivo con los ejemplos se puede descargar aquí.