[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.