Listas Enlazadas
Listas Enlazadas
Retos: 3
INTRODUCCIÓN
En este artículo voy a describir como realizar listas enlazadas utilizando tres distintas técnicas empleando diversas funciones y herramientas específicas de Excel.
El primer reto utilizará la función INDIRECTO, la herramienta de Validación de datos y el concepto de etiquetas. El segundo reto utilizará la función INDIRECTO apoyada con las funciones INDICE y BUSCARV y utilizando controles de formulario (listas). El tercer y último reto empleará controles de formulario programados con Macros, sin usar funciones nativas de Excel ni herramientas de datos y solamente unas cuantas etiquetas de ayuda para no hacer complejo el código.
El ejemplo será en torno a Países – Estados (Provincias) – Ciudades y utilizará el concepto de listas dependientes de datos, también conocidas como listas enlazadas, con el objetivo de que los usuarios puedan seleccionar de una lista un determinado País y desde otra lista aparezcan los estados del país seleccionado y, a su vez, una vez escogido un Estado aparecerán en una tercera lista las Ciudades de dicho Estado. Por ejemplo se podrá consultar al país Canadá y aparecerán sus Provincias (Estados) como Alberta, British Columbia o Quebec y al seleccionar la primera Provincia, deberán de mostrarse ciudades como Calgary, Edmonton..., o sea, solamente las pertenecientes a la Provincia de Alberta.
Se adjuntan tres archivos de Excel, uno por cada reto resuelto.
SOLUCIÓN RETO 1 Lista Enlazada
Requisitos:
- Función INDIRECTO(ref,a[1])
- Herramienta de Validación de datos (menú: Datos -> Herramientas de datos -> Validación de datos)
- Concepto de etiquetas (menú: Fórmula -> Nombres definidos -> Administrador de nombres)
Paso 1. Preparación de los datos en la hoja de cálculo.
Dispondremos de un libro de Excel que contendrá dos hojas de cálculo: Base y Consulta. En la hoja Base colocaremos la información de cada país. Para efectos prácticos solo utilizaremos tres países, algunos estados o provincias y algunas ciudades de dichos estados.
IMPORTANTE: En las celdas C1:E1 se encontrarán los nombres de las etiquetas de los países los cuales serán idénticos a los que están en el rango A2:C4 y estos no deberán tener espacios en blanco ya que para declarar las etiquetas no se permiten dichos espacios. En su defecto usar el guion_bajo. Lo mismo aplica para los nombres de etiquetas.
El la gráfica anterior no alcanza a aparecer toda la información, a continuación se enlista la información completa de las ciudades y deben de colocarse en el rango G1:O6 de la hoja de cálculo Base:
|
Alberta |
British_Columbia |
Quebec |
Guanajuato |
Jalisco |
Michoacán |
California |
Florida |
Texas |
|
Banff |
Kamloops |
Drummondville |
Celaya |
Arandas |
Apatzingán |
Los_Angeles |
Fort_Lauderdale |
Brownsville |
|
Calgary |
Richmond |
Laval |
Guanajuato |
Guadalajara |
Ciudad_Hidalgo |
Sacramento |
Jacksonville |
Dallas |
|
Edmonton |
Vancouver |
Montreal |
Irapuato |
Puerto_Vallarta |
Morelia |
San_Diego |
Miami |
Fort_Worth |
|
Jasper |
Vernon |
Quebec |
León |
Tepatitlán |
Uruapan |
San_Francisco |
Orlando |
Houston |
|
Lethbridge |
Victoria |
Sherbrooke |
Salamanca |
Tonalá |
Zamora |
San_José |
Tampa |
San_Antonio |
La hoja Consulta nos servirá para que los usuarios consulten la información de los países, inicialmente tendrá los datos siguientes:
Paso 2. Etiquetado de las listas
Ahora procederemos a etiquetar la lista de países, las tres listas de estados o provincias y las nueve listas de ciudades.
Empezaremos seleccionando los rangos "A1:A4", “C1:E4” y “G1:O6” y procederemos a etiquetarlos una opción es ir al menú Fórmulas y luego a la sección Nombres definidos y ahí dar clic en el icono "Crear desde la selección". Aparecerá una ventana emergente como se muestra a continuación.
Al darle aceptar se habrá creado las etiqueta "País", “Canadá”, “México”, “USA”, “Alberta”,… con lo cual ya no será necesario referenciar a esos datos mediante rangos sino por el nombre dado a la etiqueta. También podemos utilizar la combinación de teclas CTRL+SHIFT+F3 para que aparezca la ventana emergente de creación de nombres.
Para verificar, modificar o eliminar los rangos creados en el mismo menú Fórmulas, en la sección Nombres definidos dar clic en el icono Administrador de nombres:
De la gráfica anterior, observamos que en la columna “Nombre” aparece un icono de forma de etiqueta junto al nombre del país o estado. La columna “Valor” ofrece los primeros elementos que conforman dicha lista. Finalmente, notemos que la columna “Se refiere a” muestra los rangos de donde se obtienen los datos de las listas, verifique que todas empiecen a partir del renglón 2.
También puedes visualizar las etiquetas creadas en el Cuadro de Nombres:
Paso 3. Validación de datos y función INDIRECTO
A continuación en la hoja Consulta aplicaremos la propiedad de validación a las celdas donde se consultarán los países, los estados y las ciudades.
Nos posicionamos en la celda D3 que es donde colocaremos nuestra lista de países. Ahora, nos vamos al menú Datos y de la sección "Herramientas de Datos" le damos clic al ícono "Validación de Datos". Aparecerá la siguiente ventana emergente:
En dicha ventana seleccionaremos en "Permitir" la opción "Lista" y en vez de capturar los países en la parte de "Origen", utilizaremos la etiqueta "País" pero utilizando el signo igual para que haga referencia a la etiqueta y por ende a su lista de valores.
Al darle aceptar, aparecerá la lista correspondiente a la etiqueta "País". En este momento la celda ha quedado habilitada para recibir solamente los tres valores de la lista de países, ningún otro valor podrá ser introducido a menos que modifiquemos la propiedad de la celda mediante la herramienta "Validación de Datos".
Repetimos estos pasos para incorporar la lista de Estados pero ahora colocaremos la función INDIRECTO y como argumento pondremos la celda "D3" para referenciar el elemento que seleccionemos de la lista de países.
Nota: La función INDIRECTO devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Aquí la cadena de texto será la etiqueta y al evaluarse se obtendrá la lista de elementos de dicha etiqueta.
Utilizamos la función INDIRECTO para que la celda de Estados tenga valores dinámicos de acuerdo al país seleccionado. El argumento de esta función será cualesquiera de los elementos de la lista de países. Al evaluarse la función devolverá el conjunto de elementos de la etiqueta del país seleccionado y todos estos elementos serán los que llenen la celda “D5”. Por esta razón es importante ya tener declaradas todas las listas de países y estados para que la función devuelva las listas esperadas.
Para el caso de la lista enlazada de las ciudades hacemos el mismo paso anterior solo que ahora la función indirecto será:
=INDIRECTO(D5)
Finalmente formateamos y nuestra pantalla de consulta quedará así:
Paso 4. Conclusiones
Está es una forma fácil de crear listas enlazadas, se deben de tener algunos cuidados a la hora de crear las etiquetas y se recomienda usar esta técnica para listas sencillas como seleccionar una actividad, condición civil o sexo ya que cuando las listas son muy variadas y grandes se tiene la problemática de que hay que renombrar y redimensionar las etiquetas. Otra cuestión es que las listas enlazadas no se "refrescan" en cuanto al dato que contenían anteriormente. Esto es, que si observamos la última imagen, si cambiamos en este momento al país USA, seguirá permaneciendo a la vista, la provincia de "British_Columbia" hasta que no se despliegue la lista y se escoja un estado de la nueva lista generada.
La razón de separar los datos que se encuentran en la hoja Base de la pantalla de consulta (hoja Consulta) es por seguridad de que usuarios “primerizos” alteren o borren datos importantes. La hoja Base ahora se puede ocultar y proteger para impedir que alguien haga mal uso de la información ahí almacenada. Además, de la hoja Consulta se pueden proteger sus celdas para impedir la escritura en algunas de sus celdas y solo permitir consultar, con esto se hace una hoja de cálculo a prueba de todo tipo de usuarios. En otro blog efectuaremos el reto Seguridad y protección en hojas de cálculo.
SOLUCIÓN RETO 2 Lista Enlazada
Requisitos:
- Función INDIRECTO(ref, a[1])
- Función INDICE(matriz, núm_fila, [núm_columna])
- Función BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, [ordenado])
- Función COINCIDIR(valor_buscado, matriz_buscada, [tipo_de_coincidencia])
- Controles de Formulario Cuadro Combinado o de Lista (menú: Programador -> Controles -> Insertar)
- Concepto de etiquetas (menú: Fórmula -> Nombres definidos -> Administrador de nombres)
Paso 1. Preparación de los datos en la hoja de cálculo.
Dispondremos de un libro de Excel que contendrá nuevamente dos hojas de cálculo: Base y Consulta. En la hoja Base colocaremos la información de cada país, ahora estarán un poco más completos los datos con respecto al reto 1 y además utilizaremos elementos de ayuda tanto para las funciones como para los controles de formulario:
La columna A recibirá la información de los controles que coloquemos en la hoja Consulta. La información será numérica y cambiará en automático al seleccionar un elemento de cada control. La columna B utilizará funciones BUSCARV para localizar en nuestras listas el equivalente del número de la columna A. Por último, en las columnas D, G y L se tendrán los índices para identificar cada registro a localizar. Lo veremos a continuación.
Paso 2. Declaración de Etiquetas
Seleccionamos todos los rangos de nuestras listas cuidando de incluir los títulos de ellas (los que están en el renglón 1). Dar clic en el icono Crear desde la selección y darle aceptar a la ventana emergente.
Además crearemos dos etiquetas manualmente las cuales nos permitirán trabajar dinámicamente con el País y con el Estado (Provincia) que se haya seleccionado. La razón de esto la comprenderás cuando hayamos concluido el paso 3. Para crear estas etiquetas manualmente nos iremos al menú Fórmulas a la sección Nombres definidos y daremos clic en el icono Administrador de nombres:
Al dar clic en el botón Nuevo, aparecerá una ventana emergente donde en la casilla Nombre colocaremos el nombre de la etiqueta: “Países”. La casilla Ámbito la dejamos intacta y en la última sección que dice Hace referencia a: colocaremos la siguiente fórmula: “=INDIRECTO(INDICE(País, Base!$A$2))” que lo que hará en primera instancia será ejecutar la función INDICE la cual devolverá el nombre del país de la etiqueta “País” de acuerdo al número que contenga la celda “A2” de la hoja Base. Este dato lo tomará la función INDIRECTO para obtener la lista de elementos que conforman el nombre de la etiqueta en turno. De esta manera si en la celda “A2” de la hoja Base, se coloca el número 3, la función INDICE devolverá el tercer elemento de la lista que contiene la etiqueta “País” que será para este caso “México”, dicho dato lo tomará función INDIRECTO y lo que hará será obtener los elementos que conforman la etiqueta “México”.
También crearemos la etiqueta: “Estados”. Pero en la última sección que dice Hace referencia a: colocaremos ahora la siguiente fórmula: “=INDIRECTO(Base!$B$3)” que lo que hará será obtener el nombre que se encuentre en la celda “B3” de la hoja Base para de esta manera esta función devuelva la lista de elementos que conforman dicha etiqueta.
Nota 1. La creación de las dos etiquetas puede hacerse con cualesquiera de las dos fórmulas pero la intención de este reto es que si la función INDICE combinada con la función INDIRECTO resulta compleja de entender, entonces se tenga una opción alterna utilizando solamente la función INDIRECTO y posteriormente en la celda desde donde obtiene su dato esta función se le coloque una fórmula que se encargue de tener actualizado el País o Estado que se haya seleccionado en los Cuadros combinados.
Por esta razón en la hoja Base las columnas A y B se reservaron para estos fines:
Nota 2. Recordemos que la función INDIRECTO devolverá la referencia especificada por una cadena de texto que en este caso serán los nombres de los países o de los estados o provincias. Las referencias se evaluarán de inmediato para presentar su contenido, por lo que al ser la cadena de texto las etiquetas declaradas previamente, al evaluarse se obtendrá la lista de elementos de dicha etiqueta.
Paso 3. Colocación de los Controles de Formulario
En este punto lo primero que debemos realizar es habilitar la Ficha Programador la cual es el menú de acceso a las macros, Visual Basic, controles de formulario y otras herramientas. Para habilitar este menú es necesario ir al menú Archivo y dar clic en el icono de Opciones. Aparecerá la siguiente ventana y ahí seleccionar la casilla Programador y dar Aceptar.
Con esto aparecerá en el Cintillo el menú Programador, le damos clic y vamos a utilizar el icono en forma de maletín de herramientas para insertar tres cuadros combinados en la hoja Consulta.
Dentro del icono Insertar se encuentran dos grupos de controles: los de formulario y los Activex. Para este reto utilizaremos de la sección de Controles de Formulario, el segundo que es un Cuadro combinado o también llamado Combo.
También podemos utilizar el quinto elemento que es un Cuadro de lista. En siguientes blogs resolveremos retos que involucre a todos los controles de formulario y a todos los de Activex.
Colocaremos ahora tres controles tipo Cuadro combinado en la hoja Consulta:
Al darle clic derecho a cada control, se puede acceder a configurar sus propiedades:
Aparecerá la ventana emergente como se muestra a continuación:
Configuraremos primeramente el control de formulario para el País. En la ventana emergente Formato de Control (fig. de arriba) nos posicionamos en la última pestaña que se llama Control y el primer cuadro de texto nos pide el “Rango de entrada:” que se refiere a los elementos que desplegará la lista del control del formulario, si escribimos el nombre “País” entonces estaremos haciendo referencia a dicha etiqueta y cargará los países que le habíamos asignado previamente a esta etiqueta.
El siguiente cuadro de texto es “Vincular con la celda:” que se refiere a que dependiendo del elemento que se seleccione de la lista se devolverá un número de acuerdo a la posición que tenga ese elemento en la lista. Para nuestro reto Canadá es el elemento número 1, USA será el 2 y México el 3, de acuerdo a su posición en la lista. Esos serán los únicos valores que el control escribirá al seleccionar algún elemento de la lista. Ahí escribiremos entonces “Base!$A$2” para que en esa celda se refleje el número de elemento seleccionado de la lista. También si queremos evitar escribir dicha referencia basta con posicionarnos dentro del control y luego con el mouse pasarnos a la hoja “Base” y dar clic a la celda “A2”.
El tercer cuadro de texto es “Líneas de unión verticales:” que se refiere al número de elementos visibles cuando se despliegue la lista, aquí colocaremos el número “3” a fin de que aparezcan todos los países de la etiqueta “País”. Por último marcamos la casilla de selección para que el control de formulario adquiera una apariencia 3D.
Para los cuadros de formulario Estados y Ciudades a continuación se muestran sus datos:
|
Cuadro de Formulario Estado o Provincia |
|
Cuadro de Formulario Ciudad |
||
|
Rango de entrada: |
Países |
|
Rango de entrada: |
Estados |
|
Vincular con la celda: |
Base!$A$3 |
|
Vincular con la celda: |
Base!$A$4 |
|
Líneas de unión verticales: |
5 |
|
Líneas de unión verticales: |
7 |
Recordemos que las etiqueta “Países” la creamos manualmente con el fin de devolver solamente los estados o provincias del país seleccionado y que esto depende de lo que esté escrito en la celda “Base!$A$2” que a su vez estará alimentado por el Cuadro de formulario País.
Y que la etiqueta “Estados” dependerá de lo que haya escrito en la celda “Base!$B$3”, es ahí precisamente, en esa celda, donde colocaremos una función que obtenga el nombre del estado o provincia que se haya seleccionado en el Cuadro de formulario Estado.
Paso 4. Utilizando la función BUSCARV para localizar información
Para esto nos cambiaremos a la hoja Base y en el rango “B2:B4” colocaremos las siguientes fórmulas:
|
Celda (Hoja de cálculo Base) |
Función |
|
B2 |
=BUSCARV(A2,D:E,2,0) |
|
B3 |
=BUSCARV(A3,G:J,A2+1,0) |
|
B4 |
=BUSCARV(A4,L:U,COINCIDIR(B3,M1:U1,0)+1,0) |
La función BUSCARV (VLookUp en inglés) realiza búsquedas verticales en la primera columna de una tabla o matriz y es capaz de devolver cualquier elemento del registro encontrado (para este caso todo el renglón de la tabla).
Para el caso de la celda “B2” la función BUSCARV buscará el dato que se encuentre en la celda “A2” (que para este caso solo podrá ser 1, 2 o 3) y la región donde lo buscará será el rango “D:E” (no se pone “$D$1:$E$4” pensando que puede aumentar el número de países más adelante), el valor que devolverá será la segunda columna de dicha región, o sea, lo que se encuentre en la columna “E”, pero el renglón dependerá del número de registro que se esté buscando. El último argumento de esta función es “0” que significa que la coincidencia de lo que se está buscando debe ser exacta y no aproximada.
Para el caso de la celda “B3” se utiliza la misma función BUSCARV, el dato a buscar ahora es lo que contenga la celda “A3” (los números variaran porque cada país tendrá una cantidad distinta de estados o provincias) pero la región donde se buscará ahora será “G:J” donde en la segunda columna de ese rango se encuentra las provincias de Canadá, en la tercer columna los estados de USA y en la cuarta columna los estados de México es por esta razón que el tercer argumento de la función se obtiene con “A2+1”.
Por último, en la celda “B4” la función BUSCARV recibe ayuda de la función COINCIDIR cuyo objetivo es obtener la posición relativa del estado o provincia que aparece en la celda “B3” con respecto al rango “M1:U1”, esto servirá para saber el número de columna donde se encuentra el listado de ciudades del estado o provincia seleccionado.
Nota. Este paso 4 no será necesario en caso de optar por la opción que utiliza la formula “INDIRECTO(INDICE(matriz, fila))” que se menciona en la Nota 1 del paso 2.
Paso 5. Conclusiones
Utilizando controles de formularios se tiene más versatilidad en emplear las listas enlazadas, la necesidad de la función INDIRECTO sigue siendo indispensable. La función INDICE puede ser compleja de entender pero en su lugar se puede emplear la función BUSCARV que es más de uso común y para algunos muy cómoda y entendible de usar.
SOLUCIÓN RETO 3 Lista Enlazada
Requisitos:
- Uso de Macros
- Código Visual Basic for Applications
Paso 1. Preparación de los datos en la hoja de cálculo.
Dispondremos de un libro de Excel que contendrá las siguientes hojas de cálculo: Países, Estados, Ciudades y Consulta, será la información del reto anterior ahora un poco más amplia. Pero ahora no requeriremos de ayudas de índices o guías, solamente de datos puros.
Este libro hay que guardarlo como hoja de cálculo habilitada para macros, esto lo haremos desde el menú Archivo à Guardar Como y nos aparecerá la ventana clásica de guardado de documentos. Ahí en la lista Tipo escogeremos la opción “Libro de Excel habilitado para Macros”.
Con esto aseguraremos que el código Visual Basic que se escribirá más adelante, se guardará junto con este libro y podrá ser ejecutando cuantas veces volvamos a abrir este libro.
Paso 2. Creación de Etiquetas de Apoyo
No será necesario etiquetar cada país y estado, esto lo realizará la macro por nosotros y la razón de hacerlo así es que el trabajo de etiquetar se vuelve tedioso y errático mientras más países, estados y ciudades se involucren.
Solamente se crearán 4 etiquetas: las primeras dos controlarán las listas de estados o provincias y las listas de ciudades, estas listas cambiarán sus rangos dinámicamente dependiendo del país-estado seleccionado. La lista número tres será una lista estática vacía, esto será un rango de celdas sin valores, con el fin de controlar errores por listas aún no incorporadas, por ejemplo supongamos que se desea incorporar al país Argentina y se escribe su nombre en la hoja de países pero no se dan de alta ni sus provincias ni sus ciudades, para evitar errores las listas de estados y ciudades se presentarán vacías. Finalmente la última etiqueta, no será propiamente una etiqueta, ya que será creada en automático utilizando el concepto de tablas, tendrá características similares a las etiquetas hasta ahora creadas pero tendrá ciertas ventajas que se aprovecharan en este reto.
Esta última etiqueta en mención la crearemos de la siguiente manera: Seleccionamos la hoja “Países” y marcamos el rango “A1:A8”, luego desde el menú de Inicio localizamos la sección Estilos y damos clic en el icono Dar formato como tabla escogemos un formato de agrado a nuestro gusto.
Al darle clic al formato elegido observamos algunas cosas importantes: 1) El Cuadro de Nombres le ha llamado “Tabla1” al rango “A1:A8”, 2) aparece un nuevo menú llamado Herramientas de tablaàDiseño, 3) la tabla es redimensionable, etc. Ahí localizaremos el primer icono donde cambiaremos el nombre de ese rango (Tabla1) por el nombre “Países” a fin de tener mejor identificado este rango.
La ventaja de haber declarado esta etiqueta como Tabla es que si escribimos más países debajo del rango, estos serán incorporados automáticamente y cada vez que hagamos referencia a la etiqueta los incluirá en el control de formulario respectivo, esto lo comprobaremos más adelante.
Los controles para los estados y las ciudades los crearemos desde el menú Fórmulas:
Los llamaremos “States” y “Cities” y los crearemos de la siguiente manera: Nos vamos a FórmulasàNombres definidos y le damos clic al icono Asignar Nombre. Los datos para completar se muestran a continuación.
Aquí lo realmente importante es el nombre de la etiqueta, más no el rango (“Hace referencia a:”) ya que esta referencia cambiará mediante macros dependiendo del país o estado seleccionado.
También crearemos la etiqueta “EmptyRecord” haciendo referencia a un rango de celdas vacíos para este caso usaremos “=Países!$Z$1:$Z$3” como se ve a continuación:
Paso 3. Preparación de los controles de formularios
A continuación, desde la ficha o menú Programador incorporaremos tres cuadros combinados dando clic al maletín de herramientas Insertar y seleccionado el segundo control que es el de Cuadro combinado. Esta acción la realizaremos sobre la hoja de cálculo “Consulta”:
Los controles se colocarán por estética sobre las celdas “B2”, “B4” y “B6” tratando de que abarquen todo el espacio de la celda respectiva.
A cada control colocado, se le configurarán sus propiedades de la siguiente manera:
Paso 4. Macros
El paso más importante será la asignación de código Visual Basic a estos controles mediante el concepto de Macros.
Se podría usar la grabadora para obtener algo de código útil pero habría que modificar al menos la mitad de este para adaptarlo correctamente a nuestras necesidades. En lugar de eso lo que haremos será incorporar código directamente a los controles del país y del estado.
Nota. El control de las ciudades no llevará código, pero de tener información adicional de las ciudades como población, índices, geografía, etc. Se puede programar ese control para que dependiendo de la ciudad seleccionada nos muestre la información en algún cuadro de texto.
A continuación procederemos a grabar el control de los países. Para esto nos posicionamos en la ficha o menú Programador, seleccionamos con el botón derecho el control y escogemos del menú emergente la opción Asignar Macro:
La ventana emergente la configuraremos escribiendo en el cuadro Nombre de la Macro: “ObtieneEstados” y en la lista Macros en: “Este libro”. Damos clic en el botón Nuevo.
A continuación nos abrirá Microsoft Visual Basic para Aplicaciones (VBA) y nos presentará una pantalla similar a la siguiente:
Del lado derecho existe una gran zona en blanco donde se ha creado la subrutina ObtieneEstados y es ahí donde colocaremos código de lenguaje Visual Basic más adelante.
Del lado izquierdo aparece una estructura tipo árbol donde podemos apreciar un ramal con una carpeta llamada Microsoft Excel Objetos que contiene nuestras hojas de cálculo con su nombre original y el nombre que le dimos. Y más abajo encontraremos otro ramal con una carpeta llamada Módulos que ahí contendrá los módulos con código VBA según vayamos escribiendo o grabando macros. Si no te aparece este cuadro de Proyecto lo puedes activar en el menú VeràExplorador de Proyectos u oprimiendo CTRL+R.
También más abajo del lado izquierdo aparece la ventana de Propiedades. En la figura anterior está mostrando las propiedades del Módulo1 que es el que esta seleccionado en la ventana de proyectos. Esta ventana es muy importante cuando se crean formularios y no es importante que te aparezca, pero la puedes activar en VeràVentana de Propiedades u oprimiendo la tecla F4. En un reto posterior aprenderemos a realizar formularios desde cuatro técnicas distintas y una de ellas usará esta ventana.
En la parte superior están los menús aún en formato clásico tipo XP o versiones anteriores. No están organizados en un cintillo como en 2007 o superiores bajo el concepto “Office Fluent”. No es necesario aún el conocimiento de este menú, con el trabajaremos más a fondo en retos posteriores.
Esta ventana de Microsoft Visual Basic para Aplicaciones la podemos cerrar sin que afecte nuestro proyecto ya que está ligada a nuestro libro de Excel y si guardamos el libro en este momento con lo que se ha avanzado, también se estará guardando lo hecho en VBA. Así que podemos cerrarlo o minimizarlo para regresar a Excel.
Repetimos la operación anterior para el control de los estados. Entonces seleccionamos el cuadro combinado de los estados, le damos clic con botón derecho y seleccionamos la penúltima opción que es Asignar Macro:
Los datos son similares excepto que el nombre de la macro ahora será “ObtieneCiudades” y damos clic al botón Nuevo:
Ya se han creados los cascarones de las dos macros que responderán cuando seleccionemos algún elemento de los cuadros combinados Países/Estados.
Ahora deberemos de escribir el código y nos quedará algo así:
De la figura anterior observemos que existe colores distintos en el código introducido: Toda palabra en color azul es palabra reservada de código VBA o sea nombres de subrutinas, ciclos, condicionales, declaraciones de variables, llamadas a procedimientos, etc., si conoces algo de programación esto no tendrá problema, si no, no te preocupes en cada reto resuelto iremos conociendo más.
Toda palabra en color verde son solamente comentarios y esto se obtiene iniciando un comentario con una apóstrofe (no confundir con el acento).
Toda palabra en color negro puede ser un nombre de variable, un objeto, método o procedimiento o propiedad o una simple constante. En otro reto conoceremos todos estos conceptos.
El código completo de las dos subrutinas se muestra a continuación y además se agrega el código de una subrutina adicional que se utiliza para llenar cualquiera de los dos cuadros combinados (Países/Estados). Lo puedes copiar pero si es tu primera experiencia con macros y código te recomiendo lo vayas escribiendo.
Sub ObtieneEstados()
'Macro para obtener el listado de Estados o Provincias del País seleccionado
Dim Num_Pais As Integer 'Posición del País en la Lista (Hoja País)
Dim Col_Pais As Integer 'Número de columna del País (Hoja Estados)
Dim Pais As String 'Nombre del país
Dim Country As String 'Nombre temporal del país (Renglón 1 Hoja Estados)
Dim Num_Estados As Integer 'Cantidad de Estados del País seleccionado
Dim Estados As String 'cadena con el Rango de los Estados (Formato RC)
Call Llena_Drop(3, "EmptyRecord", "B6") 'Limpia combo estados
Call Llena_Drop(4, "EmptyRecord", "B6") 'Limpia combo ciudades
' Obtención del índice del país seleccionado a partir del combo de países
Num_Pais = Sheets("Consulta").Shapes("Drop Down 2").ControlFormat.Value
' Obtención del nombre del país a partir de su posición en la columna A
Pais = Sheets("Países").Range("A" & Num_Pais).Value
If Pais = "Países" Then Exit Sub 'El rótulo de la tabla países no cuenta
Col_Pais = 1
Country = ""
Do Until Pais = Country
'Ciclo para recorrer el Renglón 1 de la hoja Estados en busca del País
Country = Sheets("Estados").Cells(1, Col_Pais).Value
If Country = "" Then Exit Sub 'No se han dado de la alta los estados del país seleccionado
Col_Pais = Col_Pais + 1
Loop
'Comprueba que el país encontrado cuente con una lista de estados
If Sheets("Estados").Cells(2, Col_Pais - 1).Value = "" Then Exit Sub
'Obtención del número de estados o provincias del país seleccionado
Num_Estados = Sheets("Estados").Cells(1, Col_Pais - 1).End(xlDown).Row
'Actualización de la etiqueta "States" con el rango correcto
Estados = "=Estados!R2C" & Col_Pais - 1 & ":R" & Num_Estados & "C" & Col_Pais - 1
ActiveWorkbook.Names("States").RefersToR1C1 = Estados
'Llenado del combo Estados
Call Llena_Drop(3, "States", "B4")
ObtieneCiudades
End Sub
Sub ObtieneCiudades()
Dim Num_Pais As Integer
Dim Num_Estado As Integer
Dim Col_Estado As Integer
Dim Estado As String
Dim Edo As String
Dim Num_Ciudades As Integer
Dim Ciudades As String
Call Llena_Drop(4, "EmptyRecord", "B6") 'Limpia combo ciudades
Num_Pais = Sheets("Consulta").Shapes("Drop Down 2").ControlFormat.Value
Num_Estado = Sheets("Consulta").Shapes("Drop Down 3").ControlFormat.Value
Estado = Sheets("Estados").Cells(Num_Estado + 1, Num_Pais - 1).Value
Col_Estado = 0
Edo = ""
Do Until Estado = Edo
Edo = Sheets("Ciudades").Cells(1, Col_Estado + 1).Value
If Edo = "" Then Exit Sub
Col_Estado = Col_Estado + 1
Loop
If Sheets("Ciudades").Cells(2, Col_Estado).Value = "" Then Exit Sub
Num_Ciudades = Sheets("Ciudades").Cells(1, Col_Estado).End(xlDown).Row
Ciudades = "=Ciudades!R2C" & Col_Estado & ":R" & Num_Ciudades & "C" & Col_Estado
ActiveWorkbook.Names("Cities").RefersToR1C1 = Ciudades
'Llenado del combo Ciudades
Call Llena_Drop(4, "Cities", "B6")
End Sub
Sub Llena_Drop(num_dd As Integer, listFR As String, linkedC As String)
'Rutina para llenar los combos de países o estados indistintamente
'num_dd recibe el dato del número de combo (2:Países, 3:Estados)
'listFR recibe el nombre de la etiqueta que llenará el combo (States/Cities)
'linkedC recibe la celda donde el combo refleja el índice de lo seleccionado
Sheets("Consulta").Shapes.Range(Array("Drop Down " & num_dd)).Select
With Selection
.ListFillRange = listFR
.LinkedCell = linkedC
.DropDownLines = 4
.Display3DShading = True
End With
'Posicionamiento del combo en el primer elemento de la lista
Sheets("Consulta").Shapes("Drop Down " & num_dd).ControlFormat.Value = 1
Range(linkedC).Select
End Sub
Ahora solo basta cerrar Microsoft Visual Basic para Aplicaciones (VBA), guardar lo que llevamos hecho y probar los controles.
Paso 5. Conclusiones
Esta es la opción más efectiva para usar listas enlazadas ya que dinámicamente se actualizan nuevos datos insertados y el mantenimiento es casi nulo. El problema radica en que hay que tener conocimientos de programación para realmente hacer macros de impacto en los trabajos estratégicos de las organizaciones.
Si ya tienes algo de experiencia con macros o programación no deberás de tener problema para entender el código arriba escrito, pero si este no es tu caso, entonces te recomiendo ir realizando paso a paso los retos que aquí se abordaron.
Listas Enlazadas con controles sin macros.xlsx (19,1 kB)
Listas Enlazadas sin macros.xlsx (13 kB)