curso visual basic
DESCRIPTION
TRANSCRIPT
Programación Visual Basic en Excelaplicado a finanzas y seguros
Julián Oliver
Actuario de Seguros
Profesor de la Universidad Pontificia Comillas
programación VB en Excel
• introducción a la programación• Conceptos básicos• Definición de funciones, procedimientos y
variables • Modelo de objetos• Uso de funciones de la hoja de cálculo
• ejemplos básicos
programación VB en Excel
• referencia básica de VB• Operadores y funciones de VB• Estructura condicional: If-Then-Else• Estructura condicional: Select Case• Bucle con contador: For Each-Next• Bucle con condición: Do While y Do Until
• ejemplos básicos
programación VB en Excel
• ejemplos financieros y de seguros de vida• Amortización de préstamos
– referencias dinámicas, funciones VB y bucles
• Tablas PERM/F 2000– funciones VB
• ejemplos estadísticos y de seguros generales• Cálculo de probabilidades• Ajuste de distribuciones
– toma de decisiones– bucles y referencias dinámicas
• actividad habitual: cálculo de
• bases técnicas y operaciones financieras• provisiones y análisis de rentabilidad técnica
• necesidades:• automatización y precisión de los cálculos
• posibilidades:• lenguaje de programación para usuarios• potente hoja de cálculo
introducción
• programación orientada a objetos• objetos, clases y colecciones• propiedades• métodos
• principales objetos en Excel• Application: la aplicación Excel en su conjunto• WorkSheet: la hoja de cálculo• Range: el rango de celdas
• ayuda completa enAyuda de Microsoft Excel – F1
Información de programaciónReferencia a Visual Basic en Microsoft Excel
Objetos de Microsoft Excel
conceptos básicos
Definición• función
conjunto de instrucciones de VB que sirve para hacer cálculos y devolver un valor
• subrutina
conjunto de instrucciones de VB que sirve para alterar propiedades de objetos
• variable
área de memoria que sirve para almacenar datos
Definición• función
Function test(parámetro1,...)...test = ....End Function
• subrutinaSub test(parámetro1,...)...End Sub
• variableDim variable1 As tipo, variable2 As ....
Tipos de variable más usados• contadores, edades y duraciones ....
Integer
• enteros que vayan a ser usados en productos
Long
• tipos de interés, frecuencias relativas
Single
• capitales, primas, tasas, símbolos de conmut.
Double
Definición de variables
• el nombre de variable debe• empezar por una letra• estar compuesto de letras y números• tener una longitud inferior a 255 caracteres• no coincidir con palabras reservadas de VB
• ejemplo:Dim x as Integer, prima as Double
Modelo de objetos de Excel
Modelo de objetos
todo elemento que deba ser tratado se representa por un objeto que tiene propiedades y métodos:
las propiedades representan las características o variables que configuran el objeto
los métodos representan los mecanismos que permiten manipular el objeto
Modelo de objetos de Excelapplication
representa a la instancia de Excel que se está ejecutando
• propiedadesDisplayAlertsDisplayFullScreenNames (colección de nombres de celdas)OperatingSystem, Version
• métodosCalculate, InputBox
Modelo de objetos de Excel
workbookrepresenta el libro de cálculo
• propiedadesCount (colección)
• métodosAdd (colección)
Close
Open
Modelo de objetos de Excelworksheet
representa la hoja de cálculo
• propiedadesCount (colección)Visible
• métodosAdd (colección)DeletePaste (objeto ActiveSheet)
Modelo de objetos de Excelrange
representa un rango de celdas
• propiedadesCurrentRegionFormulaNumberFormatValue
• métodosCopy, Cut, SelectClearContents
Modelo de objetos de Excelinterior
representa el interior de un rango de celdas
• propiedadesColor
fontrepresenta el tipo de letra de un rango de celdas
• propiedadesBold, Italic, Size
Modelo de objetos de Excel
Application
WorkSheet
Range
representación:Application.WorkSheet.Rangeobjeto.objeto.propiedadobjeto.objeto.método
el objeto Application puede ser omitido
Modelo de objetos de Excel
ejemplo: copiar en la hoja 1 el contenido de las celdas C2 a C5 en las celdas B4 a B7
Sub test() Worksheets("Hoja1").Range("C3:C5").Copy Worksheets("Hoja1").Range(“B4").Select ActiveSheet.PasteEnd Sub
Modelo de objetos de Excel
ejemplo: ocultar la hoja 1
Sub test()WorkSheets(“Hoja1”).Visible = False
End Sub
ejemplo: mostrar la hoja 1
Sub test()WorkSheets(“Hoja1”).Visible = True
End Sub
Modelo de objetos de Excel
ejemplo: borrar el contenido del rango B1:B7Sub test()
WorkSheets(“Hoja1”).Range(“B1:B7”).ClearContents
End Sub
ejemplo: poner formato al rango A1:A7Sub test()With WorkSheets(“Hoja1”).Range(“A1:A7”)
.Font.Color = RGB(0,0,255)
.NumberFormat = “€ #,##0.00”End WithEnd Sub
Uso de funciones de Excelejemplo: encontrar el valor asociado a una letra
En Hoja1, rango E5:F7 completar
En la celda A1 completar =test(A2)
Function test()Application.VLookup(letra,
Application.Worksheets("Hoja1"). Range("E5:F7"), 2)End Function
A 2
B 5
C 3
Uso de funciones de Excel ayuda completa en
Ayuda de Microsoft Excel – F1 Información de programación
Referencia a Visual Basic en Microsoft ExcelLista de funciones de hoja de cálculo disponibles para VB
funciones habitualesAverage() , Var(), VarP()ChiInv(), Tinv()NPer(), Rate(), PMT(), PV(), FV()InputBox()
uso genéricoApplication.Función()
Algunas funciones de Excel
• Average() - Promedio()• BetaDist() - Distr.Beta()• BetaInv() - Distr.Beta.Inv()• ChiInv() - Prueba.Chi.Inv()• Fact() - Fact()• FDist() - Distr.F()• FInv() - Distr.F.Inv()• FV() - VF()
Algunas funciones de Excel
• GammaDist() - Distr.Gamma()
• GammaInv() - Distr.Gamma.Inv()
• HLookUp() - BuscarH()
• Irr() - Tir()
• Ln()
• LogInv() - Distr.Log.Inv()
• LogNormDist() - Distr.Log.Norm()
Algunas funciones de Excel
• Max(), Min()• NormDist() - Distr.Norm()• NormInv() - Distr.Norm.Inv()• NormSDist(), NormSInv()• Nper()• Pmt() - Pago()• Poisson() • PV() - VA()
Algunas funciones de Excel
• Round() - Redondear()
• RoundDown() - Redondear.Menos()
• RoundUp() - Redondear.Mas()
• StDev() - DevEst()
• StDevP() - DevEstP()
• Sum() - Suma(), SumIf() - Sumar.Si()
Algunas funciones de Excel
• TDist() - Distr.T()
• TInv() - Distr.T.Inv()
• Trim() - Espacios()
• Var(), VarP()
• VLookUp() - BuscarV()
Sub test()
Dim prob as Single, gl as Integer
With WorkSheets(“Hoja1”)
prob = .Range(“A1”)
gl = .Range(“A2”)
.Range(“B3”) = Application.ChiInv(prob,gl)
End With
End Sub
Ejemplos
Sub test()
Dim a as Integer, b as Integer, c as Integer
a = Application.InputBox(Type:=1,
Prompt:=“Introduzca la edad”)
b = MsgBox(“valor de a:” & a, vbOkCancel
+ vbInformation)
c = MsgBox(“valor de b:” & b, vbInformation)
End Sub
Ejemplos
Operadores
. operador jerárquico
+ - / * operadores aritméticos
^ potencia
\ Mod cociente y restos enteros
& concatenación de textos
= asignación de valor
Función de densidad de una variante Exponencial:
Function fdExponencial(x, m)Dim e As Double, lambda As Doublee = 2.71828182845905lambda = 1 / mfdExponencial = lambda * e ^ (-lambda * x)End Function
Ejemplos
1
)(,0,·)( · xExexf x
Asignar a la celda C5 la suma de las celdas C3 y C4:
Sub test()
Dim Hj As String, Rg As String
Dim celda1 As String, celda2 As String
Hj = "Hoja2"
Rg = "C5"
celda1 = "C3"
celda2 = "C4"
Worksheets(Hj).Range(Rg).Formula = "=SUM(" & celda1 & ":" & celda2 & ")"
End Sub
Ejemplos
Algunas funciones de VBA
• Abs()• Array()• Choose()• Chr()• CreateObject()• Date()• DateAdd()• DateDiff()• Days()• Exp()
• FormatNumber()• FV()• IRR()• Left()• Log() [Ln]• LTrim()• Mid()• Month()• NPer()• Pmt()
• PV()• Rate()• Replace()• RGB()• Right()• Round()• RTrim()• Str()• Trim()• Year()
Sub test()
Dim a As Integer, b As Integer
a = Application.InputBox(Prompt:="Introduzca la
edad", Type:=1)
If a = False Then ' pulsó Cancelar
b = MsgBox("No introdujo edad", vbOKOnly,
"Resultado")
Else ' pulsó Ok
b = MsgBox("La edad introducida es " & a)
End If
End Sub
Estructura If – End If
Sub test()Dim a As Integer, b As Integera = Application.InputBox(Prompt:="Introduzca la edad", Type:=1)If Not (a = False) Then ' pulsó Ok Select Case a Case Is < 14 b = MsgBox("No es asegurable") Case 14 To 64 b = MsgBox("Es asegurable") Case 65 b = MsgBox("Consultar a la dirección") Case Else b = MsgBox("No cumple requisitos") End SelectEnd IfEnd Sub
Estructura Select Case
Sub test()
Dim fila As Integer
Worksheets("Hoja1").Range("B5").CurrentRegion.
Interior.Color = RGB(200, 200, 0)
For fila = 5 To 10 Step 1
Worksheets("Hoja1").Range("B" & fila).Value =
Int((10 * Rnd) - 5)
Next
For fila = 5 To 10 Step 1
Worksheets("Hoja1").Range("C" & fila).Value =
Int((10 * Rnd) - 5)
Next
End Sub
Bucle For - Next
Sub test() Worksheets("Hoja1").Range("B5").CurrentRegion.
Interior.Color = RGB(200, 200, 0)
For Each c In Worksheets("Hoja1").Range("B5").CurrentRegion.
Cells
If c.Value < 0 Then
c.Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub
Bucle For - Next
Sub test() Dim k As Integerk = 1Do Until Worksheets("Hoja3").Range("B" & k).Value =
"" If Worksheets("Hoja3").Range("B" & k).Value < 0
Then Worksheets("Hoja3").Range("B“ & k).Interior.
Color = RGB(128, 128, 255) End If k = k + 1 Loop End Sub
Bucle Do Until - Loop