VISUAL BASIC PARA EXCEL

En adicin a la extensiva lista de funciones de herramientas de clculo que tiene Excel, el programa contiene un lenguaje de programacin que permite al usuario crear procedimientos, algunas veces referidos como macros, los cuales permiten desempear clculos ms avanzados o ejecutar clculos repetitivos.

A manera de ejemplo, vamos a evaluar la funcin:

Evaluacin de funciones

)sin()cos()ln(2)( 3 xe

xxxxf x ++=

En formulas matemticas y trigonomtricas Excel tiene una gran variedad de funciones incluyendo ln, cos, exp y sin.

para x=1.1, f(x)=3.49752851

Conforme vamos digitando frmulas en Excel, van apareciendo algunos errores que usualmente son debidos a un manejo inadecuado de la sintaxis o a la incompatibilidad con la configuracin de la computadora.

Errores comunes

A continuacin se describen algunas situaciones que pueden aparecer.

1. El valor de error #NOMBRE? aparece cuando Excel no reconoce texto en una frmula. Deber revisar la sintaxis de dicha frmula o, si es una macro, verificar que est en un mdulo de esta hoja.

2. El valor de error #VALOR! se origina cuando se utiliza un tipo de argumento (u operando) incorrecto. Este error se da por ejemplo, cuando evaluamos una funcin numrica en una celda que contiene algo que no sea un nmero (Por defecto, el contenido de una celda vaca es cero).

3. El valor de error #NUM! aparece cuando hay un problema con algn nmero en una frmula o funcin. Por ejemplo, si evaluamos una funcin logartmica en cero o en un nmero negativo.

4. El valor de error #DIV/0! se produce cuando se divide una frmula por 0 (cero).

5. El valor de error #REF! se da cuando una referencia a una celda no es vlida.

Para crear un cdigo VBA, o para examinar un cdigo existente, se necesita usar el editor de Visual Basic. Para acceder al editor, seleccione Macro del men de herramientas y luego editor de Visual Basic del men.

El editor de Visual Basic

La pantalla del editor de Visual Basic usualmente contiene tres ventanas importantes: La ventana del explorador de proyectos, la ventana de propiedades y la ventana de cdigo. La ventana de cdigo muestra le hoja de mdulo activa, cada hoja de mdulo puede contener uno o varios procedimientos de Visual Basic. Si el libro de trabajo actual no contiene ningn mdulo activo, la ventana de cdigo estar vaca. Para insertar una hoja de mdulo, escoja Modulo del men Insertar, as se mostrar la hoja Modulo1.

El lenguaje Visual Basic para Aplicaciones (VBA), en el contexto de Excel, constituye una herramienta de programacin que nos permite usar cdigo Visual Basic adaptado para interactuar con las mltiples facetas de Excel y personalizar las aplicaciones que hagamos en esta hoja electrnica.

Programacin de macros

Las unidades de cdigo VBA se llaman macros. Las macros pueden ser procedimientos de dos tipos:

- Funciones (Function) - Subrutinas (Sub) )

Las funciones pueden aceptar argumentos, como constantes, variables o expresiones. Estn restringidas a entregar un valor en una celda de la hoja. Las funciones pueden llamar a otras funciones y hasta subrutinas (en el caso de que no afecten la entrega de un valor en una sola celda)

Una subrutina realiza acciones especficas pero no devuelven ningn valor. Puede aceptar argumentos, como constantes, variables o expresiones y puede llamar funciones. Con las subrutinas podemos entregar valores en distintas celdas de la hoja. Es ideal para leer parmetros en algunas celdas y escribir en otras para completar un cuadro de informacin a partir de los datos ledos.

Las funciones y las subrutinas se pueden implementar en el editor de Visual Basic (Alt-F11). Para usar una funcin en una hoja de Excel se debe, en el editor de VB, insertar un mdulo y editar la funcin en este mdulo. De la misma manera se pueden editar subrutinas en un mdulo.

Editar y ejecutar macros

Una funcin tiene la siguiente sintaxis:

Function FunctionName(Argument1, ...)

VBA statements

FunctionName = result

End

Una funcin puede tener o no tener argumentos, pero es conveniente que retorne un valor. Observe que se debe usar el nombre de la funcin para especificar la salida:

Nota 1: Al interior de las funciones, se pueden hacer comentarios utilizando (antes de stos) la comilla (').

Nota 2: Para el uso de nombres de variables o de cualquier otra palabra reservada de VBA, no se discrimina entre el uso de letras maysculas y minsculas.

Una funcin se invoca en una hoja, como se invoca una funcin de Excel o una frmula. Una subrutina se puede invocar por ejemplo desde la ventana de ejecucin de macros (Alt-F8) o desde un botn que hace una llamada a la subrutina (como respuesta al evento de hacer clic sobre l, por ejemplo).

Evaluar la funcin

Ejemplo:

)sin()cos()ln(2)( 3 xe

xxxxf x ++= para

x= 1, 1.2, 5, 2.3, 1.6 usando una funcin en VBA

Solucin. Ingresamos al editor de Visual Basic, e insertamos un mdulo (Module1). En la ventana de edicin ingresamos el siguiente cdigo:

Function f(x)

f = 2 * x ^ 3 + Log(x) - Cos(x) / Exp(x) + Sin(x)

End Function

Note que en VB el logaritmo natural se escribe como log(x) a diferencia en una hoja de clculo de Excel donde se usa ln(x).

Una vez que ha sido editado el cdigo del macro, se salva y salimos del ambiente de programacin en Visual Basic para volver a la hoja electrnica de donde partimos. Esto se hace en el men Archivo, seleccionando Cerrar y Volver a Excel.

Para evaluar la funcin f(x) en alguna celda, se digita +f(celda conteniendo x).

Un programa computacional escrito mediante cualquier lenguaje de programacin puede verse a grandes rasgos como un flujo de datos, algunos jugando el papel de datos de entrada, otros son datos que cumplen alguna funcin temporal dentro del programa y otros son datos de salida. A lo largo del programa es muy frecuente que sea necesaria la entrada en accin de otros programas o procesos. A mayor complejidad del problema que resuelve el programa, mayor es la necesidad de programar por aparte algunos segmentos de instrucciones que se especializan en una tarea o conjunto de tareas.

Elementos de programacin en VBA

Hay tres tipos de estructuras bsicas que son muy utilizadas en la programacin de un algoritmo, a saber, la estructura secuencial, la estructura condicional y la repetitiva.

Flujo secuencial

El flujo secuencial consiste en seguir una secuencia de pasos que siguen un orden predeterminado.

Por ejemplo, un programa que a partir de un nmero N de das, calcula la cantidad de segundos que hay en esta cantidad de das. Este programa se puede ver como una secuencia de varios pasos:

Inicio: Ingresa el nmero N de das Paso 1: H = 24*N, para determinar la cantidad de horas Paso 2: M = 60*H, para determinar la cantidad de minutos Paso 3: S = 60*M, para determinar la cantidad de segundos Paso 4: Retorne S Fin

La macro correspondiente a esta secuencia de clculos puede escribirse como sigue:

Function CalculeSegundos(Dias)

CantHoras = 24 * Dias

CantMinutos = 60 * CantHoras

CalculeSegundos = 60 * CantMinutos

End Function

Flujo condicional (If - Else)

Un flujo condicional se presenta en un programa o procedimiento que debe escoger una accin o proceso a ejecutar, dependiendo de condiciones que puedan cumplirse.

El caso ms sencillo ocurre cuando el programa verifica si una condicin se cumple y en caso de ser verdadera ejecuta un proceso, en tanto que si es falsa ejecuta otro proceso.

En VBA tenemos la instruccin

Sintaxis

If condicin Then

instrucciones

Else instrucciones-else

Puede utilizar la siguiente sintaxis en formato de bloque:

If condici Then

instrucciones

ElseIf condicin Then

instrucciones-elseif

...

Else instrucciones-else

End If

Ejemplo. Construya un cdigo para saber si un estudiante pas un curso. Si la nota es >=3 el estudiante pas de lo contario no pas.

Solucin. Function Calificacion(Valor)

If Valor >= 3 Then

Calificacion = "Pas"

Else

Calificacion = "No pas"

End If

End Function

Flujo repetitivo (For-Next, While-Wend, Do While-Loop)

El flujo repetitivo se presenta en un algoritmo cuando se requiere la ejecucin de un proceso o parte de un proceso sucesivamente, hasta que ocurra una condicin que permita terminar. Este tipo de flujos repetitivos se presentan en tres formas que obedecen a maneras diferentes de razonarlos pero que en el fondo hacen lo mismo:

Utilizar un contador que empiece en un nmero y termine en otro, ejecutando el proceso cada vez que el contador tome un valor distinto.

Mientras una condicin sea verdadera, ejecutar un proceso y regresar a la condicin.

Ejecutar un proceso, hasta que una condicin deje de cumplirse.

En VBA tenemos las siguientes instrucciones para realizar procesos iterativos:

1. For ... Next

Sintaxis (las instrucciones entre `[ ]' son instrucciones adicionales)

. Repite un grupo de instrucciones un nmero especificado de veces.

For contador = inicio To fin [Step incremento]

instrucciones

[Exit For]

instrucciones

Next contador

2. While...Wend

Sintaxis

. Ejecuta una serie de instrucciones mientras una condicin dada sea True.

While condicin

intrucciones

Wend

Nota: No hay un Exit While. En una subrutina, si fuera necesario, se podra usar Exit Sub

3. Do

Sintaxis

. Una instruccin muy parecida a While pero ms eficiente es Do

Do while condicin

instrucciones

[Exit Do]

Loop

Sintaxis:

La estructura de una subrutina

Sub ProcedureName(Argument1, ...)

VBA statements

End Sub

Ejemplo

Podemos implementar una subrutina en una hoja, que recorra una seleccin hecha con el mouse y que vaya elevando al cuadrado el valor de cada celda.

. Elevar al cuadrado los valores de una seleccin (ejecutar desde la ventana de ejecucin de macros).

Sub elevalcuadado()

For Each cell In Selection.Cells 'para cada celda de la seleccin

cell.Value = cell.Value ^ 2 'recalcula el valor de la celda

Next cell

End Sub

Nota: La macro se aplica a los datos que estn actualmente seleccionados

Para ejecutar la macro seleccionamos la tabla con el mouse y levantamos la ventana de ejecucin de macros (Alt-F8)y damos clic en 'Ejecutar'

Otra posibilidad bastante prctica para ejecutar un programa o subrutina como los presentados en la seccin precedente es mediante un botn de comando.

Ejecucin de una subrutina mediante un botn

Primero digitamos la tabla de valores. Luego insertamos un botn. Para esto seleccionamos un botn del cuadro de controles (si la barra no est disponible, puede habilitarla con Ver - Barra de herramientas - Cuadro de Controles). Luego hacemos clic en el lugar de la hoja donde queremos el botn. Una vez que tenemos el botn, podemos agregar algunas propiedades como etiqueta, color de fondo, etc., en el men de contexto. Este men se abre con clic derecho + propiedades. Luego cerramos el men. Para editar el cdigo que debera ejecutar el botn, le damos un par de clics al botn (que todava est en modo diseo). En este caso, si es la primera vez, nos aparece el cdigo:

Private Sub CommandButton1_Click()

End Sub

Agregamos justo en el medio:

Private Sub CommandButton1_Click()

For Each cell In Selection.Cells 'para cada celda de la seleccin

cell.Value = cell.Value ^ 2 'recalcula el valor de la celda

Next cell

End Sub

Se graba y se sale del modo de diseo. Acto seguido se selecciona la(s) celda(s) que se quieren elevar al cuadrado y se oprime el botn.