Visual Basic En Ms Excel

Visual Basic en MS Excel

Fabricio Chala

Elementos básicos de VBA  Lenguaje basado en objetos con orden jerárquico (p.e. libros, hojas, rangos, 

ventanas, gráficos, etc.)  Acceso a la barra de menú:  

ALT + F11  Programador / Visual Basic

Ventana de explorador  de proyectos Ventana de código Ventana de  propiedades

Visual Basic en MS Excel ‐ Fabricio Chala

2

Entorno de VBA Ventanas  Explorador de Proyectos 

Cada libro abierto se considera un proyecto (colección de objetos programables).  En el siguiente nivel, se encuentra una carpeta con las hojas que componen el libro.  Si además se incluyen módulos de código y formularios, se muestran en el explorador.  Código 

Ventana donde se editan las macros para modificar el código, crearlas o borrarlas.

 Propiedades 

Se ven las propiedades del objeto seleccionado en el explorador de proyectos.

Barras:  Barra de menú  Barra de herramientas 

Situadas en la parte superior del entorno, desde ellas se puede acceder a todas las funcionalidades y ayudas que ofrece el entorno de desarrollo para trabajar con los proyectos.

Visual Basic en MS Excel ‐ Fabricio Chala

3

Entorno de VBA Módulo de trabajo  Sirven para agrupar procedimientos y funciones (programación de comandos).  Insertar un módulo de trabajo: 

Opción del menú Insertar/ Módulo  Mediante el siguiente procedimiento:

Visual Basic en MS Excel ‐ Fabricio Chala

4

Variables  Es un nombre que designa a una zona de memoria donde se puede almacenar 

algún tipo de datos.   Pueden cambiar su valor a lo largo de la ejecución de un programa.  El nombre de una variable:  

Comenzar con una letra  Longitud de hasta 255 caracteres.  No se admiten espacios en blanco, puntos (.), ni otros caracteres especiales; sólo el carácter de subrayado (_).  Declaración correcta de una variable:

Ámbito_de_Declaración Nombre_Variable As Tipo_dato

Visual Basic en MS Excel ‐ Fabricio Chala

5

Variables  Antes de usar variables es conveniente dimensionarlas (declararlas) y otorgarles un 

ámbito (parte de la aplicación donde la variable es accesible y puede ser utilizada).  Ámbito

Accesibilidad

Dim

La variable es accesible sólo en el procedimiento en el que ha sido definida

Private

La variable es accesible desde todos los procedimientos del módulo (Dim es  equivalente a Private de utilizarse en un módulo).

Public

La variable es accesible desde cualquier punto del proyecto.

Static

Una variable declarada con Dim es reiniciada (a cero) cada vez que se entra en el  procedimiento. Si se declara como Static la variable mantiene su valor entre  llamadas sucesivas.

Visual Basic en MS Excel ‐ Fabricio Chala

6

Variables  Tipos de variables en VBA Tipo

Tamaño

Características de los datos

Byte

1 byte

Valores de 0 a 255.

Boolean

2 bytes

Valores de True o False.

Date

8 bytes

Valores de 0:00:00 (medianoche) del 1 de enero de 0001 a 11:59:59  p.m. del 31 de diciembre de 9999.

Short

2 bytes

Valores de ‐32.768 a 32.767

Integer

4 bytes

Valores enteros de ‐2.147.483.648 a 2.147.483.647

Long

8 bytes

Valores enteros de ‐9.223.372.036.854.775.808 a  9.223.372.036.854.775.807

Single

4 bytes

‐3,4028235E+38 a ‐1,401298E‐45 para los valores negativos 1,401298E‐45 a 3,4028235E+38 para los valores positivos

8 bytes

‐1,79769313486231570E+308 a ‐4,94065645841246544E‐324 para  los valores negativos; 4,94065645841246544E‐324 a 1,79769313486231570E+308 para los  valores positivos

Double

Visual Basic en MS Excel ‐ Fabricio Chala

7

Variables Tipo

Tamaño

Características de los datos

Decimal

16 bytes

0 a +/‐79.228.162.514.264.337.593.543.950.335 (+/‐7,9... E+28) sin  separador decimal 0 a +/‐7,9228162514264337593543950335 con 28 posiciones a la  derecha del decimal el número distinto de cero más pequeño es +/‐ 0,0000000000000000000000000001 (+/‐1E‐28)

String

10 bytes +

Desde 0 a 2,000 millones de caracteres.

Variant (con  números)

16 bytes

Cualquier valor numérico hasta el intervalo de un tipo Double.

Variant (con  caracteres)

22 bytes +

El mismo intervalo que para un tipo String de longitud variable.



No es necesario declarar variables que se van a utilizar (toma por defecto el tipo  Variant).  La no declaración de variables: 

puede provocar que se utilicen variables con nombres tecleados de forma incorrecta.   las variables del tipo Variant consumen más recursos de memoria.

Visual Basic en MS Excel ‐ Fabricio Chala

8

Variables objeto  A diferencia de las variables, las variables‐objeto no solo contienen valores

específicos, sino representan un objeto dentro del entorno de VBA.  No es necesario declarar estas variables, a menos que Option Explicit esté activado. Set NombreVariable = Objeto

Ejemplo: Set Rango1 = Range(“A1:A5”) Set Rango2 = Range(“B1:B5”) [C5] = WorksheetFunction.Sum(Rango1,Rango2)

Visual Basic en MS Excel ‐ Fabricio Chala

9

Operadores básicos Tipo

Aritmético

Concatenación

Relacional

Operación

Operador en VB

Exponente

^

Multipliación

*

División

/

División entera

\

Residuo de una división entera

Mod

Suma / Resta

+ / ‐

Concatenar o enlazar

& / +

Igual a

=

Distinto

<>

Menor que / menor o igual que 

< / <=

Mayor que / mayor o igual que

> / >=

Visual Basic en MS Excel ‐ Fabricio Chala

10

Operadores básicos Tipo Otros

Lógico

Operación

Operador en VB

Comprar dos expresiones de caracteres

like

Comprar dos referencias a objetos

is

Negación

Not

Y

And

O inclusivo (cierto si FV, VF o VV)

Or

O exclusivo (cierto si FV o VF)

Xor

Equivalencia (cierto si VV o FF)

Eqv

Visual Basic en MS Excel ‐ Fabricio Chala

11

Grabación de macros  EJERICIO 1: Abra algún archivo de Excel, luego, haciendo uso de Guardar Como,

grábelo con otro nombre. Usted visualizará algo similar a: Sub Macro1() ' Macro1 Workbooks.Open Filename:="C:\Caso1.xls", UpdateLinks:=0  ActiveWorkbook.SaveAs Filename:="C:\Mydocuments\Pruebas.xls“_ , FileFormat:=  xlNormal, Password:="", WriteResPassword:="“_ ,ReadOnlyRecommended:=False ,CreateBackup:=False End Sub

Ventajas 

El grabador de macro nos ayuda a descubrir y aprender el código de algunos procedimientos cortos que pueden formar parte de una tarea más larga.  Podemos aprovechar esto para armar un código según lo que buscamos y para incrementar nuestro conocimiento de VBA.

Visual Basic en MS Excel ‐ Fabricio Chala

12

Grabación de macros Desventajas 

El grabador de macros no puede hacer tareas repetitivas, debe listarlas una a una.  La macro guarda más comandos de lo estrictamente necesario, ya que considera todas las posibles opciones, aún cuando éstas no estén siendo utilizadas.  Típicamente es posible simplificar el código extraído a partir de cualquier grabación que se realice.  EJERICIO 2: Sobre la base de la macro anterior cree una nueva macro que abra la

primera tabla de la nota semanal desde la web del BCR y la guarde en el disco de su pc con el nombre cuadro1, una contraseña de acceso y un acceso de solo lectura. Sub Ejercicio2() Workbooks.Open Filename:= _ "http://www.bcrp.gob.pe/bcr/dmdocuments/Estadistica/Cuadros/Semanales/NC_001.xls" ActiveWindow.Visible = False ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Cuadro1.xls", FileFormat:= xlNormal, Password:=“aaa", _ WriteResPassword:="", ReadOnlyRecommended:=True, CreateBackup:=False End Sub Visual Basic en MS Excel ‐ Fabricio Chala

13

Referencias absolutas vs. relativas Absolutas

Relativas

Sub Macro4A() Range("A1").Select ActiveCell.FormulaR1C1 = "Ingresos" Range("A2").Select ActiveCell.FormulaR1C1 = "costos" Range("A3").Select ActiveCell.FormulaR1C1 = "Utilidad bruta" Range("A4").Select ActiveCell.FormulaR1C1 = "Impuestos" Range("A5").Select ActiveCell.FormulaR1C1 = "Utilidad neta" Range("A6").Select End Sub

Sub Macro4RA() ActiveCell.Offset(0, 0) = “Ingresos” ActiveCell.Offset(1, 0) = “Costos” ActiveCell.Offset(2, 0) = “Utilidad Bruta” ActiveCell.Offset(3, 0) = “Impuestos” ActiveCell.Offset(4, 0) = “Utilidad Neta” End Sub Sub Macro4RB() With ActiveCell .Offset(0, 0) = "Ingresos" .Offset(0, 1) = "Costos" .Offset(0, 2) = “Utilidad Bruta " .Offset(0, 3) = “Impuestos" .Offset(0, 4) = “Utilidad Neta" End With End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

14

Objetos Application  El objeto Application representa la aplicación de Microsoft Excel.  Ocupa el lugar más alto en la jerarquía y por tanto, todos los demás objetos y propiedades dependen de él.  Ejemplo: si se quiere especificar exactamente a qué celda nos referimos escribimos [A1]=“hola”, se deberá declarar: Application.Workbooks(1).Sheets(1).[A1]= "Hola"

 Propiedades:

Caption, DisplayAlerts, DisplayFormulaBar, ScreenUpdating, WindowState, ActiveCell, ActiveChart, AutoCorrect, Left, Height, Top, Width, Version, ActiveSheet, etc.  Métodos: Calculate, Quit, GetOpenFilename, GetSaveAsFilename, etc.

Visual Basic en MS Excel ‐ Fabricio Chala

15

Objetos Workbook  El objeto Workbooks es el conjunto de todos los libros abiertos de la aplicación.  El objeto Workbook representa un libro de trabajo de Excel.  ActiveWorkbook se refiere al libro de trabajo activo.  ThisWorkbook devuelve el libro donde se ejecuta el código de Visual Basic.  El objeto Workbooks contiene tanto objetos : 

Chart (gráficos)  Worksheet (hojas de cálculo).  Propiedades: Name, Fullname, Saved, SaveLinkValues, etc.  Métodos: Activate, Close, Protect, Save, Open, etc.

Sub Ejemplo() Msgbox(“Nombre del libro activo: ” & ActiveWorkbook.Name) End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

16

Objetos Worksheets y Worksheet  El objeto Worksheets representa el conjunto de objetos Worksheet (hojas) que contiene el libro especificado. 

Propiedades: Count, Creator, Parent, Visible, etc.  Métodos: Add, Copy, Delete, Move, PrintOut, PrintPreview, Select, etc.  El objeto Worksheets representa el conjunto de objetos Worksheet (hojas) que

contiene el libro especificado. 

Propiedades: Index, Name, UsedRange, Visible, etc.  Métodos: Activate, Calculate, Delete, Copy, Protect, PivotTableWizard, etc. Sub ObjetosHojas() NumHojas = Worksheets.Count MsgBox("Número de hojas: " & NumHojas) Sheets.Add after:=Sheets(1) Sheets(2).Select ActiveSheet.Delete End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

17

Objetos Range  El objeto range puede representar una única celda, un rango de celdas, una columna o una fila entera, o una selección conteniendo múltiples áreas. Para hacer

Se utiliza

Darle un valor a la celda C28 en la hoja1

Worksheets(“Hoja1”).Range(“C28”).Value=3

Escribir una formula en la celda B4 de la  hoja activa

ActiveSheet.Range(“B4”)=“=5*10*RAND()”

Dar valor a cada celda del rango C1:E3 de la  hoja activa

Range(“C1:E3”).Value=6

Borrar los contenidos del rango A1:B2 y C4

Range(“A1:B2,C4”).ClearContents

Poner en azul el color de la fuente en un  rango con nombre “mirango” y en negrita

Range(“mirango”).Font.Color=vbBlue Range(“mirango”).Font.Color=RGB(255,0,0) Range(“mirango”).Font.ColorIndex=45 Range(“mirango”).Font.Bold=True

Visual Basic en MS Excel ‐ Fabricio Chala

18

Estructuras condicionales  Existen muchas situaciones en donde uno desea ejecutar un (o varios) comando(s)

solo si es que algunas condiciones son satisfechas. Para dicho fin se hace uso del comando IF.

IF If Condición Then Sentencia(s) Elseif Condición Then Sentencia(s) Else Sentencia(s) End If

Visual Basic en MS Excel ‐ Fabricio Chala

19

Estructuras condicionales  EJERCICIO 3: Entrar una cantidad que representa el precio de algo por el teclado

con la instrucción InputBox y guardarlo en la celda A1 de la hoja activa. Si el valor entrado desde el teclado (y guardado en A1) es superior a 1000, pedir descuento con otro InputBox y guardarlo en la casilla A2 de la hoja activa. Calcular en A3, el precio de A1 menos el descuento de A2. Sub CondicionalSimple() Sheets ("Hoja1").Select ActiveSheet.Range("A1").Value = 0 ActiveSheet.Range("A2").Value = 0 ActiveSheet.Range("A3").Value = 0 ActiveSheet.Range("A1").Value = Val(InputBox _("Entrar Precio", "Entrar")) If ActiveSheet.Range("A1").Value > 1000 Then ActiveSheet.Range("A2").Value = Val(InputBox _ ("Entrar Descuento","Entrar")) End If ActiveSheet.Range("A3").Value = ActiveSheet.Range("A1") .Value ‐ActiveSheet.Range("A2").Value End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

20

Estructuras condicionales  En ocasiones se dará el caso que en función del valor o rango de valores que

pueda tener una variable, una casilla, una expresión, etc. deberán llevarse a cabo diferentes acciones o grupos de acciones.

Select Case Select Case Expresión Case valores Instrucciones Case valores Instrucciones ... Case valores Instrucciones Case Else Instrucciones en en el resto de los casos. End Select

Visual Basic en MS Excel ‐ Fabricio Chala

21

Estructuras condicionales  EJERCICIO 4: Macro que suma, resta, multiplica o divide los valores de las casillas

A1 y A2 dependiendo de si B1 contiene el signo “+”, “‐”, “x” ó “:”. El resultado debe aparecer en A3. Si en B1 no hay ninguno de los signos anteriores en A3 debe dejarse un 0. Sub Ejercicio4() Dim Signo As String, Valor1 As Double, Valor2 As Double, Total As Double Valor1 = ActiveSheet.Range("A1").Value Valor2 = ActiveSheet.Range("A2").Value Signo = ActiveSheet.Range("B1").Value Total = 0 Select Case Signo Case "+" Total = Valor1 + Valor2 Case "‐" Total = Valor1 ‐Valor2 Case "x" Total = Valor1 * Valor2 Case ":" Total = Valor1 / Valor2 End Select ActiveCell.Range("A3").Value = Total End Sub Visual Basic en MS Excel ‐ Fabricio Chala

22

Bucles abiertos Un bucle abierto es la repetición de la ejecución de una sentencia o bloque de sentencias hasta que se cumpla una condición: un número desconocido de veces. While … Wend  Este comando responde a una estructura de bucle que ejecuta una serie de

comando o sentencias, mientras se cumpla la condición inicial. While … Wend While Condición Sentencia 1 Sentencia 2 … Sentencia N Wend

Visual Basic en MS Excel ‐ Fabricio Chala

23

Bucles abiertos  EJERCICIO 5: Rellenar la primera columna de la hoja activa con números aleatorios

generados por la función RND() (entre 0 y 1) – desde Excel se usa aleatorio() – hasta una celda en la que la suma de los todos los valores introducidos no excedan un número que el usuario puede introducir a su elección (a través de un inputbox). Borrar el resultado de la anterior ejecución cada vez que se ejecute la macro. Sub Ejemplo5() Dim x As Double, aleatorio As Double, fila As Byte, suma As Double Suma = CDbl(InputBox("Introduzca la Suma valores aleatorios")) Sheets("hoja2").Activate Cells.Select Selection.Clear Range("A1").Select While x < suma fila = fila + 1 aleatorio = Rnd() Cells(fila, 1) = aleatorio x = x + aleatorio Wend End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

24

Bucles abiertos Do While … Loop  La

estructura de control “Do…Loop” funciona exactamente igual que “While…Wend”. Sin embargo, utilizando “Do…Loop” tenemos la opción de situar la condición al principio o al final del bucle.  Situando la condición al final garantizamos que las acciones del interior del mismo se ejecutarán como mínimo una vez. Do While … Loop Do While Condición Sentencias [Exit Do] Sentencias Loop

Do While Sentencias [Exit Do] Sentencias Loop Condición

 La sentencia opcional Exit Do permite salir del bucle “Do...Loop” antes de que este

finalice. (ojo: se debe evaluar una condición que permita salir eventualmente).

Visual Basic en MS Excel ‐ Fabricio Chala

25

Bucles abiertos Do Until … Loop & Do … Loop Until  Las siguientes formas ofrecen la posibilidad de que una acción se ejecute tantas

veces como sea necesario hasta que una condición se cumpla. De nuevo, la condición se puede ubicar al principio o al final del bucle Do Until … Loop Do Until Condición Sentencias [Exit Do] Sentencias Loop

Do Sentencias [Exit Do] Sentencias Loop Until Condición

Visual Basic en MS Excel ‐ Fabricio Chala

26

Bucles abiertos  EJERCICIO 6: El programa va pidiendo datos (apellido, nombre, edad y fecha de

ingreso) para ser ingresados a través de InputBoxs en la siguiente tabla (hoja1):

Cuando al preguntar el nombre no se ingrese ningún valor (se deja vacío el cuadro de texto de apellido y se presiona Enter), terminará la ejecución del bucle cerrado “Do While...Loop”. Considere la utilización de la propiedad Offset para colocar los datos en las celdas correspondientes.

Visual Basic en MS Excel ‐ Fabricio Chala

27

Bucles abiertos Sub Ejemplo6() Dim Nombre As String, Ciudad As String, Edad As Integer Dim Fecha As Date, Contador As Byte ActiveSheet.Range("A2").Activate Apellido = InputBox("Ingrese el Apellido (Enter para Terminar) : ", "Apellido") Contador = 1 Do While Apellido <> "" Nombre = InputBox("Ingrese el Nombre : ", "Nombre") Edad = Val(InputBox("Ingrese la Edad : ", "Edad")) Fecha = CDate(InputBox("Ingrese la Fecha de Ingreso: ", "Fecha")) With ActiveCell .Value = Contador .Offset(0, 1).Value = Apellido .Offset(0, 2).Value = Nombre .Offset(0, 3).Value = Edad .Offset(0, 4).Value = Fecha End With ActiveCell.Offset(1, 0).Activate Apellido = InputBox("Ingrese el Apellido (Enter para Terminar) : ", "Apellido") Contador = Contador + 1 Loop End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

28

Bucles cerrados  Esta estructura de comandos sirve para repetir la ejecución de una sentencia o

bloque de sentencias, un número conocido de veces.

For … Next For Variable = Valor_Inicial To Valor_Final Step Incremento Sentencia 1 Sentencia 2 … Sentencia N Next Variable

Visual Basic en MS Excel ‐ Fabricio Chala

29

Bucles cerrados  EJERCICIO 7: Entrar 10 valores utilizando la función InputBox, registrarlos en la

hoja, sumarlos y guardar el resultado en la casilla B1 de la hoja1. Sub ejercicio7() Sheets("Hoja1").Select Dim i As Integer, total As Integer, Valor As Integer For i = 1 To 10 Valor = Val(InputBox("Entrar un valor", "Entrada")) Range("a1").Offset(i, 0) = Valor Total = Total + Valor Next i ActiveSheet.Range("B1").Value = Total End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

30

Bucles cerrados  El bucle “For Each...Next” se utiliza para repetir un conjunto de sentencias para

cada elemento de una colección (cada hoja de un libro, cada libro en un conjunto de libros abiertos, etc.), siendo el número de elementos indeterminado.  La estructura sería similar a la del bucle “For...Next” con la diferencia de que la variable que controla la repetición del bucle no toma valores entre un mínimo y un máximo, sino a partir de los elementos de la colección.

For each … Next For Each variable In grupo Sentencias Next

Visual Basic en MS Excel ‐ Fabricio Chala

31

Bucles cerrados  EJERCICIO 8: Programa que pregunta el nombre para cada hoja de un libro de

trabajo, si no se pone nombre a la hoja, queda el que tiene. Sub Ejm_ForEach() Dim Nuevo_Nombre As String Dim Hoja As Worksheet For Each Hoja In Worksheets Nuevo_Nombre = InputBox("Nombre de la Hoja : " _ & Hoja.Name, "Nombrar Hojas") If Nuevo_Nombre <> "" Then Hoja.Name = Nuevo_Nombre End If Next End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

32

Formulas  La diferencia fundamental entre una subrutina y una función es que esta última

puede devuelve un solo valor (al igual que las funciones del MS Excel), a diferencia de las subrutinas que no tienen por qué devolver valor alguno.  Los procedimientos Function se pueden usar en dos situaciones: 

Como parte de una expresión en un procedimiento en VBA  En fórmulas que se crean en una hoja de cálculo.

Function nombre ([parámetros]) [As Tipo] Sentencias nombre = expresión End Function

Visual Basic en MS Excel ‐ Fabricio Chala

33

Formulas  EJERCICIO 9: Función que transforma Dólares en Euros (TC: 0.77) y cuyo resultado

aparezca redondeado a dos decimales. Function EUROS(dolares as double) As double EUROS=Application.Round (dolares/0.77, 2) End Function  EJERCICIO 10: Un procedimiento para comunicar el segundo ejemplo con Excel es a través del

uso de un convertidor de dólares a euros por medio de cuadros de mensajes: Sub ejercicio10() Dim resultado As Double Dim Dolares As Long Dolares = CLng(InputBox("Introduzca monto en dólares")) resultado = euros(Dolares) MsgBox (Dolares & " dólares son: " & resultado & " euros") End Sub

Visual Basic en MS Excel ‐ Fabricio Chala

34

Formulas  EJERCICIO 11: Crear una función que utilice dos vectores como argumentos y

calcule la media ponderada de un vector de junto con un vector de ponderaciones: 1

1

Considere que si las operaciones que hay que realizar con los elementos de los vectores requieren hacer referencias a los elementos del vector de forma individual, se puede utilizar un índice o expresiones del tipo For Each / Next. Function TASAMEDIA(tasas) Parcial = 1 Numero = 0 For Each x In tasas Parcial = Parcial * (1 + x) Numero = Numero + 1 Next x TASAMEDIA = Parcial ^ (1 / Numero) ‐ 1 End Function

Visual Basic en MS Excel ‐ Fabricio Chala

35

Formulas  EJERCICIO 12: Arme una matriz de varianzas y covarianzas de las series históricas. Function Covarianzas(Datos As Range) Dim K() As Double ReDim K(Datos.Columns.Count, Datos.Columns.Count) For J1 = 1 To Datos.Columns.Count For J2 = J1 To Datos.Columns.Count K(J1 ‐ 1, J2 ‐ 1) = Excel.WorksheetFunction.Covar(Datos.Columns(J1), Datos.Columns(J2)) If J1 <> J2 Then K(J2 ‐ 1, J1 ‐ 1) = K(J1 ‐ 1, J2 ‐ 1) Next J2 Next J1 Covarianzas = K End Function

Visual Basic en MS Excel ‐ Fabricio Chala

36

Add‐in de funciones personalizadas  Visual Basic permite grabar las formulas creadas como un complemento de Excel

(“.xlam”), con ello se podrá tener acceso a la totalidad de las funciones programadas en cualquier archivo que se ejecute en el programa.

Visual Basic en MS Excel ‐ Fabricio Chala

37

Add‐in de funciones personalizadas

Visual Basic en MS Excel ‐ Fabricio Chala

38