Download - Programacion Vba Para Excel
PROGRAMACION VBA PARA EXCELINTRODUCCION:
Si bien la grabadora de macros es muy til y genera un cdigo siempre correcto, tiene dos desventajas: 1. genera mas cdigo que el necesario. 2. slo puede hacer macros con instrucciones secuenciales y sin nada de lgica, o sea que no pueden tomar desiciones ante un evento. Ambas desventajas se pueden solucionar con la programacin VBA que quiere decir programacin 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 programacin que est ntimamente relacionada con los libros y las hojas de clculo y para esto Excel cuenta con un editor de programacin donde se pone el cdigo, a este se puede acceder, en Excel 2007, yendo a la pestaa programador y luego a la seccin cdigo 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
ambas formas nos lleva, luego de hacer doble clic en Hoja1por ejemplo, al editor
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 cdigo
se escribe entre "Sub" y "End Sub" y que el nombre
no tiene espacios y termina con "( )" . Para ejecutar este cdigo 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 pantalla con el resultado , o seleccionando " Alta + F5 que nos lleva a la
que es la seleccin de la celda B5. Otro cdigo muy simple es escribir un valor en una celda. Escribamos el valor 2007 en la elda D8
y si lo queremos borrar
A estas alturas estamos en condiciones de explicar estos sencillos cdigos: En la programacin 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 cdigos 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 nmero (2007) y despues estar vaca.CODIGOS MAS SIMPLES PARA EMPEZAR 1-Seleccionar una Celda
Range("A1").Select2-Escribir en la celda que est seleccionada en el momento actual
Activecell.FormulaR1C1="Pedro" la combinacin los cdigos 1 y 2 es equivalente a esta sola lnea: Range("A1").Value=" pedro" El uso de FormulaR1C1 sera explicado mas adelante3-Letra Negrita
Selection.Font.Bold = True4-Letra Cursiva
Selection.Font.Italic = True
5-Letra Subrayada
Selection.Font.Underline = xlUnderlineStyleSingle6-Centrar Texto
With Selection .HorizontalAlignment = xlCenter End With7-Alinear a la izquierda
With Selection .HorizontalAlignment = xlLeft End With8-Alinear a la Derecha
With Selection .HorizontalAlignment = xlRight End With9-Tipo de Letra(Fuente)
With Selection .Font .Name = "Arial" End With10-Tamao de Letra(Tamao de Fuente)
With Selection.Font .Size = 12 End With11-Copiar
Selection.Copy12-Pegar
ActiveSheet.Paste
13-Cortar
Selection.Cut14-Ordenar Ascendente
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom15-Orden Descendente
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom16-Buscar
Cells.Find(What:="Csar", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate17-Insertar Fila
Selection.EntireRow.Insert18-Eliminar Fila
Selection.EntireRow.Delete19-Insertar Columna
Selection.EntireColumn.Insert20-Eliminar Columna
Selection.EntireColumn.Delete21-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 La mayoria de estos cdigos se pueden verificar con la grabadora de Macros. Significado de la FORMULA R1C1
La FORMULA R1C1 se emplea para colocar el resultado de una lnea de cdigo en la celda que actualmente est activa. Veamos el siguiente caso
supongamos que queremos sumar los nmeros de de la columna D y que el resultado aparezca en la celda F6 que es la que est seleccionada, el cdigo que se debera escribir es el siguiente
El parntesis destacado en rojo tiene por objetivo cubrir el tango donde estn los nmeros 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 funcin, 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 funcin y el parntesis el rango donde se aplica. Lo que se acaba de hacer es lo mismo que dolocar =SUMA(D1:D3) en la celda F6 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 iteracionFrecuentemente algunas lneas de cdigo de repiten muchas veces con el consiguiente aumento del tamao del programa. Esto se solucionado mediante los llamados estructuras de iteracin, tambin llamadas ciclos de repeticin o bucles.
Estos son:While - Wend
Do - While - Loop Do - Until --Loop For - Next
Estructuras de desicion:.If - Then - ElseSelect - Case
Estructura de iteracin Wile - WendINTRODUCCION:
Esta es una iteracin en la que no sabemos de antemano cuantas veces se repetir el ciclo por lo tanto debe haber una condicin para salir de el ya que en caso contrario el ciclo se repetira en forma indefinida. Para explicar While - Wend me parece que lo mejor es dar un ejemplo en el que iremos analizando cada lnea de cdigo. Vamos a sumar un rango de nmeros que estn ubicados en una columna. En la hoja de clculo solo debemos poner los nmeros (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deber quedar en la primera celda vaca. En este caso sumaremos una cantidad indefinida de nmeros naturales los que introducimos en la columna "D" empezando por la "D1" y finalizando con la introduccin del N 283226 como se ve en la figura
el programa VBA es
Explicacin del cdigo (los nmeros representan las lneas de cdigo) 1- Siempre se empieza con Sub seguida del nombre de la macro y 2 parntesis (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 lnea es la condicin para entrar o salir del While y quiere decir "mientras la celda activa no este vaca", 5- Aqu ya entramos en el While donde hay 2 lneas que se repiten hasta que la condicin no se cumpla. 6- La primera lnea dentro del While hace bajar una posicin 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 segn se cumpla o no la condicin 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 lnea se cierra el programa Finalmente una vez que aplicamos la macro, la hoja nos queda
Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden.
Estructura de iteracin Do-Wile-Loop
INTRODUCCION:
Esta es una iteracin 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 condicin para salir de el ya que en caso contrario este se repetira en forma indefinida. La evaluacin de la condicin se produce antes de entrar al ciclo. Para explicar Do-Wile-Loop utilizaremos el mismo ejemplo que en la estructura WhileWend. Vamos a sumar un rango de nmeros que estn ubicados en una columna. En la hoja de clculo solo debemos poner los nmeros (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deber quedar en la primera celda vaca. En este caso sumaremos una cantidad indefinida de nmeros naturales los que introducimos en la columna "D" empezando por la "D1" y finalizando con la introduccin del N 283226 como se ve en la figura
el programa VBA es
Explicacin del cdigo (los nmeros representan las lneas de cdigo) 1- Siempre se empieza con Sub seguida del nombre de la macro y 2 parntesis (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 lnea es la condicin para entrar o salir del While y quiere decir "hacer mientras la celda activa no este vaca", 5- Aqu ya entramos en el Do-While donde hay 2 lneas que se repiten hasta que la condicin no se cumpla. 6- La primera lnea dentro del While hace bajar una posicin 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 segn se cumpla o no la condicin. 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 lnea se cierra el programa. Cdigo para copiar y pegar Sub SumaColumna() Range("D1").Select Sum = Range("D1")
Do While ActiveCell.Value "" ActiveCell.Offset(1, 0).Select Sum = Sum + ActiveCell.Value Loop ActiveCell.Value = Sum MsgBox Sum End Sub Finalmente una vez que aplicamos la macro, la hoja nos queda
Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden y lgicamenteal dan el mismo resultado que usando la estructura Wile-Wend.
Estructura de iteracin Do-Until-LoopINTRODUCCION:
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 DoUntil-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 nmeros que estn ubicados en una columna. En la hoja de clculo solo debemos poner los
nmeros (sin dejar espacios en blanco) . Luego de aplicar la macro el resultado deber quedar en la primera celda vaca. Tambien en este caso sumaremos los mismos nmeros que en los ejemplos de Do-WhileLoop y While-Wend que introducimos en la columna "D" empezando por la "D1" y finalizando con la introduccin del N 283226 como se ve en la figura
el programa VBA es
Explicacin del cdigo (los nmeros representan las lneas de cdigo)
1- Siempre se empieza con Sub seguida del nombre de la macro y 2 parntesis (SumaColumna2()). 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 lnea es la condicin 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 lneas que se repiten hasta que la condicin no se cumpla. 6- La primera lnea dentro del While hace bajar una posicin 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 segn se cumpla o no la condicin 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 lnea se cierra el programa Finalmente una vez que aplicamos la macro, la hoja nos queda
Se puede ver que los resultados en la celda activa y en la caja de mensajes coinciden.
Estructura de iteracin For-NextINTRODUCCION: Esta es una iteracin en la que sabemos de antemano cuantas veces se repetir el ciclo, por lo tanto no hace falta una condicin para salir del mismo. Para explicar For daremos un ejemplo muy simple: Vamos a sumar los primeros 10 nmeros naturales. En la hoja de clculo solo debemos poner los nmeros (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 vaca , este debe ser 55 como se puede verificar haciendo la suma manualmente. Aprovechando las propiedades de For podemos poner los nmeros desde el cdigo
el programa VBA es
Explicacin del cdigo (los nmeros representan las lneas de cdigo) 1- Siempre se empieza con Sub seguida del nombre de la macro y 2 parntesis (Sum()). 2- El primer For es para colocar en la columna D los diez primeros nmeros 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 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 lnea hace bajar una posicin la celda seleccionada, de esta forma podemos acceder a su contenido y la segunda agrega este contenido a la variable "sum". 10-En esta lnea podemos salir o volver a entrar al For segn 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 calculo como se ve abajo
Estructura de decisin If - Then - ElseINTRODUCCION: La estructura de decisin se llama as pues puede, luego de evaluar una condicin, ejecutar un bloque de cdigo u otro.
Vamos a ver algunos ejemplos; 1- Comparar 2 nmeros 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 cdigo
Explicacin del cdigo 1-Ttulo. 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 cdigo de la lnea 3. 3-Se coloca en la celda "D4" el texto "Los valores de D1 y E1 son iguales".. 4-Sino se da la condicin de la lnea 2 se ejecuta el cdigo de la lnea 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
Cdigo para copiar y pegar Sub Condicional() If ActiveSheet.Range("D1").Value = ActiveSheet.Range("E1").Value Then ActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales" Else ActiveSheet.Range("D4").Value = "D1 es distinto que E1" End If End 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 lnea 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
Cdigo:
Notar que el segundo If se puso en la parte correspondiente al Else y se cierra antes que el primer If. Se puede tambin ver que hemos identado el cdigo para mayor claridad (cosa que recomiendo enfticamente)
Resultado
Cdigo para copiar y pegar:Sub Anidamiento() If ActiveSheet.Range("D1").Value = ActiveSheet.Range("E1").Value Then ActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales" Else If ActiveSheet.Range("D1").Value > ActiveSheet.Range("E1").Value Then
ActiveSheet.Range("D4").Value = "D1 es mayor que E1" Else ActiveSheet.Range("D4").Value = "E1 es mayor que D1" End If End If End Sub Se puede ver que este cdigo no est identado por lo que sugiero hacerlo como ejercicio.
Estructura de desicin Select-CaseINTRODUCCION: La estructura de decisin If-Then-Else puede anidarse y como este anidamiento se puede repetir tantas veces como el problema lo exija, a veces el cdigo suele hacerse confuso y frecuentemente da lugar a errores, en estos casos se puede recurrir a la estructura de decisin 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 comparbamos 2 nmeros ubicados en las celdas D1 y E1 y el programa deba responder si estos son iguales, mayores o menores, ubicando la respuesta en la celda D4 El cdigo es
escencialmente este cdigo evala el valor actual de la celda "D1" y se escribe un cdigo 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").Value
Select Case Range("D1").Value Case Is = A1 ActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales" Case Is > A1 ActiveSheet.Range("D4").Value = "D1 es mayor que E1" Case Is < A1 ActiveSheet.Range("D4").Value = "E1 es mayor que D1" End Select End Sub La utilidad esencial de esta estructura se manifiesta cuando los casos que se evalan 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 Distinguido= 9 Sobresaliente= 10 se puede responder a estas preguntas aplicando este cdigo
CODIGO PARA COPIAR Y PEGAR Sub SelectCase() a = Range("A2").Value Select Case a Case 1 To 3 ActiveSheet.Range("B2").Value = "APLAZADO" Range("E1").Select Case 4 To 5 ActiveSheet.Range("B2").Value = "APROBADO" Range("E1").Select Case 6 To 7 ActiveSheet.Range("B2").Value = "BUENO" Range("E1").Select Case 8 ActiveSheet.Range("B2").Value = "MUY BUENO" Range("E1").Select Case 9 ActiveSheet.Range("B2").Value = "DISTINGUIDO" Range("E1").Select Case 10 ActiveSheet.Range("B2").Value = "SOBRESALIENTE" Range("E1").Select
End Select End Sub El resultado final queda como se ve en la figura, en este caso hemos agregado un botn para disparar la macro, el cual esta sealado por la flecha roja
EJERCICIOSEsta es un recopilacin de ejercicios originales de los niveles bsico, medio y avanzado NIVEL BASICO Ejercicio 1: 1. Dada la factura del comercio " ELEGANT" completar los espacios vacos
Utilizar FORMULAS y la funcin SUMA2.
Completar los espacios utilizando la funcin SUMAPRODUCTO
Ejercicio 2 Mejorar el aspecto y funcionalidad de la factura del comercio "ELEGANT" , incorporando la fecha actual, un slogan y un logotipo. Utilizar la funcin HOY y ver el tutorial INSERCION DE IMAGENES1.
NIVEL MEDIO Ejercicio 1: Un comercio dispone de la siguiente tabla con las ventas del mes Enero de sus empleados correspondientes a las sucursales A y B
se quiere saber: La cantidad de empleados de cada sucursal (Funcin CONTAR.SI) La cantidad total de empleados. Usar la funcin CONTARA La cantidad total vendida (funcin SUMA) Cul fue la mayor venta ( funcin MAX ) Cul fue la menor venta ( funcin MIN ) El promedio de ventas de ambas sucursales ( funcin PROMEDIO) 7. El promedio de ventas de la sucursal A. 8. El promedio de ventas de la sucursal B. 9. Cul fue la mxima venta de la sucursal A? 10. Cual fue la mxima venta de la sucursal B? Para los puntos 7,8,9 y 10 usar FORMULAS MATRICIALES1. 2. 3. 4. 5. 6.
Ejercicio 2: Empleando la misma tabla que en el ejercicio anterior, averiguar1.
2.
Cunto vendieron los empleados de la sucursal A, ( funcin SUMAR,SI) Cunto vendieron los empleados de la sucursal B.
Ejercicio 3: Con la tabla del ejercicio 1 se quiere saber 1. 2. 3. 4. Cul fue el empleado que vendi por $ 24.000. Cul fue el empleado que vendi mas de ambas sucursales. Cul fue el empleado que vendi mas de la sucursal A, y cual fue el monto Cul fue el empleado que vendi mas de la sucursal B, y cual fue el monto
Ejercicio 4:
Con la tabla del ejercicio 1: 1. Hacer un grfico de barras que represente las ventas que hicieron en ambas sucursales. Ponerle el ttulo " Ventas de sucursales mes de Enero". 2. Hacer un grfico de barras que represente las ventas que hicieron los empleados de ambas sucursales. Ponerle el ttulo " Ventas de empleados mes de Enero". 3. Imprimir el documento. Ejercicio 5 : Considerando el mismo comercio de los ejercicios anteriores El negocio est prosperando y se ve obligado a contratar nuevos empleados durante el mes de Enero(se pueden colocar los nuevo empleados en cualquier lugar de la tabla). Se pide: Hacer los mismos clculos del ejercicio 1, pero teniendo en cuenta esta nueva circunstancia( Rangos variables) 2. Hacer los grficos correspondientes( Grficos con rangos variables)1.
Ejercicio 6 A cuales de las preguntas de los Ejercicios 1, 2, 3, 4 y 5 se pueden responder usando Tablas dinmicas 2. Responder las respuestas del punto 1 empleando Tablas Dinmicas.1.
Ejercicio 7 Mejorar la funcionalidad de la factura del comercio "ELEGANT". 1. 2. Incorporando el N de factura. Haciendo que los campos de la misma sean variables.
(Este ejercicio se resuelve con macros). Ejercicio 8 La lnea area Avianc tiene un monitor, en el aeropuerto de Alicante, donde los pasajeros pueden consultar el importe de los diferentes destinos como as tambin si estos tienen descuentos. Los precios y descuentos estn en una lista que est en la hoja llamada PRECIOS Y DESCUENTOS y los destinos otra llamada DESTINOS DESDE ALICANTE. Se pide:
1. Confeccionar una pantalla como la mostrada en la figura, donde se muestra un destino como ejemplo y donde se inserta el logotipo de AVIANC (funcin BUSCARV) .
2. Colocar un hipervnculo hacia la pgina DESTINOS DESDE ALICANTE. Se aclara que los diferentes destinos se superponen con el que est escrito. Ejercicio 9 Si se introduce un destino que no est en lista de la lnea area, deber aparecer en lugar de "DESCUENTOS", la frase, "NO EN DESTINOS"
SOLUCIONES A EJERCICIOSSoluciones a los ejercicios propuestos en la pagina correspondiente. Las solucines se encuentran en archivos que se pueden descargar de la red. NIVEL BASICO Ejercicio 1 Ejercicio 2-1 En este archivo est la solucin final y una carpeta "images" que contiene la imagen "elegant.gif" la cual debe ser copiada ( o ponerla en el portapapeles) para luego pegar en algun archivo donde Excel pueda buscarla. NIVEL MEDIO
Ejercicio 1 Ejercicio 2 Ejercicio 3 Ejercicio 4 Ejercicio 5-1 Ejercicio 8 5-2