Primer on VBA

1
Primer on VBA Excel for Discrete-Event
Simulation
See also IOE574-startup.xls
2
Visual Basic for Applications

• VBA a significant subset of the stand-alone
  Visual Basic programming language
• It is integrated into Microsoft Office
  applications (and others, like Arena)
• It is the macro language of Excel
• You can add
• Forms for dialog boxes with user input
• Classes for object definitions
• Modules containing procedures

3
VBA Excel for Discrete-Event Simulation

• Advantages
• VBA is a full-featured programming language
• You have access to Excel functions for
  computation and Excel for storing and analyzing
  outputs including USER INTERACTION!
• Disadvantages
• VBA is interpreted, not compiled, so execution is
  slow (can be overcome by compiling VB)
• Excel functions can be buggy

4
Accessing VBA in Excel

• Tools ? Macros ? Visual Basic Editor
• Enter VBA through the navigation buttons in the
  top toolbars

Design mode is the time during which no code
from the project is running and events from Excel
or your project will not execute.
VBA Design Mode
Visual Basic Editor
5
VB Edit Window
Project Explorer
Code Window
Property Inspector
6
Structure of VBA Project

• Modules are collections of VBA code
• Procedures (Subroutines - Subs) and Functions
• Declarations come before any Subs or Functions
  that are global to the Module
• UserForms are graphic objects for user input and
  output we will not have to work with UserForms

7
Variables

• Declare by Dim varname As Type
• Better to use Data Types Dim amount As Double
  note double precision is useful for us!
• Dim year As Integer Dim name As String
• Other data types Boolean, Byte, Currency, Date
• Default (no type) is Variant
• Option Explicit forces all variables to be
  declared

8
Variables(contd.)

• Can declare type by appending a symbol -
  integer - long integer ! - single -
  double_at_ currency - string
• Can modify scope (outside Subs Fcns)
• Private L As Integer(only current module)
• Public billsPaid As Currency (available to any
  module)

9
Constants Statics

• PublicPrivate Const constantName As type
  expression
• Value cannot be changed
• Public Const PI 3.1, NumPLANETS 9
• Oops, make that 8 as of August 2006!
• Static causes variables in Subs and Functions to
  retain their values (normally lost when you exit
  Sub or Function)
• Static yourName As String

10
Arrays

• Dim vect(1 to 100) as IntegerDim Elf(0 to 5, 0
  to 20) as String
• You can also dynamically allocate and reallocate
  an arrayDim Calendar() as IntegerReDim
  Calendar (1 to 31) as Integer

11
Control Structures

• Decisions If anyDate lt Now Then anyDate
  Now End If Next, consider If Then Else

12
Decisions(contd.)

• If Index 0 Then X X 1 Y
  VBA.Sqr(X)Else If Index 1 Then Y
  VBA.Sqr(X)Else If Index 2 Then Y XElse X
  0 End If

13
Decisions(contd.)

• Select Case IndexVariable Case
  0 statements Case 1 to 10 statements Case
  Is lt 0 statements Case NumSteps
  statements Case Else statementsEnd Select

Notice that the cases can be constants, ranges,
conditions and variables this is a powerful
control structure that we will use to select
events to execute
14
Loops/Iterations

• Do WhileUntil condition statements Loop
  -------------------------------------------
• Do statements Loop WhileUntil condition

15
Loops(contd.)

• For counter start To end Step
  increment statements
• Next counter
• --------------------------------
• For Each element In group statements
• Next element

16
Exiting Control Structures

• For J 1 To 10 Step 2 statement block Exit
  For statement blockNext J--------------------
  ---Do
• statement block Exit Do statement
  blockLoop Until Check False

Optional statements to allow early exit from the
loop before the termination condition
17
Exit Command Details

• Exit Do Provides a way to exit a Do...Loop
  statement. It can be used only inside a Do...Loop
  statement. Exit Do transfers control to the
  statement following the Loop statement. When used
  within nested Do...Loop statements, Exit Do
  transfers control to the loop that is one nested
  level above the loop where Exit Do occurs.
• Exit For Provides a way to exit a For loop. It
  can be used only in a For...Next or For
  Each...Next loop. Exit For transfers control to
  the statement following the Next statement. When
  used within nested For loops, Exit For transfers
  control to the loop that is one nested level
  above the loop where Exit For occurs.
• Exit Function Immediately exits the Function
  procedure in which it appears. Execution
  continues with the statement following the
  statement that called the Function.
• Exit Sub Immediately exits the Sub procedure in
  which it appears. Execution continues with the
  statement following the statement that called the
  Sub procedure.

18
Code Modules

• Excel Objects (ThisWorkbook, Sheet)
• Modules
• Typically we put our code here
• A Module is a collection of Subs and Functions
• Insert ? Module
• More
• Class Modules (see master used for simlib)
• User Forms

19
Procedures

• Sub name(arguments) (i.e., subroutine)
• no value returned in the sense of a function,
  but variables are modified.
• Called as needed via Call mySub(param1,
  param2)
• Function name(arguments) AS type (i.e.,
  function)
• value returned
• assign return value to function name e.g.,X
  myFunction(2, 7, Z)

20
Subs

• Subs can also have Public or Private scope
  (default is Public)
• Public Sub MM1_main() this is a common way to
  begin a sub that is public and all variables to
  be communicated to the routine calling it are
  public.
• Basic syntaxPublicPrivate Sub
  name(arguments) statements Exit
  Sub statementsEnd Sub

Optional way to leave the Sub before reaching the
End statement
21
Functions

• Functions can also have Public or Private scope
  (default is Public)
• Public Function Timer() As String e.g., Timer
  returned as Failure
• Basic syntaxPublicPrivate Function
  fname(arguments) AS type statements fname
  valu Exit Function statementsEnd Function

Fname returned to module with value valu
Optional way to leave the Function before
reaching the End statement
22
Arguments for Procedures

• Pass by Reference (default) means that changes to
  the value of the variable will be returnedSub
  stuff(item As String, price as Integer)
• Pass by Value means only the value is passed so
  the original variable is unchangedSub
  stuff(ByVal item As String, ByVal price as
  Integer)

23
Some Useful Code for Interacting with Excel

• The following are some pieces of code that are
  useful for doing VBA with Excel.
• See the code on the course web site for other
  examples.
• Basic_Simulation_Modeling.xls
• IOE574-startup.xls
• others yet to come.

24
Writing to a Sheet

• Put the absolute value of the variable Fudge in
  row 2 (or I), column 20 (or J) of the Worksheet
  named mySheet.

Worksheets(mySheet).Cells(2,20)
VBA.Abs(Fudge)Worksheets(mySheet).Cells(I,J)
VBA.Abs(Fudge) Worksheets(mySheet).Range(T2
)VBA.Abs(Fudge)
Range is general see next page
This is how you address VBA intrinsic functions
25
Ways to use .Range Method
Range("A1") Cell A1
Range("A1B5") Cells A1 through B5
Range("C5D9,G9H16") A multiple-area selection
Range("AA") Column A
Range("11") Row 1
Range("AC") Columns A through C
Range("15") Rows 1 through 5
Range("11,33,88") Rows 1, 3, and 8
Range("AA,CC,FF") Columns A, C, and F
26
Reading from a Worksheet

• To read in a value, use the .Value method,
  applying the same ideas used for writing X
  Worksheets(mySheet).Range(T2).Value
• note T column 20, so T2 is col. 20, row2
• Excel likes the (column, row) order rather
  than (row, column) when using Range

27
Use an Excel Function

• VBA has a limited number of built-in functions,
  but you can access the plethora of Excel
  worksheet functions.
• This example uses the Excel Max function

W WorksheetFunction.Max(0, W S - a)
28
Running the Code

• Your modules will as appear as Macros that can be
  run from Excel underTools ? Macro ? Macros ?
  dialogue-box
• Perhaps the easiest way to run the code is to
  place your cursor in the module you want to run
  and press the Run Sub/UserForm button. (there
  is a green play button on the toolbar, too)

29
Debugging
Useful tools in the Debug menu
Setting break points causes code to stop when the
point is reached (F5 to continue)
Passing the cursor over variables shows their
current value
30
An apostrophe indicates a comment
Discrete TTF EXAMPLE
Dim Clock As Double ' simulation
clock Dim NextFailure As Double ' time of next
failure event Dim NextRepair As Double ' time
of next repair event Dim S As Double
' system state Dim Tlast As Double ' time
of previous event Dim Area As Double '
area under S curve Public Function Timer() As
String ' Determine the next event and advance
time If NextFailure lt NextRepair Then
Timer "Failure" Clock NextFailure
NextFailure 1000000 Else Timer
"Repair" Clock NextRepair
NextRepair 1000000 End If End Function
These variables are global since they are
declared before any Sub or Function
Notice that Function must be typed
Value "Failure" is returned as Timer, the name of
the function.
31
Public Sub MainProgram() ' Program to generate a
sample path for the reliability example Dim
NextEvent As String S 2 Clock 0
Tlast 0 Area 0 NextFailure
WorksheetFunction.Floor(6 Rnd(), 1) 1
NextRepair 1000000 Do Until S 0
NextEvent Timer Select Case
NextEvent Case "Failure" Call
Failure Case "Repair" Call
Repair End Select Loop MsgBox
("System failure at time " _ Clock
" with average components " Area /
Clock) End Sub
NextEvent is local to this Sub since it is
declared within the Sub
Note use of an Excel function
A Do Until loop and a Select Case statement
32
Public Sub Failure() 'Failure event Area
Area (Clock - Tlast) S Tlast Clock
S S - 1 If S 1 Then
NextFailure Clock WorksheetFunction.Floor(6
Rnd(), 1) 1 NextRepair Clock 2.5
End If End Sub
33
Public Sub Repair() 'Repair event Area Area
(Clock - Tlast) S Tlast Clock
S S 1 If S 1 Then NextRepair
Clock 2.5 NextFailure Clock
WorksheetFunction.Floor(6 Rnd(), 1) 1 End
If End Sub
34
Finishing Up

• Exercise Write a Sub that inserts a worksheet
  named Count into the Workbook, then writes the
  numbers 1,2,,10 in the first row, the first ten
  columns. Use a loop to do this.

35
Finding/Creating a Sheet
FYI useful sol. To exercise
Dim found As Boolean Dim sheetNext As Worksheet
' Set up mySheet sheet for output
found False For Each sheetNext In
Worksheets If sheetNext.Name mySheet"
Then found True Exit
For End If Next sheetNext If
found True Then Worksheets(mySheet").Se
lect ActiveSheet.UsedRange.Clear
Else Worksheets.Add
ActiveSheet.Name mySheet" End If