Clase jueves 21/03/2013 (parte 2/2). Userforms en Excel con VBA

Continuando con nuestro ejemplo de userforms VBA en Excel, lo que requerimos que haga nuestro formulario es insertar un registro en la tabla Calendario. Para llevar a cabo este proceso, podemos apoyarnos en la funcionalidad de grabación de macros de Excel, y ver el código que se generaría si realizamos el proceso de forma automática. De esta forma, no necesitaremos conocer a priori los detalles de todo el código a generar, aunque siempre sea recomendable conocerlo en la mayor medida posible. En cualquier caso, la grabación facilitará la tarea. Seguimos los pasos:

  1. Iniciamos la grabación de la macro.
  2. Ya iniciada, nos situamos en la cabecera de la tabla, en la primera columna.
  3. Pulsamos la combinación de teclas Ctrl + Flecha abajo, situándonos en el último registro (fila) introducido.
  4. Pulsamos la flecha hacia abajo, para escribir en la primera fila posterior a la tabla.
  5. Escribimos cualquier texto en la celda, y nos desplazamos hacia la derecha con el tabulador, volviendo a escribir otro texto cualquiera en la siguiente celda. Como vemos, esto crea una nueva fila en la tabla, que es lo que queríamos.
  6. Paramos la macro con el botón de Stop.

El texto generado contendrá instrucciones que nos interesará, y otras que veremos que no nos son de utilidad.

Así, podemos intuir que la primera instrucción selecciona la primera columna dentro de la cabecera de nuestra tabla, y la segunda hace el efecto de situarnos al final de esa columna de la tabla. Lo que queremos es desplazarnos debajo del último registro introducido, luego podremos desplazarnos hacia abajo una fila. Como vemos, la tercera instrucción no realiza esto con carácter general, sino que nos sitúa en una posición fija, cuando nuestro botón de Aceptar deberá desplazarse una posición abajo, variando conforme vayamos añadiendo registros. Por ello, esa instrucción no nos servirá, y en lugar emplearemos ActiveCell.Offset(1, 0).Select. En cualquier caso, podemos copiar el código generado por la macro, volver a nuestro userform en la ventana de VBA, hacer doble click sobre el botón Aceptar y pegar el código. A partir de ahí, vamos usando offset para desplazarnos y los valores de las celdas y controles para escribir la información del formulario en las celdas correspondientes. El resultado es el que vemos a continuación:

Para el botón cancelar, lo único que tenemos que hacer es volver al userform, dar doble click al botón Cancelar, y en el código indicar que se cancele la operación, cerrando el formulario mediante su descarga (unload):

Para cualquier duda en lo que respecta a userforms o cualquier otro tema anterior, no duden en contactar con el profesor por los cauces habituales de tutorías, teléfono o email.

Clase jueves 21/03/2013 (parte 1/2). Userforms en Excel con VBA

Los userforms proporcionan un mecanismo para diseñar y desarrollar interfaces gráfica de usuario basadas en VBA (Visual Basic for Applications) en Excel.

Estuvimos creando en la clase anterior una interfaz como la siguiente para insertar registros en una tabla Excel.

En la sesión 10 disponible en moodle vimos varios enlaces (link1, link2) con ejemplos básicos paso a paso de creación de userforms. Además, se proporcionan en la misma presentación enlaces introductorios sobre VBA y macros.

En nuestro caso realizamos el proceso siguiente, inspirado en un ejemplo muy similar que se puede ver en este vídeo:

  • Importamos los nombres de los equipos de alguna fuente web donde estuvieran disponibles.
  • Convertimos este conjunto de datos en tabla, dotándola de un nombre como “Clubes“.
  • Creamos la tabla “Calendario” en la que se irán almacenando los partidos disputados o planificados, con los datos que se muestran en la imagen.
  • Creamos el esqueleto de un userform inicial mediante la ficha del Programador > Visual Basic; desde la ventana de visual basic, menú Insertar > UserForm. De este modo nos aparece el userform con el contenido por defecto:
  • A partir de ahí, vamos arrastrando controles del cuadro de herramientas al userform. En nuestro caso, dos cuadros combinados para los equipos local y visitante, dos botones de opción para indicar si el partido ya se ha disputado y áreas de texto con controles de número para ir introduciendo el número de goles del equipo local y visitante, además del botón correspondiente para aceptar la inserción del partido. Junto a ello, las pertinentes etiquetas para mostrar al usuario qué información introducir o seleccionar en cada control.
  • Pinchando sobre el formulario y los distintos controles se activa la información correspondiente a las Propiedades del elemento seleccionado en cada momento, donde establecemos datos como el nombre para el formulario o control, el color de fondo, etc.
  • Como comentamos, resulta interesante establecer convenciones para los nombres de los controles, como empezar por Frm en el caso de los formularios, por Cmb los cuadros combinados, Opt los botones de opción, Txt las áreas de texto, Num los controles de número, Btn los botones o Lbl las etiquetas (del inglés Label), entre otras. De esta forma podremos encontrar rápidamente el nombre de nuestro control en cuanto comencemos a escribir en el código sus primeras letras siguiendo estas convenciones.
  • Ya tenemos un primer formulario con una determinada, pero debemos llamarlo desde nuestra hoja Excel. Para ello, podemos volver a la hoja Excel e insertar en un botón de formulario o alguna otra forma que nos permita asignar la acción correspondiente a abrir el formulario. Elegimos Insertar > Forma > Rectángulo redondeado, quedando como vemos en la imagen del principio de este post. Con botón derecho sobre esta forma podemos establecer el texto que aparecerá, y también Asignar macro…, donde podemos poner un nombre a la acción que se lanzará desde nuestra forma redondeada, y pulsar en Nuevo. Esto nos llevará al código VBA, digamos subrutina (Sub), donde podemos indicar como acción que se nos muestre nuestro formulario. Como vemos, lo realiza el procedimiento o método Show sobre nuestro userform FrmCalendario.
  • Si guardamos lo que llevamos hecho, cerramos la ventana de Visual Basic y volvemos a nuestra hoja, ya podemos pulsar en el botón y se abrirá nuestro formulario.
  • A partir de aquí, habrá que crear la funcionalidad correspondiente a los controles y botones del formulario, de forma similar a lo explicado en el vídeo anterior. Para ello, habrá que volver a la ventana de visual basic, y completar varios pasos:
    • Para asociar los datos que deben aparecer en los cuadros combinados de los clubes, debemos seleccionar el cuadro correspondiente y, en Propiedades, escribir en RowSource el nombre de nuestra tabla con los equipos (en este caso Clubes).
    • Para actualizar los cuadros de texto con los goles seleccionados en los respectivos controles de número, pulsando doble click sobre cada uno de los controles nos crea directamente una sección de código, de nuevo Sub, donde podemos indicar el código a ejecutar cada vez que se modifica el valor del control de número (cada vez que pulse el usuario). En este caso, lo que hacemos es asignar el valor del control al área de texto, como se ve en la imagen, para cada control.

En el próximo post terminaremos de explicar la funcionalidad asociada a los botones de Aceptar y Cancelar.

Clase jueves 18/03/2013 (parte 4/4). Controles de formulario

Para enriquecer la apariencia de nuestras hojas Excel, aportar opciones visuales a los usuarios y tener un mayor control sobre la entrada de ciertos datos podemos hacer uso de los controles de formulario. Como se puede ver en el mini-vídeo del enlace anterior, estos controles se sitúan sobre la propia hoja Excel, al mostrarse la misma. No hay que confundir esto con los formularios de datos, proporcionados por defecto por las tablas, ni con los userforms de VBA, que comentaremos en otro post. Podemos ver la distinción entre estas 3 funcionalidades en este enlace o en office.com.

  • Para emplear controles de formulario es necesario que tengamos habilitada y visible la ficha del programador, que como muestra el enlace se activa desde el botón de Office.
  • Hecho esto, tendremos disponible en la cinta de opciones la ficha “Programador”, y en la sección de Controles un botón Insertar con la posibilidad de elegir controles de formulario o controles ActiveX. Nos centraremos en el primer tipo, como vemos en la imagen:
  • A partir de ahí podremos insertar cuadros combinados, controles de número, barras de desplazamiento, botones de opción, casillas de verificación y otros:
  • Vimos cómo proporcionar los datos para cada uno de los controles, y se puede encontrar una buena explicación aquí. En resumen, podemos configurar el uso de cada control insertado mediante: botón derecho del ratón sobre el control > formato de control. Una vez ahí se indica con “Vincular con la celda” la celda en la que se almacenará el dato indicado por el control, y el resto de datos dependerá del control concreto.

Clase jueves 18/03/2013 (parte 3/4). Tablas y gráficos dinámicos

Las tablas dinámicas proporcionan una forma compacta y flexible de aportar un resumen a partir de un conjunto de datos, atendiendo a distintos criterios de agrupación por filas y columnas, automatizando cálculos sobre la agrupación, permitiendo filtrados y en definitiva facilitando la interpretación de los datos a través de la vista resumida.

Los gráficos dinámicos parten de las mismas premisas anteriores, y complementan esta vista mediante su representación gráfica, también de forma dinámica a partir del conjunto de datos de interés.

En el curso de Aulaclic al que hicimos referencia proporcionan buenos ejemplos paso a paso analizando el mecanismo para la creación de tablas dinámicas. Para afianzar los contenidos que vimos en clase resultaría de gran utilidad realizar estos ejemplos. Además, es muy recomendable realizar los ejercicios de tablas dinámicas propuestos en la presentación de la sesión 9 disponible en moodle. Además de en el ejercicio paso a paso del manual/ejemplo de tablas dinámicas sobre coches, se puede consultar el mecanismo de creación de campos calculados en tablas dinámicas en este enlace.

Clase jueves 18/03/2013 (parte 2/4). Filtros avanzados

Vimos que los filtros automáticos proporcionan una potente herramienta de filtrado, pero nos encontramos con ciertas carencias para: expresar propiedades de tipo OR entre varias columnas, conjunciones o disyunciones con 3 o más elementos o expresar propiedades más complejas. En estas y otras situaciones necesitaremos hacer uso de un filtro avanzado (ver también este enlace o este otro).

Como vimos en clase y se puede seguir paso a paso en el enlace anterior, para realizar un filtro avanzado a partir de un rango o tabla como ésta:

hay que seguir una serie de pasos (suponiendo la tabla ya creada):

  1. Preparar un conjunto de celdas en las que se introducirán los criterios de filtrado. Deberá constar de: una fila con al menos los mismos encabezados del rango o tabla  de origen, y posiblemente celdas para criterios adicionales; un conjunto de filas bajo la anterior, en la que introducir los valores de los criterios.
  2. Escribir los criterios de filtrado, situarnos en la tabla de origen y pulsar en la cinta de opciones en la pestaña Datos >   Ordenar y filtrar > …Avanzadas
  3. En ventana de filtro avanzado que se nos abre, seleccionar el rango de origen, el de filtrado y aquel en que mostrar los resultados.
  4. Finalmente, se obtiene el resultado del filtro en el tercer rango incluido anteriormente:

En el enlace anterior se puede ver un ejemplo adicional, y es interesante revisar este otro enlace en el que se aprovecha mejor la potencia y riqueza expresiva del filtro avanzado (otros enlace complementario aquí).

Por otra parte, vimos brevemente las funciones de bases de datos. Un recurso útil para trabajar con este tipo de funciones, basada en una estructura similar al filtro avanzado, se puede encontrar en este enlace.

Clase jueves 18/03/2013 (parte 1/4)

La clase del pasado lunes 18 estuvo dedicada a trabajar con rangostablas (denominadas listas hasta Excel 2003, y tablas desde 2007), generar tablas y gráficos dinámicos e incluir en nuestras hojas Excel controles de formulario.

Hemos estado trabajando desde el principio del curso con rangos sobre un conjunto de datos, conjuntos de datos asociados en forma de columnas con encabezados y con filas aportando series de valores sobre los datos. En la primera celda de determinadas columnas íbamos creando fórmulas dependientes de otras celdas. Además, arrastrábamos las fórmulas hacia abajo para calcular la misma fórmula para las filas restantes del rango. Es una estructura de datos muy útil a la hora de realizar análisis de datos.

Estas estructuras pueden ser mejoradas en diversos aspectos por las tablas/listas. Similares a sus predecesores ya comentados, proporcionan funcionalidad adicional que viene a agilizar el trabajo con nuestros datos. Estuvimos viendo cómo crear una tabla a partir de un rango. Además, como se indica en el enlace, se vieron las principales mejoras proporcionadas por las tablas:

  • Al crear una tabla Excel se agrega automáticamente el Autofiltro, que proporciona unas opciones para filtrar los datos por un conjunto de valores o a través de filtros numéricos, de texto, de fechas, etc.
  • la tabla se expande al agregar una celda bajo la última fila rellena. Todas las referencias ligadas a la tabla se adaptan al cambio automáticamente (gráficos, nombres, fórmulas), como vimos en la clase anterior sobre rangos dinámicos.
  • Al agregar filas a la tabla, todas las fórmulas en columnas con fórmulas son copiadas automáticamente.
  • Si usamos el tabulador para navegar, Excel selecciona automáticamente la celda siguiente de izquierda a derecha y de arriba hacia abajo, facilitándonos la inclusión de datos.
  • Existe una funcionalidad de Excel que nos proporciona un formulario visual por defecto para insertar, editar o filtrar registros de la tabla.
  • Permite agregar fácilmente una fila de totales en la que seleccionar el tipo de total a generar para cada columna. Esta fila se desplaza automáticamente hacia abajo conforme se van añadiendo filas a la tabla, lo que resulta muy ventajoso para poder despreocuparnos de esa labor.

Con el objetivo de afianzar los conocimientos adquiridos, se recomienda encarecidamente completar los ejercicios sobre tablas disponibles en moodle a aquellos alumnos que no los realizaron en clase.

Clase jueves 14/03/2013 (parte 3/3 – subparte 3/3). Rangos dinámicos. TABLAS

El final de la clase nos proporcionó una interesante alternativa a la combinación de DESREF e INDIRECTO, quizás menos flexible pero más sencilla de utilizar y aplicable a los casos más frecuentes: la selección dinámica de los rangos destino a través de INDIRECTO, siendo estos rangos tablas, lo cuál lleva implícito el hecho de proporcionar rangos dinámicos. Es decir, cuando hacemos referencia a una tabla, el rango al que accedemos es dinámico, de forma que si se actualiza la tabla tendremos automáticamente actualizadas nuestras funcionalidades dependientes de ese conjunto de datos.

Se puede encontrar el ejemplo detallado con el que estuvimos trabajando en clase en este enlace. En él se explica muy bien el rol que desempeña la función INDIRECTO, como vimos anteriormente, para acceder a distintas referencias según la selección del usuario en una lista desplegable previa. Mediante distintas tablas garantizamos que los rangos a los que se hará referencia también se vean actualizados, en lugar de ceñirse a un conjunto predefinido.

Clase jueves 14/03/2013 (parte 3/3 – subparte 2/3). Rangos dinámicos. INDIRECTO

Continuando con los rangos dinámicos, vimos una buena opción para generar referencias dinámicas a través de la función INDIRECTO (para más usos de interés ver este enlace). Esta función recibe una cadena de texto con una referencia y devuelve la propia referencia. Eso permite indicar cosas como INDIRECTO(“A1″), pero también otras combinaciones como INDIRECTO(A2&”!B:B”), pudiendo contener A2 un nombre de rango o un nombre de pestaña, por ejemplo (ver este enlace con el ejemplo extendido con el que estuvimos trabajando en clase). En resumen, teníamos un dato mes, con una lista desplegable de valores posibles creados mediante validación de datos:

Por cada mes, teníamos una pestaña con los datos de las ventas del mes, con las ventas en la columna B. Así, mediante la fórmula de arriba INDIRECTO(A2&”!B:B”) estamos indicando que se concatene el mes (“febrero” en la imagen, pero será el que se vaya seleccionando) con la cadena “!B:B”; recordemos que la notación ! indica que estamos accediendo a una parte de una hoja (en el ejemplo “febrero”), concretamente a la columna B completa (B:B). Así, esta llamada a la función INDIRECTO estaría devolviendo la referencia a la columna B de una determinada pestaña, que será escogida de forma dinámica a través de la lista desplegable, pero sin tener que variar la función anterior. La referencia obtenida se pasará a la función SUMA, con: =SUMA(INDIRECTO(A2&”!B:B”)), logrando así la suma dinámica del mes que seleccione el usuario de la hoja.

Diversos ejemplos avanzados del uso de esta función se pueden encontrar en las referencias de la sesión 8 en moodle, o bien en enlaces como éste, éste o éste otro.

Clase jueves 14/03/2013 (parte 3/3 – subparte 1/3). Rangos dinámicos. DESREF

La tercera parte de la clase estuvo dedicada a los rangos dinámicos, un tema muy importante a la hora de trabajar con hojas de cálculo, en especial si pretendemos generar informes, tablas y gráficos que se actualicen automáticamente cuando actualizamos nuestros datos de partida, sin necesidad de modificar los informes ni gráficos. Veamos un escenario simplificado en el que se puede presentar esta necesidad (ver original desarrollado en el blog JLD Excel en castellano). Supongamos que disponemos de una serie de datos de ventas como el siguiente:
A partir de él, pretendemos obtener un gráfico de columnas con la evolución de las ventas mes a mes, para lo que seleccionamos los datos y vamos a la opción de la pestaña Insertar > Gráficos > Columna, 2-D, Agrupadas. De este modo obtenemos algo del tipo:
Ahora incluimos un registro adicional en el rango anterior para el mes abr-13, con ventas de 30.000 €. Como era de esperar, el gráfico generado no se actualiza. La razón es bien simple: depende de un rango de datos estático, no dinámico, no se actualiza al incluir nuevos datos. ¿La solución? Algún mecanismo que haga que nuestro rango sea dinámico. ¿Cómo? Existen varias alternativas que analizaremos a continuación:

  • Empleo de la función DESREF. Como vimos en una clase anterior, la función DESREF tiene la forma: DESREF(ref;filas;columnas;alto;ancho). Mediante esta función podemos, partiendo de una celda de referencia dada por ref, desplazarnos a la posición resultante de movernos el número de filas y columnas indicado; desde esa posición, se devolverá la referencia que empieza en la misma, y que está compuesta por el número de filas dado por alto y el número de columnas dado por el ancho. ¿Cómo podemos crear un rango dinámico con esta función?
    Se puede realizar mediante la definición de un nombre de rango dinámico. Podríamos realizarlo de la forma habitual mediante la selección del rango original, poniendo un nombre en el cuadro de nombres. Pero luego tendríamos que ir a Fórmulas > Administrador de nombres, y ahí indicar la construcción que veremos. O bien directamente ir a Fórmulas > Definir nombre… Con ambas alternativas, la fórmula a indicar para nuestro caso sería la misma. Por ejemplo, para el caso de los meses, asumiendo que la cabecera de la tabla se encuentra en la celda A1 de la hoja “Hoja1”, tendríamos para el rango “Mes” una fórmula como ésta: =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1), como vemos en la imagen:
    Es decir, partimos del inicio de la tabla (sin contar la cabecera); no nos desplazamos (0, 0), ya que nuestro punto de partida deberán ser nuestros datos de la columna correspondiente; el número de filas coincidirá con el total de filas en cada momento en la columna correspondiente, pero descartando la cabecera, por lo que restamos 1; y únicamente tomamos una columna, ya que estamos haciendo referencia a Mes. Análogamente actuaríamos creando un rango dinámico de nombre “Ventas”.
    A continuación, podríamos adaptar el gráfico inicial para tomar los datos a partir de los rangos dinámicos creados. ¿Cómo hacemos esto? La respuesta está en la función SERIES que lleva incorporado el gráfico, y que podemos ver si pulsamos sobre alguna de las columnas, apareciendo en la barra de fórmulas una expresión del tipo: =SERIES(Hoja3!$B$1;Hoja3!$A$2:$A$4;Hoja3!$B$2:$B$4;1).
    Todo lo que debemos hacer es sustituir el rango fijo Hoja3!$A$2:$A$4 por el nuevo rango Mes de la misma hoja, y de forma equivalente sustituyendo Hoja3! por Ventas. Finalmente quedará algo como: =SERIES(Hoja3!$B$1;Hoja3!Mes;Hoja3!Ventas;1). Se puede comprobar incluyendo nuevas filas en la tabla de datos, que la gráfica se actualiza automáticamente al actualizarse los datos de origen.
    Se pueden encontrar usos avanzados de la función DESREF en este enlace.

Clase jueves 14/03/2013 (parte 2/3)

La segunda parte de la clase estuvo dedicada a la importación de datos en Excel.

Como estuvimos viendo, existen diferentes opciones para importar los datos, entre las que se encuentran:

  • Importación desde una base de datos Access. Desde la pestaña Datos > Obtener datos externos > Desde Access. Esta opción nos permite seleccionar una base de datos, e importar datos como una tabla, como informe de tabla dinámica o como informe de tablas y gráficos dinámicos. La primera opción, importar como una tabla, es la más usual, y permite la importación de tablas de una base de datos a nuestra hoja de cálculo Excel.
  • Importación desde Web. Desde Datos > Obtener datos externos > Desde Web. Como vimos en clases anteriores, esta opción nos abre un navegador interno en Excel, donde podemos introducir la dirección de una web e ir buscando la página web de la que nos interese importar datos. Una vez en ella, se nos proporcionan zonas de la página que podríamos importar, como en la siguiente imagen:Pulsando en las flechas con fondo amarillo que se indican, vamos seleccionando los fragmentos de la página que nos interesan, lo cuál se llevará a cabo una vez pulsemos en Importar e indiquemos la celda donde queremos que se incluyan los datos.
  • Importación desde texto. Esta opción, Datos > Obtener datos externos > Desde texto, nos permite importar tanto archivos de texto de tipo TXT como otros formatos muy comunes como CSV. Además, podemos indicar distintas opciones para: comenzar en una determinada línea del archivo; reconocer columnas diferentes en un archivo de texto en función de los caracteres que separanlos datos o a partir de columnas con un ancho fijo; indicar los formatos para los datos a importar, incluyendo los caracteres usados para símbolos de decimales o de miles. Podemos ver una muestra de importación como la que hicimos en clase en la secuencia siguiente:

Existen otras muchas opciones para la importación, algunas de las cuáles se han dejado como referencias en la presentación “hojas de cálculo 8” disponible en moodle.

Algunas opciones interesantes para ampliar conocimientos sobre la importación de datos se pueden encontrar en este enlace.