VBA programming

1. VBA programming Introduction to Visual Basic for Applications programming in Excel

2. Resources • FAQ : http://msdn2.microsoft.com/en-us/isv/bb190540.aspx • Reference : http://msdn2.microsoft.com/en-us/isv/bb190540.aspx • Books • Excel online help

3. Excel programming • Two ways to access the Excel programming environment: • Using the menus • Alt F11

4. Development environment Editor Project explorer Property window Watch (debug)

5. Add a function to Excel Right click on “Microsoft Excel objects” : Select “Module”

6. Generalities about VBA • VBA is compiled on the fly (at the moment of execution). • Case insensitive, the VBA editor changes the text case to agree with syntax of existing objects. • A function can be called MyFunction but if you type myfunction the editor will change m and f to upper case. • Comments are from the ‘ sign to end of line. • When typing code you may consider using the edit toolbar.

7. Structure of a function Function delimiters Argument list Function type Argument type Argument name Function name Function body: Attribution acts like return value

8. Call from Excel

9. Requirements for a function to be called from Excel • Cannot change the structure of a sheet. • Cannot change the propeties of a cell. • If the function calls another function that on should respect the same restrictions. • If a function is to be called even if the arguments have not changed then the Application.Volatile property should be called. • Must return a type that Excel knows how to handle.

10. Subroutines • Sub’s do not return results unlike functions. • Sub’s cannot be directely called from Excel as they do not return results. • If a Sub is called from a function that is called from Excel then is should respect the same restrictions as functions callable from Excel.

11. Default values • Function and Sub parameters may be optional. If a parameter is optional then all following parameters should also be optional. • If an optional parameter is of type Variant then we may test it’ s absence by using the Is Missing test. Sub ajoutEspace(ByRefstr As String, Optional nb As Integer = 5) Dim prefix As String prefix = Space(nb) str = prefix & str Debug.Printstr End Sub Sub ajoutEspace(ByRefstr As String, Optional vnb As Variant) Dim prefix As String Dim nb As Integer If IsMissing(vnb) Then nb = 5 Else nb = CInt(vnb) End If prefix = Space(nb) str = prefix & str Debug.Printstr End Sub

12. Named parameters • Whan calling a functions parameters may be referenced by their name. Sub ajoutEspace(ByRefstr As String, Optional nb As Integer = 5) Dim prefix As String prefix = Space(nb) str = prefix & str Debug.Printstr End Sub ajoutEspacestr:=res, nb:=10

13. Variable declaration A variable is declared suing the Dim keyword FunctionsurfCercle(x As Double) As Double Dim pi As Double pi = 3.1415279 surfCercle = x * x * pi End Function Variable declaration may be omited. On taht case the undeclared variables are Variant. The Option Explicit instruction on top of a module makes the variable declaration mandatory.

14. Types • String : character strings. • Numerical types: integers, floating point reals (simple or double precision) and fixed point reals (decimal). • Dates : dates are represented as the number of days since January 1 1900. • Arays: collections of variables of same type, indexed by integers. The programmer as the choice of the indexes, default is 1 to N. • Variant : type that can store data of any other type. • Objets : types that are not part of the language base types and are defined on libraries.

15. Strings • A character string is declared as String as in the following example • Dim message As String • Lots of functions allow string manipulations : • Len, returns the size • InStr, looks inside for contents • Replace, modifies a String • Etc.

16. Numerical types • Integers: • Byte • Integer, integer 2 bytes • Long, integer 4 bytes • Decimal : Currency • Floating point reals: • Single, 4 bytes • Double, 8 bytes

17. Booleans and dates • Boolean, represents a logical condition (true or false). • Date, represents a date as the number of days since January 1 1900. There is a one day difference with Java GregorianCalendar dates.

18. Arrays • An array is declared by following the name of a variable with parentheses and, eventually, a size. • Elements of an array are accessed using indexes. • The global Option allows to change the starting index for arrays, Default is 1. Dim t(1 To 10) As Integer Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer i = 1 While i <= 10 t(i) = j + I i = i + 1 Wend End Sub

19. Function calls • A function is called by using its name and replacing the parameter list by a parenthesised list of expressions of same size. • A function call may replace any expression of the same type as the function. • Exemple : • X=surfCercle(2.0)+4

20. Calling a Sub • There are two ways of calling a Sub: • Call followed by sub name with arguments between() • Nme of Sub (without the keyword Call) folowed by arguments separated by , , Function HelloWorld() As String Dim res As String res = "Hello World" Call ajoutEspace(res) HelloWorld = res End Function Sub ajoutEspace(ByRefstr As String) str = " " & str End Sub Function HelloWorld() As String Dim res As String res = "Hello World" ajoutEspace res HelloWorld = res End Function Sub ajoutEspace(ByRefstr As String) str = " " & str End Sub

21. Sub, Function : parameters • Two ways to pass a parameter to a function or a Sub: • By value, the name of the parameter is preceded by the keyword ByVal. • By reference the name of the parameter is reseded by the keyword ByRef. This is the default parameter passing method.

22. Global variables • A variable declared outside a function or Sub body is global. • Two types of visibilities are possible: • Public, visible on all modules, • Private, visible only on the current module.

23. Flow control • Conditionals: • If • Iif Function Signe(x As Double) As Byte If x > 0 Then Signe = 1 ElseIf x < 0 Then Signe = -1 Else Signe = 0 End If End Function Function sPlus(x As Double) As Double sPlus = IIf(x > 0, x, 0) End Function

24. Loops Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer i = 1 While i <= 10 t(i) = j + i i = i + 1 Wend End Sub • While • For • For Each, used with collections. Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer For i = 1 To 10 t(i) = j + i Next i End Sub

25. Cell manipulation • Sheets and cells in Excel are represented by variables of type defined on an Excel library, they can be manipulated directly from VBA. • A variable exists corresponding to each sheet :

26. Object usage • Un object is a variable with a type defined by a class on an external library. • Like any other variable un object is declared using the keyword Dim. • Once declared an object is not initialized. • A non initialized object is Nothing. • In order to initialize an object the keyword Set in must be used in conjunction with the instruction New that allocates the memory needed by the object.

27. Objects and classes • A class is a type that does not belong to the VBA basic types. • Classes are defined on libraries. • A variable whose type is a class is an Object. • An Objet contains members that may be: • properties, • Methods (functions or subs) • Two objects of same type may have properties with different values.

28. Accessing cells • One of the members of the worksheet class is the method Cells. • There are several versions of the method Cells, among those: • A version with two integer arguments represnting the line and column of the cell. • A version with a single String argument represnting the cell as in “A1”. • The Cells method returns an object of type Range that can be used to manipulate the cell contents.

29. Range class members • Value, property, represents the contents of the cell. • Text, property, represents the text that is displayed on the cell. • Formula, property, represents the formula stored on the cell. • ClearContents, method, erases the cell contents. • ClearComments, method, removes the cell comment.

30. Example Option Explicit Sub displaySquare() Dim i As Integer For i = 1 To 100 result.Cells(i, 1).Value = i * i result.Cells(i, 1).Interior.ColorIndex = i Mod 32 Next i End Sub Result is an object of type Excel.Worksheet Excel -> library Worksheet -> class Cells method from theWorksheet class with 2 arguments (there is another method with same name and 1 argument) returns an object of type Excel.Range Value property of the Range class

31. Events • Events are special subroutines that are executed automatically. Event execution is triggered by external actions.. • Some examples: • Button click (with mouse or enter key). • Cell contents change • Cell selection change. Private Sub btnGo_Click() displaySquare End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub End If If Target.Address = "$A$1" Then calc.Cells(1, 4).Value = "A1 Changed" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) calc.Cells(2, 4).Value = "selection is now " & Target.Address End Sub

32. Events Class name. On certain cases Object name Event name

33. Errors • The default behavior of a program when an error occurs is to stop it and display a message box. • A different behavior can be specified using the following instructions: • On Error Goto 0, restarts the default behavior. • On Error Resume Next, ignores the errors and moves to the next instruction. • On Error Goto <label>, jumps to <label> (line marked by <label>:) when an error occurs. • If an error occurs the Err variable is initialized.

34. Errors (next) • The Err variable contains several members, the most important: • Number, contains the unique error code. • Description, contains the error description. • Raise, generates an error (useful to signal an error to another part of the program). • Clear, cleans the next error.

35. Errors(example) Sub ajoutEspace(ByRefstr As String, Optional vnb As Variant) Dim prefix As String Dim nb As Integer On Error GoToerr_label If IsMissing(vnb) Then nb = 5 Else nb = CInt(vnb) End If prefix = Space(nb) str = prefix & str Exit Sub err_label: MsgBoxErr.Description End Sub Modifies the error handling Label Error handling

36. Class modules • A class module is useful to define its own classes. • A class may contain 3 kinds of members: • Member variables • Methods • Properties • 2 different visibilities for members: • Private • Public

37. Creating a class

38. Example : Share Option Explicit Public nom As String Public cours As Currency Member variable

39. Example: share Option Explicit Private isCall As Boolean Public strike As Currency Public maturity As Date Public sousjacent As Act Public Property Let TypeContrat(tp As String) If LCase(tp) = "call" Then isCall = True Else isCall = False End If End Property Private member Public members Property: write

40. Example: share Public Property Get TypeContrat() As String If isCall Then TypeContrat = "Call" Else TypeContrat = "Put" End If End Property Property: read

41. Example : Stock shares Public Function GetPrice(r As Double, vol As Double) As Double Dim td As Date Dim days2exp As Long td = Date days2exp = maturity - td If isCall Then GetPrice = Call_Eur(sousjacent.cours, strike, days2exp, r, vol) Else GetPrice = Put_Eur(sousjacent.cours, strike, days2exp, r, vol) End If End Function Member function

42. Class usage Sub test() Dim FT As New Act Dim CallFT As New Opt Dim price As Double FT.nom = "Fance Telecom" FT.cours = 15.3 CallFT.TypeContrat = "Call" Set CallFT.sousjacent = FT CallFT.strike = 15 CallFT.maturity = "20/12/2012" price = CallFT.GetPrice(0.01, 0.2) Debug.Print price End Sub Declaration/allocation Method usage Method call

43. Forms • Forms are graphical objects that can be displayed on Excel. • Forms are used to interact with the user. • A form is composed from a canvas to which we add graphical components. • The Excel toolbox provides different controls (buttons, text boxes, labels, list boxes, etc.). • Each control provides events that are used to interact with the user. • To each form a module is associated.

44. Creating a form

45. Form and toolbox

46. Form design

47. Code of a form Event for the click button. Access to a property of another control