EJEMPLOS PRACTICOS DE MACROS Y CODIGO VBA DIRECTORIO TELEFONICO

Se puede encarar el problema anterior usando formularios, este método tiene la desventaja de que el código se complica un poco, lo que no debería sorprendernos ya que una celda es un objeto mas simple, pero como veremos, las ventajas superan a las desventajas

DIRECTORIO TELEFONICO ( USANDO FORMULARIOS)

CONSULTA EN UNA LIBRERIA

CONSULTA EN UNA LIBRERIA (USANDO FORMULARIOS)

TICKET DE COMPRA

COMBOBOX (CUADRO COMBINADO) ORDENADO ALFABRTICAMENTE

ALTAS, BAJAS Y MODIFICACIONES

INFORMES

DIRECTORIO TELEFONICO

Este directorio telefónico se hará sin recurrir a formularios, simplemente pasaremos los datos que introducimos en tres celdas en la Hoja1 y los pasaremos a la Hoja2, que llamaremos DIRECTORIO

Colocamos los rótulos verticalmente en las celdas coloreadas en azul y en las celdas F6,F7,F8 los datos que serán pasados a la Hoja DIRECTIRIO pero colocados horizontalmente, luego de aplicar la macro, disparada con el botón INTRODUCIR DATOS, todo debe quedar como se ve en la figura de abajo

A medida que se van agregando datos, se va formando el directorio

para hacer esto utilizaremos la siguiente macro en el módulo 1

Para consultar el teléfono y la dirección de alguna persona, en esta ocasión lo haremos utilizando fórmulas

colocamos en la celda B6, el apellido y nombre de la persona cuyos datos queremos obtener y en la celda B7 la fórmula

para obtener el teléfono y para la dirección la fórmula

si la persona no está en el directorio aparece la leyenda "NO ESTA EN EL DIRECTORIO" como muestra la figura

DIRECTORIO TELEFONICO (usando formularios)

Este directorio telefónico se hará usando FORMULARIOS

Esta manera de encarar el problema tiene más ventajas ya que con un solo formulario podremos introducir los datos de nuestros contactos, consultarlos y darlos de baja. Por otro lado se puede

dar al formulario un aspecto más profesional, lo que será de mucha utilidad cuando se encaren problemas más complejos

Accedemos al formulario entrando al editor VBA (Alt+F11) luego vamos a Insertar y en el menú desplegable que se abre pulsamos en UserForm

y aparece el formulario y los controles con los que podemos configurarlo

Para nuestro formulario vamos a utilizar los comandos: Etiqueta ( Label) Cuadro de texto (TextBox) y botón de comando ( CommandButton) marcados en rojo en la imagen, estos comandos están numerados segun el orden en que los `pnemos

con cada uno de ellos se insertan objetos cuyas propiedades se pueden cambiar en el explorador de propiedades ( también puede hacerse con código), Para que aparezca las propiedades de cada objeto solo debemos pulsar en ellos, por ejemplo en la figura pueden verse las propiedades de un botón al que se le han cambiado las propiedades Name y Caption, cada una de ellas tiene un nombres por defecto; por ejemplo en la propiedad Caption pusimos" INSERTAR" (pues con este botón insertaremos los datos) y en nombre "cmdInsertar" . Esto es muy importante a la hora de programar porque ayuda la claridad del código (aunque ahora que lo pienso, hubiera sido más claro poner "btnInsertar", esto es a nuestro criterio), hemos hecho lo mismo son los otros controles por ejemplo al “TextBox1” lo cambiamos por “txtApellidoNombre”.

Como el formulario en si mismo también es un objeto le cambiamos el nombre por "usfDirectorio" al que le cambiamos la propiedad Caption por "DIRECTORIO TELEFONICO" .

También nos hará falta insertar un módulo al que llamaremos " MostrarFormulario" en el que pondremos este código

se asignará a un botón en la Hoja1

que disparará la macro “ Iniciar_Captura” y como consecuencia se mostrara el formulario de la figura

Ahora lo que nos resta hacer es programar todos los botones, que como puede verse no nos hemos privado a la hora de ponerlos, la razón es que este directorio se puede adaptar para llenar y mantener una base de datos. Debemos notar que solo hemos usado tres controles.

Para que los botones funcionen hay que hacer Click en ellos, entonces debeos programar el evento Click y para hacerlo hay dos maneras: hacer doble click en el objeto "cmdInsertar" o pulsar en este mismo objeto, con el botón derecho del mouse para que aparezca menú en el que apretamos la tecla ver código

en ambos casos vamos a la siguiente pantalla

donde podemos ver en la parte superior, el nombre del botón y el evento Click y el nombre de la macro "cmdInsertar_Click" lista para que la programemos. Hay otros eventos a los que podríamos acceder

pero en este caso nos interesa el evento Cick. También podemos ver haciendo doble click en el formulario( o con el botón derecho del mouse) todos los objetos que tenemos dentro del mismo

de esta manera haciendo doble Click en cada uno de los 6 botones podemos programarlos como puede verse abajo

En programación no hay una sola forma de hacer las cosas, y es probable que haya otro algoritmo más eficiente, el que está arriba me pareció fácil de entender, pero más adelante haremos otra versión

ONSULTA EN UNA LIBRERIA

Una de las tantas veces que fui a una librería, me encontré con una computadora en la que se podían consultar, entre otras cosas, los libros que había en existencia, de un determinado autor. No sé cómo lo habrán hecho pero ahora lo vamos a hacer con las macros de Excel en una suerte de interacción usuario computadora.

Lo primero que hacemos es renombrar tres hojas como se ve en la figura

"PRINCIPAL" es la pantalla con la que se encuentra el eventual comprador al iniciar su consulta. Para que tenga un aspecto más profesional hemos diseñado la página quitado los encabezados de fila y columna y puesto la pantalla completa, también hemos colocado un cartel que oriente al cliente y un botón. Luego de poner un color que me pareció adecuado, finalmente la página quedo como se muestra

El “BOTON DE CONSLTA” tiene adjuntada la macro principal en el módulo1 y hay otras dos macros en el módulo 2 y 3 como se puede ver el inspector de proyectos

"AUTORES" es la hoja donde se debería poner una base de datos con todos los autores sus respectivos libros y el precio de cada uno. Dije se debería colocar una base de datos yo me voy a conformar colocando una tabla con algunos autores de ciencia ficción, genero que, por cierto, me gusta mucho

TABLA 1

no son muchos datos pero servirán

AUTOR es la página donde va a ir una tabla solo con los datos del autor consultado. Por ejemplo si se pregunta por Ray Bradbury la tabla debería ser

TABLA 2

que es el resultado de la consulta. Si se quiere hacer otra consulta se debe vaciar la tabla anterior y volver a la página principal, esto se hace con un botón que llamaremos “NUEVA CONSULTA” que está asociado a una macro ubicada en el módulo3

TABLA 3

Lo que ve al usuario después de apretar el “BOTON DE CONSULTA” es una pantalla que le pide que introduzca un autor

al aceptar vamos a la página "AUTOR", con el resultado de la consulta como se ve en la TASLA 2

Lo que nos resta es decir cuál es el código de las macros que van en los módulos

módulo1

módulo2

módulo3

Como se puede ver, esta no es una macro aislada sino una aplicación completa con cierto aspecto profesional, que por cierto, puede mejorarse

CONSULTA EN LIBRERIA (usando formularios)

La consulta de un autor en una librería usando formularios, es una aplicación completa muy similar a la que no los usa, podríamos decir que es solo la manera en que se muestran los informes, aun así podremos aprender muchas cosas útiles que nos podrán servir en el futuro

Empezamos cambiando el nombre de tres Hojas como se ve en la figura

El consultante solo va a ver la Hoja "INFORME" que tiene el siguiente aspecto

en esta agregamos un calendario y un reloj en la parte superior, aparte de dos imágenes alusivas a los libros, por otro lado la página se abre en pantalla completa y como se puede ver está el "BOTON DE CONSULTA" donde al apretarlo aparece la pantalla para ingresar el autor a consultar.

Y aquí viene la diferencia, luego de aceptar aparece otra pantalla con el informe

Esta pantalla no es otra cosa que un formulario que contiene un “cuadro de lista” y el botón "VUEVO INFORME" para hacer otra consulta,

Vamos a las macro: en la imagen podemos ver el inspector de proyectos

Como se ve tenemos muchos lugares donde colocar las macros como sin “ThisWorkbook”, “UserForm1” y los módulos “Copia Rotulo”, “LimpiaInforme”, "MuestraFormulario" y "Principal"

En “ThisWorkbook” ponemos la macro para la fecha, reloj y pantalla completa seleccionando el evento open del objeto Workbook (se bebe hacer clik en la pantalla)

En el “UserForm1” hay dos objetos:

1- ComandButton1: este objeto es el botón cuya etiqueta es "NUEVO INFORME". En el Inspector de "Propiedades" se pueden cambiar estos nombres a otros, que por alguna razón, consideremos convenientes. En este caso dejamos la propiedad (Name) en su valor por defecto y a la propiedad "caption" le dimos el valor "NUEVO INFORME" , como se puede ver en la figura.

Este objeto pertenece a la clase "CommandButton" como cualquier botón que está en un formulario

2-ListBox1: pertenece a la clase "ListBox" y no hemos cambiado su nombre por defecto como se muestra en la figura

Como vemos en la imagen estos dos objetos pertenecen a una clase mas general llamada “UserForm”

Lo expuesto es fundamental para programar los objetos y sus eventos

Para programar “ComandButton1” lo seleccionamos y elegimos el evento Click

Captamos los datos de la consulta inicializando el "UserForm"

Finalmente las macros quedan así

MODULOS

Se insertaron cuatro módulos con distintos nombres para mayor claridad

1-“MuestraFormulario”: esta simple macro será llamada por Principal, para que mustre la pantalla con el informe

2- “LimpiaInforme” : esta macro (Macro1) limoia los datos del rango "LIBROS" que está en la Hoja "AUTOR"

3 “Copia Rotulo”: en este nódulo está la macro "CopiaRotulo" cuya función es copiar el rótulo de la base de datos de la Hoja"autores" (en nuestro caso es una tabla) en la Hoja "AUTOR"

4-"Principal": este módulo tiene la macro "LibrosAutor" que es casi idéntica a la macro principal

de CONSULTA EN UNA LIBRERIA , la diferencia es que al final, cuando se sale del while, se

llama a la macro "MostrarFormulario" como se muestra con la flecha

Y así terminamos la explicación de CONSULTA EN LIBRERIA (usando formularios) que espero haya sido clara.

Hasta la próxima.

TICKET DE COMPRA

Este es un tutorial que programa los tickets de compra de un supuesto delivey que vende plantas y

flores, llamadoBestFlowers, este negocio pega estos Tickets en las cajas donde pone la

mercadería con el detalle de los productos y su importe antes de enviarlos a destino.

Una característica, general de los tickets es su baja calidad estética y de impresión, con Excel esto se mejora ya que se pueden insertar imágenes de logotipos o hacerlos con WordArt y tiene a su disposición una amplia variedad de fuentes, por otro lado la impresión se hará con una impresora común, papel de alta densidad y no con una ticketeadora (que por cierto tiene otras ventajas). Otra característica de los tickets es que no se dejan filas vacías, como pasa con las facturas, nuestro ticket cumplirá con esto también. Veamos dos ejemplos de lo que tenemos que hacer

;

.

como se ve los números de ticket son consecutivos y no hay filas vacías ya que estas se agregan a medida que se integran nuevos productos. Obviamente para imprimir el segundo ticket hay que vaciar los contenidos del primero.

Para agregar filas se parte poniendo en la fila 12 las siguientes fórmulas:

Celda B12:

Celda D12:

estas fórmulas deben ser copiadas, automáticamente, a las celdas B13 y D13 al colocar el código de un producto en la celda A13, completándose la fila al colocar la cantidad de unidades. El proceso continua a medida que se agregan nuevos códigos en las filas subsiguientes.

Estas acciones se hacen con la siguientes macros "Copiaformula" (módulo 1) y "AgregaFila" (módulo 2).las cuales deben entrar en acción sin nuestra intervención, puesto que de lo contrario se perdería el carácter automático.

Para hacer esto, "Copiaformula" y "AgregaFila" deben ser llamadas dentro de otra macro que se activa cuando en un objeto cambia alguna propiedad, En nuestro caso puede ser el cambio de las celdas D13 a D33 de la Hoja1, que podrían cambian de estar vacías a tener el código de un producto en su interior. A este cambio se lo denomina EVENTO, en este caso nos interesa los eventos de las Hojas o "Worksheet events" que responden a cambios en una Hoja, como el cambio en una celda o rango de celdas (en nuestro caso sería el rango D13 : D33), pero este no es el único evento. Podemos unvestigar todos los eventos disponibles para Worksheet, para esto hacemos doble click en la Hoja1 del esplorador de proyectos veremos que si desplegamos la ventana "General" aparecerá el objeto Worksheet asociado solo a esta Hoja, como se ve en la imagen

y haciendo click en el botón desplegable de la ventana que está al lado podemos elegir entre todos los eventos del objeto Worksheet

por defecto está "SelectionChange", que es el evento que se produce cuando se selecciona una celda o un rango de celdas. Pero podríamos haber usado cualquier otro de la lista que considerasemos conveniente, en nuestro caso elegimos Change y automáticamente se genera la macro "Workseet_Change"

que es la que tiene que llamar a "Copiaformula" y "AgregaFila". Finalmente la macro Worcksheet_Change es la siguiente, una vez que se le inserta el código

Nos falta hacer dos cosas, indispensables para que nuestro Ticket este preparado para un próximo cliente, vaciar su contenido e incrementar su numeración en una unidad.

Vaciar el contenido del Ticket

Para vaciar el contenido, vamos a la pestaña "Programador" y en la sección "Controles" pulsamos "ModoDiseño y luego "Insertar" para insertar un botón Active x.

que colocamos en el lugar que nos parezca conveniente

Pulsando en "propiedades" podemos cambiar la propiedad Caption por Borrar y también cambiar el nombre con que se lo identifica por "cmdBorrar" en lugar de "CommandButton1", esto último es una buena costumbre ya que si hubieran muchos botones(cosa que ocurre con mucha frecuencia) podríamos identificar rápidamente su función

El "cmdBorrar" es un objeto y está disponible en la ventana de objetos, luego haciendo click en el botón desplegable lo seleccionamos y elegimos el evento Click.

Como antes aparece una macro "cmdBorrar_Click" automáticamente

en la que se llama a la macro "Borrar", cuyo código es

Incrementar automaticamente el Nº del Ticket

Aquí también recurrimos a objetos y eventos. El objeto es el cuaderno que contiene la "Hoja1" donde está el ticket, por lo tanto hacemos doble click en "ThisWorkBook" seleccionamos el objeto" WorkBook" y como evento "befoPrint", como se puede ver en el editor VBA, o sea que después de imprimir el Ticket, este cambia la numeración

Finalmente el código que ponemos es

Una cosa importante es no olvidarse, después de imprimir, de vaciar los contenidos con el botón Borrar.

Nota: lo mas pronto que pueda, explicaré detalladamente los códigos.

COMBOBOX(CUADRO COMBINADO)ORDENADO ALFABETICAMENTE

Es muy útil ordenar un ComboBox alfabéticamente, porque nos facilita enormemente encontrar los datos, espacialmente cuando estos son muchos.

Supongamos que una agencia de alquiler de autos tiene todo su stock cargado en un ComboBox inserto en un FORMULARIO, de manera tal que cuando se elige un marca aparezcan todas sus características, tales como: estado, año, kilometraje, color, si esta en servicio o en el taller, si tiene aire acondicionado, etc. Las marcas y modelos se buscaran por orden alfabético en un cuadro combinado, apareciendo las características en los respectivos cuadros de texto (textBox). Para esto la agencia disponen de una planilla con los datos antes mencionados que serán la fuente de donde se cargará el ComboBox, como se muestra en la figura

el listado podría ser mucho más extenso ampliándolo mediante otro formulario.

Como se puede ver la planilla tiene 7 columnas, por lo tanto debemos elegir por cuál de ellas se hará el orden. En este caso nos decidimos por la columna "B" en orden ascendente.

Empezamos abriendo el editor de VBA (Alt+F11) y diseñamos un Formulario al que renombramos "usfMarcasyModelos", asimismo insertamos el modulo1 y el modulo2. El Formulario deberá ser semejante al de la figura.

Luego cambiamos los nombres de los objetosTextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6 por txtCodigo, txtAño, txtColor, txtAire, txtDisponibilidad, txtUbicacion respectivamente y al ComboBox1 por cmbMarcaModelo. Estos cambios de nombres son muy útiles para saber qué es lo que contiene cada TextBox, en especial cuando hay muchos. Lo siguiente es cargar el ComboBox con la columna según la cual queremos hacer el orden, para esto hacemos doble cick en el formulario(que es un objeto) y nos aparece por defecto lo siguiente

que nos invita a programar el UserForm con el evento click, pero nosotros queremos el evento initialize, por lo tanto lo buscamos en la parte superior derecha (donde dice click)

apareciendo, una vez borrada la macro "Userform_Click, la macro que tenemos que programar

seguidamente insertamos el siguiente código

a estas alturas ya tenemos cargado nuestro Cuadro Combinado pero sin estar ordenado alfabeticamente

Para que esto ocurra debemos llamar a la macro ordenar_arrastre, señalada por la flecha(ubicada en el módulo2)

descomentando la línea de la flecha roja. Ahora sí aparece ordenado

Casi lo único que nos falta es programar una macro para que aparezca el estado del vehículo con sus características, esta macro involucra al objeto "cmbMarcaModelo" y al evento "change"

Lo que viene es asignar una macro a un boton ( en la Hoja1) para que aparezca el Formulario

y para terminar progra mar el botón "SALIR"

Esto es todo, en el próximo TUTORIAL veremos cómo agregar datos a la planilla y que estos sigan ordenados.

ALTAS, BAJAS Y MODIFICACIONES

No se me ocurre que una empresa puede no necesitar una aplicación de ALTAS, BAJAS Y MODIFICACIONES, en nuestro caso continuaremos con la empresa de renta de autos, que apropósito se llama RODANDO RENTA CAR.

La descripción de la aplicación consiste en cuatro botones que al oprimirlos hacen surgir cuatro pantallas (formularios), el primero muestra en que situación se encuentra cada vehículo en el momento presente. El segundo es el formulario de ALTAS, el tercero el de MODIFICACIONES y por último el de BAJAS, como se muestra

FLOTA DE VEHICULOS (consulta)

En esta pantalla se busca la marca y modelo del vehículo que aparece en una lista ordenada alfabéticamente en forma descendente en un comboBox, luego de seleccionar un vehículo, aparecen algunas características del mismo que le pueden interesar al cliente; entre otras, una foto y si esta disponible para ser alquilado( textBox DISPONIBLE) etc.

AGREGAR VEHICULOS

En esta pantalla se agregan nuevas unidades, en este punto se existe la posibilidad de agregar una misma marca y modelo, pudiendo surgir un problema si no se los diferencia de alguna manera ya que al buscarlos y querer hacer una consulta sobre ellos, solo aparecerá el primero que este en la lista. La solución es muy simple, tomemos como ejemplo el SALEEN S331, si quisiéramos agrega el mismo modelo podríamos diferenciarlo poniéndole el nombre SALEEN S331-2, como se ve en la imagen

.de esta manera aparecen las dos unidades.

CAMBIAR ESTADO DE UN VEHICULO

Aquí el vehículo ha cambiado el estado de estar en disposición de ser alquilado a lo contrario.

SACAR VEHICULO DE LA FLOTA

Esta es la pantalla en la que por múltiples motivos se considera que el vehículo ya no puede pertenecer a la flota (destrucción total, mucho kilometraje, modelo antiguo, etc.)

Es de fundamental importancia que cuando se haga algún cambio en las pantallas de ALTAS, BAJAS o MODIFICACIONES, este se actualice inmediatamente en cualquiera de los formularios. También hay una advertencia en la pantalla de altas que nos alerta si algún campo quedo sin llenar, otra advertencia en la pantalla de modificaciones que también nos advierte si estamos seguros de las modificaciones que hicimos y por último una advertencia en la pantalla de bajas que nos dice si estamos seguros de la eliminación.

Debo aclarar que las fotos de los autos se deben colocar en este caso en una carpeta junto con el archivo de la aplicación, esto no sería necesario si ponemos la ruta completa donde se encuentra la carpeta con los vehículos siendo muy sencilla la modificación del código.

Por último podríamos insertar la totalidad de la flota desde el formulario de ALTAS.

Ahora vamos a mostrar los códigos correspondientes a cada uno de los FORMULARIOS, con algunos comentarios

CODIGO DE FLOTA DE VEHICULOS (INFORME)

CODIGO DE ALTAS

CODIGO DE BAJAS

CODIGO DE MODIFICACIONES

Como ya se dijo, al descargar el archivo, poner las imágenes en la misma carpeta que la aplicación. He puesto muchas imágines para que se puedan agregar y eliminar.

Hasta el próximo tutorial.