[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
  $\quad \quad$Select the cell and assign the value.
  $\quad \quad$Or assign the value directly.
  • Absolute and Relative Reference

  • ''''Absolute Reference
    Sub 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").Select
    End Sub

    ''''Relative Reference
    Sub 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").Select
    End 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:
    $\quad$ Press Alt + F11 to go to Microsoft Visual Basic.
    $\quad$ Press F1 to go to Microsoft Visual Basic Help.
    $\quad$ 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 $\quad \quad \quad$ Dim iFixedSizeArray(10) as Integer
    e.g., dynamic array $\quad \quad \quad$ 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 storage
    End 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.