PROGRAMACIN VBA PARA EXCEL
INTRODUCCION:
Si bien la grabadora de macros es muy til y genera un cdigo siempre correcto, tiene dos desventajas: 1. 2. genera mas cdigo que el necesario. 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 que aparezca el panel Macros
o en la tecla F5 para
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 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").Select
2-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 adelante
3-Letra Negrita
[Link] = True
4-Letra Cursiva
[Link] = True
5-Letra Subrayada
[Link] = 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-Tamao de Letra(Tamao de Fuente)
With [Link] .Size = 12 End With
11-Copiar
[Link]
12-Pegar
[Link]
13-Cortar
[Link]
14-Ordenar Ascendente
[Link] Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
15-Orden Descendente
[Link] Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
16-Buscar
[Link](What:="Csar", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate
17-Insertar Fila
[Link]
18-Eliminar Fila
[Link]
19-Insertar Columna
[Link]
20-Eliminar Columna
[Link]
21-Abrir un Libro
[Link] Filename:="C:\Mis documentos\Tablas [Link]"
22-Grabar un Libro
[Link] Filename:="C:\Mis documentos\[Link]", 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 iteracion
Frecuentemente 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 For-Each-in-Next Estructuras de desicion: .If - Then - Else Select - Case Tipos de datos, funciones y subrutinas: Tipos de datos Funciones Macros
Estructura de iteracin Wile - Wend
INTRODUCCION:
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-While-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 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. 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 [Link] <> "" [Link](1, 0).Select Sum = Sum + [Link] Loop [Link] = 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-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 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-While-Loop 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-Next
INTRODUCCION: 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 columna, que en este caso ser la D. 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 iteracin For-Each-In-Next
Hay dos variantes de esta estructura 1-En el primer tipo debemos especificar el rango en el que se realiza la iteracin Como ejemplo hallaremos el promedio del rango A1:D4,dejando el resultado en la celda C7, para este ejemplo pondremos valores enteros
Cdigo:
La parte ms importante del cdigo es
que significa: para cada celda del rango A1:D4 ejecutar el bloque
y cuando se llega a la ltima celda del rango, automticamente se hace la cuenta
luego se coloca el resultado en la celda C8
Cdigo para copiar y pegar Sub PromedioRango() Dim cell As range Dim sum As Integer Dim cant As Integer Dim prom As Variant For Each cell In range("A1:D4").cells sum = sum + [Link] cant = cant + 1 Next cell prom = sum / cant range("C8").Select [Link] = prom End Sub 2-El segundo tipo utiliza la propiedad CurrentRegion, esta propiedad es muy interesante pues nos permite seleccionar un rango cuyos lmites no se conocen ya que CurrentRegion devuelve el rango que rodea a una celda seleccionada. Podemos decir que CurrentRegion es un rango rodeado por filas y columnas en blanco. En la figura se puede ver dos regiones de este tipo.
Utilizaremos estas regiones para obtener el promedio de las mismas, para lo cual empleamos el cdigo
Este cdigo sirve para las dos regiones, siempre y cuando se seleccione una celda de una u otra de acuerdo en cual se quiera calcular el promedio. Por ejemplo si queremos calcular el promedio del rango de la zona de contorno verde el resultado seria
En este caso hemos seleccionado la celda H3, pero se podra haber seleccionado cualquier otra
La diferencia con el caso 1 es
en la que
devuelve el rango de la regin actual. Cdigo para copiar y pegar Sub PromedioRegion() Dim cell As range Dim sum As Integer Dim cant As Integer Dim prom As Variant For Each cell In [Link] sum = sum + [Link] cant = cant + 1 Next prom = sum / cant range("D8").Select [Link] = prom End Sub
Estructura de decisin If - Then - Else
INTRODUCCION: 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 [Link]("D1").Value = [Link]("E1").Value Then [Link]("D4").Value = "Los Valores de D1 y E1 son iguales" Else [Link]("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 [Link]("D1").Value = [Link]("E1").Value Then [Link]("D4").Value = "Los Valores de D1 y E1 son iguales" Else If [Link]("D1").Value > [Link]("E1").Value Then [Link]("D4").Value = "D1 es mayor que E1" Else [Link]("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-Case
INTRODUCCION: 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 IfThen-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 [Link]("D4").Value = "Los Valores de D1 y E1 son iguales" Case Is > A1 [Link]("D4").Value = "D1 es mayor que E1" Case Is < A1 [Link]("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 [Link]("B2").Value = "APLAZADO" Range("E1").Select Case 4 To 5 [Link]("B2").Value = "APROBADO" Range("E1").Select Case 6 To 7 [Link]("B2").Value = "BUENO" Range("E1").Select Case 8 [Link]("B2").Value = "MUY BUENO" Range("E1").Select Case 9 [Link]("B2").Value = "DISTINGUIDO" Range("E1").Select Case 10 [Link]("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
Variables simples y Tipos de datos
INTRODUCCION:
Una variable simple es una porcin de memoria donde se puede almacenar un valor y se les debe dar un nombre para identificarlas entre s, tambin estn asociados a un tipo de dato. Un tipo de dato es el rango de valores que las variables pueden aceptar o, dicho de otra manera, la cantidad de memoria que se reserva para albergar dicho rango. Por ejemplo la variable de tipo Integer, puede guardar valores en un entorno de -32.768 a +32.787 ocupando 2 bytes. Todos los tipos de datos se resumen en la tabla de abajo.
Funciones
INTRODUCCION:
Las funciones son un trozo de cdigo inserto en el programa principal que recibe uno, muchos o ningn valor (parmetros o argumentos) y que a diferencia de las subrutinas devuelven un nico valor, por esta razn se debe especificar( aunque no es obligatorio)de qu tipo de dato es dicho valor. Las funciones deben tener un nombre que las identifique y as poder ser llamadas por el programa principal. Tambin nos permiten ampliar el listado de las funciones que ya vienen por defecto en Excel, estas funciones estn asociadas a un libro o una hoja de Excel y las denominamos Funciones personalizadas, son muy tiles,entre otras cosas, cuando la frmula que se requiere para resolver un problema usando las funciones propias de las hojas Excel resulta muy larga y complicada, haciendo casi imposible su comprensin.
Funciones personalizadas:
Empezaremos dando un ejemplo trivial de una funcin personalizada que podramos resolver sin recurrir a ellas, este ejemplo es solo para mostrar como funcionan. Supongamos que queremos multiplicar 2 nmeros enteros (en la figura el entero 12 y el 2)que estn en las celdas A1 y C1 y que el resultado se devuelva en la celda B3, que obviamente se resuelve con la frmula =A1*C1 ubicada en la celda B3, mentalmente podemos decir que el resultado es 24
pero lo haremos con una funcin definida por nosotros programada con VBA que llamaremos MULTIPLICA()
Esta funcin ya esta disponible junto con las dems,(solamente en la hoja donde la definimos) como se puede ver en el recuadro rojo, tambin puede verse que su definicin est entre Function y End Function
la usamos poniendo dos nmeros como argumento, tal como puede verse
Ahora daremos un ejemplo de una funcin definida por el usuario que tiene ms utilidad. La ferretera EL BULON hace el 20% de descuento si las ventas superan las 100 unidades. Esto se puede resolver con la funcin SI, como se puede ver en la siguiente figura
Resolveremos el mismo problema definiendo una funcin, que llamaremos DESCUENTO, con el siguiente cdigo
ya definida la funcin DESCUENTO() la podemos aplicar
Se ve que el resultado es el mismo, pero si definimos la funcin DESCUENTO el proceso es mas rpido. Como se habr notado en el ejemplo anterior, est permitido omitir el tipo de dato, pero es una buena costumbre ponerlos, ya que nos puede ahorrar muchos problemas principalmente en la depuracin del cdigo en programas largos (En otros idiomas, como en C, esto es obligatorio) por surte VBA tiene la posibilidad de forzar el hecho de tener que poner obligatoriamente los tipos de datos con la instruccin Opcin xplicit al principio de cada mdulo y mejor an, se puede configurar el editor de VBA para que se ponga automticamente esta instruccin en todos los nuevos mdulos, esto se hace yendo a Herramientas->Opciones->Solapa Editor y en ella tildando la casilla Requerir declaracin de variables, como se puede ver en la siguiente imagen
Ahora dar un ejemplo, que si bien puede resolverse con funciones (mas precisamente funciones matriciales) les puedo asegurar que la solucin es muy complicada y es aqu donde se ve la verdadera utilidad de las funciones personalizadas. Se trata de la obtencin de las iniciarles de un nombre completo. Su cdigo es el siguiente:
usaremos la funcion recien definida en la siguiente tabla
Llegando a este punto debemos hacer algunas aclaraciones del cdigo En primer lugar podemos ver que se usan las funciones de librera de VBA (funciones que ya estn definidas), ms precisamente las funciones de cadena Len(), Asc() y Mid(). Una cadena es una serie de caracteres tratados como una misma unidad, estos caracteres pueden ser letras, nmeros o caracteres especiales, como pueden ser /, *, &, %, @ y otros muchos incluido el espacio. A todos estos caracteres les corresponde un cdigo numrico llamado ASCII . Estas funciones sirven para el manejo de cadenas, hay otras que iremos analizando cuando sea necesario.
Funcin Len
Esta funcin nos devuelve el nmero de caracteres de una cadena, por lo tanto retorna un nmero entero y recibe un parmetro que es un tipo de dato string. Su sintaxis es: Len([cadena de caracteres]) Ejemplo: Si Texto="hola como estas" Len(Texto) devuelve el valor 15.
Funcin Asc
Con la funcin Asc podemos obtener el cdigo ASCII de un caracter
Funcin Mid
Extrae partes de una cadena y recibe 3 parmetros. Sintaxis: Mid(cadena, inicio, longitud) El parmetro cadena es la cadena a extraer caracteres. inicio es el carcter desde donde se comienza la extraccin. longitud es la cantidad de caracteres devueltos a partir del carcter de inicio. Dicho esto el cdigo se puede entender mas claramente 1 Function INICIALES(Texto As String) As String 2 Dim strLong As Long, i As Long 3 Dim textTemp As String 5 strLong = Len(Texto) 7 For i = 1 To strLong 8 If Asc(Mid(Texto, i, 1)) >= 65 And Asc(Mid(Texto, i, 1)) <= 90 Then 9 textTemp = textTemp & Mid(Texto, i, 1) 9 End If 10 Next i 12 INICIALES = textTemp 14 End Function Lnea 1: se da el nombre a la funcin (INICIALES) y se define el nombre (Texto) y el tipo del parmetro que recibe.
Lneas 2 y 3: se declaran las variables que se van a usar. Lnea 5: se asigna a la variable strLong la longitud del string Texto, o sea que se cuentan cuantos caracteres hay en la celda que contiene el nombre Lnea 7: se entra en un ciclo for-Next que recorre el string Texto, o sea que comienza desde 1 y termina en strLong. Lnea 8: esta es la lnea ms importante. ya que en cada ciclo se evala cada carcter y determina si es una letra mayscula (Asc>=65 o Asc<=90). Lnea 9: Si es mayscula agrega el carcter a la variable strTemp. Lnea 12: Al terminar la rutina, la funcin INICIALES recibe el valor de strTemp que es donde se guardan las iniciales. Para comprobar que funciona dejo el cdigo para copiar y pegar Function INICIALES(Texto As String) As String Dim strLong As Long, i As Long Dim textTemp As String strLong = Len(Texto) For i = 1 To strLong If Asc(Mid(Texto, i, 1)) >= 65 And Asc(Mid(Texto, i, 1)) <= 90 Then textTemp = textTemp & Mid(Texto, i, 1) End If Next i INICIALES = textTemp End Function
FUNCIONES DE HOJA( NATIVAS):
Para definir funciones personalizadas, a dems de las funciones propias de VBA , se pueden usar las llamadas funciones de hoja ( Work Sheet Functions en ingles) tambin llamadas funciones nativas, es decir las funciones que normalmente estn disponibles en las hojas de Excel, como son , SUMA, BUSCARV, MAX, PROMEDIO, COINCIDIR, por nombras algunas. Para usarlas debemos anteponer al nombre de la funcin( que debe estar en ingles) la expresin [Link].[nombre de la funcin](parmetros).Si queremos usar la funcin BUACARV, la expresin debe ser:
[Link](Valor a buscar, Matriz de bsqueda; Columna, Valor lgico) Alternativamente: [Link]() o [Link]() Como se puede ver los parmetros de la funcin son los mismos que los de las funciones nativas Empezaremos dando un ejemplo en el que se usa la funcin DESREF que en ingles es Match. Como todos sabemos la funcin BUACARV solo puede traer valores que estn en la misma fila del valor buscado pero en columnas a la derecha de este, el problema se podria resolver con un anidamiento de las funciones INDICE y COINCIDIR, nuestro objetivo es definir una funcin muy parecida a BUSCARV pero que busque hacia la izquierda, a esta funcin la llamaremos BUSCARVIZQ Supongamos que tenemos la siguiente tabla
y queremos: A) Introducir la tabla en una hoja se Excel B) Que dado el cdigo de producto se devuelva en dos celdas la herramienta y el precio de la misma C) No emplear anidamiento de funciones Como vemos el cdigo del artculo se encuentra en la columna central, por lo tanto para responder debemos hacer una bsqueda a la izquierda y otra a la derecha, por lo tanto debemos recurrir a BUSCARV y a una UDF( funcin definida por el usuario) que como ya dijimos llamaremos BUSCARIZQ que definimos con el siguiente cdigo: Function BUSCARVIZQ(valor_a_buscar, matriz_de_busqueda As Range, indice_de_Col As Integer, ValorExacto As Boolean) Dim NrFila As Long NrFila = [Link](valor_a_buscar, matriz_de_busqueda.Resize( , 1), ValorExacto) BUSCARVIZQ = matriz_de_busqueda(NrFila, 1).Offset(0, indice_de_Col) End Function ya estamos en condiciones de itroducir la frmula BUSCARIZQ con sus parmetros
que ubicamos en la celda D16 y BUSCARV
que ubicamos en la celda D15, haciendo esto podemos reponder al punto B) como puede verse en la figura
marcamos con un recuadro rojo la frmula y el rango introducido, que como se ve es el mismo para ambas frmulas, no as la columna que para BUSCARIZQ es -1
EXPLICACION DEL CODIGO
Function BUSCARVIZQ(valor_a_buscar, matriz_de_busqueda As Range, indice_de_Col As Integer, ValorExacto As Boolean) se da el nombre a la funcin y los parmetros, proporcionando el tipo de dato. Notar que no se da el tipo de dato del parmetro valor_a_buscar ya que este puede ser una cadena de caracteres, un nmero o una combinacin de ambos. Lo mismo podemos decir del parmetro que devuelve la funcin
Dim NrFila As Long Se define la variable NrFila NrFila = [Link](valor_a_buscar, matriz_de_busqueda.Resize( , 1), ValorExacto) Se asigna a NrFila lo que devuelve la funcion Match (COINCIDIR) donde se redimenciona lamatriz_de_busqueda con Recize matriz_de_busqueda.Resize( , 1) esto se hace para que la matriz de bsqueda seleccionada, que es rectangular, se transforme en una matriz columna, ya que Match solo busca en rangos columna. Veamos cono funciona: [Link](12,4).Select si el rango seleccionado inicialmente es C4:E12 , o sea 8 filas y 3 columnas, luego aplicar Resize el Rango queda con 12 filas y 4 columnas en nuestro caso el rango seleccionado es 11 filas y 2 columnas, como a las filas las queremos dejar igual no se pone nada como primer parmetro y si,ademas, queremos que el rango final sea de una columna se pone( ,1) as, el rango seleccionado queda un rango columna Se podra haber omitido el Resize seleccionando solo el rango central, esto se hizo para que los parmetros sean iguales a los de la funcin BUSCADV, salvo por el -1 de indice_de_Col finalmente el cdigo
BUSCARVIZQ = matriz_de_busqueda(NrFila, 1).Offset(0, indice_de_Col) asigna a BUSCARVIZQ el valor que est en la celda activa matriz_de_busqueda(NrFila, 1)desplazada una celda a la izquierda(indice_de_Col ). Esto se logra con la PROPIEDAD Offset que seleciona celdas a cierta distancia de la celda activa por ejemplo: [Link](-5,2).Select selecciona la celda que se encuentra 5 filas hacia arriba y 2 columnas a la derecha de la celda activa. de otra manera: Sheets(1).Range("C2").Offset(2,4).Select a partir de C2 selecciona 2 filas hacia abajo y 4 columnas a la derecha, es decir C2 pasa a ser G4(C2 = G4), tener en cuenta que 2 es fila y 4 columna.
como se ve Offset hace lo mismo que DESREF. La programacin VBA tiene sus propias funciones como Mid y Len, que ya se describieron, estas permiten trabajar con cadenas de caracteres, con nmeros, fechas, horas, mostrar mensajes por pantalla y muchas otras que podemos listar por categoras, como sigue: 1-Funciones de conversin de tipos (Conversion) 2-Funciones Financieras (Financial) 3-Funciones de cadenas (Strings) 4-Funciones matemticas (Math) 5-Funciones de comprobacin (Information) 6-Funciones de Interaccin(Interaction) 7-Funciones de Fecha y Hora (DateTime) 8-funciones del sistema (FileSystem)
Macros
INTRODUCCION:
Las macros son pequeos trozos de cdigo VBA que no deberan tener ms de 15 lneas y no devuelven un valor en su nombre, como las funciones, sino que hacen tareas especificas, estas tareas muchas veces pueden realizase manualmente y otras no, en definitiva sirven para automatizar Excel. Las macros pueden vincularse entre s mediante la palabra recervada Call y de esta forma unirse para formar cdigos mas grandes
Estos cdigos por lo general se ejecutan a pedido del operador, pero tambin pueden hacerlo automticamente, una forma es debido a que se produzca algn cambio en una hoja de Excel, como puede ser el cambio en el valor de una celda. A estos cambios se los denomina EVENTOS y para que se ejecuten las macros, hay que programar dichos EVENTOS. La forma ms prctica de poner en accin una macro es mediante botones asociados a ellas. Para hacerlo vamos a la pestaa programador y de all pulsamos el botn Insertar para que se desplieguen los controles de formulario, como se ve en la figura
al apretar el cono del botn aparece una cruz, ubicamos esta cruz en el lugar que nos parece conveniente y arrastrndola, con el botn izquierdo del mouse apretado, podemos darle el tamao adecuado. Al soltar el mouse tenemos algo parecido a esta imagen
simultneamente aparece un panel donde podemos seleccionar la macro que queremos asociar
al aceptar ya tenemos el botn asociado a la macro, posteriormente lo podemos rotular convenientemente. A continuacion daremos un ejemplo prctico simple: Un banco quiere dar de baja las cuentas que no tuvieron movimientos por 3 o ms aos, considerndolas inactivas en dicho caso y activas en caso contrario. El banco tiene un listado con las cuentas activas y no activas
Se quiere actualizar dicho listado, o sea eliminar las cuentas inactivas
Este listado tiene 14 clientes y se lo podra actualizar manualmente, pero si imaginamos que la lista tiene 5000 o ms clientes, se tardara mucho tiempo, por tal motivo es un problema para resolver con una macro. Coloquemos la lista en la hoja 1 de un libro de Excel 2007 o Excel 2003
insetamos el siguiente cdigo
Luego de de oprimir el botn que asociamos a la macro, que llamamos ActualizarListado, el listado queda
Cdigo para copiar y pegar Sub ActualizarListado() Range("E3").Select Do While [Link] <> "" If [Link] = "inactiva" Then [Link] Else [Link](1, 0).Select End If Loop Range("A1").Select End Sub
Cuando se dispara una macro no hay vuelta atrs, si se borra un dato, este se pierde(a no ser que se cierre el archivo no se guarden los cambios y se lo abra otra vez). Muchas veces podemos necesitar el listado original, por ejemplo para obtener, en el caso que estamos tratando, otro con los clientes que fueron dados de baja, el que se hara con una macro casi idntica a la dada ms arriba, que dicho sea de paso la confeccion un empleado del rea de computacin del banco, al que le pidieron las dos listas a ltimo momento. Cacho (as le dicen al empleado), que por suerte no haba apretado el botn ACTUALIZAR, decidi hacer las dos cosas al mismo tiempo, es decir: dejar en la Hoja1un listado con los clientes activos y en la Hoja2 los clientes dados de baja, a esta hoja la renombro "Bajas", tambin copio como respaldo, el listado original en la Hoja3. Cuando termin el trabajo le quedaron las siguientes macros
y para el rtulo de "Bajas"
"CopiaRotulo" es llamada por "ActualizarListado2" mediante la palabra reservada "Call" Las dos listas quedaron como se ve Lista de los clientes activos
Lista de los clientes dados de baja
Cacho pens que despus de todo, se hubiera tomado menos trabajo cerrando el archivo y abrindolo otra vez, aunque pensndolo mejor se dijo "ya tengo hecha la macro y seguro que la voy a volver a usar. Si, seguro que la iba a volver usar y esta vez todo el trabajo sera ms rpido. Macros para copiar y pegar Option Explicit Sub ActualizarListado2() Dim i As Integer Dim k As Integer k=1 Dim j As Integer j=1 Call CopiaRotulo Sheets("Hoja1").Select [Link] = False Range("E3").Select Do While [Link] <> "" If [Link] = "inactiva" Then Sheets("Bajas").Select Range("E65536").End(xlUp).Offset(1, 0).Select i = [Link] Range("C" & i).Value = Worksheets("hoja1").Range("C" & i + k - j).Value Range("C" & i).[Link] = 24 Range("C" & i).Borders(xlEdgeBottom).LineStyle = xlContinuous Range("C" & i).Borders(xlEdgeLeft).LineStyle = xlContinuous Range("C" & i).Borders(xlEdgeTop).LineStyle = xlContinuous Range("D" & i).Value = Worksheets("hoja1").Range("D" & i + k - j).Value Range("D" & i).[Link] = 24 Range("D" & i).Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D" & i).Borders(xlEdgeLeft).LineStyle = xlContinuous Range("D" & i).Borders(xlEdgeTop).LineStyle = xlContinuous Columns("D:D").[Link] Range("E" & i).Value = Worksheets("hoja1").Range("E" & i + k - j).Value Range("E" & i).[Link] = 24 Range("E" & i).Borders(xlEdgeBottom).LineStyle = xlContinuous Range("E" & i).Borders(xlEdgeLeft).LineStyle = xlContinuous Range("E" & i).Borders(xlEdgeTop).LineStyle = xlContinuous Range("E" & i).Borders(xlEdgeRight).LineStyle = xlContinuous j=j+1 Range("A1").Select Sheets("Hoja1").Select [Link] Else k=k+1 [Link](1, 0).Select End If Loop Range("A1").Select End Sub Option Explicit Sub CopiaRotulo() Range("C2:E2").Select [Link] Sheets("Bajas").Select Range("C2").Select [Link] Columns("C:C").[Link] Columns("D:D").[Link] Columns("E:E").[Link] End Sub
Nos podramos preguntar de donde se sac la tabla que uso Cacho, bueno, esta es realidad una base de datos de la cual se ha obtenido la que tiene el rtulo ESTADO (cuenta activa, cuenta inactiva). La BASE DE DATOS se obtiene de los FORMULARIOS que tenemos que llenar cuando decidimos sacar una cuenta corriente en un banco Este es un modelo real de formulario para la solicitud de una cuenta bancaria de una empresa
De la misma manera, una empresa, podra generar su propia BASE DE DATOS de clientes, recurriendo a la capacidad que tiene Excel para generar FORMULARIOS, o podramos crear un directorio telefnico y esto es justamente lo que haremos en el prximo ejemplo de macros pero sin recurrir a los formularios, tema que dejaremos para despues.. En adelante, como se vio en caso del empleado del banco (Cacho), procurar presentar casos de la vida cotidiana, con los que se puede encontrar el empleado de una empresa, o de una pyme, situaciones con las que frecuentemente se ven involucrados profesionales de cualquier orientacin o de un usuario que en realidad no sabe que esta frente a una aplicacin Excel
EJEMPLOS PRACTICOS DE MACROS Y CODIGO VBA
DIRECTORIO TELEFONICO
Se puede encarar el problema anterior usando formularios, este mtodo tiene la desventaja de que el cdigo se complica un poco, lo que no debera sorprendernos ya que una celda es un objeto mas simple, pero como veremos, las ventajas superan a las desventajas
DIRECTORIO TELEFONICO ( USANDO FORMULARIOS) CONSULTA EN UNA LIBRERIA CONSULTA EN UNA LIBRERIA (USANDO FORMULARIOS) TICKET DE COMPRA COMBOBOX (CUADRO COMBINADO) ORDENADO ALFABRTICAMENTE ALTAS, BAJAS Y MODIFICACIONES
INFORMES
