Curso Macros Excel Programacion VBA _ Daniel Zegarra Zavaleta.pdf

(1)

Curso: Macros Excel

Programación VBA

2016

Daniel Zegarra Zavaleta

(2)

Elaborado por: Daniel Zegarra Zavaleta Pag. 1

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

(3)

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

(4)

Elaborado por: Daniel Zegarra Zavaleta Pag. 3 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

(5)

Elaborado por: Daniel Zegarra Zavaleta Pag. 4 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

(6)

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)

(7)

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:

(8)

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:

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ú

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

(9)

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 llamando al menú

Herramientas/Macro/ Editor de Visual Basic.

La primera forma para ver una macro es llamando al menú

(10)

Elaborado por: Daniel Zegarra Zavaleta Pag. 9 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

(11)

Elaborado por: Daniel Zegarra Zavaleta Pag. 10 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.

(12)

(13)

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)

(14)

Elaborado por: Daniel Zegarra Zavaleta Pag. 13 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 en grupos semanales ordenados

descendentemente.

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

(15)

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.

(16)

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.

(17)

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

(18)

Elaborado por: Daniel Zegarra Zavaleta Pag. 17 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:

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

Guión bajo precedido de un espacio en blanco.

(19)

Elaborado por: Daniel Zegarra Zavaleta Pag. 18 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.

(20)

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.

(21)

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}

(22)

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

(23)

Elaborado por: Daniel Zegarra Zavaleta Pag. 22 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: [referencia].Select

Donde: 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: ActiveCell.Range(referencia).Select

Donde: 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

(24)

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

(25)

 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

(26)

h. Para desplazar el puntero de celda:

Uso de la sentencia Offset:

Sintaxis: Activecell.Offset(filas, columnas).Select

Donde: 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

(27)

 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: Referencia.Clear

Referencia.ClearContents

Referencia.ClearFormats

Donde: 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: Referencia.Delete

Donde: 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:

(28)

Sintaxis: Referencia.Insert

Donde: 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

(29)

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:

(30)

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.

(31)

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

(32)

Elaborado por: Daniel Zegarra Zavaleta Pag. 31 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

(33)

Elaborado por: Daniel Zegarra Zavaleta Pag. 32 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

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

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

(34)

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.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

(35)

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 hacia arriba

xlDown hacia abajo

xlToRight hacia la derecha

xlToLeft 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

(36)

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)

(37)

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.

(38)

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

(39)

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") = "CUENTAS" Range("A3") = "SERVICIOS" Range("B3") = "PAGOS" Range("B4") = 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.

(40)

Elaborado por: Daniel Zegarra Zavaleta Pag. 39 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

(41)

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:

SUMA como SUM

PROMEDIO como AVG

CONTAR como COUNT

ENTERO como INT

BUSCARV como VLOOKUP

SI como IF

REDONDEAR como ROUND

ESBLANCO como ISBLANK

Nota.- 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"

(42)

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.

(43)

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.

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.

(44)

Elaborado por: Daniel Zegarra Zavaleta Pag. 43 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()

(45)

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.

(46)

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.

2.- Nombre los siguientes rangos de celda:

B2  Fecha B3  Anterior B4  Deposito B5  Retiro B6  Actual

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)