[Slides notes] Financial Modeling using VBA

Initialization:
1. Left click Office Button, Left click Excel Options. Check Show Developer tab in the Ribbon. Left click OK.
2. Select Developer tab. Left click on Macro Security.
3. Check Enable all macros and Trust access to the the VBA project model. Left click OK.

Macro Recorder

Press Alt + F8 to look over macro, to edit or run it.

• Record your actions in VBA
  
  • Under the Developer Tab, left click on Record Macro. Name the record and click OK.
  • Begin record the actions in VBA and left click the mouse button on Stop Recording
• Assign marco to button
  
  • Select Developer tab, left click on Insert. And select Button
  • Left click mouse button anywhere, and draw a button.
  • In the Assign Macro dialogue, select a function and click OK.
  • Name the button.
• Modifying macro code
  Select the cell and assign the value.
  Or assign the value directly.
• Absolute and Relative Reference

''''Absolute ReferenceSub EnterValues()'' EnterValues Macro' Macro recorded 7/21/2008 by CD Shum'    Range("B40").Select    ActiveCell.FormulaR1C1 = "ABC"    Range("B41").Select    ActiveCell.FormulaR1C1 = "DEFG"    Range("B42").Select    ActiveCell.FormulaR1C1 = "100"    Range("B43").Select    ActiveCell.FormulaR1C1 = "200"    Range("B44").Select    ActiveCell.FormulaR1C1 = "=R[-1]C"    Range("B45").SelectEnd Sub

''''Relative ReferenceSub EnterValuesRelative()'' EnterValuesRelative Macro' Macro recorded 7/21/2008 by CD Shum'    ActiveCell.Offset(0, 0).Range("A1").Select    ActiveCell.FormulaR1C1 = "ABC"    ActiveCell.Offset(1, 0).Range("A1").Select    ActiveCell.FormulaR1C1 = "DEFG"    ActiveCell.Offset(1, 0).Range("A1").Select    ActiveCell.FormulaR1C1 = "100"    ActiveCell.Offset(1, 0).Range("A1").Select    ActiveCell.FormulaR1C1 = "200"    ActiveCell.Offset(1, 0).Range("A1").Select    ActiveCell.FormulaR1C1 = "=R[-1]C"    ActiveCell.Offset(1, 0).Range("A1").SelectEnd Sub

---

Object

Object Model Example Properties Worksheets(“Object”).ActiveCell**.Font.Size** Properties Worksheets(“Object”).Range(“ClearContents”).Value Method Worksheets(“Object”).Range(“ClearContents”).ClearContents Method Workbooks**.Open(“excel_intro_ans.xls”)** Method Worksheets(“Macro Recorder”).Active Hierarchy Application.Workbooks(“vba_intro_ans.xls”).Sheets(“Object”).Range(“YellowCells”)

Help:
Press Alt + F11 to go to Microsoft Visual Basic.
Press F1 to go to Microsoft Visual Basic Help.
Under the Excel Help, left click Excel Object Model Reference.

---

Basics

• Invoke Visual Basic Editor
  Press Alt + F11 or under Developer tab, left click Visual Basic.
• Project Explorer Window
  If not visible, select View–> Project Explorer.
  Code in Microsoft Excel Objects (e.g. Sheet1) or Modules (e.g. Module 1)
  Double click left mouse button on Module 1 to view code.
  To insert a module, select Insert –> Module.
  To remove a module, select the module, right click and select Remove Module.
• Entering Code
  In the Project Explorer Window, double click left mouse on a sheet to view code.
  Each Sub and End Sub is a procedure.
• Getting help
  MsgBox is a VBA built-in function. To get help on MsgBox, in the Code Window, right click mouse button on MsgBox. Select Definition. Left click mouse button on ?.
• Adding Comment
  Text after (‘) is a comment.
• Line Continuation
  A statement is on a line.
  Continue a statement in the next line by putting a space followed by an underscore(_) at the end.

---

Variables

• Variables: Named storage.
• Naming Variables: Case insensitive. E.g., “aBC” is the same as “abc”.
  Cannot use space( ), period(.), exclamation mark(!) or @, &, $, and #. Also, cannot use key words reversed by VBA (e.g., Dim, Integer, Sub).
  Best Practice: Variable names should be descriptive(e.g. SpotPrice, StrikePrice)
• Data Types:

Type Description Boolean (True or False) Converted from numeric types (0 to False; others to True) Byte (0 - 255) Currency@ (15 digits to the left and 4 digits to the right of decimal point) 64-bit Date (date literals #9 Aug 2008#)(e.g., dateV = #9 Aug 2008#) Single (floating point) 32-bit Double # (floating point) 64-bit Integer % (-32,768 to 32,767) 16-bit Long & (-2,147,483,648 to 2,147,483,647) 32-bit String $ (fixed length 64K characters; variable-length 2^31 characters) Object (addresses to objects) Variant (any kind of data except fixed-length String)

• Scope:
  
  • Procedure-Level: Declared within procedure using Dim. Available only within procedure of declaration. Variables not explicitly declared are treated as procedure-level variables.
  • Static: Declared within procedure using Static. Available as long as code (project) is running (even after procedure has ended).
  • Module-Level: Declared before 1st procedure of module using Dim or Private. Available as long as code (project) is running.
  • Project-Level: Declared using Public at the beginning (before 1st procedure) of one of the modules. Available to all procedures as long as code (project) is running.

---

Constants

• Date Constants
  
• Color Constants
  
• MsgBox Constants
  
  
• VarType Constants
  

---

Operators

• Common Operators
  
• Comparison Operators
  
  

---

Statements

• If … Then … Else

If condition Then [statements][Else elsestatements]

or

If condition Then    [statements][ElseIf condition Then    [elseifstatements]...[Else    [elsestatements]]End If

• Select Case

Select Case testexpression    [Case expressionslist        [statements]]...    [Case Else        [elsestatements]]End Select

• For … Next

For counter = start To end [Step step]    [statements]    [Exit For]    [statements]Next[counter]

• For Each … Next

For Each element In group    [statements]    [Exit For]    [statements]Next[element]

• Do … Loop

Do [{While | Until} condition]    [statements]    [Exit Do]    [statements]Loop

Or

Do    [statements]    [Exit Do]    [statements]Loop [{While | Until} condition]

---

Array

fixed-size array: known fixed storage
dynamic array：dynamic storage, can be reallocated using ReDim or released using Erase

• Dim

Dim [Preserve] varname(subscripts) [As type][,varname(subscripts)[As type]]...

e.g., fixed-size array Dim iFixedSizeArray(10) as Integer
e.g., dynamic array Dim iDynamicArray() as Integer

• ReDim:

ReDim [Preserve] varname (subscripts) [As type][, varname(subscripts) [As type]]...

Preserve: preserve data in an existing array, can resize only the last array dimension, can only change the upper bound.
without Preserve: numeric variable is initialized to 0, variable-length string is initialized to zero-length string (“”), fix-length string is filled with zeros. Variant variable are initialized to Empty, object variable has special value Nothing.

• Erase
  fixed-size: reinitialize, recover no memory
  dynamic: frees memory

• Array Copy

  • Cannot assign to fixed-size array
  • Assign to dynamic OK:
  • Assign to Variant OK:

Sub fixedArray()    Dim iFA(10) As Integer   'initialized to 0    Dim i As Integer    For i = 0 To 10        iFA(i) = i    Next i    'ReDim iFA(20)           'uncomment and compile to see error    Erase iFA    Dim fsA(5) As String * 5    'initialized to 0    Dim b() As Byte    b = fsA(0)End Sub

Sub dynamicArray()    Dim sDA() As String    ReDim sDA(1, 1)         'initialized to ""    sDA(0, 0) = "element(0,0)": sDA(0, 1) = "element(0,1)"    sDA(1, 0) = "element(1,0)": sDA(1, 1) = "element(1,1)"    ReDim Preserve sDA(1, 2)    'ReDim Preserve sDA(2, 2) 'uncomment and run to see error    ReDim sDA(2, 2)          'all reinitialized to ""    Erase sDA                'release dynamic storageEnd Sub

/// Do not use Variant often, since the complier can not find some type mistake by unknown types

---

Sub & Function

• Sub

[Private | Public][Static] Sub name [(arglist)]    [statements]    [Exit Sub]    [statements]End Sub

• Function

[Public | Private][Static] Function name [(arglist)][As type]    [statements]    [name = expression]    [Exit Function]    [statements]    [name = expression]End Function    

A sub is a procedure return nothing, a function return something with using [As type].

---

Built-In Function

In VBA, there are types of build-in function you can use. Press Alt + F11 to go to Microsoft Visual Baisc. In the standard toolbar, left click mouse button on Object Browser.