30
PROGRAMACION VBA PARA EXCEL INTRODUCCION: Si bien la grabadora de macros es muy útil y genera un código siempre correcto, tiene dos desventajas: 1. genera mas código que el necesario. 2. sólo puede hacer macros con instrucciones secuenciales y sin nada de lógica, o sea que no pueden tomar desiciones ante un evento. Ambas desventajas se pueden solucionar con la programación VBA que quiere decir programación visual basic para aplicaciones, lo lamento pero aquí no tenemos mas remedio que aprender a programar y eso es lo que van a ir aprendiendo con los tutorarles de este apartado. VBA es una programación que está íntimamente relacionada con los libros y las hojas de cálculo y para esto Excel cuenta con un editor de programación donde se pone el código, a este se puede acceder, en Excel 2007, yendo a la pestaña programador y luego a la sección código donde hacemos clic en Visual Basic En Excel 2003 hay que ir al menú desplegable herramientas y de ahí la ruta macros y Editor de Visual Basic

PROGRAMACION VBA PARA EXCEL

Embed Size (px)

Citation preview

Page 1: PROGRAMACION VBA PARA EXCEL

PROGRAMACION VBA PARA EXCEL 

INTRODUCCION:

Si bien la grabadora de macros es muy útil y genera un código siempre correcto, tiene dos desventajas:

 

1. genera mas código que el necesario.2. sólo puede hacer macros con instrucciones secuenciales y sin nada de lógica, o sea que

no pueden tomar desiciones ante un evento.

Ambas desventajas se pueden solucionar con la programación VBA que quiere decir programación visual basic para aplicaciones, lo lamento pero aquí no tenemos mas remedio que aprender a programar y eso es lo que van a ir aprendiendo con los tutorarles de este apartado.

VBA es una programación que está íntimamente relacionada con los libros y las hojas de cálculo y para esto Excel cuenta con un editor de programación donde se pone el código, a este se puede acceder, en Excel 2007, yendo a la pestaña programador y luego a la sección código donde hacemos clic en Visual Basic

En Excel 2003 hay que ir al menú desplegable herramientas y de ahí la ruta macros y Editor de Visual Basic

Page 2: PROGRAMACION VBA PARA EXCEL

ambas formas nos lleva, luego de hacer doble clic en Hoja1por ejemplo, al editor

Page 3: PROGRAMACION VBA PARA EXCEL

las macros que se escriban aquí, estaran relacionadas con la Hoja1.

Comencemos por lo mas simple y escribamos una macro que seleccione la celda B5de la Hoja1 del libro VBAProject (Libro2)

donde podemos ver que el código

Page 4: PROGRAMACION VBA PARA EXCEL

se escribe entre "Sub" y "End Sub" y que el nombre

no tiene espacios y termina con  "( )" . Para ejecutar este código pulsamos en el icono

o en la tecla F5 para que aparezca el panel Macros

donde puede verse el nombre de la macro que ya está seleccionada, luego pulsamos en

"ejecutar" y despues en el icono , o seleccionando  " Alta + F5 que nos lleva a la pantalla con el resultado

que es la selección de la celda B5.

Otro código muy simple es escribir un valor en una celda.

Escribamos el valor 2007 en la elda D8

Page 5: PROGRAMACION VBA PARA EXCEL

y si lo queremos borrar

A estas alturas estamos en condiciones de explicar estos sencillos códigos:

En la programación VBA se trabaja con OBJETOS ( Hojas, celdas, Rangos, etc) que como todo objeto,  tiene propiedades,  por ejemplo el objeto celda pude tener la propiedad de alto, ancho, estar seleccionada, tener un valor, o no tener ninguno, etc

En los códigos que hemos escrito  tenemos los objetos Range("B5") ( celda B5) con la propiedad de estar seleccionada y  el objeto Range("D8") ( celda D8) con la propiedad de tener un número (2007) y despues  estar vacía.

CODIGOS MAS SIMPLES PARA EMPEZAR

1-Seleccionar una Celda

Range("A1").Select

2-Escribir en la celda que está seleccionada en el momento actual

Activecell.FormulaR1C1="Pedro"

la combinación  los códigos 1 y 2 es equivalente a esta sola línea:

Range("A1").Value=" pedro"

El uso de   FormulaR1C1  sera explicado mas adelante

3-Letra Negrita

Selection.Font.Bold = True

4-Letra Cursiva

Page 6: PROGRAMACION VBA PARA EXCEL

Selection.Font.Italic = True

5-Letra Subrayada

Selection.Font.Underline = xlUnderlineStyleSingle

6-Centrar Texto

With Selection

           .HorizontalAlignment = xlCenter

End With

7-Alinear a la izquierda

With Selection

              .HorizontalAlignment = xlLeft

End With

8-Alinear a la Derecha

With Selection

            .HorizontalAlignment = xlRight

End With

9-Tipo de Letra(Fuente)

With Selection

.Font .Name = "Arial"

End With

10-Tamaño de Letra(Tamaño de Fuente)

With Selection.Font .Size = 12

End With

11-Copiar

Selection.Copy

Page 7: PROGRAMACION VBA PARA EXCEL

12-Pegar

ActiveSheet.Paste

13-Cortar

Selection.Cut

14-Ordenar Ascendente

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

15-Orden Descendente

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

16-Buscar

Cells.Find(What:="César", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate

17-Insertar Fila

Selection.EntireRow.Insert

18-Eliminar Fila

Selection.EntireRow.Delete

19-Insertar Columna

Selection.EntireColumn.Insert

20-Eliminar Columna

Selection.EntireColumn.Delete

21-Abrir un Libro

Workbooks.Open Filename:="C:\Mis documentos\Tablas dinamicas.xls"

22-Grabar un Libro

ActiveWorkbook.SaveAs Filename:="C:\Mis documentos\tablas.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False

Page 8: PROGRAMACION VBA PARA EXCEL

La mayoria de estos códigos se pueden verificar con la grabadora   de Macros.

 

Significado de la FORMULA R1C1

La FORMULA R1C1 se emplea para colocar el resultado de una línea de código en la celda que actualmente está activa.

Veamos el siguiente caso

 

supongamos que queremos sumar los números de de la columna D y que el resultado aparezca en la celda F6 que es la que está seleccionada, el código que se debería escribir es el siguiente

 

El paréntesis destacado en rojo tiene por objetivo cubrir el tango donde están los números a sumar, o sea, desplazarme 2 columnas a la izquierda [-2] con 5 y 2 filas hacia arriba es decir

[-5] y [-2]. Se entiende que R significan filas y C columnas y que anteponemos un - si nos desplazamos hacia la izquierda o hacia arriba. Cuando escribimos una función, como en el caso anterior, siempre debe ser escrita ActiveCell.FormulaR1C1 = "=SUM(R[]C[]:R[]C[])", pues el segundo igual es que caracteriza a la función y el paréntesis el rango donde se aplica. Lo que se acaba de hacer es lo mismo que dolocar =SUMA(D1:D3) en la celda F6

Page 9: PROGRAMACION VBA PARA EXCEL

Hasta ahora hemos hecho una breve intrcduccion a la programacion VBA , pero una cosa fundamental es entender las estructuras de control de flujo de programa, lo que haremos mediante ejemplos

Estructuras de iteracion

Frecuentemente algunas líneas de código de repiten muchas veces con el consiguiente aumento del tamaño del programa. Esto se solucionado mediante los llamados estructuras de iteración, también llamadas ciclos de repetición o bucles.

Estos son:

While - Wend

Do - While - Loop

Do - Until --Loop

For - Next

Estructuras de desicion:

 

.If - Then - Else

Select - Case

Estructura de iteración Wile - Wend 

INTRODUCCION:

Esta es una iteración en la que no sabemos de antemano cuantas veces se repetirá el ciclo por lo tanto debe haber una condición para salir de el ya que en caso contrario el ciclo se repetiría en forma indefinida.

Para explicar While - Wend me parece que lo mejor es dar un ejemplo en el que iremos analizando cada línea de código.

Vamos a sumar un rango de números que están ubicados en una columna. En la hoja de cálculo solo debemos poner los números (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deberá quedar en la primera celda vacía.

Page 10: PROGRAMACION VBA PARA EXCEL

En este caso sumaremos una cantidad indefinida de números naturales los que introducimos en la columna "D" empezando por la "D1" y finalizando con la introducción del Nº 283226 como se ve en la figura

 

el programa VBA es

 

Page 11: PROGRAMACION VBA PARA EXCEL

Explicación del código (los números representan las líneas de código)

1- Siempre se empieza con Sub seguida del nombre de la macro y 2 paréntesis (Sum()).

2- Seleccionamos la celda superior. "D1".

3- Asignamos a la variable “sum” (que es en la que quedará contenido el resultado) el valor de la celda superior.

4- Esta línea es la condición para entrar o salir del While y quiere decir "mientras la celda activa no este vacía",

5- Aquí ya entramos en el While donde hay 2 líneas que se repiten hasta que la condición no se cumpla.

6- La primera línea dentro del While hace bajar una posición la celda seleccionada, de esta forma podemos acceder a su contenido y la segunda agrega este contenido a la variable "sum".

7-Con Wend salimos o volvemos a entrar al ciclo según se cumpla o no la condición

8- Una vez que salimos del ciclo colocamos el resultado queda en una celda activa.

9-Tanbien mostramos el resultado mediante un MsgBox (caja de mensajes)

10- Con esta línea se cierra el programa

 

Finalmente una vez que aplicamos la macro, la hoja nos queda

 

Page 12: PROGRAMACION VBA PARA EXCEL

 

Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden

Estructura de iteración Do-Wile-Loop 

INTRODUCCION:

Esta es una iteración en la que no sabemos de antemano cuantas veces se repetirá el ciclo al igual en la estructura While-Wend y como en esta debe haber una condición para salir de el ya que en caso contrario este se repetiría en forma indefinida. La evaluación de la condición se produce antes  de entrar al ciclo.

Para explicar Do-Wile-Loop utilizaremos el mismo ejemplo que en la estructura While-Wend.

Vamos a sumar un rango de números que están ubicados en una columna. En la hoja de cálculo solo debemos poner los números (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deberá quedar en la primera celda vacía.

Page 13: PROGRAMACION VBA PARA EXCEL

En este caso sumaremos una cantidad indefinida de números naturales los que introducimos en la columna "D" empezando por la "D1" y finalizando con la introducción del Nº 283226 como se ve en la figura

 

el programa VBA es

 

Explicación del código (los números representan las líneas de código)

Page 14: PROGRAMACION VBA PARA EXCEL

1- Siempre se empieza con Sub seguida del nombre de la macro y 2 paréntesis (SumColumna()).

2- Seleccionamos la celda superior. "D1".

3- Asignamos a la variable “sum” (que es en la que quedará contenido el resultado) el valor de la celda superior.

4- Esta línea es la condición para entrar o salir del While y quiere decir "hacer mientras la celda activa no este vacía",

5- Aquí ya entramos en el Do-While donde hay 2 líneas que se repiten hasta que la condición no se cumpla.

6- La primera línea dentro del While hace bajar una posición la celda seleccionada, de esta forma podemos acceder a su contenido y la segunda agrega este contenido a la variable "sum".

7-Con Loop salimos o volvemos a entrar al ciclo según se cumpla o no la condición.

8- Una vez que salimos del ciclo colocamos el resultado queda en una celda activa.

9-Tanbien mostramos el resultado mediante un MsgBox (caja de mensajes).

10- Con esta línea se cierra el programa.

 

Código para copiar y pegar

Sub SumaColumna()Range("D1").SelectSum = Range("D1")Do While ActiveCell.Value <> ""ActiveCell.Offset(1, 0).SelectSum = Sum + ActiveCell.ValueLoopActiveCell.Value = SumMsgBox SumEnd Sub

 

Finalmente una vez que aplicamos la macro, la hoja nos queda

 

Page 15: PROGRAMACION VBA PARA EXCEL

 

Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden y lógicamenteal dan el mismo resultado que usando la estructura Wile-Wend

Estructura de iteración Do-Until-Loop 

INTRODUCCION:

Do-Until-Loop es similar a Do-While-Loop y a While-Wend, la diferencia esta en la en la forma en que expresamos la condicion: por ejemplo en Do-While-Loop y While-Wend la condicion podria ser " ejecutar el codigo mientras que la celda no este vacia" y en Do-Until-Loop seria "ejecutar el codigo hasta que la celda este vacia".

Para comparar con Do-While-Loop y While-Wend vamos a sumar un rango de números que están ubicados en una columna. En la hoja de cálculo solo debemos poner los números (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deberá quedar en la primera celda vacía.

Tambien en este caso sumaremos los mismos números que en los ejemplos de Do-While-Loop y While-Wend que introducimos en la columna "D" empezando por la "D1" y finalizando con la introducción del Nº 283226 como se ve en la figura

Page 16: PROGRAMACION VBA PARA EXCEL

 

el programa VBA es

 

Explicación del código (los números representan las líneas de código)

1- Siempre se empieza con Sub seguida del nombre de la macro y 2 paréntesis (SumaColumna2()).

2- Seleccionamos la celda superior. "D1".

Page 17: PROGRAMACION VBA PARA EXCEL

3- Asignamos a la variable “sum” (que es en la que quedará contenido el resultado) el valor de la celda superior.

4- Esta línea es la condición para entrar o salir del While y quiere decir "ejecutar el codigo hasta que la celda este vacia",

5- Aquí ya entramos en el While donde hay 2 líneas que se repiten hasta que la condición no se cumpla.

6- La primera línea dentro del While hace bajar una posición la celda seleccionada, de esta forma podemos acceder a su contenido y la segunda agrega este contenido a la variable "sum".

7-Con Wend Loop salimos o volvemos a entrar al ciclo según se cumpla o no la condición

8- Una vez que salimos del ciclo colocamos el resultado que queda en una celda activa.

9-Tanbien mostramos el resultado mediante un MsgBox (caja de mensajes)

10- Con esta línea se cierra el programa

 

Finalmente una vez que aplicamos la macro, la hoja nos queda

 

Page 18: PROGRAMACION VBA PARA EXCEL

 

Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden

Estructura de iteración For-Next 

INTRODUCCION:

Esta es una iteración en la que sabemos de antemano cuantas veces se repetirá el ciclo, por lo tanto no hace falta una condición para salir del mismo.

Para explicar For daremos un ejemplo muy simple:

Vamos a sumar los primeros 10 números naturales. En la hoja de cálculo solo debemos poner los números (sin dejar espacios en blanco) en una celda que en este caso será la D4. Luego de aplicar la macro el resultado deberá quedar en la primera celda vacía , este debe ser 55 como se puede verificar haciendo la suma manualmente.

Aprovechando las propiedades de For podemos poner los números desde el código

 

Page 19: PROGRAMACION VBA PARA EXCEL

el programa VBA es

 

Explicación del código (los números representan las líneas de código)

1- Siempre se empieza con Sub seguida del nombre de la macro y 2 paréntesis (Sum()).

2- El primer For es para colocar en la columna D los diez primeros números naturales y significa: para la variable i yendo de 1 hasta 10

3-Se coloca en la celda (i,4) el valor actual de i que se repite al entrar nuevamente en el For

Page 20: PROGRAMACION VBA PARA EXCEL

4- Con Next i se incrementa i en 1 y se vuelve a entrar al For

5-Se selecciona la celda "D1"

6- Se coloca en la variable sum el contenido de la celda "D1" ( Sum acumula la suma de los 10 primeros nº narutales)

7-Se entra al 2º For

8 y 9-Dentro del For la primera línea hace bajar una posición la celda seleccionada, de esta forma podemos acceder a su contenido y la segunda agrega este contenido a la variable "sum".

10-En esta línea podemos salir o volver a entrar al For según se haya llegado a 10 o no.

11- En la celda activa colocamos el valor de sum.

12-Tambien mostramos el resultado mediante un MsgBox.

13-Se termina el programa.

 

Quedando la hoja de cálculo como se ve abajo

Estructura de decisión  If - Then - Else

Page 21: PROGRAMACION VBA PARA EXCEL

 INTRODUCCION:

La estructura de decisión se llama así pues puede, luego de evaluar una condición, ejecutar un bloque de código u otro.

Vamos a ver algunos ejemplos;

1- Comparar 2 números ubicados en 2 celdas, y responder, en una tercera celda, si son iguales o no

Compararemos los Nº 59 y 63 ubicados en las celdas D1 y E1 respectivamente

para responder a la pregunta utilizaremos el siguiente código

 

Explicación del código

1-Título.

2-Si el valor del Nº que está en la celda "D1" es igual al que está en la celda "E1" , entonces se ejercita el código de la línea 3.

Page 22: PROGRAMACION VBA PARA EXCEL

3-Se coloca en la celda "D4" el texto "Los valores de D1 y E1 son iguales"..

4-Sino se da la condición de la línea 2 se ejecuta el código de la línea 5.

5 -Se coloca en la celda "D4" el texto "D1 es distinto que E1".

6-Se cierra el If.

7-Se cierra el Sub

Código para copiar y pegar

Sub Condicional()If ActiveSheet.Range("D1").Value = ActiveSheet.Range("E1").Value ThenActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales"ElseActiveSheet.Range("D4").Value = "D1 es distinto que E1"End IfEnd Sub

En nuestro caso el resultado se tiene que ver como en la figura

 

 

Esta estructura puede anidarse, lo que quiere decir poner otro If en la línea 3, 5 o ambas. Esto es necesario al querer averiguar si los Nº son mayores, iguales o distintos, para hacerlo generamos otra macro en el mismo libro que llamaremos, por ejemplo, Anidamiento

 

Código:

Page 23: PROGRAMACION VBA PARA EXCEL

Notar que el segundo If se puso en la parte correspondiente al Else y se cierra antes que el primer If. Se puede también ver que hemos identado el código para mayor claridad (cosa que recomiendo enfáticamente)

Resultado

 

 

Código para copiar y pegar:

 

Page 24: PROGRAMACION VBA PARA EXCEL

Sub Anidamiento()If ActiveSheet.Range("D1").Value = ActiveSheet.Range("E1").Value ThenActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales"ElseIf ActiveSheet.Range("D1").Value > ActiveSheet.Range("E1").Value ThenActiveSheet.Range("D4").Value = "D1 es mayor que E1"ElseActiveSheet.Range("D4").Value = "E1 es mayor que D1"End IfEnd IfEnd Sub

 

Se puede ver que este código no está identado por lo que sugiero hacerlo como ejercicio.

 

Estructura de desición Select-Case 

INTRODUCCION:

La estructura de decisión If-Then-Else puede anidarse y como este anidamiento se puede repetir tantas veces como el problema lo exija, a veces el código suele hacerse confuso y frecuentemente da lugar a errores, en estos casos se puede recurrir a la estructura de decisión Select-Case.

En primer lugar veremos que funciona igual que If-Then-Else, para lo que utilizaremos el mismo ejemplo que If-Then-Else en la parte en que comparábamos 2 números ubicados en las celdas D1 y E1 y el programa debía responder si estos son iguales, mayores o menores, ubicando la respuesta en la celda D4

El código es

 

Page 25: PROGRAMACION VBA PARA EXCEL

escencialmente este código evalúa el valor actual de la celda "D1" y se escribe un código diferente de acuerdo al caso de que este valor sea = , > o < que el valor actual de la celda "E1"

CODIGO PARA COPIAR Y PEGAR

Sub SelectCase()A1 = Range("E1").ValueSelect Case Range("D1").ValueCase Is = A1ActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales"Case Is > A1ActiveSheet.Range("D4").Value = "D1 es mayor que E1"Case Is < A1ActiveSheet.Range("D4").Value = "E1 es mayor que D1"End SelectEnd Sub

La utilidad esencial de esta estructura se manifiesta cuando los casos que se evalúan son mas numerosos como veremos en el siguiente ejemplo.

Introducir en una celda la nota de un alumno y en otra una leyenda que diga si esta aplazado, aprobado y en caso de estar aprobado si su nota fue buena, muy buena, distinguida o sobresaliente, teniendo en cuenta que:

Aplazado= 1,2,3

Aprobado= 4,5

Bueno= 6,7

Muy bueno= 8

Page 26: PROGRAMACION VBA PARA EXCEL

Distinguido= 9

Sobresaliente= 10

se puede responder a estas preguntas aplicando este código

 

 

CODIGO PARA COPIAR Y PEGAR

Sub SelectCase()a = Range("A2").ValueSelect Case aCase 1 To 3ActiveSheet.Range("B2").Value = "APLAZADO"Range("E1").SelectCase 4 To 5ActiveSheet.Range("B2").Value = "APROBADO"Range("E1").SelectCase 6 To 7ActiveSheet.Range("B2").Value = "BUENO"

Page 27: PROGRAMACION VBA PARA EXCEL

Range("E1").SelectCase 8ActiveSheet.Range("B2").Value = "MUY BUENO"Range("E1").SelectCase 9ActiveSheet.Range("B2").Value = "DISTINGUIDO"Range("E1").SelectCase 10ActiveSheet.Range("B2").Value = "SOBRESALIENTE"Range("E1").SelectEnd SelectEnd Sub

El resultado final queda como se ve en la figura, en este caso hemos agregado un botón para disparar la macro, el cual esta señalado por la flecha roja