Curso Macros Excel Programacion VBA _ Daniel Zegarra Zavaleta.pdf

2016

Curso: Macros Excel Programación VBA

Daniel Zegarra Zavaleta

Manual de Microsoft Excel Programación VBA

Microsoft Excel Programación VBA 1. ¿Cómo se crea un programa en Excel? a. La grabadora de macros b. El Editor de Visual Basic para Excel 2. Uso de la Grabadora de Macros a. Macros con referencias Absolutas b. Macros con referencias Relativas c. Asignar una macro a un objeto d. Como eliminar una macro e. Diferencia de Macros en Excel 2003 y en Excel 2007/2010 3. Escritura de Macros en el Editor de Visual Basic para Excel a. Ingreso al Editor de Visual Basic de Excel b. Qué es un programa en Visual Basic c. Estructura de un programa para Excel d. El Código en Visual Basic i. Sentencias de Declaración ii. Sentencias de Asignación iii. Sentencias Ejecutables e. La ventana Explorador de Proyectos f. Ejecución de macros paso a paso 4. Manejo de Rangos, Hojas y Archivos a. Propiedades y Métodos b. Selección de rangos: Directa y relativa c. Desplazamiento y selección: Propiedad Rango y Offset d. Nombres de rangos y nombres de hojas: Directa y relativa e. Abrir, cerrar, guardar y eliminar archivos f. Uso de funciones de Visual Basic g. Uso de funciones de hoja de cálculo en Visual Basic 5. Sentencias de Control a. If...then…else b. Select Case c. Do While/Until d. For Next e. For Each Next f. With End With

Elaborado por: Daniel Zegarra Zavaleta

Pag. 1

Manual de Microsoft Excel Programación VBA 6. Uso de botones de formulario a. Ejecución de macros con botones de formulario 7. Las funciones MsgBox e InputBox a. Cajas de salida de información MsgBox b. Cajas de ingreso de información InputBox 8. Creación de formularios UserForm a. Herramientas de formularios b. La ventana Propiedades c. Diseño de formularios interactivos 9. El Código en Visual Basic para Excel a. Sentencias de declaración i. Constantes ii. Variables 1. Publicas 2. Privadas 3. Estáticas iii. Arreglos b. Sentencias de asignación i. De datos ii. De objetos (Set) c. Tipos de objetos i. Aplicaciones, libros, hojas, rangos, celdas, gráficos, formas, etc. ii. Colecciones de objetos 10. Control del flujo de un programa a. Sentencias de bifurcación b. Sentencias de repetición o bucle c. Sentencia de ejecución múltiple con un objeto d. Las Sentencias: Goto, Call, Exit 11. Manejo de Rangos a. Mover y copiar rangos b. Nombrar rangos c. Obteniendo la intersección de rangos d. Proteger y desproteger rangos 12. Manejo de hojas a. Insertar hojas b. Nombrar hojas c. Eliminar hojas d. Ocultar y mostrar hojas e. Proteger y desproteger hojas 13. Manejo de archivos a. Abrir un archivo b. Abrir un archivo de texto como hoja de calculo c. Abrir un archivo elegido por el usuario d. Cerrar archivos e. Eliminar archivos f. Verificar si un archivo existe Elaborado por: Daniel Zegarra Zavaleta

Pag. 2

Manual de Microsoft Excel Programación VBA g. Verificar si un libro está abierto 14. Uso de arreglos a. Declarar un arreglo b. Arreglos multidimensionales c. Convertir una lista en un arreglo d. Redimensionar un arreglo 15. Formularios avanzados a. Inicializar formularios b. Formularios de fichas múltiples c. Formulario para ingreso de fechas con calendario d. Formularios para ingreso de usuarios con contraseña 16. Crear Funciones personalizadas a. Crear funciones de usuario 17. Uso de botones de control ActiveX a. Propiedades de los controles b. Código VBA de los controles 18. Manejo de gráficos a. Redefinir los rangos a graficar b. Mostrar gráficos en una ventana de formulario 19. Manejo de formas e imágenes a. Mover, copiar, cambiar tamaño b. Escribir formulas en las formas 20. Manejo de eventos de Excel a. Macros autoejecutables i. Para libros ii. Para hojas iii. Para rangos de celdas iv. Para acciones del mouse v. Para acciones del teclado 21. Manejo de módulos a. Insertar un modulo b. Eliminar un modulo c. Ocultar los módulos (contraseña) 22. Control de pausas al ejecutar sentencias a. Application.Wait b. Application.OnTime 23. Control de errores a. On Error Resume Next b. On Error GoTo c. Err.Number d. Err.Description e. Control de mensajes de alerta (DisplayAlert) f. Control de actualización de pantalla (Screen.Updating) 24. 1. El Código en Visual Basic para Excel a. Sentencias de declaración i. Constantes ii. Variables 1. Publicas 2. Privadas 3. Estáticas


Pag. 3

Manual de Microsoft Excel Programación VBA iii. Arreglos b. Sentencias de asignación i. De datos ii. De objetos (Set) c. Tipos de objetos i. Aplicaciones, libros, hojas, rangos, celdas, gráficos, formas, etc. ii. Colecciones de objetos 2. Control del flujo de un programa a. Sentencias de bifurcación b. Sentencias de repetición o bucle c. Sentencia de ejecución múltiple con un objeto d. Las Sentencias: Goto, Call, Exit 3. Manejo de Rangos a. Mover y copiar rangos b. Nombrar rangos c. Obteniendo la intersección de rangos d. Proteger y desproteger rangos 4. Manejo de hojas a. Insertar hojas b. Nombrar hojas c. Eliminar hojas d. Ocultar y mostrar hojas e. Proteger y desproteger hojas 5. Manejo de archivos a. Abrir un archivo b. Abrir un archivo de texto como hoja de calculo c. Abrir un archivo elegido por el usuario d. Cerrar archivos e. Eliminar archivos f. Verificar si un archivo existe g. Verificar si un libro está abierto 6. Uso de arreglos a. Declarar un arreglo b. Arreglos multidimensionales c. Convertir una lista en un arreglo d. Redimensionar un arreglo 7. Formularios avanzados a. Inicializar formularios b. Formularios de fichas múltiples c. Formulario para ingreso de fechas con calendario d. Formularios para ingreso de usuarios con contraseña 8. Crear Funciones personalizadas 9. Uso de botones de control ActiveX a. Propiedades de los controles b. Código VBA de los controles 10. Manejo de gráficos a. Redefinir los rangos a graficar b. Mostrar gráficos en una ventana de formulario 11. Manejo de formas e imágenes a. Mover, copiar, cambiar tamaño b. Escribir formulas en las formas


Pag. 4

Manual de Microsoft Excel Programación VBA 12. Manejo de eventos de Excel a. Macros autoejecutables i. Para libros ii. Para hojas iii. Para rangos de celdas iv. Para acciones del mouse v. Para acciones del teclado 13. Manejo de módulos a. Insertar un modulo b. Eliminar un modulo c. Ocultar los módulos (contraseña) 14. Control de pausas al ejecutar sentencias a. Application.Wait b. Application.OnTime 15. Control de errores a. On Error Resume Next b. On Error GoTo c. Err.Number d. Err.Description e. Control de mensajes de alerta (DisplayAlert) f. Control de actualización de pantalla (Screen.Updating)


Pag. 5


La Programación en Excel g. Uso de la grabadora de macros A pesar que la programación se realiza internamente utilizando el lenguaje de Visual Basic, no es necesario que los usuarios de Excel sepan necesariamente el uso de este lenguaje. Y esto es así, gracias a que existe la posibilidad de que se puedan grabar las acciones que necesitamos utilizando para ello una grabadora de macros, la cual almacena en memoria todas las acciones a grabar, e internamente convierte a estas en un programa en Visual Basic.

h. Que es la grabadora de macros Es un programa de Office que se encarga de almacenar todos los pasos y acciones que se realizan en la pantalla, ya sea usando para ello el teclado o el mouse. Todo lo que se realice será grabado como un grupo de acciones que luego podrán repetirse cuantas veces quiera y en el momento que se desee. Para crear una macro haciendo uso de la grabadora de macros se siguen los siguientes pasos: 1.- Llamar al menú Herramientas/ Macro/Grabar nueva macro 2.- Escribir un nombre para la macro a grabar. Este nombre no deberá contener espacios en blanco. 3.- Al aparecer el mensaje “Grabando” en la barra de estado, hay que realizar cuidadosamente y en forma secuencial, todas las acciones que se desean grabar. 4.- Para finalizar la grabación hacer un clic en el botón: , o llamar al menú Herramientas/Macro/Finalizar grabación, con lo cual deberá desaparecer el mensaje “Grabando” en la barra de estado. Nota.-

Las macros al ser creadas pueden grabarse en el libro actual, como se puede apreciar en la ventana de dialogo anterior en la opción: Este libro , con lo cual la macro solo funcionará en ese libro; o también se pudo elegir en la ventana de diálogo la opción: Libro de macros personal, para cuyo caso la macro podría funcionar también en todos los libros de Excel, sin embargo hay que tener cuidado; pues si se graba en el libro de macros personal esto hará que dicha macro y todas las que se encuentren allí sean cargadas en memoria RAM cada vez que se ingrese a Excel, restando con ello memoria al computador innecesariamente quizas.

Una vez creada, veamos a continuación como se hace para que la macro funcione:


Pag. 6


i. Ejecución de una macro Una macro puede ejecutarse con el menú Herramientas/Macro/Macros, o presionar Alt + F8 y en la ventana de dialogo, seleccionar su nombre y luego elegir el botón Ejecutar. Sin embargo, un modo mas controlado y fácil de ejecutar una macro es asignándola a un elemento de imagen, como puede ser a un dibujo de Autoformas, a un botón de herramientas personalizado o incluso hasta a un gráfico. Para asignar una macro a un botón de Autoformas realizar lo siguiente:

Clic derecho ---------------------------------------------Asignar macro... -------------------------------

1.- Señalar con el mouse el borde del botón y hacer un clic derecho para llamar al menú contextual 2.- En el menú elegir la opción Asignar macro. 3.- Seleccionar el nombre de la macro de la lista y luego clic en Aceptar. Ejemplo Se va ha crear una macro que alinee verticalmente al centro, el contenido de una celda: 1.- Para esto primero escribir en una celda un dato. 1.- Llame al menú Herramientas/Macro/Grabar nueva macro y escriba como nombre de la macro: CentradoVertical (sin dejar espacio en blanco entre las 2 palabras), y luego Aceptar. 2.- Al aparecer el mensaje “Grabando” en la barra de estado, realizar la acción de centrado, esto es: llamar al menú


Pag. 7

Manual de Microsoft Excel Programación VBA Formato/Celdas y en la ficha Alineación elegir en la opción Alineación del texto Vertical, la alternativa Centrar, luego elegir el botón Aceptar. 3.- Finalmente terminar la macro con el botón Detener grabación o con el menú Herramientas/ Macro/Detener grabación. 4.- A continuación dibuje en la hoja un botón con Autoformas y haciendo un clic derecho en él, asígnele la macro CentradoVertical. 5.- Ahora, al escribir en una celda un dato podrá centrar el dato verticalmente haciendo un clic en el botón de la macro.

j. Cómo es el programa creado por la grabadora de macros Para la macro del ejemplo anterior, el programa o subrutina en el lenguaje de Visual Basic que se habría generado automáticamente, sería el siguiente: Sub CentradoVertical() ' ' Macro creada por Daniel Zegarra - UNI ' With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With End Sub Este programa se encontraría escrito en una hoja de Modulo insertada dentro del libro actual de Excel, y para ver esta hoja de módulo habría que ingresar a la ventana del Editor de Visual Basic.

k. Como ver el programa en el Editor de Visual Basic Se puede hacer de dos formas: i.

Presionando teclas Alt+F8, o llamando al menú Herramientas/Macro/ Macros

ii.

Presionando las teclas Alt+F11, o Herramientas/Macro/ Editor de Visual Basic.

llamando

al

menú

La primera forma para ver una macro es llamando al menú Herramientas/Macro/Macros o presionar las teclas Alt + F8 y seleccionando el


Pag. 8

Manual de Microsoft Excel Programación VBA nombre de la macro deseada, luego hacer un clic en el botón de Modificar de la ventana de dialogo.

La segunda forma de ver una macro es llamando al menú Herramientas/Macro/ Editor de Visual Basic. Si al ingresar al Editor de Visual Basic la ventana estuviese totalmente vacía, llamar al menú Ver y elegir la opción Explorador de Proyectos, o presionar Ctrl+R. Se verá entonces la siguiente ventana en la pantalla:


Pag. 9

Manual de Microsoft Excel Programación VBA En la ventana de Proyecto de la izquierda, haga doble clic en el elemento Modulo1 para abrir la ventana conteniendo el código en Visual Basic de la macro.

l. Cómo eliminar una macro Se puede hacer de dos formas: i.

Presionando teclas Alt+F8, o llamando al menú Herramientas/Macro/ Macros, seleccionando el nombre de la macro a eliminar, y luego haciendo clic en el botón Eliminar. Finalmente hacer clic en el botón Si.

ii.

Ingresando al / Editor de Visual Basic, y luego de seleccionar las sentencias de la macro a eliminar, presionar la tecla Suprimir.

Nota.-

Si al crear una macro con la grabadora de macros, se le da como nombre el de una macro ya existente, aparecerá una ventana consultándonos si se desea sobrescribir esa macro. Si se le contesta que Si, entonces se borrará la macro existente y se grabará una nueva en su lugar.


Pag. 10



Pag. 11


Ejercicio Nº 1: Acciones repetitivas con macros Hay ocasiones en que se necesita en la hoja de calculo repetir un grupo de acciones muchas veces para diferentes rangos de celda. En estos casos es cuando las macros nos serán de gran ayuda. Suponga que se dispone de una lista diaria de clientes que asisten durante un mes a realizar sus compras a una zapatería, y se desea ordenar esta lista en grupos semanales, y cada semana ordenada descendentemente según la cantidad de clientes asistentes. Una vez ordenada cada semana en orden descendente poner en negrita y de color rojo la cantidad maxima de personas que asistieron en cada semana. Veamos como se soluciona este caso con ayuda de las macros: 1ra Parte: Crear la tabla de clientes que asisten a una zapatería. 1.- En una hoja en blanco escriba lo siguiente:

2.- Seleccionando la celda A5, llame al menú Formato/Celdas y en la ficha Número, categoría personalizada, diseñe el siguiente formato en la casilla Tipo: ddd dd mmm La fecha aparecerá como: Mié 01 Jun 3.- Luego con el botón derecho del mouse arrastre el cuadro de relleno de la celda A5 hasta llegar a la celda A26, y cuando al soltar el botón del mouse aparezca un menú contextual, elegir la opción Rellenar días de la semana, para copiar la serie de días laborales del mes de Junio. (Solo aparecerán 5 días laborables en cada semana)


Pag. 12

Manual de Microsoft Excel Programación VBA Luego en la columna B vamos a escribir la cantidad de personas que asistieron cada día a la zapatería. Para simular la cantidad de personas vamos a hacer que Excel escriba números al azar entre 10 y 150: 4.- Escriba la siguiente formula en la celda B5: =10+ENTERO(ALEATORIO()*140) 5.- Ahora copie esta formula haciendo doble clic en el cuadro de relleno. Luego convierta el resultado de estas formulas en valores: 6.- Seleccione el rango B5:B26 y elija el menú Edición/Copiar, luego en el menú Edición/Pegado especial, marque la opción Valores y Aceptar. Finalmente [Esc].

2da Parte: Dividir las personas descendentemente.

en

grupos

semanales

ordenados

Como se explicó al principio, se desea una macro que haga lo siguiente:

7.-

Primero seleccione la celda A8.

8.-

Llame al menú Herramientas/Macro/Grabar nueva macro

9.-

Como nombre para la macro escriba la palabra Grupos y como letra de método abreviado escriba la letra "k". Luego Aceptar


Pag. 13

Manual de Microsoft Excel Programación VBA 10.- Al aparecer el mensaje "Grabando" en la barra de estado, verifique que se encuentre activado el botón de herramientas Referencia relativa:

11.- Luego realice los siguientes pasos: 

Llamar al menu Insertar/Fila



Seleccionar la celda A7 y presionar las teclas Ctrl+* para seleccionar la región actual (A5:B7)



Llamar al menú Datos/Ordenar y elegir ordenar por Columna B, en orden Descendente y verifique que No se considere tener fila de encabezamiento. Luego haga clic en el botón Aceptar.



Seleccionar la celda B5 y ponerla en Negrita, y de color de fuente Roja.



Seleccione la celda A14 y haga un clic en el botón Detener grabación.

13.- Guarde el libro con el nombre de archivo Asistencia. Con esto ya tendremos ordenado el primer grupo delos días correspondientes a la primera semana de Julio. Y como el puntero de celda se encuentra en A14, solo bastará con presionar las teclas Ctrl+k para ejecutar la macro y que esta se encargue de ordenar el grupo de la segunda semana, y así sucesivamente hasta terminar con el resto del mes.


Pag. 14


16. Escritura de programas en el Editor de Visual Basic a. Que es Visual Basic para Aplicaciones Es un lenguaje de programación por medio del cual se pueden escribir instrucciones secuenciales y detalladas dentro de uno o varios programas, para lograr así automatizar el uso de las aplicaciones del paquete Office. A este grupo de instrucciones escritas para una aplicación se la conoce como una Macro (Macro instrucción o grupo de varias instrucciones)

b. El Editor de Visual Basic La escritura de estas instrucciones se realiza con ayuda de un Editor de Visual Basic, que es una ventana de aplicación adicional a Excel o a cualquier otra aplicación de Office, pero la macro que se crea a través del Editor será guardada conjuntamente con el libro de Excel para el cual se han escrito estas instrucciones. Para ingresar al Editor de Visual Basic: Estando en cualquier libro de Excel, llamar al menú Herramientas/ Macro/Editor de Visual Basic, o sino, presionar las teclas Alt+F11.

Si al ingresar al Editor de Visual Basic, no estuvieran visibles el explorador de proyectos o la ventana de módulo, entonces proceda del siguiente modo: 1.- Llamar al menú Ver/Explorador de proyectos. 2.- Llamar al menú Insertar/Módulo.


Pag. 15

Manual de Microsoft Excel Programación VBA El Explorador de proyectos nos muestra en un árbol de carpetas los diferentes archivos o libros abiertos en Excel, y dentro de cada libro se pueden apreciar los objetos que existen en su interior: hojas de cálculo, hojas de módulos, formularios, etc. La ventana de Módulo es donde se escribirán las instrucciones que nos permitirán crear las Macros en Visual Basic.

17. Las instrucciones de Visual Basic Aclaración previa: Si para usted es la primera vez que va a escribir un programa para computadora, entonces al principio esto le parecerá que es algo complicado, y lo que inicialmente vemos escrito dentro de un programa es como leer chino, pero no se desanime; lo que ocurre es que a pesar de que el computador es una máquina inteligente, ésta sin embargo, no razona como lo haría cualquier persona; así que cuando hay que darle ordenes al computador hay que establecer ciertas reglas estrictas con el fin de que entienda claramente que es lo que deseamos que haga. Por ejemplo Si en su casa se ha puesto la tetera para hervir agua en una cocina a gas, y comienza a hervir el agua; entonces usted podría ordenarle a su hijo menor que por favor apague la cocina. Si su hijo aun es pequeño, ¿cómo cree que haría para apagar la cocina?: - Comienza a soplar la hornilla hasta que se apaga? - Hecha agua a la hornilla para apagarla? - Gira la perilla de encendido para apagar la hornilla? Usted tendría que ser más preciso en darle las instrucciones al pequeño para que pueda apagar la cocina como es debido sin que ocurra ninguna desgracia, pues él quizás aun no se percata exactamente del peligro que esta acción conlleva. Pues veamos ahora, el computador tampoco se da cuenta de lo que en realidad está haciendo, el solo cumple las ordenes que le damos, de allí que estas órdenes deben ser muy precisas, sin ambigüedades para que no lleven a erróneas suposiciones por parte del computador. Es así que primeramente cada orden dada al computador tiene una forma exacta de escribirse; a esta forma exacta de escribir las órdenes se conoce como la sintaxis de las instrucciones. Por ejemplo, si en vez de ordenar "apaga la cocina", se le dijese al niño "paga la cocina". El no va a poder obedecernos porque no conoce como se realiza esa acción ya que aun no maneja dinero y no sabe cómo se paga un artefacto. Esto es un error de sintaxis, la palabra está mal escrita. Pero además las instrucciones deben seguir una cierta lógica adecuada porque si no también se estaría incurriendo en un error de lógica al escribir el programa. Por ejemplo, un error de lógica podría ser que se le diga al niño: " por favor apaga la cocina y luego sírveme un café". La orden aparentemente parece


Pag. 16

Manual de Microsoft Excel Programación VBA correcta. Pero el niño podría ir y apagar la luz de la cocina y luego intentar a oscuras servirme un café. Esto se conoce como un error de lógica. Entonces debimos haber dicho: "por favor apaga la hornilla derecha de la cocina y luego sírveme un café". Algo así es como funciona la lógica de un computador, y teniendo en cuenta todo esto es que se escriben los programas de computadora. Observe además que la lógica empleada para dar órdenes a un niño en algún lugar de nuestro planeta es independiente de si estas órdenes las damos en cualquier idioma, ya sea en español, o inglés, o ruso o japonés; dependiendo del lugar donde nos encontremos. Es decir; el idioma puede ser diferente, pero la lógica es la misma. La lógica de las computadoras es siempre la misma, pero el lenguaje que usamos para dar las instrucciones es la que puede ser diferente; en nuestro caso usaremos el lenguaje de Visual Basic para Aplicaciones de Office.

Una instrucción de Visual Basic puede incluir palabras clave, operadores, variables, constantes y expresiones. Todas las instrucciones pertenecen a una de las tres categorías siguientes: 1. INSTRUCCIONES DE DECLARACIÓN, que dan nombre a una variable, constante o procedimiento y pueden también especificar su tipo de datos. 2. INSTRUCCIONES DE ASIGNACIÓN, que asignan un valor o expresión a una variable o constante. 3. INSTRUCCIONES EJECUTABLES, que inician acciones. Estas instrucciones pueden ejecutar un método o función y pueden saltar o evitar bloques de código. Las instrucciones ejecutables a menudo contienen operadores condicionales o matemáticos.

a. Continuar instrucciones en múltiples líneas Una instrucción cabe normalmente en una línea, pero puede continuarse en la siguiente línea utilizando un carácter de continuación de línea que es un carácter de subrayado o guión bajo ( _ ) precedido siempre por un espacio en blanco. En el siguiente ejemplo, la instrucción ejecutable MsgBox se extiende dos tres líneas: Guión bajo precedido de un espacio en blanco.

Sub Mensaje() Dim Cliente As String Cliente = "José Samuel" MsgBox Prompt:="Hola " & Cliente, Title:="Cuadro de saludo", _ Buttons:=vbExclamation End Sub

b. Añadir comentarios Los comentarios pueden explicar un procedimiento o una instrucción en particular a cualquier persona que tenga que leer el código. Visual Basic ignora los comentarios cuando ejecuta los procedimientos. Las líneas de comentario comienzan por un apóstrofe (') o con la palabra clave Rem seguida por un espacio y puede colocarse en cualquier lugar del procedimiento. Para añadir un comentario a la misma línea que ocupa una instrucción, debe insertarse un


Pag. 17

Manual de Microsoft Excel Programación VBA apóstrofe después de esta, seguido por el comentario. Los comentarios aparecen en pantalla en color verde, color predefinido. Sub Mensaje() ‘ Macro que muestra un mensaje de saludo en la pantalla Dim Cliente As String ‘ Declara la variable Cliente como String Cliente = "José Samuel" ‘ Asigna un texto a la variable Cliente ‘ La siguiente sentencia muestra una ventana con el mensaje de saludo MsgBox Prompt:="Hola " & Cliente, Title:="Cuadro de saludo", _ Buttons:=vbExclamation End Sub

c. Comprobar errores de sintaxis Si se presiona la tecla Enter después de escribir una línea de código y esta línea aparece en pantalla en color rojo (puede que aparezca también un mensaje de error), esto indica que se ha incurrido en un error de sintaxis, o sea la instrucción está mal escrita y se debe observar cuál es el problema en la instrucción y corregirlo.

18. Escribir instrucciones de declaración Las instrucciones de declaración se usan para dar nombre y definir procedimientos, variables, matrices y constantes. Cuando se declara un procedimiento, variable o constante, también se define su alcance que depende del lugar en que se coloque la declaración y de las palabras clave que se usan para ello. El siguiente ejemplo contiene tres declaraciones. Sub DarFormato() Const Limite As Integer = 33 Dim miCelda As Range End Sub

La instrucción Sub, la instrucción Const y la instrucción Dim, son instrucciones de declaración. La instrucción Sub (con la correspondiente instrucción End Sub) declara un procedimiento llamado DarFormato. Todas las instrucciones que aparecen entre las instrucciones Sub y End Sub se ejecutan cuando el procedimiento DarFormato se ejecuta o se llama. La instrucción Const declara la constante Limite, especificando el tipo de datos Integer y un valor de 33. La instrucción Dim declara la variable miCelda. El tipo de datos es objeto, en este caso, un objeto Range de Microsoft Excel. Se puede declarar una variable que sea cualquiera de los objetos que están accesibles a la aplicación que se está usando. Las instrucciones Dim son un tipo de instrucción que se utiliza para declarar variables. Otras palabras clave utilizadas en las declaraciones son ReDim, Static, Public, Private, Function y Const.


Pag. 18


a. Qué son los tipos de datos Variant El tipo de datos Variant se especifica automáticamente si no se especifica otro tipo de datos al declarar una constante, variable, o argumento. Las variables declaradas como del tipo de datos Variant pueden contener valores numéricos, cadenas de texto, fecha, hora o Booleans y pueden convertir los valores que contienen de forma automática. Los valores numéricos Variant ocupan 16 bytes de memoria (lo que sólo es significativo en procedimientos grandes o módulos complejos) y son más lentos a la hora de su acceso que las variables de tipo explícito de los restantes tipos. Es muy raro utilizar el tipo de datos Variant para una constante. Los valores de cadena Variant necesitan 22 bytes de memoria. Las siguientes instrucciones crean variables Variant: Dim SUELDO Dim EDAD As Variant EMPRESA = "Editora Macro"

La última instrucción no declara explícitamente la variable EMPRESA, sino que la declara implícitamente, o automáticamente. Las variables que se declaran implícitamente se especifican como del tipo de datos Variant. Nota:

Si se especifica un tipo de datos para una variable o argumento y a continuación se utiliza un tipo erróneo de datos, se producirá un error de tipo de datos. Para evitar errores de tipo de datos, se deben usar sólo variables (del tipo de datos Variant) o declarar explícitamente todas las variables y especificar para ellas un tipo de datos. El último método es el preferible.

19. Escribir instrucciones de asignación Las instrucciones de asignación, asignan (almacenan o guardan) un valor o expresión a una variable o constante. Las instrucciones de asignación incluyen siempre un signo igual (=). El siguiente ejemplo almacena la coordenada de la celda activa en la variable Lugar. Luego MsgBox muestra esta coordenada en una ventana. Sub Ubicacion() Dim Lugar As String Lugar = ActiveCell.Address MsgBox "La celda actual es " & Lugar End Sub

Este otro ejemplo utiliza InputBox para preguntar el nombre a una persona y luego asignar la respuesta a la variable Nombre. Luego MsgBox muestra el nombre de la persona en una ventana. Sub Pregunta()


Pag. 19

Manual de Microsoft Excel Programación VBA Dim Nombre As String Nombre = InputBox("¿Cómo se llama usted?") MsgBox "Su nombre es " & Nombre End Sub

La instrucción Set se utiliza para asignar un objeto a una variable que ha sido declarada como objeto. La palabra clave Set es necesaria. En el siguiente ejemplo, la instrucción Set asigna la celda A1 de la Hoja3 a la variable de objeto Celda. Luego le asigna la propiedad Bold e Italica al objeto Font y finalmente asigna a Celda el valor 2013. Sub DarFormato() Dim Celda As Range Set Celda = Worksheets("Hoja3").Range("A1") With Celda.Font .Bold = True .Italic = True End With Celda= 2013 End Sub

Las instrucciones que establecen valores de propiedad son también instrucciones de asignación. El siguiente ejemplo asigna la propiedad Bold (negrita) del objeto Font (fuente) para la celda activa: Como resultado el contenido de la celda activa aparecerá en negrita. ActiveCell.Font.Bold = True

20. Trabajo con celdas en Visual Basic Es una tarea común en Visual Basic especificar una celda o un rango de celdas, y a continuación realizar alguna acción en ellas, como ingresar un dato, escribir una fórmula o cambiar el formato. Normalmente esto se realiza en una instrucción que primero identifica el rango y luego además cambia una propiedad o aplica un método sobre ese rango. Un objeto Range en Visual Basic puede ser una celda individual o un rango de celdas. Los siguientes temas muestran las maneras más usuales de identificar y trabajar con objetos Range.

a. Referencia a celdas y rangos utilizando la notación A1 La siguiente tabla muestra algunas referencias de estilo A1 utilizando el método Range. Referencia

Significado

Range("A1")

Celda A1

Range("A1:B4")

Rango A1:B4

Range("A1:B4,E3:F9")

Múltiple áreas separadas


Pag. 20

Manual de Microsoft Excel Programación VBA Referencia

Significado

Range("A:A")

Toda la columna A

Range("3:3")

Toda la fila 3

Range("A:C")

Columnas de la A a la C

Range("1:5")

Filas de la 1 a la 5

Range("1:1,3:3,8:8")

Múltiples filas 1, 3 y 8

Range("A:A,C:C,F:F")

Múltiples columnas A, C y F

Range("A1:B4","E3:F9")

Equivalente a todo el rango A1:F9

Utilice la propiedad Rows o Columns para trabajar con filas o columnas enteras. Estas propiedades devuelven un objeto Range que representa un rango de celdas. La siguiente tabla muestra algunas referencias de fila y columna, utilizando las propiedades Rows y Columns.

Referencia

Significado

Rows(1)

Fila uno

Rows

Todas las filas de la hoja de cálculo

Columns(1)

Columna uno

Columns("A")

Columna uno

Columns

Todas las columnas de la hoja de cálculo

b. Sentencias usadas para manejar celdas y rangos de celdas Los métodos: Range

Select

Activate

Clear

ClearContents

ClearFormats

Delete

Insert

Las propiedades: Selection

ActiveCell

CurrentRegion

Offset

Columns, Rows

Count

21. Para seleccionar celdas y rangos de celdas a. Selección directa de celdas y rangos Sintaxis:

Range(referencia).Select


Pag. 21

Manual de Microsoft Excel Programación VBA Donde: 

referencia puede ser un rango de celdas o un nombre de rango, escritos entre comillas

Selecciona el rango B5 Range("B5").Select



Selecciona el rango B5:B9 Range("B5:B9").Select



Selecciona el rango B5:B9 Range("B5", "B9").Select



Selecciona solo dos celdas B5 y B9 Range("B5,B9").Select



Selecciona los rangos B5:B7 y B14:B16 Range("B5:B7,B14:B16").Select



Selecciona el rango llamado TABLA Range("TABLA").Select

Otra forma para seleccionar un rango, es escribiéndolo entre corchetes: Sintaxis: Donde: 

[referencia].Select referencia puede ser un rango de celdas o un nombre de rango, escritos entre comillas

Selecciona el rango B5:C7 [B5:C7].Select



Selecciona el rango llamado TABLA [TABLA].Select

b. Selección de rangos de manera relativa: Sintaxis: Donde:



ActiveCell.Range(referencia).Select referencia Es un rango de celdas que considera a la celda actual como si fuera la primera celda superior izquierda de la hoja de calculo

Selecciona 4 celdas hacia abajo a partir de la celda actual ActiveCell.Range("A1:A4").Select



Selecciona 4 celdas hacia la derecha a partir de la celda actual ActiveCell.Range("A1:D1").Select



Selecciona 4 celdas hacia abajo ubicadas 2 columnas a la derecha de la celda actual ActiveCell.Range("C1:C4").Select



Selecciona la región actual


Pag. 22

Manual de Microsoft Excel Programación VBA ActiveCell.CurrentRegion.Select

c. Selección consecutiva de celdas ocupadas o vacías: Sintaxis:



End(xlDown)

hasta la última celda hacia abajo

End(xlUp)

hasta la última celda hacia arriba

End(xltoRight)

hasta la última celda hacia la derecha

End(xltoLeft)

hasta la última celda hacia la izquierda

Selecciona todas las celdas ocupadas o vacías hacia abajo, a partir de B4 Range("B4").Select Range("B4", ActiveCell.End(xlDown)).Select



Selecciona todas las celdas ocupadas o vacías hacia abajo, a partir de B4 Range("B4", Range("B4").End(xlDown)).Select



Selecciona todas las celdas ocupadas o vacías hacia abajo, a partir de la celda actual Range(ActiveCell, ActiveCell.End(xlDown)).Select



Selecciona todas las celdas ocupadas hacia abajo + 1 adicional

Range(ActiveCell, ActiveCell.End(xlDown).Offset(1, 0)).Select



Selecciona una fila de 4 celdas y a partir de ellas hacia abajo todas las celdas ocupadas + 5 filas adicionales Range(ActiveCell.Range("A1:D1"), ActiveCell.End(xlDown). _ Offset(5, 0)).Select

d. Selección directa de Columnas 

Selecciona toda la columna E Range("E:E").Select



Selecciona todas las columnas desde la E hasta la K Range("E:K").Select



Selecciona todas las columnas desde la E hasta la K Range("EE”,”K:K").Select



Selecciona únicamente las columnas E y K Range("E:E,K:K").Select

e. Selección relativa de Columnas 

Selecciona toda la columna de la celda actual ActiveCell.EntireColumn.Select


Pag. 23

Manual de Microsoft Excel Programación VBA 

Selecciona todas las columnas del rango seleccionado Selection.EntireColumn.Select



Selecciona en la región actual las celdas hacia abajo a partir de la celda actual inicio = ActiveCell.Row fin = ActiveCell.CurrentRegion.Rows.Count + _ ActiveCell.CurrentRegion.Row - 1 Range(ActiveCell.EntireColumn.Cells(inicio, 1), _ ActiveCell.EntireColumn.Cells(fin, 1)).Select



Selecciona en la región actual las celdas hacia abajo a partir de la celda actual Range(ActiveCell.EntireColumn.Cells(ActiveCell.Row, 1), _

ActiveCell.EntireColumn.Cells(ActiveCell.CurrentRegion.Rows. _ Count + ActiveCell.CurrentRegion.Row - 1, 1)).Select

f. Selección directa de Filas 

Selecciona toda la fila 12 Range("12:12").Select



Selecciona todas las filas desde la 7 hasta la 12 Range("7:12").Select



Selecciona unicamente las filas 4, 12 y 20 Range("4:4,12:12,20:20").Select

g. Selección indirecta de Filas 

Selecciona toda la fila de la celda actual ActiveCell.EntireRow.Select



Selecciona todas las filas del rango seleccionado Selection.EntireRow.Select


Pag. 24


h. Para desplazar el puntero de celda: Uso de la sentencia Offset: Sintaxis: Donde:



Activecell.Offset(filas, columnas).Select filas Cantidad de filas que se desplaza hacia abajo, si filas es positivo Cantidad de filas que se desplaza hacia arriba, si filas es negativo columnas Cantidad de columnas que se desplaza hacia la derecha, si columnas es positivo Cantidad de columnas que se desplaza hacia la izquierda, si columnas es negativo

Baja el puntero 5 celdas a partir de la celda B4 Range("B4").Select ActiveCell.Offset(5, 0).Select



Baja el puntero 5 celdas a partir de la celda B4 Range("B4").Offset(5, 0).Select



Mueve el puntero 2 celdas abajo y 3 a la derecha a partir de la celda B4 Range("B4").Offset(2, 3).Select

Uso de la sentencia While: Sintaxis

While condición intrucciones Wend

Donde: condición

Es una expresión numérica o expresión de cadena cuyo valor es Verdadero o Falso. Si condición es Null, condición se considera Falso.

instrucciones

Opcional. Una o más instrucciones que se ejecutan mientras la condición sea Verdadera.



Selecciona la primera celda vacía debajo de una columna de datos Range("B4").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Activate Wend



Selecciona la última celda ocupada hacia abajo en la columna actual While ActiveCell.Row <> 65536 Selection.End(xlDown).Select Wend Selection.End(xlUp).Select


Pag. 25


Selecciona la ultima celda de la region actual

ActiveCell.CurrentRegion.Cells(ActiveCell.CurrentRegion.Rows. _ Count, ActiveCell.CurrentRegion.Columns.Count).Select



Selecciona la ultima celda de la region actual (otra forma) With ActiveCell.CurrentRegion .Cells(.Rows.Count, .Columns.Count).Select End With

i. Para borrar rangos de celdas: Uso de la sentencia Clear, Clear.Contents y Clear.Format: Sintaxis:

Donde: 

Referencia.Clear Referencia.ClearContents Referencia.ClearFormats Referencia Es un rango de celdas

Borra el contenido de la celda actual ActiveCell.ClearContents



Borra todo en el rango C4:C7 (Contenido y Formato) Range("C4:C7").Clear



Borra el formato del rango llamado CUADRO Range("Cuadro").ClearFormats

j. Para eliminar filas o columnas enteras: Uso de la sentencia Delete: Sintaxis: Donde: 

Referencia.Delete Referencia Es un rango de filas o columnas

Elimina la columna de la celda actual ActiveCell.EntireColumn.Delete



Elimina la fila de la celda actual ActiveCell.EntireRow.Delete



Elimina las columnas del rango seleccionado Range("F4:G4").Select Selection.EntireColumn.Delete



Elimina las filas del rango seleccionado Range("F4:G4").Select Selection.EntireRow.Delete

k. Para insertar filas o columnas: Uso de la sentencia Insert: Elaborado por: Daniel Zegarra Zavaleta

Pag. 26


Sintaxis: Donde: 

Referencia.Insert Referencia Es un rango de filas o columnas

Inserta una columna en la celda actual ActiveCell.EntireColumn.Insert



Inserta una fila en la celda actual ActiveCell.EntireRow.Insert



Inserta varias columnas en el rango seleccionado Range("F4:G4").Select Selection.EntireColumn.Insert



Inserta varias filas en el rango seleccionado Range("F4:G4").Select Selection.EntireRow.Insert

l. Para nombrar un rango de celdas: 

Selecciona el rango A4:C10 y le asigna el nombre TABLA Range("A4:C10").Select Selection.Name="TABLA"



Asigna el nombre TABLA al rango A4:C10 Range("A4:C10").Name="TABLA"



Selecciona la región actual y le asigna el nombre CUADRO ActiveCell.CurrentRegion.Select Selection.Name ="CUADRO"



Asigna el nombre CUADRO a la región actual ActiveCell.CurrentRegion.Name ="CUADRO"


Pag. 27


Ejercicio Nº 2: Procedimientos Sub para seleccionar rangos Vamos a suponer que usted no sabe nada de programación, asi que vamos a crear una serie de macros simples para que realicen varias de las acciones a las cuales estamos acostumbrados a realizar nosotros mismos en la hoja de calculo con el mouse y el teclado.

1.- En una hoja en blanco escriba los cuadros que se muestran en la imagen anterior y luego guarde el archivo con el nombre Muebles. 2.- Déle nombre a los siguientes rangos de celda: A3:C7  Tabla

E3:F7  Precios

B10:D12  Resumen

3.- Ingrese al Editor de Visual Basic con las teclas Alt+F11 y luego inserte una nueva hoja de módulo (Menú Insertar/Módulo). 4.- En la hoja de módulo escriba el siguiente procedimiento Sub:

5.- Regrese a la hoja de cálculo de Excel y seleccione la celda A1.


Pag. 28

Manual de Microsoft Excel Programación VBA 6.- Para ejecutar ahora la macro presione las teclas Alt+F8, luego en la ventana que aparece elegir la macro Prueba con un clic y a continuación Clic en Ejecutar.

La macro se ejecutará y moverá el puntero a la celda B4: A continuación presentamos una serie de ejemplos de procedimientos Sub para realizar diversos tipos de selección de celdas. Para poder observar cual es el efecto de cada uno de estos ejemplos, escríbalos en la hoja de modulo anterior debajo del procedimiento Prueba. A medida que los escriba puede nombrar cada procedimiento como Prueba2, Prueba3, etc. A medida que los vaya escribiendo regrese a la hoja de Excel y pruebe ejecutarlos con las teclas Alt+F8 y eligiendo la macro que quiera ejecutar respectivamente. Compare luego sus resultados con las imágenes que aquí se muestran en cada caso.


Pag. 29


Ejemplos de procedimientos Sub para seleccionar celdas: 1) Seleccionar la celda B4 Range("B4").Select

2) Seleccionar el rango de celdas A3:C7 Range("A3:C7").Select

3) Seleccionar el rango de celdas A3:C7 y luego convertir a la celda B5 en activa sin perder la selección del rango anterior Range("A3:C7").Select Range("B5").Activate

Nota.-

Para seleccionar un rango de celdas, use el método Select. Para activar sólo una celda, puede utilizar el método Activate. Si se activa una celda que se encuentra dentro del rango seleccionado entonces no se pierde la selección, pero si la celda que se activa está fuera del rango seleccionado la selección se perderá y solo quedará seleccionada la celda activa.

4) Seleccionar el rango de celdas cuyo nombre es Tabla Range("Tabla").Select

5) Seleccionar el rango Tabla y luego seleccionar la primera celda de ese rango. Range("Tabla").Select ActiveCell.Select


Pag. 30


6) Seleccionar los rangos Tabla, Precios y Resumen (rangos separados) Range("Tabla, Precios, Resumen").Select

7) Seleccionar los rangos desde Tabla hasta Resumen (el resultado es un rango mayor que incluye Tabla y Resumen, y todas las celdas que se encuentran entre estos dos rangos separados) Range("Tabla", "Resumen").Select

8) Seleccionar el rango de celdas de la región actual, (Región actual es el rango de las celdas ocupadas que rodean la celda activa.) ActiveCell.CurrentRegion.Select

9) Seleccionar el rango A3:C7 y luego "mover la selección" 2 filas hacia abajo y una columna hacia la derecha Range("A3:C7").Select Selection.Offset(2,1).Select


Pag. 31


10) Seleccionar el rango A3:C7 y luego "activar la celda" 2 filas hacia abajo y una columna hacia la derecha dentro del rango seleccionado Range("A3:C7").Select Selection.Offset(2,1).Activate

11) Seleccionar el rango A3:C7 y luego "mover el puntero de celda" 2 filas hacia abajo y una columna hacia la derecha dentro del rango seleccionado Range("A3:C7").Select ActiveCell.Offset(2,1).Select

12) Seleccionar la región actual y luego seleccionar la celda en blanco situada al final de la primera columna de la región actual


Pag. 32

Manual de Microsoft Excel Programación VBA ActiveCell.CurrentRegion.Select ActiveCell.Offset(Selection.Rows.Count, 0).Select

13) Seleccionar la región actual y luego seleccionar la última celda ocupada al final de la primera columna de la región actual ActiveCell.CurrentRegion.Select ActiveCell.Offset(Selection.Rows.Count - 1, 0).Select

14) Suponiendo una tabla con una fila de encabezado, a continuación se muestran 2 formas para seleccionar la tabla sin seleccionar la fila de encabezados. Debe haber una celda activada en algún lugar de la tabla antes de ejecutar el ejemplo. ActiveCell.CurrentRegion.Select ActiveCell.Offset(1, 0).Resize(Selection.Rows.Count - 1, _ Selection.Columns.Count).Select

ActiveCell.CurrentRegion.Rows(2).Select Range(Selection, ActiveCell.Offset(ActiveCell.CurrentRegion. _ Rows.Count - 2, 0)).Select


Pag. 33


La Propiedad End Desplaza el puntero a la celda situada al final de la región que contiene el rango fuente. Equivale a presionar las teclas FIN+FLECHA ARRIBA, FIN+FLECHA ABAJO, FIN+FLECHA IZQUIERDA o FIN+FLECHA DERECHA. Sintaxis: Expresión.End(Dirección) Donde: Expresión.-

Objeto a partir del cual se realizará el desplazamiento, (por ejemplo: ActiveCell, ó Range("A3"), ó Selection).

Dirección puede ser una de las siguientes constantes: xlUp xlDown xlToRight xlToLeft

hacia arriba hacia abajo hacia la derecha hacia la izquierda

Ejemplos del uso de la propiedad End: 1) Seleccionar la celda B3 y luego seleccionar la ultima celda ocupada al final de esa columna Range("B3").select ActiveCell.End(xlDown).Select

2) Selecciona la primera celda superior de la columna B, en la región que contiene la celda B6 Range("B6").End(xlUp).Select

3) Selecciona la última celda de la derecha de la fila 4, en la región que contiene la celda A4 Range("A4").End(xlToRight).Select

4) Amplía la selección desde la celda B4 hasta la última celda ocupada hacia la derecha Range("B4", Range("B4").End(xlToRight)).Select


Pag. 34


Ejercicio Nº 3: Nombrar rangos, Seleccionar columnas y Borrar 1.- En una hoja en blanco escriba el siguiente cuadro y luego guarde el archivo con el nombre Destinos. 2.- Ingrese al Editor de Visual Basic con las teclas Alt+F11 y luego de insertar una nueva hoja de modulo (Menú Insertar/Módulo) al igual que en el ejercicio anterior, escriba los siguientes procedimientos Sub: Ejemplos de procedimientos Sub: 1) Definir con el nombre abc al rango seleccionado. (Se supone que previamente se ha seleccionado un rango como B3:E7) Selection.Name="abc"

2) Definir con el nombre abc a la región actual. (previamente ubicar el puntero de celda en cualquier celda ocupada del cuadro) ActiveCell.CurrentRegion.Name="abc

Nota.-

Para los dos ejemplos anteriores que nombran un rango como abc, en el primer caso hay que seleccionar el rango y después ejecutar la macro. En el segundo caso basta con que el puntero de celda se encuentre dentro del cuadro y al ejecutar la macro, a todo el cuadro (Región actual) se le dará el nombre de rango.

3) Seleccionar la segunda columna del rango llamado abc Range("abc").Columns(2).Select

4) Seleccionar la segunda columna de la región actual. (Ubicar previamente el puntero en cualquier celda dentro del cuadro)


Pag. 35

Manual de Microsoft Excel Programación VBA ActiveCell.CurrentRegion.Columns(2).Select

Nota.-

Este ejemplo es semejante al anterior, con la diferencia que funciona para cualquier cuadro sin importar su tamaño, basta con seleccionar una celda del cuadro y cuando se ejecute la macro se seleccionará solo la 2da columna del cuadro. Observe que la 2da columna del cuadro no es la columna B, sino en este caso la columna C.

5) Seleccionar toda la columna de la celda activa ActiveCell.EntireColumn.Select

6) Seleccionar todas las columnas de la seleccion Selection.EntireColumn.Select

7) Seleccionar todas las columnas de la region actual ActiveCell.CurrentRegion.EntireColumn.Select

8) Seleccionar las 3 columnas a partir de la celda activa. Suponer que la celda activa es la B5. ActiveCell.Columns("A:C").EntireColumn.Select

Nota.-

En este ejemplo la referencia "A:C" es considerada como una referencia relativa, o sea que no indica que se seleccionen las 3 primeras columnas A,B y C de la hoja de calculo, sino que se seleccionen las 3 primeras columnas de la región actual.

9) Seleccionar la 3ra columna a partir de la celda activa. Suponer que la celda activa es la celda B5. ActiveCell.Columns(3).EntireColumn.Select


Pag. 36

Manual de Microsoft Excel Programación VBA Nota.-

En este ejemplo siendo B5 la celda activa, al ejecutarse la macro se seleccionará la tercera columna a partir de la columna B.

10) Seleccionar todas las columnas del rango abc Range("abc").Select Selection.EntireColumn.Select

11) Seleccionar las columnas del rango abc y del rango xyz. (rangos separados) Range("abc, xyz").Select Selection.EntireColumn.Select

12) Seleccionar dentro de la región actual la columna de la celda activa. Suponer que la celda activa es la C4. ActiveCell.CurrentRegion.Columns(ActiveCell.Column – ActiveCell _ .CurrentRegion.Column + 1).Select

13) Borrar el contenido del rango C5:D7


Pag. 37

Manual de Microsoft Excel Programación VBA Range("C5:D7").ClearContents

m. Para ingresar datos en celdas y rangos de celdas Uso de la Propiedad: Formula Uso de la función: Date Cuando se trabaja en la hoja de cálculo de Microsoft Excel, usualmente hay que seleccionar primero una o varias celdas y a continuación realizar una acción sobre estos rangos, como escribir valores o darles formato. Pero en cambio, en Visual Basic normalmente no es necesario "seleccionar" las celdas antes de modificarlas, bastara únicamente con referirnos a ellas y entonces Visual Basic podrá modificar sus propiedades. Nota.-

Si desea escribir en una celda utilizando Visual Basic, no es necesario seleccionar la celda, sólo necesita devolver el objeto Range y a continuación asignarle el dato que desee, como se muestra en los siguientes ejemplos:

Ejercicio Nº 4: Asignar valores a celdas y rangos 1.- Abra un nuevo libro en blanco, y ubique el puntero en cualquier celda de la Hoja1. Ejemplos de procedimientos Sub para asignar valores: 1) Escribir el titulo CUENTAS en la celda A1, luego los títulos SERVICIOS y PAGOS en las celdas A3 y B3, y finalmente el número 120 en la celda B4. Range("A1") Range("A3") Range("B3") Range("B4")

= = = =

"CUENTAS" "SERVICIOS" "PAGOS" 120

Nota.- Cuando se asigna un valor a un objeto la asignación va de derecha a izquierda, es decir; el valor escrito a la derecha del signo igual, es asignado al objeto situado a la izquierda.


Pag. 38

Manual de Microsoft Excel Programación VBA 2) Las siguientes instrucciones realizan de manera equivalente la misma tarea anterior. Observe el uso de la propiedad offset para desplazar la referencia de celda y escribir datos en ella: Range("A1").select ActiveCell = "CUENTAS" ActiveCell.offset(2,0) = "SERVICIOS" ActiveCell.offset(2,1) = "PAGOS" ActiveCell.offset(3,1) = 120

3) Escribir el número 120 en todas las celdas del rango llamado Pagos. (El rango llamado Pagos es B4:B9) 4) Escribir el contenido de la celda B4 multiplicado por 2, en la celda B9 5) Escribir la función SUMA del rango Pagos, en la celda B10, y luego poner en negrita la celda B10. 6) Escribir la fecha actual en la celda C3 Range("Pagos") = 120 Range("B9") = Range("B4")*2 Range("B10").Formula = "=SUM(Pagos)" Range("B10").Font.Bold = True Range("C3") = Date

Nota.-

Cuando se escriben formulas en las instrucciones de Visual Basic, estas formulas deberán estar encerradas entre comillas dobles, los argumentos de las funciones siempre se escribirán separados con comas y además los nombres de las funciones


Pag. 39

Manual de Microsoft Excel Programación VBA deberán estar escritas tal y como se escribirían en la versión en inglés de Excel, es decir los nombres de función no se escriben como las conocemos en español, sino en su equivalente según la versión original. Por ejemplo:

Nota.-

SUMA

como

SUM

PROMEDIO

como

AVG

CONTAR

como

COUNT

ENTERO

como

INT

BUSCARV

como

VLOOKUP

SI

como

IF

REDONDEAR

como

ROUND

ESBLANCO

como

ISBLANK

Si al escribir alguna formula en Visual Basic no se conoce su equivalente en inglés, lo que hay que hacer es regresar a la ventana de Excel y encender la grabadora de macros, luego escribir la fórmula en español en cualquier celda, apagar la grabadora y observar en la hoja de modulo como ha escrito el editor de Visual Basic su equivalente en la versión en inglés.

n. Usos del método Select y la propiedad Selection El método Select activa las hojas y los objetos de las hojas. La propiedad Selection devuelve un objeto que representa la selección actual de la hoja activa del libro activo. Antes de utilizar la propiedad Selection, debe activar un libro, o activar o seleccionar una hoja y luego a continuación, seleccionar un rango, u otro objeto, con el método Select. La grabadora de macros suele crear una macro que utiliza el método Select y la propiedad Selection. El siguiente procedimiento Sub se creó utilizando la grabadora de macros, y muestra cómo trabajan juntas Select y Selection. Sub Macro1() Range("A3").Select ActiveCell.FormulaR1C1 = "CLIENTE" Range("B4").Select ActiveCell.FormulaR1C1 = "PAGOS" Range("A3:B3").Select Selection.Font.Bold = True End Sub

El siguiente ejemplo en cambio, realiza de manera equivalente la misma tarea anterior, pero sin activar ni seleccionar las celdas. Sub Macro2() Range("A3") = "CLIENTE" Range("B3") = "PAGOS"


Pag. 40

Manual de Microsoft Excel Programación VBA Range("A3:B3").Font.Bold = True End Sub

Este segundo ejemplo evidentemente es más simple y a la vez más eficiente que el anterior, pues la macro no pierde tiempo desplazando el puntero de celda en la pantalla para seleccionar ningun rango previamente al ingreso de los datos; la imagen en la pantalla permanece estática y solo se verán que aparecen los datos en las celdas correspondientes.


Pag. 41


Ejercicio Nº 5: Procedimientos Sub para Cambio de moneda Caso1: Se desea crear un procedimiento en Visual Basic para convertir soles a su equivalente en dólares. 1.- En una hoja en blanco escriba lo siguiente:

2.- Ingrese al Editor de Visual Basic con Alt+F11 3.- Inserte una hoja de modulo con el menú Insertar/Modulo 4.- En la hoja de modulo escriba el siguiente procedimiento Sub: Sub Dolares() Monto = range("B3") tcambio = range("B4") Range("B5") = Monto / tcambio End Sub

Como puede verse, las instrucciones en este procedimiento hacen lo siguiente: -

El contenido de la celda B3 lo guarda en la variable Monto

-

El contenido de la celda B4 lo guarda en la variable Tcambio

-

Divide el valor de la variable Monto entre la variable Tcambio y el resultado lo guarda en la celda B5.

4.- Regrese a la hoja de cálculo y dibuje un botón como el que se muestra en la figura anterior (use Autoformas/Formas básicas/Bisel) 5.- Asigne la macro Dolares a este botón. Cuando haga un clic en el botón de la macro Dólares, el procedimiento se ejecutará y aparecerá el resultado en la celda B5. Nota.-

Cuando se ejecuta esta macro, observe que en el contenido de las celdas no existe ninguna fórmula, todos los cálculos se realizaron en Visual Basic y en las celdas solamente aparecen los resultados. Habrá otros casos como ya se verá más adelante en los cuales se va a desear que la macro escriba también formulas en las celdas.

Caso2: Elaborado por: Daniel Zegarra Zavaleta

Pag. 42

Manual de Microsoft Excel Programación VBA Crear otro procedimiento en Visual Basic para convertir dólares a su equivalente en soles. 1.- En la misma hoja de cálculo anterior borre la celda B3 y escriba un monto en la celda B5:

2.- Ingrese a la ventana de Visual Basic y debajo del procedimiento anterior escriba este otro procedimiento Sub: Sub Soles() Monto = range("B5") tcambio = range("B4") Range("B3") = Monto * tcambio End Sub

En esta ocasión, las instrucciones de este procedimiento hacen lo siguiente: -

El contenido de la celda B5 lo guarda en la variable Monto

-

El contenido de la celda B4 lo guarda en la variable Tcambio

-

Multiplica el valor de la variable Monto por la variable Tcambio y el resultado lo guarda en la celda B3.

3.- Regrese a la hoja de cálculo y dibuje otro botón para la macro Soles como se muestra en la figura anterior. 4.- Luego asigne la macro Soles a este segundo botón. Cuando haga un clic en el botón de la macro Soles, se ejecutará el procedimiento y los dólares de la celda B5 se convertirán a soles que aparecerán en la celda B3.

Caso3: Ahora crear un procedimiento que borre el contenido del rango B3:B5 y luego seleccione la celda B3. 1.- Ingrese a la ventana de Visual Basic y debajo del procedimiento anterior escriba este otro procedimiento Sub: Sub Borrar() Range("b1:b3").ClearContents


Pag. 43

Manual de Microsoft Excel Programación VBA Range("b1").Select End Sub

2.- En la hoja de cálculo dibuje otro botón para la macro Borrar como se muestra en la siguiente figura:

3.- Luego asigne la macro Borrar a este botón. Cuando haga un clic en el botón de la macro Borrar, se borraran las celdas B3, B4 y B5 y se ubicará el puntero de celda en B3.


Pag. 44


Ejercicio Nº 6: Procedimientos Sub para Ahorro personal Caso1: Se desea crear un procedimiento en Visual Basic para controlar los ahorros en una cuenta personal. 1.- En una hoja en blanco escriba lo siguiente:

2.- Nombre los siguientes rangos de celda: B2  Fecha

B3  Anterior

B5  Retiro

B6  Actual

B4  Deposito

2.- Ingrese al Editor de Visual Basic con Alt+F11 3.- Inserte una hoja de modulo con el menú Insertar/Modulo y escriba el siguiente procedimiento Sub: Sub Ahorro() Range("Fecha") = Date Range("Deposito") = InputBox("Ingrese Depósito") Range("Retiro") = InputBox("Ingrese Retiro") Range("Anterior") = Range("Actual") Range("Actual") = Range("Anterior")+Range("Deposito")Range("Retiro") End Sub

Las instrucciones en este procedimiento hacen lo siguiente: -

Guarda la fecha actual en la celda llamada Fecha

-

La sentencia InputBox mostrará una ventana solicitando que se ingrese el monto del deposito, y luego almacena este monto en la celda llamada Depósito.

-

Luego otra ventana solicitará el monto de retiro el cual se almacena en la celda llamada Retiro. (si no existe un monto de retiro, al aparecer la ventana solicitando el retiro solo se debe presionar Enter o hacer clic en Aceptar)

-

Guarda el valor de la celda llamada Actual en la celda llamada Anterior

-

Calcula el nuevo saldo actual y lo guarda en la celda llamada Actual

4.- Regrese a la hoja de cálculo y dibuje un botón como el que se muestra en la figura anterior (use Autoformas/Formas básicas/Bisel)


Pag. 45

Manual de Microsoft Excel Programación VBA 5.- Luego asigne la macro Ahorro a este botón. Cuando haga un clic en el botón de la macro Ahorro, ingrese un monto de depósito y si desea también de retiro, y al aceptar aparecerán los resultados en la hoja de cálculo. Nota.-

Este procedimiento tal y como esta diseñado, una vez que ya se ha ingresado un depósito y nos está solicitando ingresar ahora el retiro, no nos da opción a enmendar el anterior ingreso si es que hubo un error en él. Más adelante en otro ejercicio veremos como insertar instrucciones condicionales que permitan abortar una transacción o continuar ejecutándola.

La macro anterior a medida que se realizan movimientos en la cuenta va calculando el nuevo saldo, pero no lleva un registro de todos los movimientos efectuados. Para poder guardar cada uno de los depósitos o retiros efectuados día tras día, será necesario crear en la misma hoja de cálculo un cuadro adicional de 4 columnas que registre la fecha, los depósitos y retiros, y el saldo final en cada uno de los movimientos efectuados en la cuenta de ahorros. Para ello haga usted lo siguiente: 6.- Escriba en la hoja de cálculo anterior lo siguiente:

7.- Ingrese a la ventana de Visual Basic y al procedimiento anterior agréguele las siguientes instrucciones: Sub Ahorro() Range("Fecha") = Date Range("Deposito") = InputBox("Ingrese Depósito") Range("Retiro") = InputBox("Ingrese Retiro") Range("Anterior") = Range("Actual") Range("Actual")=Range("Anterior")+Range("Deposito")-Range("Retiro") Range("A9").Select ActiveCell.CurrentRegion.Select ActiveCell.Offset(Selection.Rows.Count, 0).Select ActiveCell = Date ActiveCell.Offset(0, 1) = Range("Deposito") ActiveCell.Offset(0, 2) = Range("Retiro") ActiveCell.Offset(0, 3) = Range("Actual")


Pag. 46

Manual de Microsoft Excel Programación VBA MsgBox ("Su saldo Actual es de " & Range("Actual") & " soles") End Sub

Luego de haber ingresado el depósito o el retiro, y haber calculado el saldo final, las instrucciones adicionales en este procedimiento hacen lo siguiente: -

Primero selecciona la celda A9

-

Selecciona la región actual

-

Desplaza el puntero de celda hasta la ultima celda ocupada de la región actual y se ubica en una celda vacía de la columna A.

-

En esa celda vacía de la columna A escribe la fecha actual

-

Se desplaza una celda a la derecha de A y escribe el monto del depósito

-

Se desplaza dos celdas a la derecha de A y escribe el monto del retiro

-

Se desplaza tres celdas a la derecha de A y escribe el saldo final

-

Finalmente muestra un mensaje en pantalla indicando cual es el monto del saldo actual en la cuenta.

8.- Regrese a la hoja de cálculo y haga un clic en el botón de la macro para registrar uno o varios movimientos en la cuenta. Si la macro funciona como se espera, regrese a la ventana de Visual Basic y agregue al procedimiento los siguientes comentarios con la finalidad de entender mejor cual es la función de cada grupo de instrucciones dentro de la macro. (Recuerde que los comentarios deben empezar a escribirse con un apóstrofe (‘).) Sub Ahorro() Range("Fecha") = Date 'Solicita datos Range("Deposito") = InputBox("Ingrese Depósito") Range("Retiro") = InputBox("Ingrese Retiro") Range("Anterior") = Range("Actual") 'Calcula el saldo actual Range("Actual") = Range("Anterior")+Range("Deposito")Range("Retiro") 'Ubica el puntero al final del registro de movimientos Range("A9").Select ActiveCell.CurrentRegion.Select ActiveCell.Offset(Selection.Rows.Count, 0).Select 'Registra los datos ActiveCell = Date ActiveCell.Offset(0, 1) = Range("Deposito") ActiveCell.Offset(0, 2) = Range("Retiro") ActiveCell.Offset(0, 3) = Range("Actual") 'Muestra un mensaje con el saldo actual MsgBox ("Su saldo Actual es de " & Range("Actual") & " soles") End Sub


Pag. 47



Pag. 48


22. Uso de funciones de hoja de cálculo de Microsoft Excel en Visual Basic Aunque el lenguaje de Visual Basic para aplicaciones tiene una lista propia de funciones disponibles, habrán casos en los cuales se va ha desear que las macros utilicen las funciones de hoja de calculo de Excel, pero no para escribir estas funciones en las celdas, sino para obtener un resultado con ellas y utilizar este resultado dentro del programa en Visual Basic. Se pueden usar la mayoría de las funciones de hoja de cálculo de Microsoft Excel en las instrucciones de Visual Basic.

a. Lista de las funciones de hoja de cálculo que se pueden usar en Visual Basic


Pag. 49


A

Contar.Si

Acos

ContarA

Distr.Norm.Estand.In v

Acosh

Cosh

Distr.Norm.Inv

Asen

Covar

Asenoh

Crecimiento

Distr.T.Inv

Atan2

Cuartil

E

Atanh

Curtosis

Elegir

B

D

EncontrarB

BDContar

DB

Error.Tipico.XY

BDContarA

DDB

EsErr

BDDesvEst

Decimal

EsError

BDDesvEstP

Despejar

EsLogico

BDExtraer

DesvEst

EsNod

BDMax

DesvEstP

EsNoTexto

BDMin

Desvia2

EsNumero

BDProducto

DesvProm

Espacios

BDPromedio

Dias360

EsTexto

BDSuma

DiaSem

BDVar

Dist.Weibull

BDVarP

Distr.Beta

Binom.Crit

Distr.Beta.Inv

Buscar

Distr.Binom

Buscar

Distr.Chi

BuscarH

Distr.Exp

BuscarV

Distr.F

C

Distr.F.Inv

Coef.De.Correl

Distr.Gamma

Coeficiente.Asimetria

Distr.Gamma.Inv

Coeficiente.R2

Distr.Hipergeom

H

Coincidir

Distr.Log.Inv

Hallar

Combinat

Distr.Log.Norm

HallarB

Contar

Distr.Norm

I

Contar.Blanco

Distr.Norm.Estand

Índice


Distr.T

Estimacion.Lineal Estimacion.Logaritmi ca

F Fact Fisher Fonetico Frecuencia

G Gamma.Ln Grados

Pag. 50

Manual de Microsoft Excel Programación VBA Int.Pago.Dir

PagoPrin

Interseccion.Eje

Pearson

Intervalo.Confianza

Pendiente

Jerarquia

Percentil

K.Esimo.Mayor

Permutaciones

K.Esimo.Menor

Pi

L

Poisson

Ln

Potencia

Log

Probabilidad

Log10

Producto

M

Promedio

Max

Pronostico

MDeterm

Prueba.Chi

Media.Acotada

Prueba.Chi.Inv

Media.Armo

Prueba.F

Media.Geom

Prueba.Fisher.Inv

Mediana

Prueba.T

Min

Prueba.Z

MInversa

R

MMult

Radianes

Moda

Rango.Percentil

Moneda

Redondea.Impar

Multiplo.Inferior

Redondea.Par

Multiplo.Superior

Redondear

N

Redondear.Mas

NegBinomDist

Redondear.Menos

NomPropio Normalizacion NPer Numero.Romano

P Pago PagoInt Elaborado por: Daniel Zegarra Zavaleta

Pag. 51

Manual de Microsoft Excel Programación VBA Reemplazar

SumaX2MasY2

Transponer

ReemplazarB

SumaX2MenosY2

U

Repetir

SumaXMenosY2

USDollar

RTD

Sustituir

V

S

SYD

VA

SenoH

T

Var

SLn

TanH

VarP

Subtotales

Tasa

Vdb

Suma

Tendencia

VF

Suma.Cuadrados

Texto

VNA

SumaProducto

Tir

Sumar.Si

TirM

Importante: Tenga en cuenta que en Visual Basic, el nombre de la función debe escribirse en su equivalente en inglés. Nota.-

Algunas funciones de hoja de cálculo no tienen utilidad en Visual Basic. Por ejemplo, la función Concatenar no se necesita, ya que en Visual Basic puede usar el operador de concatenación & para unir varios valores de texto..

b. Como llamar a una función de hoja de cálculo desde Visual Basic Cuando se desee usar una funciones de hoja de calculo en Visual Basic, esta función deberá ejecutarse mediante el objeto WorksheetFunction. El siguiente procedimiento Sub usa la función MÍN para obtener el valor más pequeño de un rango de celdas. Primeramente, se declara la variable CUADRO como un objeto Range, y a continuación se la asigna como el rango A1:C10. Luego a la variable MINIMO se le asigna el resultado de aplicar la función MÍN a CUADRO. Finalmente, el valor de la variable MINIMO es utilizada para obtener un cálculo y escribirlo en la celda A12. Sub UsarFuncion() Dim CUADRO As Range Set CUADRO = Range("A1:C10") MINIMO = Application.WorksheetFunction.Min(CUADRO) Range("A12") = MINIMO * 100 End Sub

Otra manera más simple de obtener el mismo resultado que la macro anterior sería de la siguiente forma: Elaborado por: Daniel Zegarra Zavaleta

Pag. 52


Sub OtraFuncion() Range("A12") = Application.WorksheetFunction.Min(Range("A1:C10"))*100 End Sub

Si usa una función de hoja de cálculo que requiere como argumento una referencia de rango, deberá especificar un objeto Range. Por ejemplo, puede usar la función de hoja de cálculo BUSCARV para efectuar una búsqueda en un rango de celdas. En una celda de hoja de cálculo, podría insertar una fórmula como =BUSCARV(4,A1:B10,2,0). No obstante, en un procedimiento de Visual Basic, para obtener el mismo resultado debe especificar un objeto Range para referirse al rango A1:B10. En el siguiente ejemplo, el procedimiento Sub BuscarDato asigna a la variable RPTA el resultado de la función VLOOKUP (que es el nombre en inglés de la función BUSCARV) y luego muestra en pantalla un mensaje con el valor de la variable RPTA. Sub BuscarDato() RPTA = Application.WorksheetFunction.VLOOKUP(4,Range("A1:B10"),2,0) MsgBox RPTA End Sub Nota.-

Las funciones de Visual Basic no usan el calificador WorksheetFunction. Una función de Visual Basic puede tener el mismo nombre que una función de Microsoft Excel y, sin embargo, dar otros resultados. Por ejemplo, Application.WorksheetFunction.Log, y la función Log dan resultados diferentes.

c. Insertar una función de hoja de cálculo en una celda En cambio, si lo que se quiere es que la macro escriba un función de hoja de cálculo en una celda, entonces habrá que especifique la función como el valor de la propiedad Formula del objeto Range correspondiente. En el siguiente ejemplo el procedimiento Sub InsertarFormulas, escribirá en varias celdas del la Hoja2, diversas funciones de hoja de calculo: Sub InsertarFormulas() Worksheets("Hoja2").Activate . Range("A3:A5").Formula = "=RAND()" Range("A6").Formula = "=SUM(A3:A5)" Range("A7").Formula = "=AVG(A3:A5)" Range("A8").Formula = "=COUNT(A3:A5)" End Sub

El equivalente de la función

ALEATORIO

es

RAND

SUMA

es

SUM

PROMEDIO

es

AVG

CONTAR

es

COUNT


Pag. 53


d. La Función InputBox Muestra un mensaje en un cuadro de diálogo, espera que el usuario escriba un texto o haga clic en un botón y devuelve el dato escrito Si el usuario hace clic en Aceptar o presiona Enter , la función InputBox devuelve lo que haya escrito en el cuadro de texto como un dato tipo String (texto). Si el usuario hace clic en Cancelar, la función devuelve una cadena de caracteres de longitud cero (""). Sintaxis InputBox (Mensaje , titulo, defecto, xpos, ypos, ayuda, contexto) Donde: Mensaje

titulo

defecto

xpos

ypos

ayuda

contexto

Nota.-

Obligatorio. Texto que se muestra como mensaje en el cuadro de diálogo. Si mensaje consta de más de una línea, puede separarlos utilizando un carácter de retorno de carro Chr(13), un carácter de avance de línea Chr(10) o una combinación de los caracteres de retorno de carro-avance de línea Chr(13) y Chr(10) entre cada línea y la siguiente. Opcional. Texto que se muestra en la barra de título del cuadro de diálogo. Si omite titulo, en la barra de título se coloca el nombre de la aplicación Excel Opcional. Texto que se muestra en el cuadro de texto como respuesta predeterminada cuando no se suministra una cadena. Si omite defecto, se muestra el cuadro de texto vacío. Opcional. Numero que especifica, en pixels, la distancia en sentido horizontal entre el borde izquierdo del cuadro de diálogo y el borde izquierdo de la pantalla. Si se omite xpos, el cuadro de diálogo se centra horizontalmente. Opcional. Expresión numérica que especifica, en pixels, la distancia en sentido vertical entre el borde superior del cuadro de diálogo y el borde superior de la pantalla. Si se omite ypos, el cuadro de diálogo se coloca a aproximadamente un tercio de la altura de la pantalla. Opcional. Texto que identifica el archivo de Ayuda que se utilizará para proporcionar ayuda interactiva para el cuadro de diálogo. Si se especifica ayuda, también deberá especificarse contexto. Opcional. Expresión numérica que es el número de contexto de Ayuda asignado por el autor al tema de Ayuda correspondiente. Si se especifica contexto, también deberá especificarse ayuda.

Si desea especificar más que el primer argumento con nombre, debe utilizar InputBox en una expresión. Si desea omitir algunos argumentos de posición, debe incluir los delimitadores de coma correspondiente. Inputbox("Ingrese nombre:", ,"Desconocido")


Pag. 54


e. El Método InputBox Muestra un cuadro de diálogo para que el usuario introduzca información. Devuelve la información introducida en el cuadro de diálogo. Sintaxis: expresión.InputBox (Mensaje, titulo, defecto, xpos, ypos, ayuda, contexto, tipo) Donde: expresión Mensaje

Title Defecto

xpos ypos Ayuda Contexto Tipo

Expresión obligatoria que asigna un objeto Application. Dato obligatorio que se mostrará en el cuadro de diálogo. Puede ser un texto, un número, una fecha o un valor Boolean (Excel convierte el dato en texto antes de que muestre). Opcional. Tiítulo del cuadro de entrada. Si este argumento se omite, el título predeterminado será "Entrada". Opcional. Especifica un valor que aparecerá en el cuadro de texto cuando se muestre inicialmente el cuadro de diálogo. Si este argumento se omite, el cuadro de texto permanecerá vacío. Este valor puede ser un objeto Range. Opcional. Especifica la posición X del cuadro de diálogo con respecto a la esquina superior izquierda de la pantalla, en puntos. Opcional. Especifica la posición Y del cuadro de diálogo con respecto a la esquina superior izquierda de la pantalla, en puntos. Opcional. El nombre del archivo de Ayuda para este cuadro de entrada. Opcional. La identificación de contexto del tema de Ayuda. Opcional. Especifica el tipo de datos que se devuelve. Si este argumento se omite, el cuadro de diálogo devolverá texto. Puede ser uno de los siguientes valores o bien una suma de ellos. Valor 0 1 2 4 8 16 64

Significado Una fórmula Un número Texto (una cadena) Un valor lógico (True o False) Una referencia a una celda, como un objeto Range. Un valor de error, como por ejemplo #N/A Una matriz de valores

Es posible usar una suma de valores permitidos para Tipo. Por ejemplo, para que un cuadro de entrada acepte texto o números, establezca Tipo como 1 + 2. Use InputBox para mostrar un cuadro de diálogo sencillo que permite introducir información para usarla en una macro. El cuadro de diálogo tiene un botón Aceptar y un botón Cancelar. Si elige el botón Aceptar, InputBox devolverá el valor introducido en el cuadro de diálogo. Si elige el botón Cancelar, InputBox devolverá False. Si Type es 0, InputBox devolverá la fórmula en forma de texto, por ejemplo, "=2*PI()/360". Si existen referencias en la fórmula, se devolverán como referencias de


Pag. 55

Manual de Microsoft Excel Programación VBA estilo A1. (Utilice ConvertFormula para realizar conversiones entre estilos de referencia). Si Type es 8, InputBox devolverá un objeto Range. Debe usar el enunciado Set para asignar el resultado a un objeto Range, como se muestra en el ejemplo siguiente: Set TABLA = Application.InputBox("Ejemplo", type := 8)

Si no usa el enunciado Set, se asignará a la variable el valor contenido en el rango y no el objeto Range mismo.

Conclusión: El método InputBox difiere de la función InputBox en que el primero permite la validación selectiva de las entradas del usuario y en que puede usarse con objetos, valores de error y fórmulas de Excel. Tenga en cuenta que Application.InputBox llama al método InputBox, y el dato que se escriba puede ser considerado de diferentes tipos (texto, numero, formula, etc); en cambio InputBox sin calificador de objeto llama a la función InputBox, y el dato escrito será considerado siempre como del tipo texto.

Ejemplos En este ejemplo se solicita un número al usuario. EDAD = Application.InputBox("Escriba su edad:")

En este otro ejemplo se solicita al usuario que seleccione una celda de la Hoja1. Se usa el argumento Type para asegurar que al hacer clic en una celda, se considere como que se hubiese escrito la coordenada de esta celda con el teclado. Worksheets("Hoja1").Activate Set SUELDO = Application.InputBox("Seleccione su sueldo", Type:=8)


Pag. 56


Ejercicio Nº 7 - Pago de un préstamo hipotecario En este ejemplo se usa la función de hoja de cálculo PAGO para calcular el pago mensual de un préstamo hipotecario. 1ra Solución: 1.- En la Hoja1 de un nuevo libro escribir los siguientes datos que se muestran en la imagen adjunta. 2.- Luego, en la celda B7 escribir la función PAGO siguiente: =PAGO(B3/1200,B4,-B5) Interés

Monto del préstamo

Número de pagos

3.- Guarde el archivo con el nombre Pago de Hipoteca. 2da Solución: Ahora, el mismo caso anterior se va ha resolver pero utilizando íntegramente un procedimiento Sub. Es decir no se necesitarán en absoluto datos de ninguna celda. 1.- Ingresar al Editor de Visual Basic con Alt+F11 y luego inserte una hoja de módulo con el menú Insertar/Modulo. 2.- En la hoja de módulo escriba las siguientes instrucciones: Sub PagoMensual() Interes = Val(InputBox("Ingrese el Interés:")) Periodos = Val(InputBox("Ingrese los Periodos:")) Prestamo = Val(InputBox("Ingrese el Préstamo:")) Pago = Application.WorksheetFunction.Pmt(Interes/1200, _ Periodos,-Prestamo) MsgBox ("El Pago mensual es de: " & Format(Pago, "Currency")) End Sub

Como la función Inputbox devuelve datos tipo texto al escribir el Interés, los Períodos o el Préstamo, entonces, se le antepone la función Val para convertir esos datos en números, y así poder operarlos matemáticamente con la función Pago (que en ingles su equivalente es Pmt) 3.- Con Autoformas/Formas básicas/Bisel dibuje un botón en la hoja de calculo y asígnele la macro PagoMensual.


Pag. 57

Manual de Microsoft Excel Programación VBA Cuando la macro se ejecute mostrará las siguientes ventanas en la pantalla, en las cuales en cada caso se deberá escribir los datos que se solicitan y hacer clic en el botón Aceptar:

4.- Guarde nuevamente el archivo con Ctrl+G. En conclusión, como puede verse, Excel puede resolver el mismo problema de dos formas distintas: 

En el primer caso, calcula el pago mensual de la hipoteca escribiendo y calculando los datos en el interior de las celdas de la hoja de cálculo.



En el segundo caso, realiza el mismo calculo pero ingresando y mostrando los resultados íntegramente en el ambiente de Visual Basic, sin utilizar en absoluto las celdas de la hoja de cálculo.

Habrán otros casos como hemos visto ya, en los cuales para resolver los problemas es necesario utilizar ambos ambientes en forma combinada, es decir; usar los datos y cálculos existentes en las celdas, y también los datos y cálculos provenientes de las instrucciones de Visual Basic. 3ra Solución: Una tercera forma de ingresar los datos para el calculo del pago de la hipoteca es haciendo uso del Método Inputbox, en vez de la Función Inputbox. 1.- Ingresar al Editor de Visual Basic con Alt+F11 y en la hoja de módulo escriba las siguientes instrucciones:


Pag. 58


Sub OtroPago() Interes = Application.InputBox("Seleccione el Interés:") Periodos = Application.InputBox("Seleccione los Periodos:") Prestamo = Application.InputBox("Seleccione el Préstamo:") Pago = Application.WorksheetFunction.Pmt(Interes / 1200, _ Periodos, -Prestamo) MsgBox ("El Pago mensual es de: " & Format(Pago, "Currency")) End Sub

En este caso el método Interés = Application.Inputbox trata al dato que uno escribe, directamente como del tipo numérico, ya no como texto. Por esta razón ya no es necesario usar la función Val. 2.- Luego asigne esta macro al mismo botón de autoformas del caso anterior. Cuando la macro se ejecute y aparezcan las ventanas para introducir datos, se podrán escribir los datos o sino también se podrá seleccionar con el mouse las celdas que contienen los datos que se solicitan y hacer luego clic en el botón Aceptar:


Pag. 59


23. Funciones propias de Visual Basic a. Lista breve de las funciones más comunes Val

Convierte los números contenidos en una cadena como un valor numérico del tipo adecuado. Por ejemplo, Val("15 años"), da como respuesta el número: 15

Str

Convierte en un tipo texto (String) la representación de cadena de un número. Por ejemplo, Str(15) & "años", da como respuesta el texto: 15 años

Left

Extrae un número especificado de caracteres del lado izquierdo de una cadena. Por ejemplo, Left("Limatambo",4), da como respuesta el texto: Lima.

Right

Extrae un número especificado de caracteres del lado derecho de una cadena. Por ejemplo, Rigth("Limatambo",5), da como respuesta el texto: tambo.

Mid

Extrae un numero especificado de caracteres de la parte interior de una cadena. Por ejemplo, Mid("Limatambo",3,4), da como respuesta el texto: mata.

Int, Fix

Las funciones Int y Fix eliminan la fracción de un número y devuelven el valor entero resultante. La diferencia entre Int y Fix es que si el número es negativo, Int devuelve el primer entero negativo menor o igual a número, mientras que Fix devuelve el primer entero negativo mayor o igual a número. Por ejemplo, Int(-8.4) da como respuesta: -9, Y en cambio Fix(-8.4) da como respuesta: -8.

Abs

Devuelve el valor absoluto de un número o expresión de cálculo Por ejemplo, Abs(3-7) da como respuesta: 4

Sgn

Devuelve el número 1,0,ó -1 que indica el signo de un número. Por ejemplo, Sgn(34) da como respuesta 1 Sgn(0) da como respuesta 0 Sgn(-23) da como respuesta -1

Len

Da como resultado la cantidad de caracteres que tiene una cadena de texto. Por ejemplo, Len("Limatambo") da como respuesta el número: 9

Round

Redondea un número a una cantidad especificada de lugares decimales.


Pag. 60

Manual de Microsoft Excel Programación VBA Por ejemplo, Round(3.1415927,4) da como respuesta el número: 3.1416 Date

Obtiene la fecha actual del sistema. Por ejemplo, Date da como respuesta: 11/05/2005

Timer

Obtiene el número de segundos transcurridos desde la medianoche.

Hour

Obtiene un número entero entre 0 y 23, inclusive, que representa la hora del argumento de la función. Por ejemplo, Hour(3:15:27) da como respuesta: 3

Now

Obtiene un número que especifica la fecha y hora actuales de acuerdo con la configuración de la fecha y la hora del sistema de su equipo. Por ejemplo, Now da como respuesta: 11/05/2005 3:15:27

Day

Obtiene un número entero entre 1 y 31, inclusive, que representa el día del mes. Por ejemplo, Day(Now) da como respuesta: 5

Minute

Obtiene un número entero entre 0 y 59, inclusive, que representa el minuto de la hora actual del sistema. Por ejemplo, Minute(Now) da como respuesta: 15

Second

Obtiene número entero entre 0 y 59, inclusive, que representa los segundos de la hora actual del sistema. Por ejemplo, Second(Now) da como respuesta: 27

Year

Obtiene un número entero que representa el año actual del sistema. Por ejemplo, Year(Now) da como respuesta: 2005

Format

formatea un dato de acuerdo a las instrucciones contenidas en una expresión de formato.

Por ejemplo: Pago = 3562.4 Z = Format(Pago, "Currency") Y = Format(Pago, "#,##0.00")

Entonces Z será igual a: S/. 3,562.40 En cambio Y será igual a: 3,562.40


Pag. 61


24. Estructuras de Control en las instrucciones macro f. Que son las sentencias de control Mediante el uso de instrucciones condicionales (como IF o Select), e instrucciones de bucle (como While, For, For each, o Do), también conocidas como estructuras de control, es posible escribir código de Visual Basic que tome decisiones y repita determinadas acciones. Otra estructura de control útil es la instrucción With, permite ejecutar una serie de instrucciones sin necesidad de recalificar un objeto. Entonces las sentencias de control pueden ser de varias tipos: Instrucciones Condicionales

 If…then Else  Select…Case Instrucciones de Bucle o de lazo repetitivo

 While…Wend  For…Next  Do…Loop  For…Each…Next Instrucciones de ejecución múltiple sobre un mismo objeto

 With

g. Que hacen las Instrucciones Condicionales 

If...Then...Else:

Salta a una u otra instrucción cuando una condición es verdadera o Falsa



Select Case:

Selecciona la instrucción a ejecutar en función de un conjunto de condiciones

h. Que hacen las Instrucciones de Bucle Empleando bucles es posible ejecutar un grupo de instrucciones de forma repetida. Algunos bucles repiten las instrucciones hasta que una condición es Falsa, otros las repiten hasta que la condición sea Verdadera. Hay también bucles que repiten un conjunto de instrucciones un número determinado de veces o una vez para cada objeto de una colección. 

Do...Loop:

Sigue en el bucle mientras o hasta que una cierta condición sea verdadera.


Pag. 62


For...Next:

Utiliza un contador para ejecutar un grupo de iinstrucciones un número determinado de veces.



For Each...Next:

Repite un grupo de instrucciones para cada uno de los objetos de una colección.

i. Que hacen las Instrucciones de ejecución múltiple Normalmente, en Visual Basic, debe especificarse un objeto antes de poder ejecutar uno de sus métodos o cambiar una de sus propiedades. Se puede usar la instrucción With para especificar un objeto una sola vez y poder aplicar sobre ella una serie completa de instrucciones. 

With:

Ejecuta una serie de instrucciones sobre un mismo objeto sin necesidad de recalificar dicho objeto

25. La Instrucción If...Then...Else Ejecuta condicionalmente un grupo de instrucciones, dependiendo del valor de una expresión. Sintaxis If condición Then instrucciones Else instrucciones_else Puede utilizar la siguiente sintaxis en formato de bloque: If condición Then Instrucciones… ElseIf condición-n Then instrucciones_elseif... Else instrucciones_else End If Donde: condición

Requerido. Uno o más de los siguientes dos tipos de expresiones: Una expresión numérica o expresión de cadena que puede ser evaluada como Verdadera o Falsa. Si condición es Null, condición se considera Falsa.

instrucciones

Opcional en formato de bloque; se requiere en formato de línea sencilla que no tenga una cláusula Else. Una o más instrucciones


Pag. 63

Manual de Microsoft Excel Programación VBA separadas por dos puntos ejecutados si la condición es Verdadera. condición-n

Opcional. Igual que condición.

instrucciones_elseif Opcional. Una o más instrucciones ejecutadas si la condición-n asociada es Verdadera. instrucciones_else

Opcional. Una o más instrucciones ejecutadas si ninguna de las expresiones anteriores condición o condición-n es Verdadera.

Puede utilizar la forma de una sola línea (Sintaxis 1) para pruebas cortas y sencillas. Sin embargo, el formato de bloque (Sintaxis 2) proporciona más estructura y flexibilidad que la forma de línea simple y, generalmente, es más fácil de leer, de mantener y de depurar. Nota.-

Con la sintaxis1 es posible ejecutar múltiples instrucciones como resultado de una decisión If...Then, pero todas deben estar en la misma línea y separadas por dos puntos, como en la instrucción siguiente:

If N > 1 Then A = N * 2 : B = B + N : C = C - N

Una instrucción con formato de bloque If debe ser la primera de la línea. Las partes Else, ElseIf y End If, de la instrucción, solamente pueden ir precedidas de un número de línea o una etiqueta de línea. El bloque If debe terminar con una instrucción End If. Para determinar si una instrucción If es un bloque, examine lo que sigue a la palabra clave Then. Si lo que aparece detrás de Then en la misma línea no es un comentario, la instrucción se considera como una instrucción If de una sola línea. Las cláusulas Else y ElseIf son opcionales. Puede tener en un bloque ElseIf, tantas cláusulas If como desee, pero ninguna puede aparecer después de una cláusula Else. Las instrucciones de bloque If se pueden anidar; es decir, unas pueden contener a otras. Cuando se ejecuta un bloque If (Sintaxis 2), se prueba condición. Si condición es Verdadera, se ejecutan las instrucciones que están a continuación de Then. Si condición es False, se evalúan una a una las condiciones ElseIf (si existen). Cuando se encuentra una condición Verdadera se ejecutan las instrucciones que siguen inmediatamente a la instrucción Then asociada. Si ninguna de las condiciones ElseIf es Verdadera (o si no hay cláusulas ElseIf), se ejecutan las instrucciones que siguen a Else. Después de la ejecución de las instrucciones que siguen a Then o Else, la ejecución continúa con la instrucción que sigue a End If.

a. Como usar las instrucciones If...Then...Else Se puede usar la instrucción If...Then...Else para ejecutar una instrucción o bloque de instrucciones determinadas, dependiendo del valor de una condición.


Pag. 64


b. Ejecutar una sola instrucción cuando la condición es Verdadera Para ejecutar una sola instrucción cuando una condición es Verdadera, se puede usar la sintaxis de línea única de la instrucción If...Then...Else. El siguiente ejemplo muestra la sintaxis de línea única, en la que se omite el uso de la palabra clave Else: (Nota: las fechas se escriben entre numerales #) Sub Cumpleaños() MiCumple = #23/8/2009# If MiCumple > Now Then msgbox "Aún falta para mi cumpleaños" End Sub

Para ejecutar más de una línea de código, es preciso utilizar la sintaxis de múltiples líneas. Esta sintaxis incluye la instrucción End If, tal y como muestra el siguiente ejemplo: Sub HombreMujer() If Range("Sexo") = "F" Then Range("Sexo").Font.ColorIndex = 3 Range("Sexo").Font.Bold = True Range("Sexo").Font.Italic = True End If End Sub

Letra Roja, Negrita y Cursiva

c. Ejecutar unas instrucciones si la condición es Verdadera y ejecutar otras si es Falsa Use una instrucción If...Then...Else para definir dos bloques de instrucciones ejecutables: un bloque que se ejecutará cuando la condición es Verdadera y el otro que se ejecutará si la condición es Falsa. Sub HombreMujer() If Range("Sexo") = "F" Then Range("Sexo").Font.ColorIndex = 3 Range("Sexo").Font.Bold = True Range("Sexo").Font.Italic = True Else Range("Sexo").Font.ColorIndex = 5 Range("Sexo").Font.Bold = False Range("Sexo").Font.Italic = False End If End Sub

Letra Roja, Negrita y Cursiva

Letra Azul, sin Negrita ni Cursiva

d. Comprobar una segunda condición si la primera condición es False Se pueden añadir instrucciones ElseIf a una instrucción If...Then...Else para comprobar una segunda condición si la primera es Falsa. Por ejemplo, el siguiente procedimiento función calcula una bonificación salarial dependiendo de la clasificación del


Pag. 65

Manual de Microsoft Excel Programación VBA trabajador. La instrucción que sigue a la instrucción Else sólo se ejecuta cuando las condiciones de todas las restantes instrucciones If y ElseIf son Falsas. Sub Remuneracion() Sueldo = Range("C4") If Sueldo < 1500 Then Aumento = Sueldo * 0.20 ElseIf Sueldo < 2000 Then Aumento = Sueldo * 0.15 ElseIf Sueldo < 3000 Then Aumento = Sueldo * 0.10 Else Aumento = Sueldo * 0.05 End If Range("C5") = Aumento End Sub

Las instrucciones If...Then...Else se pueden anidar en tantos niveles como sea necesario. Sin embargo, para hacer más legible el código quizás sea aconsejable utilizar una instrucción Select Case en vez de recurrir a múltiples niveles de instrucciones If...Then...Else anidadas.

26. La Instrucción While...Wend Ejecuta una serie de instrucciones mientras una condición dada sea True. Sintaxis While condición intrucciones Wend Donde: condición

Requerido. Expresión numérica o expresión de cadena cuyo valor es Verdadero o Falso. Si condición es Null, condición se considera Falso.

instrucciones

Opcional. Una o más instrucciones que se ejecutan mientras la condición es Verdadera.

Si condición es Verdadera, todas las instrucciones se ejecutan hasta que se encuentra la instrucción Wend. Después, el control vuelve a la instrucción While y se comprueba de nuevo la condición. Si la condición es aún Verdadera, se repite el proceso. Si no es Verdadera, la ejecución se reanuda con la instrucción que sigue a la instrucción Wend. Los bucles While...Wend se pueden anidar a cualquier nivel. Cada Wend coincide con el While más reciente. Nota.-

A diferencia de While...Wend, la instrucción Do...Loop proporciona una manera más flexible y estructurada de realizar los bucles.


Pag. 66


La siguiente macro busca a partir de la celda B3 hacia abajo una celda que se encuentre vacía. Sub BuscaBlanco() Range("B3").select While ActiveCell <> Empty ActiveCell.Offset(1,0).Activate Wend End Sub

27. La Instrucción For...Next Repite un grupo de instrucciones un número especifico de veces. Sintaxis For contador = principio To fin Step incremento instrucciones Exit For instrucciones Next contador Donde: contador

Requerido. Variable numérica que se utiliza como contador de bucle. La variable no puede ser Booleana (Verdadero o Falso) ni un elemento de matriz.

principio

Requerido. Valor inicial del contador.

fin

Requerido. Valor final del contador.

incremento

Opcional. Cantidad en la que cambia el contador cada vez que se ejecuta el bucle. Si no se especifica, el valor predeterminado de incremento es uno.

instrucciones

Opcional. Una o más instrucciones entre For y Next que se ejecutan un número especificado de veces.

El argumento incremento puede ser positivo o negativo. El valor del argumento incremento determina la manera en que se procesa el bucle, como se muestra a continuación: Valor

El bucle se ejecuta si

Positivo o 0

contador <= fin


Pag. 67


Negativo

contador >= fin

Una vez que se inicia el bucle y se han ejecutado todas las instrucciones en el bucle, incremento se suma a contador. En este punto, las instrucciones del bucle se pueden ejecutar de nuevo (si se cumple la misma prueba que causó que el bucle se ejecutara inicialmente) o bien se sale del bucle y la ejecución continúa con la instrucción que sigue a la instrucción Next. Sugerencia Cambiar el valor de contador mientras está dentro de un bucle hace difícil su lectura y depuración. Se pueden colocar en el bucle cualquier número de instrucciones Exit For como una manera alternativa de salir del mismo. La instrucción Exit For, que se utiliza a menudo en la evaluación de alguna condición (por ejemplo, If...Then), transfiere el control a la instrucción que sigue inmediatamente a la instrucción Next. Se pueden anidar bucles For...Next, colocando un bucle For...Next dentro de otro. Para ello, proporcione a cada bucle un nombre de variable único para su contador. La siguiente construcción es correcta: For Pais = 1 To 5 For Ciudad = 1 To 3 For Distrito = 1 To 10 ... ... Next Distrito Next Ciudad Next Pais

Nota-- Si omite mencionar un contador en una instrucción Next, la ejecución continúa como si este se hubiera incluido. En cambio si se produce un error si se encuentra una instrucción Next antes de su instrucción For correspondiente.

a. Como usar las instrucciones For...Next Las instrucciones For...Next se pueden utilizar para repetir un bloque de instrucciones un número determinado de veces. Los bucles For usan una variable contador cuyo valor se aumenta o disminuye cada vez que se ejecuta el bucle. El siguiente procedimiento hace que el equipo emita un sonido 50 veces. La instrucción For determina la variable contador x y sus valores inicial y final. La instrucción Next incrementa el valor de la variable contador en 1. Sub Bips() For x = 1 To 50 Beep Next x End Sub

Mediante la palabra clave Step, se puede aumentar o disminuir la variable contador en el valor que se desee. En el siguiente ejemplo TotalPares, la variable contador N se


Pag. 68

Manual de Microsoft Excel Programación VBA incrementa en 2 cada vez que se repite la ejecución del bucle. Cuando el bucle deja de ejecutarse, Total representa la suma de 2, 4, 6, 8 y 10. Sub TotalPares() For Num = 2 To 10 Step 2 Total = Total + Num Next Num MsgBox "El total es " & Total End Sub

Para disminuir la variable contador utilice un valor negativo en Step. Para disminuir la variable contador es preciso especificar un valor final que sea menor que el valor inicial. En el siguiente ejemplo OtroTotal, la variable contador Cant se disminuye en 2 cada vez que se repite el bucle. Cuando termina la ejecución del bucle, Total representa la suma de 16, 14, 12, 10, 8, 6, 4 y 2. Sub OtroTotal() For Cant = 16 To 2 Step -2 Total = Total + Cant Next Cant MsgBox "El total es " & total End Sub

Nota.-

En realidad no es necesario incluir el nombre de la variable contador después de la instrucción Next. En los ejemplos anteriores, el nombre de la variable contador se ha incluido solo para facilitar la lectura del código y poder identificar así donde empieza el bucle For y donde termina. Esto de usar nombre del contador después de la instrucción Next es de mayor utilidad cuando existen varias instrucciones For…Next anidadas.

Se puede abandonar una instrucción For...Next antes de que el contador alcance su valor final, para ello se utiliza la instrucción Exit For. Por ejemplo, si se produce un error se puede usar la instrucción Exit For en el bloque de instrucciones Verdadero de una instrucción If...Then...Else o Select Case que detecte específicamente ese error. Si el error no se produce, la instrucción If…Then…Else es False y el bucle continuará ejecutándose normalmente.

28. La Instrucción Select Case Ejecuta uno de varios grupos de instrucciones, dependiendo del valor de una expresión. Sintaxis Select Case expresión_prueba Case lista_expresion-n instrucciones-n... Case Else instrucciones_else End Select


Pag. 69

Manual de Microsoft Excel Programación VBA Donde: expresión_prueba

Requerido. Es cualquier expresión numérica o expresión de cadena.

lista_expresión-n

Requerido si aparece la palabra clave Case. Lista delimitada por comas de una o más de las formas siguientes: expresión, expresión To expresión, Is operador_de_comparación expresión. La palabra clave especifica un intervalo de valores. Si se utiliza la palabra clave To, el valor menor debe aparecer antes de To. Utilice la palabra clave Is con operadores de comparación para especificar un intervalo de valores. Si no se escribe, la palabra clave Is se insertará automáticamente.

instrucciones-n

Opcional. Una expresión_prueba lista_expresión-n.

o más coincide

instrucciones ejecutadas con cualquier parte

si de

instrucciones_else

Opcional. Una o más instrucciones que se ejecuten si expresión_prueba no coincide con ninguna de las cláusulas Case.

Si expresión_prueba coincide con cualquier lista_expresión asociada con una cláusula Case, las instrucciones que siguen a esa cláusula Case se ejecutan hasta la siguiente cláusula Case o, para la última cláusula, hasta la instrucción End Select. El control pasa después a la instrucción que sigue a End Select. Si expresión_prueba coincide con una expresión de lista_expresión en más de una cláusula Case, sólo se ejecutan las instrucciones que siguen a la primera coincidencia. La cláusula Case Else se utiliza para indicar las instrucciones que se van a ejecutar si no se encuentran coincidencias entre expresión_prueba y una lista_expresión en cualquiera de las otras selecciones de Case. Aunque no es necesario, es buena idea tener una instrucción Case Else en el bloque Select Case para controlar valores imprevistos de expresión_prueba. Cuando no hay una instrucción Case Else y ninguna expresión de la lista en las cláusulas Case coincide con la expresión de prueba, la ejecución continúa en la instrucción que sigue a End Select. Se pueden utilizar expresiones múltiples o intervalos en cada cláusula Case. Por ejemplo, la línea siguiente es válida:

Case 1 To 4, 7 To 9, 11, 17, Is > Grande

Nota.-

El operador de comparación Is no es lo mismo que la palabra clave Is utilizada en la instrucción Select Case.


Pag. 70

Manual de Microsoft Excel Programación VBA También puede especificar intervalos y expresiones múltiples para cadenas de caracteres. En el siguiente ejemplo, Case coincide con las cadenas que son exactamente carnes, cadenas que están entre nueces y sopa en orden alfabético y el valor actual de la variable Fruta

Case "carnes", "nueces" To "sopa", Fruta

Las instrucciones Select Case se pueden anidar. Cada instrucción Select Case debe tener su correspondiente instrucción End Select.

a. Como usar la instruccion Select Case La instrucción Select Case se utiliza como alternativa a las instrucciones ElseIf en instrucciones If...Then...Else cuando se compara una expresión con varios valores diferentes. Mientras que las instrucciones If...Then...Else pueden comparar una expresión distinta para cada instrucción ElseIf, la instrucción Select Case compara únicamente la expresión que evalúa al comienzo de la estructura de control. En el siguiente ejemplo, la instrucción Select Case evalúa el argumento rendimiento que se pasa al procedimiento. Observe que cada instrucción Case puede contener más de un valor, una gama de valores, o una combinación de valores y operadores de comparación. La instrucción opcional Case Else se ejecuta si la instrucción Select Case no encuentra ninguna igualdad con los valores de la instrucciones Case. Sub Pago() Sueldo = Range("C4") Select Case Sueldo Case Is <1500 Aumento = Sueldo Case Is <2000 Aumento = Sueldo Case Is <3000 Aumento = Sueldo Case 3000 To 4000 Aumento = Sueldo Case Else Aumento = 0 End Select Range("C5") = Aumento End Sub

* 0.20 * 0.15 * 0.10 * 0.05

29. La Instrucción Do...Loop Repite un bloque de instrucciones cuando una condición es Verdadera o hasta que una condición se convierta en Falsa. Sintaxis


Pag. 71

Manual de Microsoft Excel Programación VBA Do While | Until condición instrucciones Exit Do instrucciones Loop O bien, puede utilizar esta sintaxis: Do instrucciones Exit Do instrucciones Loop While | Until condición Donde: condición

Opcional. Expresión numérica o expresión de cadena que es Verdadera o Falsa. Si la condición es Null, la condición se considera Falsa.

Instrucciones

Una o más instrucciones que se repiten mientras (While) o hasta que (Until) la condición sea Verdadera.

Se puede utilizar cualquier número de instrucciones Exit Do ubicadas en cualquier lugar dentro de una estructura de control Do…Loop, para proporcionar una salida alternativa de un Do…Loop. La instrucción Exit Do se utiliza frecuentemente en la evaluación de alguna condición, por ejemplo, If…Then; en este caso, la instrucción Exit Do transfiere el control a la instrucción que sigue inmediatamente a la instrucción Loop. Cuando se utiliza con instrucciones anidadas Do…Loop, la instrucción Exit Do transfiere control al bucle que está anidado un nivel por encima del bucle donde ocurre.

a. Como usar las instrucciones Do...Loop Se pueden usar instrucciones Do...Loop para ejecutar un bloque de instrucciones un número indefinido de veces. Las instrucciones se repiten mientras (While) una condición sea Verdadera o hasta (Until) que llegue a ser Verdadera.

b. Repetir instrucciones mientras una condición es Verdadera Hay dos formas de utilizar la palabra clave While (Mientras) para comprobar el estado de una condición en una instrucción Do...Loop. Se puede comprobar la condición antes de entrar en el bucle, o después de que el bucle se haya ejecutado al menos una vez. En el siguiente procedimiento WhileInicial, la condición se comprueba antes de entrar en el bucle. Mientras la Nota es menor a que 10, aumentar la Nota en 1.Si Nota es


Pag. 72

Manual de Microsoft Excel Programación VBA mayor que 10, las instrucciones contenidas en el bucle no se ejecutarán nunca. En el procedimiento WhileFinal, las instrucciones contenidas en el bucle al menos se ejecutarán una vez y aumentarán la Nota en 1, antes de comprobarse que la condición es Falsa. Sub WhileInicial() Contador = 0 Nota = Range("D4") Do While Nota < 10 Nota = Nota + 1 Contador = contador + 1 Loop MsgBox "El bucle se ha repetido " & Contador & " veces." End Sub Sub WhileFinal() Contador = 0 Nota = Range("D4") Do Nota = Nota + 1 Contador = contador + 1 Loop While Nota < 10 MsgBox "El bucle se ha repetido " & Contador & " veces." End Sub

c. Para repetir instrucciones hasta que una condición llegue a ser Verdadera Hay dos formas de utilizar la palabra clave Until (Hasta que) para comprobar el estado de una condición en una instrucción Do...Loop. Se puede comprobar la condición antes de entrar en el bucle (como muestra el procedimiento UntilInicial) o se pueden comprobar después de que el bucle se haya ejecutado al menos una vez (como muestra el procedimiento UntilFinal). El bucle seguirá ejecutándose mientras la condición siga siendo Falsa. Sub UntilInicial() Contador = 0 Nota = Range("D4") Do Until Nota = 10 Nota = Nota + 1 Contador = Contador + 1 Loop MsgBox "El bucle se ha repetido " & Contador & " veces." End Sub Sub UntilFinal() Contador = 0 Nota = Range("D4") Do Nota = Nota + 1 Contador = Contador + 1 Loop Until Nota = 10 MsgBox "El bucle se ha repetido " & Contador & " veces." End Sub


Pag. 73


d. Para salir de Do...Loop desde dentro del bucle Es posible salir de Do...Loop usando la instrucción Exit Do. Por ejemplo, para salir de un bucle sin fin, se puede usar la instrucción Exit Do en el bloque de instrucciones True de una instrucción If...Then...Else o Select Case. Si la condición es False, el bucle seguirá ejecutándose normalmente. En el siguiente ejemplo, si se asigna a Edad un valor que crea un bucle sin fin, por ejemplo si la Edad escrita en D4 es menor que 25. La instrucción If...Then...Else comprueba esa condición y ejecuta entonces la salida, evitando así el bucle sin fin. Sub EjemploSalida() Contador = 0 Edad = Range("E4") Do Until Edad = 25 Edad = Edad - 1 Contador = contador + 1 If Edad < 25 Then Exit Do Loop MsgBox "El bucle se ha repetido " & Contador & " veces." End Sub

Nota.-

Para detener manualmente la ejecución de un bucle sin fin que se repite sin control, presione la tecla Esc o la combinación de teclas Ctrl+Pausa.

30. La instrucción For Each...Next Repite un grupo de instrucciones para cada elemento de una matriz o colección. Sintaxis For Each elemento In grupo instrucciones Exit For instrucciones Next elemento Donde: elemento

Requerido. Variable que se utiliza para iterar por los elementos del conjunto o matriz. Para conjuntos, elemento solamente puede ser una variable del tipo Variant, una variable de objeto genérica o cualquier variable de objeto específica.

grupo

Requerido. Nombre de un conjunto de objetos o de una matriz .

instrucciones

Opcional. Una o más instrucciones que se ejecutan para cada elemento de un grupo.


Pag. 74

Manual de Microsoft Excel Programación VBA La entrada al bloque For Each se produce si hay al menos un elemento en grupo. Una vez que se ha entrado en el bucle, todas las instrucciones en el bucle se ejecutan para el primer elemento en grupo. Después, mientras haya más elementos en grupo, las instrucciones en el bucle continúan ejecutándose para cada elemento. Cuando no hay más elementos en el grupo, se sale del bucle y la ejecución continúa con la instrucción que sigue a la instrucción Next. Se pueden colocar en el bucle cualquier número de instrucciones Exit For. La instrucción Exit For se utiliza a menudo en la evaluación de alguna condición (por ejemplo, If...Then) y transfiere el control a la instrucción que sigue inmediatamente a la instrucción Next. Puede anidar bucles For Each...Next, colocando un bucle For Each...Next dentro de otro. Sin embargo, cada elemento del bucle debe ser único. Nota.-

Si se omite el nombre del elemento en una instrucción Next, la ejecución continúa como si se hubiera incluido. En cambio si se encuentra una instrucción Next antes de su instrucción For correspondiente, se producirá un error.

a. Como usar las instrucciones For Each...Next Las instrucciones For Each...Next repiten un bloque de instrucciones para cada uno de los objetos de una colección o para cada elemento de una matriz. Visual Basic asigna valor automáticamente a una variable cada vez que se ejecuta el bucle.

b. Para recorrer un conjunto de elementos Se puede usar el bucle For Each...Next para recorrer las celdas pertenecientes a un rango determinado. El siguiente ejemplo borra todas las celdas cuyo valor es cero dentro del rango previamente seleccionado. (La palabra Celda es el nombre que representa a cada elemento del rango. Puede usarse cualquier otro nombre si se desea.) Sub BorraCeros() For Each Celda In Selection If Celda=0 Then Celda.ClearContents Next End Sub

El siguiente ejemplo vuelve negrita todas aquellas celdas dentro del rango C4:C20 que sean mayores que 10. Sub Negrita() For Each Casilla In Range("C4:C20") If Casilla > 10 Then Casilla.Font.Bold = True Next End Sub

El código siguiente recorre todas las celdas de un rango, asignado a la variable Cuadro con la instrucción Set, e introduce el valor de la variable P en aquellas celdas cuyos contenidos son menores o iguales a 10.


Pag. 75

Manual de Microsoft Excel Programación VBA Sub Escribe() P = Range("B1") Set Cuadro = Range("C4:C20") For Each Cel In Cuadro If Cel <= 10 Then Cel.Value = P Next End Sub

El siguiente ejemplo recorre las celdas del rango B3:B15 y convierte cualquier valor absoluto menor de 0.01 en 0 (cero). Sub ValeCero() Set Lecturas = Range("B3:B15") For Each Dato in Lecturas If Abs(Dato.Value) < 0.01 Then Dato.Value = 0 Next End Sub

El siguiente procedimiento cierra todos los formularios excepto el que contiene al procedimiento que se está ejecutando. Sub CierraFormularios() For Each Ficha In Application.Forms If Ficha.Caption <> Screen.ActiveForm.Caption Then Ficha.Close Next End Sub

Este otro ejemplo agrega tantas hojas nuevas al libro activo como textos hay en el rango B1:B5, y a continuación nombra a estas hojas con los textos que va encontrando. Sub CreaHojas() For Each Nombre In Range("B1:B5") If Nombre <> Empty Then Set NewSheet = Worksheets.Add NewSheet.Name = Nombre End If Next End Sub


Pag. 76

Manual de Microsoft Excel Programación VBA Este último ejemplo muestra el nombre de cada una de las hojas de cálculo del libro activo. Sub VerHojas() For Each Hoja In Worksheets MsgBox Hoja.Name Next End Sub

c. Para salir de un bucle For Each...Next antes de que finalice Se puede salir de un bucle For Each...Next mediante la instrucción Exit For. El siguiente ejemplo detecta la primera celda del rango C3:C30 que no contiene un número. Si se encuentra una celda en esas condiciones, se selecciona dicha celda y se presenta un mensaje en pantalla, luego Exit For abandona el bucle.

Sub EncuentraTexto() For Each Celda In Range("C3:C30") If Not IsNumeric(Celda) Then Celda.Select MsgBox "El objeto contiene un valor no numérico." Exit For End If Next End Sub

31. La instrucción With Ejecuta una serie de instrucciones sobre un único objeto. Sintaxis With objeto instrucciones End With Donde: objeto

Requerido. Nombre de un objeto.

instrucciones

Opcional. Una o más instrucciones que se van a ejecutar sobre el objeto.

La instrucción With permite realizar una serie de instrucciones sobre un objeto especificado sin volver a calificar el nombre del objeto. Por ejemplo, para cambiar un número de propiedades diferentes en un único objeto, es más conveniente colocar las instrucciones de asignación de las propiedades en la estructura de control With,


Pag. 77

Manual de Microsoft Excel Programación VBA haciendo referencia al objeto una vez, en lugar de hacerlo con cada asignación de propiedad.

a. Como usar las instrucciones With La instrucción With permite especificar una vez un objeto o tipo definido por el usuario en una serie entera de instrucciones. Las instrucciones With aceleran la ejecución de los procedimientos y ayudan a evitar el tener que escribir repetidas veces las mismas palabras. El siguiente ejemplo introduce en un rango de celdas el número 30, aplica a esas celdas un formato en negrita y hace que su color de fondo sea el amarillo. Si se deseara seleccionar la celda A1 de la Hoja2 y luego modificar en ella lo siguiente; Ancho de columna = 50 Altura de fila = 20 Letra negrita Tamaño de fuente 20 puntos Color de fuente Roja Centrado en la celda Podríamos escribir las siguientes instrucciones: Sub Titulo() Worksheets("Hoja2").Range("A1").Select Selection.ColumnWidth = 50 Selection.RowHeight = 20 Selection.Font.Bold = True Selection.Size = 20 Selection.Font.ColorIndex = 3 Selection.HorizontalAlignment = xlCenter End Sub

El siguiente ejemplo realiza lo mismo que el caso anterior e ilustra el uso de la instrucción With para asignar valores a varias propiedades del mismo objeto. Sub Titulo() Range("A1").Select With Selection .ColumnWidth = 50 .RowHeight = 20 .Font.Bold = True .Size = 20 .Font.ColorIndex = 3 .HorizontalAlignment = xlCenter End With End Sub

Nota Una vez que se ha entrado en un bloque With no es posible cambiar el objeto. Por tanto, no puede utilizar una única instrucción With para varios objetos.


Pag. 78


Nota En general, no es aconsejable que salte hacia dentro o hacia fuera de bloques With. Si se ejecutan instrucciones en bloques With, sin que se ejecuten las instrucciones With o End With conserva en memoria una variable temporal que contiene una referencia al objeto hasta que se salga del procedimiento.

Aquí otro ejemplo del uso de With: Sub RangoFormato() With Worksheets("Clientes").Range("A1:C10") .Value = 30 .Font.Bold = True .Interior.Color = RGB(255, 255, 0) End With End Sub

Las instrucciones With se pueden anidar para aumentar su eficiencia. El siguiente ejemplo inserta una formula en la celda C4 que obtiene la raíz cuadrada de 200 y selecciona a continuación el tipo de letra. Luego modifica el ancho de la columna. (Note que se especifica que C4 se encuentra dentro de la hoja Clientes del archivo Pagos.xls. De no hacerse así la macro supondría que se trata de la celda C4 de la hoja y libro activos en ese instante.) Sub RaizCuadrada() With Workbooks("Pagos.xls").Worksheets("Clientes").Range("C4") .Formula = "=SQRT(200)" With .Font .Name = "Arial" .Bold = True .Size = 8 End With .ColumnWidth = 20 End With End Sub


Pag. 79


32. Creación de formularios userforms a. Para crear un formulario Al igual como se inserta una hoja de módulo para escribir en ella las macros, tambien se puede insertar una hoja de formulario para disenar en ella las ventanas de formulario. Solo tiene que seguir las siguientes instruciones: 1. Ingresar al Editor de Visual Basic y elegir el menú Insertar/Userform. Se creará un formulario llamado UserForm1, tal como se muestra en la figura:

Escriba el Nombre del formulario

Escriba el Título del formulario

2. En la ventana Propiedades, escribir en la propiedad (Name): Alumnos, y en la propiedad Caption escriba: Ficha del Alumno.

b. Para diseñar un formulario Una vez creado el formulario lo siguiente es diseñar los elementos dentro del formulario. Para ello es necesario habilitar el cuadro de herramientas, para lo cual deberá hacer clic en el boton


Pag. 80


El cuadro de herramientas contiene una serie de controles de, cada uno de los cuales cumplen una función especial una vez que se insertan dentro del formulario.

Controles para formularios del cuadro de herramientas:

Seleccionar objetos

Cuadro de texto Cuadro de lista

Etiqueta

Página múltiple

B.de opción B.de alternar

Cuadro combinado

Cuadro de texto

Marco

Barra de tabulaciones

B.de comando

Imagen Botón de número Barra de desplazamiento

Como agregar un control al formulario Utilice cualquiera de los siguientes métodos para agregar un control del Cuadro de herramientas al formulario. 

Haga clic en un control en el Cuadro de herramientas y después haga clic en el formulario. El control aparecerá en su tamaño predeterminado. Luego puede arrastrar el control para cambiar su tamaño.



Arrastre un control del Cuadro de herramientas al formulario. El control aparecerá en su tamaño predeterminado.



Haga doble clic en el control del Cuadro de herramientas y después haga clic en el formulario una vez por cada control que desee crear. Por ejemplo, para crear cuatro botones de comando, haga doble clic en el control CommandButton del Cuadro de herramientas y después haga clic cuatro veces en el formulario.

Ejercicio Nº 8 - Llenar un Registro de alumnos


Pag. 81

Manual de Microsoft Excel Programación VBA En este ejemplo se usará un formulario para ingresar datos de varios alumnos en una lista de Excel. 1. En una hoja de Excel escriba el siguiente Registro de alumnos:

2. Seleccionar el rango A3:F6 y presionar Ctrl+Q para convertir el rango en tabla. 3. Seleccionar el rango A3:F6 y elegir la Ficha Fórmulas/Crear desde la selección, marque solo la casilla Fila superior, y Aceptar. 4. Con la Ficha Insertar/Formas/Rectángulo, dibuje el botón “Abrir Ficha”. 5. Al rango A3:F6 dele el nombre LISTA. 6. Ingrese al Editor de Visual Basic, e inserte una hoja de Módulo (menú Insertar/Módulo), y un Formulario (menú Insertar/UserForm). 7. En la hoja de Módulo escriba la siguiente subrutina: Sub FichaAlumno() Alumnos.Show End Sub

8. En la ventana de formulario diseñe el siguiente formulario:

9. Seleccione el formulario y dele las siguientes propiedades: 

(Name)


: Alumnos

Pag. 82


Caption

: Ficha del Alumno

10. Luego defina las siguientes propiedades de los controles en el formulario: Cuadro combinado: 

(Name)

: Codigo



RowSource

: Codigo



Tab index

:1

Cuadro de texto1: 

(Name)

: Nombre



Tab index

:2


(Name)

: Apellidos



Tab index

:3


(Name)

: Edad



Tab index

:4


(Name)

: Telefono



Tab index

:5

Botón de opción1: 

(Name)

: Masculino

Botón de opción2: 

(Name)

: Femenino

Image1: 

(Name)

: Foto



PictureSizeMode

: 1 - fmPictureSizeModeStretch

CommandButton1: 

(Name)

: Registrar

CommandButton2: 

(Name)

: Cerrar

CommandButton3: 

(Name)

: Nuevo

Luego, haciendo doble clic sobre los siguientes controles escriba para ellos el siguiente código: Para el botón Cerrar: Private Sub Cerrar_Click()


Pag. 83

Manual de Microsoft Excel Programación VBA Unload Me End Sub

Para el cuadro combinado Codigo: Private Sub Codigo_Change() Ruta = ActiveWorkbook.Path Codigo = UCase(Codigo) Nombre = "" Apellido = "" Edad = "" Telefono = "" Masculino = False Femenino = False If Len(Codigo) = 4 Then On Error GoTo SinFoto Nombre = Application.WorksheetFunction.VLookup(Codigo, _ Range("Lista"), 2, 0) Apellido = Application.WorksheetFunction.VLookup(Codigo, _ Range("Lista"), 3, 0) Edad = Application.WorksheetFunction.VLookup(Codigo, _ Range("Lista"), 4, 0) Sexo = Application.WorksheetFunction.VLookup(Codigo, _ Range("Lista"), 5, 0) If Sexo = "M" Then Masculino = True If Sexo = "F" Then Femenino = True Telefono = Application.WorksheetFunction.VLookup(Codigo, _ Range("Lista"), 6, 0) Foto.Visible = True Foto.Picture = LoadPicture(Ruta & "\" & Codigo & ".jpg") Else Foto.Visible = False End If Exit Sub SinFoto: Foto.Visible = False End Sub

Para el botón Nuevo (New): Private Sub Nuevo_Click() Range("Codigo").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select Wend ActiveCell.Offset(-1, 0).Select Codigo = "A" & Right("00" & Val(Right(ActiveCell, 3)) + 1, 3) Nombre.SetFocus End Sub

Para el botón Registrar: Private Sub Registrar_Click() If Codigo = "" Then Codigo.SetFocus: Exit Sub If Nombre = "" Then Nombre.SetFocus: Exit Sub Range("Codigo").Select


Pag. 84

Manual de Microsoft Excel Programación VBA ActiveCell.Select While ActiveCell <> Empty If ActiveCell = Codigo Then Rpta = MsgBox("Alumno ya existe desea reemplazarlo?", _ vbYesNo) If Rpta = vbYes Then GoTo Sigue Exit Sub End If ActiveCell.Offset(1, 0).Select Wend Sigue: If Masculino Then Sexo = "M" If Femenino Then Sexo = "F" ActiveCell = Codigo ActiveCell.Offset(0, 1) = Nombre ActiveCell.Offset(0, 2) = Apellido ActiveCell.Offset(0, 3) = Val(Edad) ActiveCell.Offset(0, 4) = Sexo ActiveCell.Offset(0, 5) = Telefono MsgBox ("Alumno registrado con exito") Range("Codigo").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select Wend ActiveCell.Offset(-1, 0).Select Codigo = "A" & Right("00" & Val(Right(ActiveCell, 3)) + 1, 3) Nombre = "" Apellido = "" Edad = "" Telefono = "" Masculino = False Femenino = False Nombre.SetFocus End Sub

Para el Formulario: Private Sub UserForm_Initialize() Range("Codigo").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select Wend ActiveCell.Offset(-1, 0).Select Codigo = "A" & Right("00" & Val(Right(ActiveCell, 3)) + 1, 3) Nombre.SetFocus Foto.Visible = False End Sub

11. Luego regrese a la hoja de Excel y asigne la macro FichaAlumno al botón “Abrir Ficha”. 12. Finalmente guarde el archivo como un libro habilitado para macros y póngale el nombre Registro de alumnos.


Pag. 85

Manual de Microsoft Excel Programación VBA 13. En la misma carpeta donde guarde el anterior archivo de Excel, deberá guardar también los archivos con formato jpg que contengan las fotos de los alumnos, los cuales deberán tener nombres como: A001.jpg, A002.jpg, … etc.

33. Objetos, Colecciones, Métodos, Propiedades, y Eventos Un Objeto representa un elemento de una aplicación, como una hoja de cálculo, una celda, una columna, un grafico, un formulario o un dibujo. En código de Visual Basic, un objeto debe identificarse antes de que se pueda aplicar uno de los métodos del objeto o cambiar el valor de una de sus propiedades. Una Colección es un objeto que contiene varios objetos que normalmente, pero no siempre, son del mismo tipo. En Microsoft Excel, por ejemplo, el objeto Worksheets contiene todas las hojas Worksheet de un libro. En Visual Basic, la colección Forms contiene todos los objetos Form existentes en la aplicación Excel. Los elementos de una colección se pueden identificar mediante su número o su nombre. Por ejemplo, Woksheets(1) identifica al primer objeto Worksheet de un libro. El siguiente procedimiento ocultado la Hoja1 del libro actual. Sub OcultaHoja() Worksheets(1).Visible = False End Sub

El siguiente procedimiento utiliza un nombre especificado como cadena de texto para identificar un objeto Form. Sub CierraFormulario() Forms("MiForm.frm").Close End Sub

También es posible operar al mismo tiempo sobre toda una colección de objetos siempre que los objetos compartan métodos comunes. Por ejemplo, el siguiente procedimiento cierra todos los formularios abiertos. Sub CierraTodosLosFormularios() Forms.Close End Sub

Método es toda acción que puede realizar un objeto. Por ejemplo, Add es un método del objeto ComboBox ya que sirve para añadir un nuevo elemento a un cuadro combinado. El siguiente procedimiento utiliza el método Add para añadir dos elementos a un ComboBox. Sub AñadeElementos() Combo1.AddItem "Masculino" Combo1.AddItem "Femenino" End Sub


Pag. 86

Manual de Microsoft Excel Programación VBA Propiedad es uno de los atributos de un objeto, que define una de las características del objeto, tal como su tamaño, color o localización en la pantalla, o un aspecto de su comportamiento, por ejemplo si está visible o activado. Para cambiar las características de un objeto, se cambia el valor de sus propiedades. Para dar valor a una propiedad, hay que colocar un punto después de la referencia a un objeto, después el nombre de la propiedad y finalmente el signo igual (=) y el nuevo valor de la propiedad. Por ejemplo, el siguiente procedimiento cambia el título de un formulario de Visual Basic dando un valor a la propiedad Caption. Sub CambiaTitulo() miFormulario.Caption = "Datos del Cliente" End Sub

Hay propiedades a las que no se puede dar valor. El tema de ayuda de cada propiedad indica si es posible leer y dar valores a la propiedad (lectura/escritura), leer sólo el valor de la propiedad (sólo lectura) o sólo dar valor a la propiedad (sólo escritura). También se puede obtener información sobre un objeto devolviendo (extrayendo) el valor de una de sus propiedades. El siguiente procedimiento utiliza un cuadro de diálogo para presentar el título que aparece en la parte superior del formulario activo en ese momento. Sub MuestraTitulo() Titulo = Screen.ActiveForm.Caption MsgBox Titulo End Sub

Evento es toda acción que puede ser reconocida por un objeto, como puede ser el clic del mouse o la pulsación de una tecla y para la que es posible escribir código como respuesta. Los eventos pueden ocurrir como resultado de una acción del usuario o del código del programa, también pueden ser originados por el sistema. El siguiente procedimiento escrito en el código de la Hoja1, establece el zoom al 75% y selecciona la celda A1. Esto ocurrirá cuando se produzca el evento de activar la Hoja1. Private Sub Worksheet_Activate() ActiveWindow.Zoom = 75 Range("A1").select End Sub


Pag. 87


34. Declarar constantes Al declarar una constante, se puede asignar a un valor un nombre que tenga algún significado apropiado. La instrucción Const se utiliza para declarar una constante y darle valor. Una constante no puede modificarse o cambiar de valor una vez que ha sido declarada. Se puede declarar una constante dentro de un procedimiento o al principio de un módulo, en la sección de Declarations. Las constantes a nivel de módulo son privadas, a menos que se especifique lo contrario. Para declarar una constante pública a nivel de módulo, la instrucción Const debe ir precedida por la palabra clave Public. Se puede declarar explícitamente una constante como privada colocando la palabra clave Private antes de la instrucción Const para facilitar la lectura y comprensión del código. Si desea más información, consulte la sección "Comprender el alcance y la visibilidad" en la Ayuda de Visual Basic. El siguiente ejemplo declara la constante Public EdadCon como un Integer y le asigna el valor 34. Public Const EdadCon As Integer = 34

Las constantes se pueden declarar de uno de los siguientes tipos de datos: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, o Variant. Dado que ya se conoce el valor de una constante, es muy fácil elegir el tipo de datos en la instrucción Const. Si desea más información sobre tipos de datos, consulte la sección "Tipo de datos Summary" en la Ayuda de Visual Basic. En una sola instrucción se pueden declarar varias constantes. Para especificar un tipo de datos, debe incluirse el tipo de datos para cada constante. En la siguiente instrucción se declaran como Integer y Currency a las constantes EdadCon y SalarioCon. Const EdadCon As Integer = 34, SalarioCon As Currency = 35000

Comprender la sintaxis de Visual Basic En la sección de Ayuda de Visual Basic correspondiente a un método, instrucción o procedimiento Function la sintaxis muestra todos los elementos necesarios para utilizar correctamente ese método, instrucción o función. Los ejemplos siguientes explican cómo deben interpretarse los elementos sintácticos más comunes. Sintaxis del método Activate objeto.Activate En la sintaxis del método Activate, la palabra "objeto" en cursiva es la posición reservada para la información que introduce el usuario — en este caso, código que devuelve un objeto. Las palabras que se muestran en negrita deben escribirse exactamente tal y como se indica. Por ejemplo, el siguiente procedimiento activa la segunda ventana en el documento activo. Sub Activar() Windows(2).Activate End Sub


Pag. 88

Manual de Microsoft Excel Programación VBA Sintaxis de la función MsgBox MsgBox(texto[, botones] [, título] [, archivoayuda, contexto]) En la sintaxis de la función MsgBox, las palabras en cursiva y negrita son argumentos con nombre de la función. Los argumentos que aparecen entre corchetes son opcionales. (No escriba los corchetes en el código de Visual Basic). En el caso de la función MsgBox, el único argumento que se debe incluir es el texto de la pregunta. Los argumentos para funciones y métodos se pueden especificar en el código mediante su posición o por su nombre. Para especificar argumentos mediante su posición, siga el orden que se indica en la sintaxis, separando los argumentos con una coma, por ejemplo: MsgBox "¡Su respuesta es correcta!",0,"Cuadro de Respuesta"

Para especificar un argumento mediante su nombre basta con usar el nombre del argumento seguido de dos puntos y un signo igual (:=) y el valor del argumento. Los argumentos con nombre se pueden especificar en cualquier orden, por ejemplo: MsgBox Title:="Cuadro de Respuesta", Prompt:="¡Su respuesta es correcta!"

La sintaxis de las funciones y algunos métodos muestran los argumentos entre paréntesis. Estas funciones y métodos devuelven valores, por eso deben encerrarse los argumentos entre paréntesis al asignar un valor a una variable. Si se ignora el valor de retorno o si no se pasan argumentos en forma alguna, no deben incluirse los paréntesis. Los métodos que no devuelven valores no necesitan que sus argumentos aparezcan encerrados entre paréntesis. Estas normas son aplicables tanto si se usan argumentos posicionales o nominativos. En el siguiente ejemplo, el valor que devuelve la función MsgBox es un número que indica el botón seleccionado almacenado en la variable miVar. Dado que se utiliza el valor que devuelve la función, es preciso utilizar paréntesis. Otro cuadro de mensaje presenta entonces en pantalla el valor de la variable. Sub Pregunta() miVar = MsgBox(Prompt:="Me gusta mi trabajo.", _ Title:="Cuadro de respuesta", Buttons:="4") MsgBox miVar End Sub

Sintaxis de la instrucción Option Option Compare {Binary | Text | Database} En la sintaxis de la instrucción Option Compare, las llaves y la línea vertical indican una elección obligatoria entre tres opciones. (No escriba las llaves en la instrucción de Visual Basic). Por ejemplo, en Visual Basic al comparar dos textos se tienen en cuenta las mayúsculas y minúsculas, pero si se escribe la siguiente instrucción en el área de declaraciones dentro del módulo (antes de todas las subrutinas) entonces las cadenas de texto que se comparen no importan ya si se escriben en mayúsculas o minúsculas. Option Compare Text

Sintaxis de la instrucción Dim


Pag. 89

Manual de Microsoft Excel Programación VBA Dim nombrevariable[([subscriptos])] [As tipo] [, nombrevariable[([subscriptos])] [As tipo]] . .. En la sintaxis de la instrucción Dim, la palabra Dim es una palabra clave exigida. El único elemento necesario es nombrevariable (el nombre de la variable). Por ejemplo, la siguiente instrucción crea tres variables: miVar, siguienteVar y terceraVar. Estas variables se declaran automáticamente como Variant. Dim miVar, siguienteVar, terceraVar

El siguiente ejemplo declara una variable como String. Al incluir un tipo de datos se ahorra memoria y se pueden evitar errores en el código. Dim miRespuesta As String

Para declarar varias variables en una instrucción, debe incluirse el tipo de datos para cada variable. Las variables declaradas sin un tipo de datos se declaran automáticamente como Variant. Dim x As Integer, y As Integer, z As Integer

En la siguiente instrucción, a x e y se les asigna el tipo de datos Variant. Sólo a z se le asigna el tipo de datos Integer. Dim x, y, z As Integer

Si se declara una variable matriz, deben incluirse los paréntesis. Los subscriptos son opcionales. La siguiente instrucción define las dimensiones de una matriz dinámica, miMatriz. Dim miMatriz()

35. Declarar variables Para declarar variables se utiliza normalmente una instrucción Dim. La instrucción de declaración puede incluirse en un procedimiento para crear una variable de nivel de procedimiento. O puede colocarse al principio de un módulo, en la sección Declarations, para crear una variable de nivel de módulo. El siguiente ejemplo crea la variable NombreTexto y específicamente le asigna el tipo de datos String. Dim NombreTexto As String

Si esta instrucción aparece dentro de un procedimiento, la variable NombreTexto se puede usar sólo en ese procedimiento. Si la instrucción aparece en la sección Declarations del módulo, la variable NombreTexto estará disponible en todos los procedimientos dentro del módulo, pero no para los restantes módulos del proyecto. Para hacer que esta variable esté disponible para todos los procedimientos de un proyecto, basta con comenzar la declaración con la instrucción Public, tal y como muestra el siguiente ejemplo: Public NombreTexto As String

Si desea más información sobre cómo dar nombre a sus variables, puede consultar la sección "Visual Basic Naming Rules" en la Ayuda de Visual Basic. Las variables se pueden declarar como de uno de los siguientes tipos de datos: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String (para cadenas de longitud variable), String * longitud (para cadenas de longitud fija), Object, o Variant. Si no se especifica el tipo de datos, el tipo de datos Variant es el predefinido. También es posible crear un tipo definido por el usuario empleando la instrucción Type. Si desea más


Pag. 90

Manual de Microsoft Excel Programación VBA información sobre tipos de datos puede consultar la sección "Tipo de datos Summary" en la Ayuda de Visual Basic. Se pueden declarar varias variables en una instrucción. Para especificar el tipo de datos se debe incluir un tipo de datos para cada variable. En la siguiente instrucción se declaran las variables intX, intY, e intZ como del tipo Integer. Dim intX As Integer, intY As Integer, intZ As Integer

En la siguiente instrucción, intX e intY se declaran como del tipo Variant; y sólo intZ se declara como del tipo Integer. Dim intX, intY, intZ As Integer

No es necesario especificar el tipo de datos en la instrucción de declaración. Si se omite, la variable será del tipo Variant.

Utilizar la instrucción Public La instrucción Public se puede utilizar para declarar variables públicas de nivel de módulo. Public NombreTexto As String

Las variables públicas se pueden usar en cualquier procedimiento del proyecto. Si una variable pública se declara en un módulo estándar o en un módulo de clase, también se podrá usar en los proyectos referenciados por el proyecto en que se declara la variable pública.

Utilizar la instrucción Private La instrucción Private se puede usar para declarar variables privadas de nivel de módulo. Private MiNombre As String

Las variables Private pueden ser usadas únicamente por procedimientos pertenecientes al mismo módulo. Nota Cuando se utiliza a nivel de módulo, la instrucción Dim es equivalente a la instrucción Private. Sería aconsejable usar la instrucción Private para facilitar la lectura y comprensión del código.

Utilizar la instrucción Static Cuando se utiliza la instrucción Static en lugar de la instrucción Dim, la variable declarada mantendrá su valor entre llamadas sucesivas.

Utilizar la instrucción Option Explicit En Visual Basic se puede declarar implícitamente una variable usándola en una instrucción de asignación. Todas las variables que se definen implícitamente son del tipo Variant. Las variables del tipo Variant consumen más recursos de memoria que la mayor parte de las otros tipos de variables. Su aplicación será más eficiente si se declaran explícitamente las variables y se les asigna un tipo de datos específico. Al declararse explícitamente las variables se reduce la posibilidad de errores de nombres y el uso de nombres erróneos. Si no desea que Visual Basic realice declaraciones implícitas, puede incluir en un módulo la instrucción Option Explicit antes de todos los procedimientos. Esta instrucción exige que todas las variables del módulo se declaren explícitamente. Si un módulo incluye la instrucción Option Explicit, se producirá un error en tiempo de compilación cuando Visual


Pag. 91

Manual de Microsoft Excel Programación VBA Basic encuentre un nombre de variable que no ha sido previamente declarado, o cuyo nombre se ha escrito incorrectamente. Se puede seleccionar una opción del entorno de programación de Visual Basic para incluir automáticamente la instrucción Option Explicit en todos los nuevos módulos. Consulte la documentación de su aplicación para encontrar la forma de modificar las opciones de entorno de Visual Basic. Tenga en cuenta que esta opción no tiene ningún efecto sobre el código que se haya escrito con anterioridad. Nota Las matrices fijas y dinámicas (variables de arreglo o arrays) siempre se tiene que declarar explícitamente.

Declarar una variable de objeto para automatización Cuando se utiliza una aplicación para controlar los objetos de otra aplicación, debe establecerse una referencia a la biblioteca de tipos de la otra aplicación. Una vez que se ha establecido la referencia, se pueden declarar variables de objeto conforme a su tipo más específico. Por ejemplo, si desde Microsoft Word se establece una referencia a la biblioteca de tipos de Microsoft Excel, se puede declarar una variable del tipo Worksheet desde Microsoft Word para representar un objeto Worksheet de Microsoft Excel. Si se utiliza otra aplicación para controlar objetos de Microsoft Access, es posible, en la mayor parte de los casos, declarar variables objetos del tipo más específico. Se puede usar también la palabra clave New para crear automáticamente una nueva definición de un objeto. Sin embargo, puede ser necesario indicar que se trata de un objeto Microsoft Access. Por ejemplo, cuando se declara una variable de objeto para representar un formulario de Microsoft Access desde Microsoft Visual Basic, debe distinguirse entre el objeto Form de Microsoft Access y un objeto Form de Visual Basic. Para ello se incluye el nombre de la biblioteca de tipos en la declaración de la variable, como muestra el siguiente ejemplo: Dim frmPedidos As New Access.Form

Algunas aplicaciones no reconocen algunos de los tipos de objetos de Microsoft Access. En ese caso, incluso después de establecer una referencia a la biblioteca de tipos de Microsoft Access, será necesario declarar todas las variables objeto de Microsoft Access como del tipo Object. Tampoco puede usarse la palabra clave New para crear una nueva definición del objeto. El siguiente ejemplo muestra cómo declarar una variable que represente una nueva definición del objeto Application de Microsoft Access desde una aplicación que no reconoce los tipos de objeto de Microsoft Access. La aplicación crea entonces una nueva definición del objeto Application. Dim appAccess As Object Set appAccess = CreateObject("Access.Application")

Para determinar la sintaxis a utilizar con una aplicación determinada debe consultarse la documentación de la aplicación.


Pag. 92


36. Resumen de tipos de datos La tabla siguiente muestra los tipos de datos compatibles, incluyendo el tamaño de almacenamiento y el intervalo. Tipo de datos

Tamaño de almacenamiento

Intervalo

Byte

1 byte

0 a 255

Boolean

2 bytes

True o False

Integer

2 bytes

-32,768 a 32,767

Long (entero largo)

4 bytes

-2,147,483,648 a 2,147,483,647

Single (coma flotante/ precisión simple)

4 bytes

-3,402823E38 a –1,401298E-45 para valores negativos; 1,401298E-45 a 3,402823E38 para valores positivos

Double (coma flotante/ precisión doble)

8 bytes

-1.79769313486231E308 a -4,94065645841247E-324 para valores negativos; 4,94065645841247E-324 a 1,79769313486232E308 para valores positivos

Currency (entero a escala)

8 bytes

-922.337.203.685.477,5808 a 922.337.203.685.477,5807

Decimal

14 bytes

+/-79.228.162.514.264.337.593.543.950.335 sin punto decimal; +/-7,9228162514264337593543950335 con 28 posiciones a la derecha del signo decimal; el número más pequeño distinto de cero es +/-0,0000000000000000000000000001

Date

8 bytes

1 de enero de 100 a 31 de diciembre de 9999

Object

4 bytes

Cualquier referencia a tipo Object

String (longitud variable)

10 bytes + longitud de la cadena

Desde 0 a 2.000 millones

String (longitud fija)

Longitud de la cadena

Desde 1 a 65.400 aproximadamente

Variant (con números)

16 bytes

Cualquier valor numérico hasta el intervalo de un tipo Double

Variant (con caracteres)

22 bytes + longitud de la cadena

El mismo intervalo que para un tipo String de longitud variable

Definido por el usuario (utilizando Type)

Número requerido por los elementos

El intervalo de cada elemento es el mismo que el intervalo de su tipo de datos.

Nota: Las matrices de cualquier tipo de datos requieren 20 bytes de memoria, más cuatro bytes para cada dimensión de matriz, más el número de bytes que ocupan los propios


Pag. 93

Manual de Microsoft Excel Programación VBA datos. Puede calcular la memoria que ocupan los datos multiplicando el número de elementos de datos por el tamaño de cada elemento. Por ejemplo, los datos de una matriz unidimensional que consten de cuatro elementos de datos tipo Integer de dos bytes cada uno, ocupan ocho bytes. Los ocho bytes que requieren los datos más los 24 bytes necesarios para la matriz suman un requisito total de memoria de 32 bytes para dicha matriz. Un tipo Variant que contiene una matiz requiere 12 bytes más que la matriz por sí sola.

Comprender la vida de las variables El tiempo durante el que una variable conserva su valor se conoce como vida. El valor de la variable puede cambiar durante su vida, pero conserva algún valor. Cuando una variable pierde su alcance, ya no tiene ningún valor. Cuando un procedimiento comienza a ejecutarse, se inicializan todas las variables. Las variables numéricas se inicializan a cero, las cadenas de longitud variable se inicializan a una cadena vacía de longitud cero ("") y una cadena de longitud fija se llena con los caracteres correspondientes al código ASCII 0, o Chr(0). Las variables Variant se inicializan a Empty. Cada uno de los elementos de una variable de tipo definido por el usuario se inicializa como si se tratase de una variable independiente. Cuando se declara una variable de objeto, se reserva el espacio correspondiente en memoria, pero se le da el valor Nothing hasta que se le asigne una referencia al objeto mediante la instrucción Set. Si el valor de una variable no cambia durante la ejecución del código, conserva su valor de inicialización hasta que pierda alcance. Una variable de nivel de procedimiento declarada mediante la instrucción Dim conserva su valor hasta que el procedimiento termina de ejecutarse. Si el procedimiento llama a otros procedimientos, la variable conserva su valor también mientras se ejecutan esos procedimientos. Si una variable de nivel de procedimiento se declara con la palabra clave Static, la variable conserva su valor mientras haya código en ejecución, sea cual sea el módulo. Cuando todo el código ha terminado de ejecutarse, la variable pierde su alcance y su valor. Su vida es la misma que la de una variable de nivel de módulo. Una variable de nivel de módulo es distinta a una variable estática. En un módulo estándar o un módulo de clase, conserva su valor hasta que se termina de ejecutar el código. En un módulo de clase conserva su valor mientras exista una definición de la clase. Las variables de nivel de módulo consumen memoria hasta que sus valores se vuelven a inicializa, por eso sólo se deben utilizar cuando sea necesario. Si se incluye la palabra clave Static antes de una instrucción Sub o Function, los valores de todas las variables de nivel de procedimiento del procedimiento se conservarán entre las sucesivas llamadas.

Comprender los tipos de datos Variant


Pag. 94

Manual de Microsoft Excel Programación VBA El tipo de datos Variant se especifica automáticamente si no se especifica otro tipo de datos al declarar una constante, variable, o argumento. Las variables declaradas como del tipo de datos Variant pueden contener valores numéricos, cadenas de texto, fecha, hora o Booleans y pueden convertir los valores que contienen de forma automática. Los valores numéricos Variant ocupan 16 bytes de memoria (lo que sólo es significativo en procedimientos grandes o módulos complejos) y son más lentos a la hora de su acceso que las variables de tipo explícito de los restantes tipos. Es muy raro utilizar el tipo de datos Variant para una constante. Los valores de cadena Variant necesitan 22 bytes de memoria. Las siguientes instrucciones crean variables Variant: Dim miVar Dim tuVar As Variant laVar = "Esto es un texto."

La última instrucción no declara explícitamente la variable laVar, sino que la declara implícitamente, o automáticamente. Las variables que se declaran implícitamente se especifican como del tipo de datos Variant. Sugerencia Si se especifica un tipo de datos para una variable o argumento y a continuación se utiliza un tipo erróneo de datos, se producirá un error de tipo de datos. Para evitar errores de tipo de datos, se deben usar sólo variables (del tipo de datos Variant) o declarar explícitamente todas las variables y especificar para ellas un tipo de datos. El último método es el preferible.

Comprender el alcance y la visibilidad El alcance se refiere a la disponibilidad de una variable, constante, o procedimiento para ser usado por otro procedimiento. Hay tres niveles de alcance: nivel de procedimiento, nivel de módulo privado y nivel de módulo público. El alcance de una variable se determina cuando se declara. es aconsejable declarar todas las variables explícitamente para evitar errores de conflicto de nombres entre variables que tiene alcances distintos.

Definir el alcance de un nivel de procedimiento Una variable o constante definida desde dentro de un procedimiento no es visible fuera de ese procedimiento. Sólo el procedimiento que contiene la declaración de la variable puede usarlos. En el siguiente ejemplo, el primer procedimiento presenta un cuadro de mensaje que contiene una cadena de texto. El segundo procedimiento presenta un cuadro de mensaje en blanco ya que la variable strMensaje es local para el primer procedimiento. Sub VariableLocal() Dim strMensaje As String strMensaje = "Esta variable no se puede usar fuera de este procedimiento." MsgBox strMensaje End Sub Sub FueraAlcance() MsgBox strMensaje End Sub

Definir el alcance de nivel de módulo privado Se pueden definir variables y constantes de nivel de módulo en la sección Declarations de un módulo. Las variables de nivel de módulo pueden ser públicas o privadas. Las variables


Pag. 95

Manual de Microsoft Excel Programación VBA públicas están disponibles para todos los procedimientos de todos los módulos de un proyecto; las variables privadas sólo están disponibles para los procedimientos de ese módulo. Las variables declaradas con la instrucción Dim en la sección Declarations tiene un alcance privado a no ser que se especifique otra cosa. Sin embargo, es aconsejable colocar la palabra clave Private delante del nombre de la variable para que el alcance sea evidente al leer el código. En el siguiente ejemplo, la variable de cadena strMensaje está disponible para cualquier procedimiento definido en el módulo. Cuando se llama al segundo procedimiento, presenta el contenido de la variable de cadena strMensaje en un cuadro de dialogo. ' Añada lo siguiente a la sección de Declarations del módulo. Private strMensaje As String Sub InicializaVariablePrivada() strMensaje = "Esta variable no se puede usar fuera de este módulo." End Sub Sub UsaVariablePrivada() MsgBox strMensaje End Sub

Nota Los procedimientos públicos de un módulo estándar o módulo de clase están disponibles para cualquier proyecto de referencia al proyecto actual. Para limitar el alcance de todos los procedimientos de un módulo al proyecto actual, añada una instrucción Option Private Module a la sección Declarations del módulo. Las variables y procedimientos públicos seguirán estando disponibles para los restantes procedimientos del proyecto actual, pero no lo estarán para los proyectos que a los que puedan hacer referencia.

Definir el alcance de nivel de módulo público Si se declara una variable de nivel de módulo como pública, estará disponible para todos los procedimientos del mismo proyecto. En el siguiente ejemplo, la variable de cadena strMensaje se puede usar en cualquier procedimiento de un módulo del proyecto. ' Incluya esto en la sección Declarations del módulo. Public strMensaje As String

Todos los procedimientos son públicos a menos que se especifique lo contrario, excepto en el caso de los procedimientos evento. Cuando Visual Basic crea un procedimiento evento, la palabra clave Private se inserta automáticamente antes de la declaración del procedimiento. Para los restantes procedimientos, debe declarar explícitamente con la palabra clave Private si no desea que sean públicos. Se pueden usar procedimientos, variables y constantes públicas definidas en módulos estándar o módulos de clase de proyectos que hagan referencia al actual. Sin embargo, es preciso establecer una referencia al proyecto en que están definidos. Los procedimientos, variables y constantes públicas definidas en módulos que no sean estándar o de clase, como pueden ser los módulos de formulario o módulos de informe, no están disponibles desde los proyectos que hacen referencia al actual, ya que estos módulos son privados para el proyecto en el que residen.


Pag. 96


Ejercicio Nº 1: Comportamiento de diversas variables en Visual Basic Las variables tienen un comportamiento diferente dependiendo de las condiciones en que fueron declaradas. En el siguiente ejercicio se declaran las siguientes 5 variables distintas: La variable "A" dentro de un procedimiento (Macro1) La variable privada "A" en el área de declaraciones del Módulo1 La variable estática "A" dentro de otro procedimiento (Macro4) La variable pública "B" en el área de declaraciones del Módulo1 La variable privada "A" en el área de declaraciones del Módulo2 A pesar de que 4 de ellas se llaman "A", cada una de estas variables son totalmente independientes una de la otra.

La variable "A" dentro del procedimiento Macro1:


Pag. 97

Manual de Microsoft Excel Programación VBA Al ejecutarse la Macro1 la sentencia DIM declara a la variable A y esta se inicializa siempre en cero. Luego la macro le suma 1 a la variable y al finalizar la macro un mensaje muestra que la variable A tiene el valor de 1. Note que cada vez que termine de ejecutarse la Macro1 la variable A siempre tendrá el valor final de 1. La variable privada "A" en el área de declaraciones del Módulo1: Al ejecutarse la Macro2 o la Macro3, estas macros incrementan el valor de la misma variable privada A, esta variable conserva su valor hasta que una de las 2 macros anteriores se ejecute y le vuelva a sumar 1. La variable estática "A" dentro del procedimiento Macro4: Al ejecutarse la Macro4 se declara la variable estática A y esta se incrementa su valor en 1 cada vez, pues esta variable conserva su valor cada vez que se ejecuta la macro. La variable pública "B" en el área de declaraciones del Módulo1: Al ejecutarse la Macro5 o la Macro7 la variable pública B incrementa su valor en 1 cada vez, pues esta variable conserva su valor cada vez que se ejecutan estas macros, a pesar de que ellas se encuentren en diferentes módulos. La variable privada "A" en el área de declaraciones del Módulo2: En cambio al ejecutarse la Macro6 la variable privada A incrementa su valor en 1 cada vez, pero el valor que conserva esta variable privada del Modulo2 es totalmente independiente del valor de la variable privada A del Modulo1.

Ejercicio Nº 2: Para buscar datos en una lista Este ejercicio muestra tres métodos distintos, cada uno de ellos más eficiente y más rápido que el otro, para buscar y copiar un dato que se intenta localizar dentro de una lista:

Para Buscar datos en una lista 1er Metodo: Utiliza el mandato While para recorrer la lista buscando el dato, y se detiene cuando lo encuentra. Este método es lento cuando la lista es muy grande. Sub Busca1() T = Timer Range("Valor").ClearContents Application.ScreenUpdating = False Range("Lista").Select While ActiveCell <> Range("Dato") ActiveCell.Offset(1, 0).Select Wend Range("Valor") = ActiveCell.Offset(0, 1) Range("Valor").Select Application.ScreenUpdating = True Range("Tiempo") = Timer - T End Sub


Pag. 98


2do Metodo: Utiliza la opción Buscar de la ficha Inicio/Buscar y reemplazar/Buscar. Este método es mas rápido que el anterior. Sub Busca2() T = Timer Range("Valor").ClearContents Dato = Range("Dato") Cells.Find(What:=Dato, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range("Valor") = ActiveCell.Offset(0, 1) Range("Valor").Select Range("Tiempo") = Timer - T End Sub

3er Metodo: Utiliza la sentencia Find del mismo Visual Basic.. Este método es mucho más rápido que los anteriores porque ya no utiliza herramientas de la hoja de calculo de Excel, sino la sentencia Find que es nativa de Visual Basic. Sub Busca3() T = Timer Range("Valor").ClearContents Dato = Range("Dato") Set c = Range("Lista").Find(Dato, LookIn:=xlValues) If Not c Is Nothing Then Celda = c.Address Range("Valor") = Range(Celda).Offset(0, 1) Range("Valor").Select Range("Tiempo") = Timer – T End Sub

Para Abrir archivos Application.GetOpenFilename: Utiliza el mandato Application.GetOpenFilename para mostrar en pantalla la ventana de dialogo tradicional de Excel para abrir un archivo. Este método permite elegir de la ventana el archivo que se desea abrir de una carpeta previamente definida.


Pag. 99


Sub AbrirArchivo() 'elije un archivo para abrirlo ChDrive Left(ActiveWorkbook.Path, 1) ChDir ActiveWorkbook.Path Archivo = Application.GetOpenFilename("Archivos del Excel" & _ "(*.xls*),*.xls*", , "Elija el Archivo que desea Abrir") On Error GoTo Sale Workbooks.Open Archivo Archivo = ActiveWorkbook.Name Rpta = MsgBox("El archivo: " & Archivo & " ha sido cargado" & _ "vbNewLine & "¿Desea cerrarlo?", vbYesNo) If Rpta = vbNo Then Exit Sub Application.DisplayAlerts = False ActiveWorkbook.Close Sale: End Sub


Pag. 100


Verifica si un Libro está abierto Workbooks.Open: Utiliza el mandato For each…next para recorrer por los libros abiertos y verificar si un cierto archivo ya ha sido abierto anteriormente. Si no es así, abre el archivo, sino, activa la ventana del archivo ya existente en la memoria.

Sub AbrirLibro2() Ruta = ActiveWorkbook.Path Archivo = InputBox("Nombre del archivo a abrir (Ej: Lista.xls ó Lista.xlsx)") If Archivo = "" Then Exit Sub For Each Libro In Application.Workbooks If UCase(Libro.Name) = UCase(Archivo) Then Windows(Archivo).Activate Exit Sub End If Next On Error GoTo NoHay Workbooks.Open Ruta & "\" & Archivo Exit Sub NoHay: MsgBox ("El archivo " & Archivo & " no existe en la carpeta actual") End Sub


Pag. 101


Ingreso de fechas en las celdas a través de un Calendario Control de Calendario 12.0: En el Editor de Visual Basic inserte un formulario, luego haga clic derecho en un lugar vacio de la ventana Cuadro de Herramientas y elija la opción "Controles adicionales". En la ventana que se abre, active la casilla de la opción "Control de Calendario 12.0" y luego Aceptar.

Luego con esta nueva herramienta, dibuje el calendario dentro del formulario, y al hacer doble clic sobre él, agréguele el siguiente código: Private Sub Calendar1_Click() ActiveCell = Calendar1.Value Unload Me End Sub Private Sub UserForm_Activate() Calendar1.Value = date End Sub

Crear en la hoja de cálculo una barra de avance del proceso


Pag. 102


ProgressBar: Cuando se realicen procesos que demandan mucho tiempo para Excel, quizás se desee mostrar una barra de avance de este proceso a fin de que el usuario sepa lo que está ocurriendo. Por ejemplo si se desea crear este cuadro:

Barra con ActiveX

Realice los siguientes pasos: 1. Ficha Programador/ Insertar/ Controles ActiveX 2. Clic en la herramienta "Mas controles" y seleccionar la opción "Microsoft ProgressBar Control, version 6.0" y clic en Aceptar.

3. Entonces dibujar los siguientes objetos en la hoja de calculo:

4. Luego cambiar las siguientes propiedades: (Name) BorderStyle Scrolling

Barra1 1-ccFixedSingle 1-ccScrollingSmooth

(Name)

Barra2


Pag. 103

Manual de Microsoft Excel Programación VBA BorderStyle Scrolling

1-ccFixedSingle 1-ccScrollingStandard

5. En un Modulo de Visual Basic escribir la siguiente macro: Sub Avance() Dim i As Double, j As Double ActiveSheet.Barra1.Visible = True ActiveSheet.Barra2.Visible = True Range("A3:C13").ClearContents Range("A3").Select For i = 1 To 100 For j = 1 To 200000 Sqr (j) Next ActiveCell.Cells(i / 10 + 1, 1) = i ActiveCell.Cells(i / 10 + 1, 2) = i * i ActiveCell.Cells(i / 10 + 1, 3) = Sqr(i) ActiveSheet.Barra1.Value = i ActiveSheet.Barra2.Value = i Next MsgBox ("Terminado") ActiveSheet.Barra1.Value = 0 ActiveSheet.Barra2.Value = 0 End Sub 6. Finalmente asignar la macro Avance al botón "Barra con ActiveX"


Pag. 104


Crear un Formulario con una barra de avance del proceso ProgressBar:

Barra con Formulario

1. Crear el formulario que se muestra a continuacion. 2. En el cuadro de herramientas hacer un clic derecho y elegir Controles adicionales, luego activar la casilla: Microsoft ProgressBarr Control, version 6.0, y Aceptar.

3. Con el nuevo control dibuje los controles como se muestran en la ventana anterior, y cambie las siguientes propiedades: (Name) Barra1 BorderStyle 1-ccFixedSingle Scrolling 1-ccScrollingSmooth (Name) Barra1 BorderStyle 1-ccFixedSingle Scrolling 1-ccScrollingSmooth 4. Escribir los siguientes codigos para los botones "Calculo" y "Cerrar" Private Sub Calculo_Click() Dim i As Double, j As Double Range("A3:C13").ClearContents Range("A3").Select Mensaje.Caption = "Procesando..." Application.Wait (Now + TimeValue("00:00:01")) For i = 1 To 100 For j = 1 To 200000 Sqr (j) Next ActiveCell.Cells(i / 10 + 1, 1) = i ActiveCell.Cells(i / 10 + 1, 2) = i * i ActiveCell.Cells(i / 10 + 1, 3) = Sqr(i) Barra.Value = i


Pag. 105

Manual de Microsoft Excel Programación VBA Barra2.Value = i Next Mensaje = "Proceso terminado" Barra.Value = 0 Barra2.Value = 0 End Sub Private Sub Cerrar_Click() Unload Me End Sub 5. En un Modulo de Visual Basic escribir la siguiente macro: Sub Ventana() UserForm1.Show End Sub 6. Finalmente asignar la macro Ventana al botón "Barra con Formulario"

.


Pag. 106


Para pintar de color los objetos ColorIndex: Sintaxis:

Objeto.ColorIndex = número


Pag. 107


Dos formas de ordenar una lista Sort: El primer método es el usado por la versión 2003 de Excel y el segundo es de la versión 2007.


Pag. 108


Formulario para ver Video Windows Media Player: En el Editor de Visual Basic Insertar un formulario y en la ventana "Cuadro de Herramientas" activar el control adicional "Windows Media Player"

. Luego diseñar el siguiente formulario:

En la ventana Modulo1 escribir la siguiente Subrutina: Sub VerVideo() UserForm1.Show End Sub

En la ventana del Formulario escribir lo siguiente: Private Sub Cerrar_Click() Unload Me End Sub Private Sub Pelicula_Change() If Pelicula = "" Then Exit Sub RUTA = ActiveWorkbook.Path Video.URL = RUTA & "\" & Pelicula End Sub


Pag. 109

Manual de Microsoft Excel Programación VBA En la hoja de cálculo asignar la macro VerVideo al botón:

Nota.- Los archivos de video deberán estar guardados en la misma carpeta donde se encuentra el archivo de Excel.

Insertar opciones al Menú Contextual CommandBar: En el Editor de Visual Basic escribir las siguientes rutinas:


Pag. 110


Práctica De Laboratorio Nº1

Objetivos: Entender la diferencia al grabar macros con referencias absolutas y macros con referencias relativas. 

Uso de la grabadora de macros utilizando referencias absolutas y relativas.



Asignación de macros a botones

Un Empresa de taxis se comunica por radio con sus unidades a fin de establecer la tarifa que deben cobrar los choferes a sus clientes, teniendo como datos los lugares de origen y destino de cada carrera.

Para lograr esto, en la central de taxis se cuenta con una hoja de cálculo en Excel que contiene las tarifas a cobrar entre cada punto origen y destino de los distritos de Lima: 

El chofer llama a la central informando los sitios origen y destino de la carrera



La central de taxis calcula el monto a cobrar en la carrera y si el cliente la acepta la central registrará la carrera en el cuadro de viajes


Pag. 111


1ra Parte:

Creación el Cuadro de Viajes y la Tabla de Tarifas

1. En la Hoja1 crear el Cuadro de Viajes, tal y como se muestra a continuación en la figura.

2. En la Hoja2 crear la siguiente Tabla de Tarifas: Dar nombre a los siguientes rangos: A6:A16 ORIGEN B5:L5 DESTINO Luego dar nombre de rango a cada fila y columna de la tabla, para lo cual haga lo siguiente: 

Seleccionar el rango A5:L16



Elegir la Ficha Formulas/Crear desde la selección, y luego Aceptar

3. En la Hoja1 hacer lo siguiente: 

Dar nombre al rango G5:G9

CHOFERES



Validar la Celda B5:

Permitir: Lista, Origen: =CHOFERES



Validar la Celda C5:

Permitir: Lista, Origen: =ORIGEN



Validar la Celda D5:

Permitir: Lista, Origen: =DESTINO

4. Dar nombre de rango a cada columna del cuadro de viajes: 

Seleccionar el rango A4:E5


Pag. 112


Elegir la Ficha Formulas/Crear desde la selección, verificar que solo esté marcada la casilla Fila superior, y luego Aceptar

5. En la celda H5 escribir la siguiente fórmula matricial: {=SUMA(SI((Chofer=G5)*(Dia>=H$2)*(Dia
2da Parte: Grabar la macro que registre las carreras en el Cuadro de Viajes 6. En la Hoja1 ingresar los siguientes datos en el Cuadro de Viajes:

Para Grabar la macro Registrar: 7. En la ficha Programador verificar que esté habilitado el comando Usar referencias relativas 8. Estando el puntero en la celda D5, en la Ficha Programador elegir el comando Grabar macro, escribir como nombre de la macro: Registrar, y luego Aceptar. 9. Estando encendida la grabadora de macros realice las siguientes acciones: 

Seleccionar la celda E5



Escribir la fórmula: =INDIRECTO(B5) INDIRECTO(C5) y presionar Enter.



Seleccionar la celda E5 y presionar Ctrl+C, luego clic derecho Pegado especial, marcar Valores y Aceptar. Luego presionar Esc.



Seleccionar la celda A5 y escribir la formula =AHORA() y presionar Enter



Seleccionar la celda A5 y presionar Ctrl+C, luego clic derecho Pegado especial, marcar Valores y Aceptar. Luego presionar Esc.



Seleccionar B5:D5 y con el cuadro de relleno copiar estas 3 celdas en la fila 6.



Seleccionar el rango B6:D6 y presionar al tecla Suprimir.



Seleccionar la celda B6


Pag. 113


Presionar Ctrl+*



Ficha Formulas/Crear desde la selección, marcar solo Fila superior, y Aceptar



Seleccionar la celda B6, y luego presionar Ctrl+↓



Finalmente presionar la tecla ↓ una vez

10. Finalizar la grabación de la macro con un clic en el boton

Detener grabación.

11. La Hoja1 se verá entonces así:

Para Ver y Modificar la macro Registrar en la ventana del Editor de Visual Basic: 12. En la Ficha Programador/Macros, seleccionar la macro Registrar y luego clic en el boton Modificar. Se abrirá la ventana del Editor de Visual Basic y se verá en ella el código de la siguiente subrutina: Sub Registrar() ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-2]) INDIRECT(RC[-1])" ActiveCell.Select Selection.Style = "Currency" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, -4).Range("A1").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, 1).Range("A1:C1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:C2"), Type:= _ xlFillDefault ActiveCell.Range("A1:C2").Select ActiveCell.Offset(1, 0).Range("A1:C1").Select Selection.ClearContents ActiveCell.Select Application.CutCopyMode = False Selection.CurrentRegion.Select Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _ False Range("B4").Select


Pag. 114

Manual de Microsoft Excel Programación VBA Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select End Sub

13. El usar la grabadora de macros crea en ocasiones algunas lineas de codigo innecesarias. Lineas que aparecen en negrita en la subrutina anterior. Si se desea estas lineas se pueden borrar o modificar para hacer mas eficiente la ejecucion de la macro, además agregar la sentencia Application.DisplayAlerts=False, luego de lo cual la subrutina quedaría como se muestra a continuación: Sub Registrar() ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-2]) INDIRECT(RC[-1])" Selection.Style = "Currency" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False ActiveCell.Offset(0, -4).Select ActiveCell = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Range("A1:C1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:C2"), Type:= _ xlFillDefault

ActiveCell.Offset(1, 0).Range("A1:C1").ClearContents

Application.CutCopyMode = False Selection.CurrentRegion.Select Application.DisplayAlerts = False Selection.CreateNames Top:=True, Left:=False

Range("B4").Select Selection.End(xlDown).Offset(1, 0).Select End Sub

Nota: La sentencia Application.DisplayAlerts=False evitará que aparezca la ventana de dialogo advirtiendo que se van a modificar los tamaños de los nombres de rango del cuadro de viajes.


Pag. 115

Manual de Microsoft Excel Programación VBA Para Escribir la macro Nuevo: 14. Debajo de la sentencia End Sub escriba la siguiente subrutina: Sub Nuevo() Range("B4").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select Wend End Sub

15. Finalmente dibuje los botones “Nuevo” y “Registrar”, y asigneles a cada uno las macros correspondientes. 16. Si en la celda H2 se escribe la fecha, entonces la Hoja1 se deberá ver asi:

17. Para ingresar una nueva carrera de taxi, solo habrá que elegir en la celda B6 el nombre del chofer, en C6 el lugar origen, en D6 el lugar destino, y luego bastará con hacer clic en el boton “Registrar” para que se complete el registro de un nuevo viaje. 18. Automáticamente en la columna H se iran calculando los montos acumulados de los diferentes choferes en la fecha escrita en la celda H2. 19. Finalmente guarde este archivo como un Libro habilitado para macros y póngale el nombre Taxi. El archivo se guardará con el nombre Taxi.xlsm


Pag. 116



Objetivos: Se desea ingresar datos en tres diferentes listas ubicadas en hojas distintas de un libro, a partir de datos escritos en la primera hoja de cálculo. Para los cual se van a crear dos macros: 

Una macro que crea códigos correlativos para cada una de las listas.



Una segunda macro que registra los datos en la lista respectiva.

1. En la hoja Datos escribir lo siguiente:

2. Seleccionar la celda B3 y en la ficha Datos elegir el comando Validación de datos: En el casillero Permitir, elegir la opción Lista. En el casillero Origen, escribir Personal,Equipo,Materiales Luego Aceptar. 3. Dibujar también los botones rectangulares para las macros Crea Código y Registrar, tal como se muestra en la figura. 4. En la hoja Personal ingrese lo siguiente:


Pag. 117

Manual de Microsoft Excel Programación VBA 5. En la hoja Equipo ingrese lo siguiente:

6. En la hoja Materiales ingrese lo siguiente:

7. Ingresar a Visual Basic y en una hoja de módulo escriba el siguiente código para las macros: Sub CreaCodigo() If Range("B3") = "" Then Exit Sub Cuadro = Range("B3") Sheets(Cuadro).Select Range("A3").Select Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Selection.End(xlDown).Select Cod = Left(ActiveCell, 1) & Right("000" & _ Val(Right(ActiveCell, 4)) + 1, 4) Sheets("Datos").Select Range("B4") = Cod End Sub Sub Registrar() Cuadro = Range("B3") Codigo = Range("B4") Descripcion = Range("B5") Unidad = Range("B6") Costo = Range("B7") Sheets(Cuadro).Select Range("A3").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select


Pag. 118

Manual de Microsoft Excel Programación VBA Wend ActiveCell = Codigo ActiveCell.Offset(0, 1) = Descripcion ActiveCell.Offset(0, 2) = Unidad ActiveCell.Offset(0, 3) = Costo Sheets("Datos").Select MsgBox ("Los datos han sido registrados exitosamente") Range("B3:B7").ClearContents End Sub

8. Regrese a Excel y asigne las macros “CreaCodigo” y “Registrar” a los botones correspondientes. 9. Finalmente guarde el libro con el nombre “Registrar Datos en Listas” en un archivo habilitado para macros.


Pag. 119



Objetivos: Utilizar las distintas sentencias de control para la solución de múltiples tareas en el manejo de datos. Las sentencias de control a utilizarse son las siguientes: 

If..then..else,



Select Case



While…wend,



For…next,



For each…next.

1. En la hoja de cálculo escribir los siguientes datos:

2. En Visual Basic insertar una hoja de módulo y escribir las siguientes subrutinas: 'POSICIONA EL PUNTERO DEBAJO DEL TITULO EDAD Sub PrimeraEdad1() Range("A3").Select ActiveCell.CurrentRegion.Columns(4).Select ActiveCell.Offset(1, 0).Select End Sub Sub PrimeraEdad2() Range("A3").Select


Pag. 120

Manual de Microsoft Excel Programación VBA ActiveCell.CurrentRegion.Select ActiveCell.Select While ActiveCell <> "Edad" ActiveCell.Offset(0, 1).Select Wend ActiveCell.Offset(1, 0).Select End Sub 'POSICIONA EL PUNTERO EN LA PRIMERA COLUMNA AL FINAL DE LA LISTA Sub Nuevo() Range("A3").End(xlDown).Offset(1, 0).Select End Sub Sub Nuevo2() Range("A3").Select ActiveCell.CurrentRegion.Select ActiveCell.Offset(Selection.Rows.Count,0).Select End Sub Sub Nuevo3() Range("A3").Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select Wend End Sub

'CUENTA LA CANTIDAD DE ALUMNOS INSCRITOS Sub ContarInscritos() Range("A4").Select N = 0 While ActiveCell <> Empty N = N + 1 ActiveCell.Offset(1, 0).Select Wend MsgBox ("Hay " & N & " alumnos inscritos") End Sub 'CUENTA LA CANTIDAD DE MUJERES Y HOMBRES INSCRITOS Sub ContarPorSexo1() Range("A4").Select H = 0 M = 0 While ActiveCell <> Empty If ActiveCell.Offset(0, 2) = "M" Then H = H + 1 If ActiveCell.Offset(0, 2) = "F" Then M = M + 1 ActiveCell.Offset(1, 0).Select Wend MsgBox ("Hay " & M & " Mujeres y " & H & " Hombres inscritos") End Sub Sub ContarPorSexo2() Range("A3").Select


Pag. 121

Manual de Microsoft Excel Programación VBA ActiveCell.CurrentRegion.Columns(3).Select H = Application.WorksheetFunction.CountIf(Selection, "M") M = Application.WorksheetFunction.CountIf(Selection, "F") Range("A3").Select MsgBox ("Hay " & M & " Mujeres y " & H & " Hombres inscritos") End Sub 'INDICA EL TIPO DE PERSONA SEGUN SU EDAD Sub TipoPersona() Edad = Val(InputBox("Ingrese su edad")) If Edad = Empty Then Exit Sub If Edad < 18 Then MsgBox ("Ud. es menor de Edad") If Edad >= 18 Then MsgBox ("Ud. es mayor de edad") End Sub

'INSCRIBE A UNA PERSONA AL FINAL DE LA LISTA Sub Inscripcion() Nombre = InputBox("Ingrese el Nombre de la persona:") Range("A3").End(xlDown).Offset(1, 0).Select ActiveCell = Nombre End Sub 'REGISTRA LA ENTREGA DE CARNET A LOS ALUMNOS Sub Entrega1() Range("A3").Select Nombre = InputBox("Ingrese el Nombre de la persona:") If Nombre = Empty Then Exit Sub While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select If UCase(ActiveCell) = UCase(Nombre) Then If ActiveCell.Offset(0, 1) = "Entregado" Then MsgBox ("A " & Nombre & " ya se le ha entregado su Carnet") Exit Sub End If ActiveCell.Offset(0, 1) = "Entregado" Exit Sub End If Wend MsgBox (Nombre & " no existe en esta lista") End Sub Sub Entrega2() Range("A3").Select Nombre = InputBox("Ingrese el Nombre de la persona:") If Nombre = "" Then Exit Sub While ActiveCell <> Nombre ActiveCell.Offset(1, 0).Select If ActiveCell = Empty Then MsgBox (Nombre & " no existe en esta lista") Exit Sub End If Wend


Pag. 122

Manual de Microsoft Excel Programación VBA ActiveCell.Offset(0, 1) = "Entregado" End Sub 'IDENTIFICA EL TIPO DE PERSONA PARA CADA ALUMNO Sub Persona() Range("A4").Select While ActiveCell <> Empty Edad = ActiveCell.Offset(0, 3) Select Case Edad Case Is >= 18 ActiveCell.Offset(0, 4) = "Adulto" Case 13 To 17 ActiveCell.Offset(0, 4) = "Adolescente" Case 1 To 13 If ActiveCell.Offset(0, 2) = "M" Then ActiveCell.Offset(0, 4) = "Niño" Else ActiveCell.Offset(0, 4) = "Niña" End If End Select ActiveCell.Offset(1, 0).Select Wend End Sub 'CALCULA LA DEUDA DE CADA ALUMNO Sub CalculaDeuda() Range("A3").CurrentRegion.Select Alumnos = Selection.Rows.Count - 1 Range("G4").Select For N = 1 To Alumnos ActiveCell = 200 - ActiveCell.Offset(0, -1) ActiveCell.Offset(1, 0).Select Next End Sub 'CUENTA LA CANTIDAD DE MUJERES Y HOMBRES INSCRITOS Sub ContarPorSexo3() Range("A3").CurrentRegion.Select Alumnos = Selection.Rows.Count - 1 Range("C4").Select H = 0 M = 0 For N = 1 To Alumnos If ActiveCell = "M" Then H = H + 1 If ActiveCell = "F" Then M = M + 1 ActiveCell.Offset(1, 0).Select Next MsgBox ("Hay " & M & " Mujeres y " & H & " Hombres inscritos") End Sub Sub ContarPorSexo4() Range("A3").CurrentRegion.Columns("C").Select H = 0


Pag. 123

Manual de Microsoft Excel Programación VBA M = 0 For Each Celda If Celda = If Celda = Next MsgBox ("Hay " End Sub

In Selection "M" Then H = H + 1 "F" Then M = M + 1 & M & " Mujeres y " & H & " Hombres inscritos")

'BORRA LOS PAGOS QUE SON CERO Sub BorraCeros() Range("A3").CurrentRegion.Columns("F:G").Select N = 0 For Each Celda In Selection If Celda = 0 Then Celda.ClearContents: N = N + 1 Next MsgBox "habian " & N & " ceros" End Sub 'OCULTA TODAS LAS HOJAS MENOS LA ACTIVA Sub OcultarHojas() N = 1 For Each H In Worksheets If ActiveSheet.Name <> H.Name Then H.Visible = False N = N + 1 Next End Sub 'MUESTRA TODAS LAS HOJAS Sub MostrarHojas() N = 1 For Each H In Worksheets If ActiveSheet.Name <> H.Name Then H.Visible = True N = N + 1 Next End Sub 'CREA 2 HOJAS MUJERES Y HOMBRES Y COPIA A LOS ALUMNOS EN ELLAS Sub SeparaAlumnos() Application.DisplayAlerts = False For Each H In Worksheets If H.Name = "Hombres" Then Sheets("Hombres").Delete Next For Each H In Worksheets If H.Name = "Mujeres" Then Sheets("Mujeres").Delete Next Range("A3").Select Selection.AutoFilter Field:=3, Criteria1:="M" Selection.CurrentRegion.Select Selection.Copy Sheets.Add ActiveSheet.Name = "Hombres" Range("A3").Select


Pag. 124

Manual de Microsoft Excel Programación VBA ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Select Sheets("Hoja1").Select Range("A3").Select Selection.AutoFilter Field:=3, Criteria1:="F" Selection.CurrentRegion.Select Selection.Copy Sheets.Add ActiveSheet.Name = "Mujeres" Range("A3").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Select Sheets("Hoja1").Select Range("A3").Select Selection.AutoFilter End Sub


Pag. 125



Objetivos: Se desea ingresar datos de diferentes clientes en una lista, utilizando tres métodos diferentes:. 

Copiando datos del cliente desde un rango de celdas.



Ingresar datos del cliente utilizando ventanas de Inputbox.



Ingresar datos del cliente a través de formularios

3. En la hoja de cálculo escribir los siguientes datos:

4. Luego dibuje tres botones rectangulares tal como se aprecia en la figura. 5. A continuación ingrese a Visual Basic, inserte una hoja de módulo y en ella escriba las siguientes subrutinas: Sub IngresoDatos1() Rpta = MsgBox("¿Seguro desea registrar estos datos?", vbYesNo) If Rpta = vbNo Then Exit Sub DNI = Range("B6")


Pag. 126

Manual de Microsoft Excel Programación VBA Cliente = Range("B7") Telefono = Range("B8") Range("A11").Select While ActiveCell <> Empty If ActiveCell = DNI Then Rpta = MsgBox("Cliente ya existe, desea reemplazarlo?", vbYesNo) If Rpta = vbYes Then GoTo Sigue Exit Sub End If ActiveCell.Offset(1, 0).Select Wend Sigue: ActiveCell = DNI ActiveCell.NumberFormat = "00000000" ActiveCell.Offset(0, 1) = Cliente ActiveCell.Offset(0, 2) = Telefono ActiveCell.Offset(0, 2).NumberFormat = "#-####" Range("B6:B8").ClearContents Range("B6").Select End Sub

Sub IngresoDatos2() DNI = Val(InputBox("Ingrese DNI")) Cliente = InputBox("Ingrese nombre del cliente") Telefono = Val(InputBox("Ingrese numero de telefono")) Range("A11").Select While ActiveCell <> Empty If ActiveCell = DNI Then Rpta = MsgBox("Cliente ya existe, desea reemplazarlo?", vbYesNo) If Rpta = vbYes Then GoTo Sigue Exit Sub End If ActiveCell.Offset(1, 0).Select Wend Sigue: ActiveCell = DNI ActiveCell.NumberFormat = "00000000" ActiveCell.Offset(0, 1) = Cliente ActiveCell.Offset(0, 2) = Telefono ActiveCell.Offset(0, 2).NumberFormat = "#-####" End Sub

Sub IngresoDatos3() Ficha1.Show End Sub

6. Luego inserte una hoja de formulario (menú Insertar/Userform) y diseñe en ella el formulario tal como se muestra a continuación:


Pag. 127


7. Seleccione el fondo del formulario, y en la ventana Propiedades póngale en la propiedad (Name) el nombre Ficha1 8. También póngale nombre a cada cuadro de texto: DNI, Cliente y Telefono, respectivamente . 9. Igualmente con la propiedad (Name), póngale nombres a los botones de comando: Registrar, y Cancelar, respectivamente. 10. Luego haga doble clic en el botón Registrar, y escriba el siguiente código: Private Sub Registrar_Click() Range("A11").Select While ActiveCell <> Empty If ActiveCell = Val(DNI) Then Rpta = MsgBox("Cliente ya existe, desea reemplazarlo?", vbYesNo) If Rpta = vbYes Then GoTo Sigue Exit Sub


Pag. 128

Manual de Microsoft Excel Programación VBA End If ActiveCell.Offset(1, 0).Select Wend Sigue: ActiveCell = Val(DNI) ActiveCell.NumberFormat = "00000000" ActiveCell.Offset(0, 1) = Cliente ActiveCell.Offset(0, 2) = Val(Telefono) ActiveCell.Offset(0, 2).NumberFormat = "#-####" DNI = "" Cliente = "" Telefono = "" DNI.SetFocus End Sub

11. Seguidamente regrese al formulario y haga doble clic en el botón de comando Cancelar, y allí escriba el siguiente código: Private Sub Cancelar_Click() Unload Me End Sub

12. Regrese a la ventana de Excel, y asigne a cada uno de los botones las macros: “IngresoDatos1”,”IngresoDatos2”, e “IngresoDatos3” respectivamente. 13. Finalmente guarde el libro con el nombre “Ingreso de Clientes” en un archivo habilitado para macros.


Pag. 129



Objetivos: Se van a confeccionar un cuadro que permita controlar las horas de ingreso y salida del personal de vigilancia de la empresa, para luego al final de la semana calcular el monto del pago por este servicio. 

Uso de elementos de formulario.



Manejo de Fechas y Horas y sus respectivos formatos



Empleo de las funciones Now y CountA en Visual Basic



Protección de la hoja de calculo con contraseñas



Como crear macros que asignan otras macros.

Un Empresa contrata los servicios de un vigilante particular el cual debe cumplir con un horario normal de 8:00 de mañana hasta las 4:00 de la tarde, y por lo cual se le pagará un jornal diario de 90 soles. Adicionalmente se han acordado estas otras condiciones que deberán cumplirse por ambas partes: 

Si trabaja 5 días a la semana sin faltar, hay una bonificación de 1 jornal adicional.



Por cada minuto de tardanza habrá un descuento de 1 sol.



Por tiempo extra de trabajo previamente autorizado, se pagará el doble que las horas normales.

1ra Parte:

Creación del Cuadro de Pago por Vigilancia

20. En una hoja de cálculo de Excel, a la cual se le pondrá el nombre Vigilancia, escriba el siguiente Cuadro de Pagos por Servicio de Vigilancia, tal y como se muestra a continuación en la figura. 21. Combinar y centrar los siguientes rangos de celdas: D2:C2 Tamaño de letra 12 puntos E2:G2, E3:F3, E10:H10, E12:F12 y G12:H12 22. Dar los siguientes colores de relleno a las celdas: B2:C2 Negro E2:H2 Negro B3:D3 Azul oscuro E3:F3 Rojo G3:H3 Oro F4:F8 Rojo H4:H8 Oro E10:H10 Azul oscuro D12 Rojo E12:F12 Oro G12H12 Turquesa claro 23. Seleccionar el rango C3:D8 y llamando al menú Formato/Celdas elegir la ficha Numero, categoría Personalizada y elegir el tipo de formato: hh:mm AM/PM, luego en la celda C3 escribir 8:00, y en D3 escribir 16:00.


Pag. 130

Manual de Microsoft Excel Programación VBA 24. En los rangos E4:E9 y F4:F9 darle el formato personalizado: hh:mm

25. A la Hoja1 del libro darle el nombre Semana haciendo doble clic en etiqueta de hoja, y a la Hoja2 darle el nombre Horas. 26. En la hoja Horas escribir en A1 el título THORAS y en las celdas A3 y A4 las horas 7:30 y 7:31, luego darle a ambas el formato personalizado hh:mm AM/PM. 27. Seleccionar las celdas A3:A4 y arrastrar el cuadro de relleno hacia abajo hasta llegar a la celda A873 en la cual deberá aparecer las 10:00 pm. 28. Seleccionar el rango A3:A873 y darle el nombre de rango THORAS. 29. En las celdas C3 y C4 de la hoja Horas escribir las fechas 15/1/04 y 12/1/04 que corresponden a los dos primeros dias Lunes del año 2004. Luego seleccionando ambas celdas, arrastrar el cuadro de relleno hacia abajo hasta la celda C158 que correspondería al 25/12/06. 30. Seleccionar el rango C3:C158 y darle el nombre de rango SEMANAS. 31. En las celdas E3:F14 escribir los números y nombres de los doce meses del año como se muestra en la figura, y luego seleccionando el rango E3:F14 darle el nombre de rango MESES.


Pag. 131


32. Regrese a la Hoja Semana y seleccionando el rango B4:C8 llamar al menú Datos/Validación, en la casilla “Permitir” elegir Lista y en “Origen” escribir =THORAS, luego en la Ficha “Mensaje de error” desactivar la casilla “Mostrar mensaje de error si se introducen datos no validos”, finalmente haga clic en Aceptar. 33. Usando la lista desplegable de validación ingrese en el rango C4:D8 los siguientes datos que se muestran en el cuadro: 34. En la hoja Semana definir los siguientes nombres de rango: F4:F8  ChDctos

H4:H8  ChExtras

C4:D8  Horas

C4:C8  Ingreso

D4:D8  Salida 35. También escriba las siguientes formulas: En la celda E2:

="del "&DIA(BUSCARV(HOY(),Semanas,1))&" al " &DIA(BUSCARV(HOY(),Semanas,1)+4)&" de " &BUSCARV(MES(BUSCARV(HOY(),Semanas,1)),Meses,2,0 )

En la celda B10:

=SI(C12=5,B12,0)


Pag. 132

Manual de Microsoft Excel Programación VBA C12:

=CONTAR(C4:C8)

D12:

=E9*24*60

E12:

=G9*24*B12/4

F12:

=B10+B12*C12-D12+E12

E4:

=SI(ESTEXTO(C4),"",SI(Y(C4>C$3,C4<>""),C4-C$3,""))

Copie luego la anterior formula con el cuadro de relleno de E4 hasta E8. G4:

=SI(ESTEXTO(D4),"",SI(D4>D$3,D4-D$3,""))

Copie igualmente la anterior formula de G4 hasta G8. En la celda E9: G9:

=SUMAR.SI(F4:F8,VERDADERO,E4:E8) =SUMAR.SI(H4:H8,VERDADERO,G4:G8)

36. Abra las herramientas de formulario con el menú Ver/Barra de herramientas/ Formularios, y con el botón “Casilla de verificación” dibuje una casilla y borre totalmente el texto que lo acompaña.


Pag. 133

Manual de Microsoft Excel Programación VBA 37. En la casilla que acaba de crear haga un clic derecho y elija la opción Formato de control, luego en la casilla Vincular con la celda escriba F4, marque la casilla Sombreado 3D, y luego Aceptar. 38. Luego copie esta casilla 4 veces más otras celdas de la columna F y con la opción Formato de control cambie las celdas de vinculación para que sean F5, F6, F7 y F8 respectivamente. Luego repita el mismo procedimiento para las celdas G4:G8. 39. Cierre el grupo de herramientas de Formulario y luego marque con un check las casillas reverificación de las celdas F4, F5, F6, F7, F8, G7 y G8. tal y como se muestra en la figura más adelante. A continuación vamos a dar formato condicional a las celdas que tienen las horas para que cambien de color automáticamente de la siguiente forma: 

las horas de llegada si son más de las 8:00 am deberan cambiar a color rojo el fondo y letras blancas.



Si hay descuentos, siempre y cuando este marcado un check en la columna G, entonces la celda también se pintará de fondo rojo y letras blancas.



Las horas extras previamente autorizadas con un check en la columna H se pintarán de fondo color Oro.

40. Seleccionar el rango C4:C8 y llamando al menú Formato/Formato condicional elija lo siguiente: En el botón de Formato elija.- Fuente: Color Blanco y en Negrita; Bordes: Contorno; Tramas: Color Rojo.

41. Seleccionar el rango D4:D8 y dele el siguiente formato condicional: En el botón de Formato elija.- Fuente: Negrita; Bordes: Contorno; Tramas: Color Oro.


Pag. 134


42. Seleccionar ahora el rango F4:F8 y dele este otro formato condicional: En el botón de Formato elija.- Fuente: Color Blanco y en Negrita; Bordes: Contorno; Tramas: Color Rojo.

43. Seleccionar el rango D4:D8 y dele el siguiente formato condicional: En el botón de Formato elija.- Fuente: Negrita; Bordes: Contorno; Tramas: Color Oro.

44. A continuación guarde el archivo con el nombre Vigilancia. 45. El cuadro de vigilancia deberá verse entonces como se muestra a continuación:


Pag. 135


2da Parte: Creación de macros para automatizar el Cuadro 46. Con el botón que se encuentra en las herramientas de Formulario, dibujar los siguientes botones: Ingreso, Falta, Salida y Borrar Todo, como los que se muestran a continuación:

47. Además hacia el dalo superior derecho del cuadro dibujar con Autoformas/Formas básicas/Señal de prohibido, el círculo con diagonal que se muestra en la figura anterior y luego píntela con la herramienta de color de relleno, de color Rojo. Además haga un clic derecho en este botón y elija Formato de autoforma, luego en la ficha Proteger desactive la casilla Bloqueada.


Pag. 136

Manual de Microsoft Excel Programación VBA 48. Luego ingrese al Editor de Visual Basic con Alt+F11 y escriba en la hoja de Módulo las siguientes macros: Sub Ingreso() N = Application.WorksheetFunction.CountA(Range("Ingreso")) If N = 5 Then End Range("Horas").Select ActiveCell.Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Activate Wend On Error GoTo Sale ActiveCell = Now: Selection.NumberFormat = "h:mm AM/PM" Range("A1").Select Sale: End Sub Sub Salida() N = Application.WorksheetFunction.CountA(Range("Salida")) If N = 5 Then End Range("Horas").Select ActiveCell.Select ActiveCell.Offset(0, 1).Activate While ActiveCell <> Empty ActiveCell.Offset(1, 0).Activate Wend If ActiveCell.Offset(0, -1) = Empty Then End ActiveCell = Time: Selection.NumberFormat = "h:mm AM/PM" Range("A1").Select End Sub Sub Falta() N = Application.WorksheetFunction.CountA(Range("Ingreso")) If N = 5 Then End Range("Horas").Select ActiveCell.Select While ActiveCell <> Empty ActiveCell.Offset(1, 0).Activate Wend On Error GoTo Sale ActiveCell = "Faltó" ActiveCell.Offset(0, 1) = "Faltó" Range("A1").Select Sale: End Sub Sub BorraTodo() Rpta = MsgBox("Seguro desea Borrar todo?", _ vbYesNo + vbCritical + vbDefaultButton2) If Rpta = vbNo Then End On Error GoTo Sale Range("Horas").ClearContents Range("ChDctos") = True Range("ChExtras").ClearContents


Pag. 137

Manual de Microsoft Excel Programación VBA Sale: End Sub Sub QuitarSeguro() ActiveSheet.Unprotect ActiveSheet.Shapes("LLAVE").Select Selection.OnAction = "PoneSeguro" Range("a1").Select MsgBox "Protección ha sido retirada" End Sub Sub PonerSeguro() ActiveSheet.Protect Password:="MACRO", DrawingObjects:=True, _ Contents:=True ActiveSheet.Shapes("LLAVE").Select Selection.OnAction = "QuitaSeguro" Range("a1").Select ActiveWorkbook.Save MsgBox "La Hoja se encuentra protegida" End Sub

49. Regrese a la Hoja Semana de Excel y a cada uno de los botones Ingreso, Falta, Salida y Borrar todo, asígnele su respectiva macro. 50. A la autoforma “señal de prohibido” (circulo con diagonal rojo) asígnele la macro PonerSeguro. 51. Ejecute la macro BorrarTodo haciendo un clic en el botón correspondiente. 52. Vuelva a guardar el archivo Vigilancia con Ctrl.+G. Ahora estamos listos para empezar a registrar las horas de ingreso y salida del vigilante. Sin embrago, con la finalidad de que nadie mas que usted sea el que se encargue de llevar el control, el cuadro deberá estar protegido constantemente para que nadie pueda modificar su contenido. Veamos…, si realmente fuera la hora de ingreso en este instante habria que hacer un clic en el botón Ingreso y se escribiría automaticamente la hora en la celda correspondiente del dia Lunes. Sino, haga un clic en la lista desplegable y elija como hora de ingreso las 7:48 am. Luego, para proteger el cuadro bastará con hacer un clic en el botón de Señal de Prohibido y listo. La hoja quedará protegida y además el archivo Vigilancia se habrá grabado también automáticamente en disco. En pantalla aparecerá un mensaje indicando que la hoja ha quedado protegida. Cuando se desee quitar la protección, vuelva a presionar el botón de Señal de Prohibido y cuando le solicite la contraseña escriba la palabra MACRO, que coincide con la palabra que se escribió tambien en la macro QuitarSeguro de Visual Basic. Esta palabra clave usted la puede cambiar cuando quiera alli en visual basic, y con ella se podrá quitar la protección a la hoja. Una vez retirada la protección, en la pantalla aparecerá un mensaje indicando que la hoja se encuentra sin protección y entonces podrá usted seguir ingresando las demás horas, tanto de salida como de ingreso a medida que pasen los dias. Una vez ingresada una fecha recuerde que debe volver a conectar la protección con un clic en el botón correspondiente.


Pag. 138

Manual de Microsoft Excel Programación VBA Si las formulas en la hoja han sido escritas correctamente, los cálculos deberán realizarse automáticamente. Las celdas que definen las condiciones de pago y que usted puede cambiar son las siguientes:  La celda B2: Nombre del Vigilante  La celda C3: hora de ingreso oficial (actualmente 8:00 am)  La celda D3: hora de salida oficial (actualmente 4:00 pm)  La celda B12: Jornal diario (actualmente 90 soles)


Pag. 139


Resumen de tipos de datos La tabla siguiente muestra los tipos de datos compatibles, incluyendo el tamaño de almacenamiento y el intervalo. Tamaño de almacenamiento 1 byte 2 bytes 2 bytes

0 a 255 True o False -32,768 a 32,767

4 bytes

-2,147,483,648 a 2,147,483,647

4 bytes

-3.402823E38 a –1.401298E-45 para valores negativos; 1,401298E-45 a 3,402823E38 para valores positivos

Double (coma flotante/ precisión doble)

8 bytes

-1.79769313486231E308 a -4,94065645841247E-324 para valores negativos; 4,94065645841247E-324 a 1,79769313486232E308 para valores positivos

Currency (entero a escala)

8 bytes

-922,337,203,685,477.5808 a 922,337,203.685,477.5807

Tipo de datos Byte Boolean Integer Long (entero largo) Single (coma flotante/ precisión simple)

Intervalo

+/-79,228,162,514,264,337,593,543,950,335 sin punto decimal; +/-7.9228162514264337593543950335 con 28 posiciones a la derecha del signo decimal; el número más pequeño distinto de cero es +/-0.0000000000000000000000000001 1 de enero de 100 a 31 de diciembre de 9999 Cualquier referencia a tipo Object

Decimal

14 bytes

Date Object String (longitud variable) String (longitud fija) Variant (con números) Variant (con caracteres) Definido por el usuario (utilizando Type)

8 bytes 4 bytes 10 bytes + longitud de la Desde 0 a 2.000 millones cadena

Nota

Longitud de la cadena

Desde 1 a 65,400 aproximadamente

Cualquier valor numérico hasta el intervalo de un tipo Double 22 bytes + longitud de la El mismo intervalo que para un tipo String de cadena longitud variable 16 bytes

Número requerido por los elementos

El intervalo de cada elemento es el mismo que el intervalo de su tipo de datos.

Las matrices de cualquier tipo de datos requieren 20 bytes de memoria más cuatro bytes para cada dimensión de matriz, más el número de bytes que ocupan los propios datos. Puede calcular la memoria que ocupan los datos multiplicando el número de elementos de datos por el tamaño de cada elemento. Por ejemplo, los datos de una matriz unidimensional que consten de cuatro elementos de datos tipo Integer de dos bytes cada uno, ocupan ocho bytes. Los ocho bytes que requieren los datos más los 24 bytes necesarios para la matriz suman un requisito total de memoria de 32 bytes para dicha matriz.

Un tipo Variant que contiene una matiz requiere 12 bytes más que la matriz por sí sola. Nota

Use la funnción StrConv para converter un dato sting a otro tipo de dato


Pag. 140


Funciones de conversión de tipos de datos Cada función convierte una expresión a un tipo de datos específico. Sintaxis CBool(expresión)

CByte(expresión)

CCur(expresión)

CDate(expresión)

CDbl(expresión)

CDec(expresión)

CInt(expresión)

CLng(expresión)

CSng(expresión)

CStr(expresión)

CVar(expresión) Donde, el argumento obligatorio expresión es cualquier expresión de cadena o expresión numérica. Tipos devueltos El nombre de la función determina el tipo devuelto, como se muestra a continuación: Función CBool CByte CCur CDate

Tipo devuelto Boolean Byte Currency Date

CDbl

Double

CDec

Decimal

CInt

Integer

CLng

Long

CSng

Single

CStr

String

CVar

Variant

Intervalo del argumento expresión Cualquier expresión de cadena o numérica válida. 0 a 255. -922.337.203.685.477,5808 a 922.337.203.685.477,5807. Cualquier expresión de fecha. -1.79769313486231E308 a -4,94065645841247E-324 para valores negativos; 4,94065645841247E-324 a 1,79769313486232E308 para valores positivos. +/-79.228.162.514.264.337.593.543.950.335 para números basados en cero, es decir, números sin decimales. Para números con 28 decimales, el intervalo es +/-7,9228162514264337593543950335. La menor posición para un número que no sea cero es 0,0000000000000000000000000001. -32.768 a 32.767; las fracciones se redondean. -2.147.483.648 a 2.147.483.647; las fracciones se redondean. -3,402823E38 a -1,401298E-45 para valores negativos; 1,401298E-45 a 3,402823E38 para valores positivos. El mismo intervalo que Double para valores numéricos. El mismo intervalo que String para valores no numéricos. El valor de retorno de CStr depende del argumento expresión.

Comentarios Si la expresión del argumento expresión que se pasa a la función está fuera del intervalo del tipo de dato al que se va a convertir, se producirá un error.


Pag. 141


En general, el código se puede documentar utilizando las funciones de conversión de tipos de datos para indicar que el resultado de alguna de las operaciones se debería expresar como un tipo de datos en particular, no como el tipo de datos predeterminado. Por ejemplo, utilice Ccur para forzar la ejecución de operaciones con aritmética monetaria en los casos en los que se haría con precisión simple, doble precisión o aritmética entera. Debe utilizar funciones de conversión de tipos en lugar de Val para proporcionar conversiones que reconozcan las variantes internacionales. Por ejemplo, cuando utiliza CCur, se reconocen diferentes separadores decimales, diferentes separadores de millares y varias opciones monetarias dependiendo de la configuración regional establecida en su equipo. Cuando la parte fraccionaria es exactamente 0,5, CInt y CLng siempre redondean al número par más cercano. Por ejemplo, 0,5 redondea a 0, y 1,5 redondea a 2. CInt y CLng se diferencian de las funciones Fix y Int en que truncan la parte fraccionaria de un número, en lugar de redondear. Además, Fix y Int siempre devuelven un valor del mismo tipo del que se le pasa. Utilice la función IsDate para determinar si se puede convertir date a una fecha o una hora. CDate reconoce literales de fecha y literales de hora además de números comprendidos dentro del intervalo de fechas aceptables. Al convertir un número a una fecha, la parte numérica entera se convierte a una fecha. Cualquier parte fraccionaria del número se convierte a la hora del día, comenzando a medianoche. CDate reconoce formatos de fecha que se ajusten a la configuración regional de su sistema. Es posible que no se determine el orden correcto del día, mes y año si se proporciona en un formato diferente del que reconoce la configuración de fecha. Además, no se puede reconocer un formato de fecha largo si contiene la cadena del día de la semana. Se proporciona una función CVDate por compatibilidad con versiones anteriores de Visual Basic. La sintaxis de la función CVDate es idéntica a la de la función CDate; sin embargo, CVDate devuelve un Variant de subtipo Date en lugar del tipo Date real. Puesto que ahora hay un tipo de dato intrínseco Date, no es necesario CVDate. Se puede observar el mismo efecto al convertir una expresión a Date y asignarla después a un Variant. Esta técnica es coherente con la conversión de todos los demás tipos intrínsecos a sus equivalentes subtipos Variant. Nota

La función CDec no devuelve un tipo de dato discreto; en su lugar, siempre devuelve un Variant con los valores convertidos a un subtipo Decimal.

Conversión, resumen de palabras clave Acción Valor ANSI a cadena. Cadena a minúsculas o a mayúsculas. Fecha a número de serie. Número decimal a otra base. Número a cadena. Un tipo de dato a otro. Fecha a día, mes, día de la semana o año. Hora a hora, minuto o segundo. Cadena a valor ASCII. Cadena a número. Hora a número de serie.


Palabras clave Chr Format, LCase, UCase DateSerial, DateValue Hex, Oct Format, Str CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar, CVErr, Fix, Int Day, Month, Weekday, Year Hour, Minute, Second Asc Val TimeSerial, TimeValue

Pag. 142


Equivalencia de Funciones en Español e Inglés


Pag. 143


Teclas de Función en Microsoft Excel Función

Tecla F1

F2

F3

F4

F5 F6

F7

F8

F9

F10

F11 F12

Tecla

Abrir ventana de ayuda Modificar la celda activa Pegar un nombre en una formula Repetir la ultima acción Ir a Ir al siguiente Panel Menú Herramientas/ Ortografía Ampliar una selección de celdas Calcular todas la hojas de todos los libros abiertos Activar la barra de menú Crear un grafico Menú Archivo/ Guardar como

Shift

Ctrl Abrir o cerrar el Panel de Tareas

Modificar el comentario de la celda activa Pegar una función en una formula Repetir el ultimo Buscar (Buscar siguiente) Menú Edición/Buscar Ir al Panel Anterior

Agregar selecciones múltiples de celdas Calcular la hoja activa Presentar un menú contextual Insertar una nueva hoja de calculo Menú Archivo/ Guardar (Ctrl+G)

Función


Shift

Definir un nombre de rango Cerrar la ventana del libro actual Restaurar el tamaño de la ventana Ir a la siguiente ventana del libro Mover la ventana del libro actual

Alt Insertar una hoja de grafico Menú Archivo/Guardar como Menú Insertar/Nombre/Crear Cerrar la ventana de Excel

Ir a la primera ventana del libro anterior

Cambiar el tamaño de la ventana Minimizar la ventana del libro activo Maximizar o restaurar la ventana del libro actual Insertar una hoja de Macros (Excel 4.0) Menú Archivo/ Abrir

Mostrar la ventana de diálogo Macro

Ctrl

Alt

Abrir el Editor de Visual Basic

Pag. 144


Códigos ASCII para caracteres especiales: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

☺ ☻ ♥ ♦ ♣ ♠ • ◘ ○ ◙ ♂ ♀ ♪ ♫ ☼ ► ◄ ↕ ‼ ¶ § ▬ ↨ ↑ ↓

26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

→ ← ∟ ↔ ▲ ▼ ! " # $ % & ( ) * + , . 0 1 2 3

51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

3 76 4 77 5 78 6 79 7 80 8 81 9 82 : 83 ; 84 < 85 = 86 > 87 ? 88 @ 89 A 90 B 91 C 92 D 93 E 94 F 95 G 96 H 97 I 98 J 99 K 100

L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d

101 e 126 ~ 151 ù 176 ░ 102 f 127 ⌂ 152 ÿ 177 ▒ 103 g 128 Ç 153 Ö 178 ▓ 104 h 129 ü 154 Ü 179 │ 105 i 130 é 155 ø 180 ┤ 106 j 131 â 156 £ 181 Á 107 k 132 ä 157 Ø 182 Â 108 l 133 à 158 × 183 À 109 m 134 å 159 ƒ 184 © 110 n 135 ç 160 á 185 ╣ 111 o 136 ê 161 í 186 ║ 112 p 137 ë 162 ó 187 ╗ 113 q 138 è 163 ú 188 ╝ 114 r 139 ï 164 ñ 189 ¢ 115 s 140 î 165 Ñ 190 ¥ 116 t 141 Ä 166 ª 191 ┐ 117 u 142 Ä 167 º 192 └ 118 v 143 Å 168 ¿ 193 ┴ 119 w 144 É 169 ® 194 ┬ 120 x 145 æ 170 ¬ 195 ├ 121 y 146 Æ 171 ½ 196 ─ 122 z 147 ô 172 ¼ 197 ┼ 123 { 148 ö 173 ¡ 198 ã 124 | 149 ò 174 « 199 Ã 125 } 150 û 175 » 200 ╚

201 ╔ 226 202 ╩ 227 203 ╦ 228 204 ╠ 229 205 ═ 230 206 ╬ 231 207 ¤ 232 208 ð 233 209 Ð 234 210 Ê 235 211 Ë 236 212 È 237 213 ı 238 214 Í 239 215 Î 240 216 Ï 241 217 ┘ 242 218 ┌ 243 219 █ 244 220 ▄ 245 221 ¦ 246 222 Ì 247 223 ▀ 248 224 Ó 249 225 ß 250

Ô Ò õ Õ µ þ Þ Ú Û Ù ý Ý ¯ ´ ± ‗ ¾ ¶ ì ÷ ¸ ° ¨ ·

251 252 253 254 255

¹ ³ ² ■

Teclas de Método Abreviado: Alt + Enter.Ctrl + Enter.-

Para escribir en la siguiente línea dentro de la celda actual. Para escribir un mismo dato simultáneamente en todas las celdas previamente seleccionadas. Ctrl + asterisco.Para seleccionar la región actual. (Llámese región a un grupo rectangular de celdas ocupadas consecutivamente). Ctrl + G.Guarda el documento actual en el mismo destino donde fue guardado la última vez. Ctrl + espacio.Selecciona íntegramente todas las columnas del rango seleccionado. Shift + espacio.Selecciona íntegramente todas las filas del rango seleccionado. Ctrl + Shift + espacio.- Selecciona íntegramente todas las celdas de la hoja actual. Ctrl + Shift + ; .Escribe la fecha actual en la celda activa. Ctrl + Shift + : .Escribe la hora actual en la celda activa, pero como un dato del tipo texto. Ctrl + flecha.Desplaza el puntero de celda hasta la última celda ocupada en la dirección de la flecha. Ctrl + Shift + flecha.- A partir de la celda actual selecciona todas las celdas hasta la última celda ocupada en la dirección de la flecha.


Pag. 145

Curso Macros Excel Programacion VBA _ Daniel Zegarra Zavaleta.pdf

Recommend Documents