[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
    • Object
    • Basics
    • Variables
    • Constants
    • Operators
    • Statements
    • Array
    • Sub Function
    • Built-In Function

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 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(, ).Range("A1").Select
        ActiveCell.FormulaR1C1 = "ABC"
        ActiveCell.Offset(, ).Range("A1").Select
        ActiveCell.FormulaR1C1 = "DEFG"
        ActiveCell.Offset(, ).Range("A1").Select
        ActiveCell.FormulaR1C1 = "100"
        ActiveCell.Offset(, ).Range("A1").Select
        ActiveCell.FormulaR1C1 = "200"
        ActiveCell.Offset(, ).Range("A1").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C"
        ActiveCell.Offset(, ).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:

    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)
    [email protected]	(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() As Integer   'initialized to 0
        Dim i As Integer
        For i =  To 
            iFA(i) = i
        Next i
        'ReDim iFA(20)           'uncomment and compile to see error
        Erase iFA
        Dim fsA() As String *     'initialized to 0
        Dim b() As Byte
        b = fsA()
    End Sub
               

    Sub dynamicArray()
        Dim sDA() As String
        ReDim sDA(, )         'initialized to ""
        sDA(, ) = "element(0,0)": sDA(, ) = "element(0,1)"
        sDA(, ) = "element(1,0)": sDA(, ) = "element(1,1)"
        ReDim Preserve sDA(, )
        'ReDim Preserve sDA(2, 2) 'uncomment and run to see error
        ReDim sDA(, )          '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

    .