Programación Visual Basic (VBA) para Excel y Análisis Numérico PDF

Preview

Summary

1 Programación Visual Basic (VBA) para Excel y Análisis Numérico M.Sc. Walter Mora F., M.Sc. José Luis Espinoza B. Escuela de Matemática Instituto Tecnológico de Costa Rica Octubre 2005 Versión 0.1 Contents 1 Programación Visual Basic (VBA) para Excel 3 1.1 Introducción . . . . . . . . . . ...

Description

1

Programaci´ on Visual Basic (VBA) para Excel y An´ alisis Num´ erico M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. Escuela de Matem´ atica Instituto Tecnol´ ogico de Costa Rica Octubre 2005 Versi´ on 0.1

Contents 1 Programaci´ on Visual Basic (VBA) para Excel 1.1 Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 Evaluaci´on de funciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.1 Funciones definidas por el usuario . . . . . . . . . . . . . . . . . . . 1.2.2 Errores comunes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.3 Evaluando una funci´on en varios tipos de par´ametros . . . . . . . . . 1.3 Gr´aficas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4 Programaci´on de macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.1 Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.4.2 Funciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.5 Elementos de programaci´on en VBA . . . . . . . . . . . . . . . . . . . . . . 1.5.1 Flujo secuencial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.5.2 Flujo condicional (If - Else) . . . . . . . . . . . . . . . . . . . . . 1.5.3 Flujo repetitivo (For-Next, While-Wend, Do While-Loop) . . . . . 1.5.4 Manejo de rangos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.5.5 Subrutinas. Edici´on y ejecuci´on de una subrutina . . . . . . . . . . . 1.5.6 Ejecuci´on de una subrutina mediante un bot´on . . . . . . . . . . . . 1.5.7 Matrices din´amicas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.5.8 Inclusi´on de procedimientos de borrado . . . . . . . . . . . . . . . . 1.6 Evaluando expresiones matem´aticas escritas en lenguaje matem´atico com´ un 1.6.1 Usando clsMathParser. Sintaxis . . . . . . . . . . . . . . . . . . . . 1.6.2 Ejemplo: un graficador 2D . . . . . . . . . . . . . . . . . . . . . . . 1.6.3 Ejemplo: un graficador de superficies 3D . . . . . . . . . . . . . . . . 1.6.4 Ejemplo: series num´ericas y series de potencias . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . .

3 3 3 3 4 5 8 9 9 9 13 13 14 16 22 23 25 29 35 38 38 42 46 49

2 Elementos de An´ alisis Num´ erico 2.1 Soluci´on de ecuaciones de una variable . . . . . . . . . . . . . . . . 2.1.1 M´etodo de Newton-Raphson . . . . . . . . . . . . . . . . . 2.2 Integraci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.2.1 M´etodo de Romberg para integraci´on . . . . . . . . . . . . 2.2.2 La funci´on Gamma . . . . . . . . . . . . . . . . . . . . . . . 2.2.3 Cuadratura gaussiana e integral doble gaussiana. . . . . . . 2.3 Problemas de valor inicial para ecuaciones diferenciales ordinarias . 2.3.1 Existencia y unicidad . . . . . . . . . . . . . . . . . . . . . 2.3.2 M´etodo de Euler . . . . . . . . . . . . . . . . . . . . . . . . 2.3.3 M´etodos de Heun . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

54 54 54 56 56 58 59 66 66 67 71

2

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

Chapter 1

Programaci´ on Visual Basic (VBA) para Excel 1.1

Introducci´ on

c es un software para el manejo de hojas electr´onicas agrupadas en libros para c´alculos de casi Microsof Excel° cualquier ´ındole. Entre muchas otras aplicaciones, es utilizado en el tratamiento estad´ıstico de datos, as´ı como para la presentaci´on gr´afica de los mismos. La hoja electr´onica Excel es ampliamente conocida, en forma generalizada, por profesionales y estudiantes en proceso de formaci´on, pero hay una gran cantidad de usuarios que no conocen a profundidad su gran potencial y adaptabilidad a los diferentes campos del conocimiento. Para cient´ıficos e ingenieros, el Excel constituye una herramienta computacional muy poderosa. Tambi´en tiene gran utilidad para ser utilizado en la ense˜ nanza de las ciencias y la Ingenier´ıa, particularmente, en la ense˜ nanza de los m´etodos num´ericos. Pese a que existen en el mercado programas computacionales muy sofisticados, tales como MATLAB, MATHEMATICA, etc., no est´an tan disponibles como Excel, que usualmente forma parte del c de Microsoft. paquete b´asico de software instalado en las computadoras que funcionan bajo el sistema Windows° A continuaci´ on se brinda al lector una breve introducci´on a algunas actividades de programaci´on con macros escritos en VBA (una adaptaci´on de Visual Basic para Office de Microsoft), definidos desde una hoja electr´onica de Excel. Salvo peque˜ nas diferencias para versiones en ingl´es, el material puede ser desarrollado en cualquier versi´on.

1.2 1.2.1

Evaluaci´ on de funciones Funciones definidas por el usuario

A manera de ejemplo, vamos a evaluar la funci´on f (x) = 2x3 + ln(x) −

cos(x) + sen(x) ex

1. Como al evaluar f (x) se debe recurrir a varias funciones b´asicas que se invocan desde Excel, se puede tener acceso a su sintaxis, pulsando el ´ıcono fx y seleccionar ‘Matem´aticas y Trigonom´etricas’. 2. Para escribir una f´ormula, seleccionamos una celda para escribir el valor a ser evaluado; por ejemplo, podemos digitar el valor 1.1 en la celda B3.

3

VBA para Excel

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 4

Figure 1.1: Funciones predefinidas en Excel.

3. Ahora en la celda C3 digitamos, de acuerdo a la sintaxis de la versi´on de Excel en espa˜ nol1 , la f´ormula: =2*B3^3+LN(B3)-COS(B3)/EXP(B3)+SENO(B3) Una vez que ha sido digitada, simplemente se pulsa la tecla ‘Entrar’ o ‘Enter’.

Figure 1.2: Al evaluar f´ormulas, a menudo se requiere evaluar varias funciones predefinidas.

1.2.2

Errores comunes

Conforme vamos digitando nuestras primeras f´ormulas, nos van apareciendo algunos errores que usualmente son debidos a un manejo inadecuado de la sintaxis o a la incompatibilidad con la configuraci´on de la computadora. A continuaci´ on se describen algunas situaciones que pueden aparecer. 1. El valor de error #¿NOMBRE? aparece cuando Excel no reconoce texto en una f´ormula. Deber revisar la sintaxis de dicha f´ormula o, si es una macro, verificar que est´e en un m´odulo de esta hoja. 2. El valor de error #¡VALOR! da cuando se utiliza un tipo de argumento (u operando) incorrecto. Este error se da por ejemplo, cuando evaluamos una funci´on num´erica en una celda que contiene algo que no sea un n´ umero (Por defecto, el contenido de una celda vac´ıa es cero). 3. El valor de error #¡NUM! se aparece cuando hay un problema con alg´ un n´ umero en una f´ormula o funci´on. Por ejemplo, si evaluamos una funci´on logar´ıtmica en cero o en un n´ umero negativo. 1 La versi´ on que estamos usando est´ a en espa˜ nol. Por ejemplo, en la versi´ on en ingl´ es de Excel, se usa SIN(x) en lugar de SENO(x).

VBA para Excel

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 5

4. El valor de error #¡DIV/0! se produce cuando se divide una f´ormula por 0 (cero). 5. El valor de error #¡REF! se da cuando una referencia a una celda no es v´alida. 6. Dependiendo de la forma en que est´e configurado el sistema Windows, debe usarse punto o coma para separar la parte decimal de los n´ umeros a evaluar. Para personalizarlo, se debe entrar al panel de control y en la ‘Configuraci´on regional’ se selecciona ‘N´ umeros’. En la primera cejilla, ‘S´ımbolo Decimal’ se selecciona el punto o la coma, seg´ un sea el caso. Finalmente, se presiona el bot´on ‘Aplicar’ y luego ‘Aceptar’. 7. Una situaci´on que a veces es confundida con un error se da cuando el sistema trabaja con poca precisi´on y se presentan valores num´ericos no esperados. Por ejemplo, si el formato de una celda se ha definido para dos posiciones, entonces la operaci´on +1.999+1 efectuado en dicha celda dar´a como resultado el valor 2, que no es otra cosa que el resultado de tal suma redondeado a dos decimales. El valor correcto se obtiene aumentado la precisi´on con el ´ıcono correspondiente:

Tambi´en se puede cambiar la precisi´on en el men´ u ‘Formato-Celdas-N´ umero-Posiciones decimales’. Estos cambios son s´olo de apariencia, pues, independientemente del n´ umero de d´ıgitos que sean desplegados, Excel manipula los n´ umeros con una precisi´on de hasta 15 d´ıgitos. Si un n´ umero contiene m´as de 15 d´ıgitos significativos, Excel convertir´a los d´ıgitos adicionales en ceros (0).

1.2.3

Evaluando una funci´ on en varios tipos de par´ ametros

Muchas f´ormulas a evaluar tienen argumentos de distinto tipo, pues algunos argumentos var´ıan (a veces con un incremento determinado), mientras que otros permanecen constantes. Por lo general estos argumentos son tomados de celdas espec´ıficas, por lo que es importante saber manejar distintos escenarios para la evaluaci´on de una funci´on o f´ormula.

Evaluaci´ on con argumentos variables Continuando con el ejemplo que iniciamos en la secci´on 2.1, a partir de la celda B4 podemos continuar digitando valores, siempre en la columna B y con el cuidado de que estos n´ umeros no se salgan del dominio de la funci´on cos(x) f (x) = 2x3 + ln(x) − + sen(x), que en este caso es el conjunto de los n´ umeros reales positivos. Una vez ex hecho ´esto, se eval´ ua la funci´on f (x) en la celda C3, como se hizo previamente. Luego, seleccionamos esta misma celda C3 y se ubica el mouse en la esquina inferior derecha, arrastr´andolo hasta la celda deseada. Otra posibilidad es hacer un doble clic en la esquina inferior derecha de la celda a copiar y esto realiza la copia autom´aticamente.

Evaluaci´ on con argumentos variables y/o constantes Es com´ un tener que evaluar funciones o f´ormulas que dependen de varios par´ametros, algunos de los cuales se mantienen fijos mientras que otros son variables.

Ejemplo 1

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 6

VBA para Excel

Figure 1.3: Copia de una f´ormula en un grupo de celdas.

El siguiente ejemplo describe una funci´on con dos par´ametros y una variable. La funci´on P (t) = Aqu´ı:

K K − P0 , con A = , describe el tama˜ no de una poblaci´on en el momento t. −kt 1 + Ae P0

. k es una constante de proporcionalidad que se determina experimentalmente, dependiendo de la poblaci´on particular que est´a siendo modelada, . P0 es la poblaci´on inicial y . K es una constante llamada capacidad de contenci´ on o capacidad m´ axima que el medio es capaz de sostener. Si queremos evaluar P (t) para distintos valores del tiempo t en d´ıas, seguimos la siguiente secuencia de pasos: 1. Para empezar, es importante escribir encabezados en cada una de las columnas (o filas) donde vamos a escribir los datos que ser´an los argumentos de la funci´on. En este caso, comenzando en la celda B3, escribimos las etiquetas

P0

K

k

t

P(t).

2. A continuaci´ on escribimos los valores de los par´ametros, comenzando en la celda B4

100

1000

0.08

0.

3. Ahora escribimos la f´ormula de la funci´on P (t) en la celda G4: =C$4/(1+((C$4-B$4)/B$4)*EXP(-D$4*E4)) Como puede observarse, el u ´nico argumento variable es t y nos interesa mantener a los otros argumentos constantes. Para mantener un valor (que se lea en una celda) constante, se le agrega el s´ımbolo $ antes del n´ umero de fila, como por ejemplo C$4. En nuestro ejemplo, los argumentos constantes son los que est´an en las celdas B4, C4 y D4, mientras que el valor de t en la celda E4, es variable.

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 7

VBA para Excel

Figure 1.4: Evaluaci´on con par´ametros constantes y un par´ametro con un incremento variable.

4. Finalmente, escribimos varios valores para t en la columna E, seleccionamos la celda F4 y arrastramos para evaluar P (t) en el resto de valores de t. µ ¶ dP P Nota: P (t) es la soluci´on de la llamada ecuaci´ on log´ıstica = kP 1 − . dt K Construyendo rangos con un incremento fijo A menudo necesitamos evaluar una funci´on en una secuencia de valores igualmente espaciados, por lo que a continuaci´ on se explica c´omo hacerlo, modificando el ejemplo previo de crecimiento de una poblaci´on. 1. Podemos seleccionar la columna C para poner los valores fijos P0 , K, k y el incremento h. En este caso, por ejemplo, h = 5 servi´a como incremento entre un tiempo y el siguiente, iniciando con t = 0. 2. En la celda E4 escribimos el tiempo inicial t = 0 y en la celda E5 se escribe el nuevo tiempo con el incremento h: =+E4+C$6 Debemos usar C$6 para que el incremento se mantenga inalterado al copiar esta operaci´on en otra fila celda situada en una fila diferente. 3. Ahora seleccionamos esta celda E5 y la arrastramos hacia abajo para obtener los nuevos tiempos con el respectivo incremento.

Figure 1.5: Evaluaci´on con un par´ametro de incremento fijo

Nota: Esto tambi´en se puede hacer escribiendo, en celdas consecutivas, un valor y luego el valor m´as el incremento y luego seleccionando ambas celdas y arrastrando. Sin embargo, en algunos algoritmos es m´as c´omodo

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 8

VBA para Excel

tener una celda d´onde leer el incremento.

1.3

Gr´ aficas

Continuando con el ejemplo anterior en el que se ha evaluado la poblaci´on P (t) en un conjunto de valores del tiempo t, recordemos que en las columnas E y F se han escrito, respectivamente, los valores de t y P (t). Para graficar P (t) con respecto a t, pordemos seguir los siguientes pasos: 1. Seleccionamos el rango en el cual se encuentran los valores de t y P (t). Este rango puede incluir las celdas que contienen los r´otulos de las columnas.

2. Presionamos el icono

, que activa el asistente para gr´aficos.

Hay varias opciones que podemos elegir para el gr´afico y en nuestro caso podemos elegir el tipo Dispersi´ on.

Figure 1.6: Selecci´on del tipo de gr´afico.

3. Presionamos el bot´on Siguiente y luego Finalizar . Antes de finalizar se pueden escoger distintas opciones para personalizar el gr´afico. A continuaci´ on se muestra la salida del gr´afico.

Figure 1.7: Curva obtenida para el modelo log´ıstico de crecimiento de una poblaci´on. La curva obtenida se llama curva log´ıstica o sigmoide, por la forma de ‘S’ que tiene.

VBA para Excel

1.4

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 9

Programaci´ on de macros

1.4.1

Introducci´ on

El lenguaje Visual Basic para Aplicaciones (VBA), en el contexto de Excel, constituye una herramienta de programaci´on que nos permite usar c´odigo Visual Basic adaptado para interactuar con las m´ ultiples facetas de Excel y personalizar las aplicaciones que hagamos en esta hoja electr´onica. Las unidades de c´odigo 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. Est´an 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 espec´ıficas pero no devuelven ning´ un 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 par´ametros en algunas celdas y escribir en otras para completar un cuadro de informaci´on a partir de los datos le´ıdos.

Editar y ejecutar macros. Las funciones y las subrutinas se pueden implementar en el editor de Visual Basic (Alt-F11). Para usar una funci´on en una hoja de Excel se debe, en el editor de VB, insertar un m´odulo y editar la funci´on en este m´odulo. Esta acci´on de describe m´as adelante. De la misma manera se pueden editar subrutinas en un m´odulo. Una funci´on se invoca en una hoja, como se invoca una funci´on de Excel o una f´ormula. Una subrutina se puede invocar por ejemplo desde la ventana de ejecuci´on de macros (Alt-F8) o desde un bot´on que hace una llamada a la subrutina (como respuesta al evento de hacer clic sobre ´el, por ejemplo). El c´odigo que ejecuta un bot´on puede llamar a subrutinas y a las funciones de la hoja. El c´odigo del bot´on no est´a en un m´odulo. En la hoja de edici´on donde se encuentra el c´odigo del bot´on, se pueden implementar funciones para uso de este c´odigo pero que ser´an desconocidas para la hoja (mensaje de error #¿NOMBRE?). Nota: un error frecuente es editar una funci´on en un m´odulo que corresponde a una hoja y llamarlo desde otra hoja. En este caso se despliega el error (mensaje de error #¿NOMBRE?).

1.4.2

Funciones

Una funci´on tiene la siguiente sintaxis: Function NombreFun(arg1, arg2,...,argn) Declaraci´on de Variables y constantes Instruccci´ on 1 Instruccci´ on 2 ...

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 10

VBA para Excel

Instruccci´ on k NombreFun = Valor de retorno ’comentario End Function Una funci´on puede tener o no tener argumentos, pero es conveniente que retorne un valor. Observe que se debe usar el nombre de la funci´on para especificar la salida: NombreFun = Valor de retorno Nota 1: Al interior de las funciones, se pueden hacer comentarios utilizando (antes de ´estos) 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 may´ usculas y min´ usculas. Ejemplo 1: implementar una funci´ on. Vamos a implementar como una macro la funci´on con la que se trabaj´o previamente: f (x) = 2x3 + ln(x) −

cos(x) + sen(x) ex

Para su definici´on y utilizaci´on, se siguen los pasos: 1. Ingresamos al men´ u y en la opci´on Herramientas seleccionamos Macros. Luego se elige Editor de Visual Basic:

Figure 1.8: Primeros pasos para la definici´on de una macro.

Tambi´en puede usar Alt - F11 2. Nuevamente, en el men´ u de la ventana que se abre, se elige Insertar, para luego seleccionar M´ odulo: 3. Ahora en la pantalla de edici´on del m´odulo, escribimos el siguiente c´odigo:

Function f(x) f = 2 * x ^ 3 + Log(x) - Cos(x) / Exp(x) + Sin(x) End Function 4. Una vez que ha sido editado el c´odigo del macro, se salva y salimos del ambiente de programaci´on en Visual Basic para volver a la hoja electr´onica de donde partimos. Esto se hace en el men´ u Archivo, seleccionando Cerrar y Volver a Excel.

VBA para Excel

M.Sc. Walter Mora F., M.Sc. Jos´e Luis Espinoza B. 11

Figure 1.9: Se inserta un m´odulo en ...