Slide 1: Primer on VBA & Excel for Discrete-Event Simulation VBA & Excel.ppt 1
Slide 2: Visual Basic for Applications • VBA a significant subset of the standalone 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 VBA & Excel.ppt 2
Slide 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 VBA & Excel.ppt 3
Slide 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. Visual Basic Editor VBA & Excel.ppt VBA Design Mode 4
Slide 5: VB Edit Window Project Explorer Code Window Property Inspector VBA & Excel.ppt 5
Slide 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 VBA & Excel.ppt 6
Slide 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 VBA & Excel.ppt 7
Slide 8: Variables(cont’d.) • Can declare type by appending a symbol: % - integer & - long integer ! - single # - double @ currency $ - string • Can modify scope (outside Subs & Fcns) – Private L As Integer (only current module) – Public billsPaid As Currency (available to any module) VBA & Excel.ppt 8
Slide 9: Constants & Statics • [Public|Private] 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 VBA & Excel.ppt 9
Slide 10: Arrays • Dim vect(1 to 100) as Integer Dim Elf(0 to 5, 0 to 20) as String • You can also dynamically allocate and reallocate an array Dim Calendar() as Integer ReDim Calendar (1 to 31) as Integer VBA & Excel.ppt 10
Slide 11: Control Structures • Decisions If anyDate < Now Then anyDate = Now End If Next, consider If … Then … Else VBA & Excel.ppt 11
Slide 12: Decisions(cont’d.) 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=X Else X=0 End If VBA & Excel.ppt 12
Slide 13: Decisions(cont’d.) Select Case IndexVariable Case 0 statements… Case 1 to 10 statements… Case Is < 0 statements… Case NumSteps statements… Case Else statements… End Select VBA & Excel.ppt 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 13
Slide 14: Loops/Iterations Do {While|Until} …condition statements… Loop ------------------------------------------Do statements… Loop {While|Until} …condition VBA & Excel.ppt 14
Slide 15: Loops(cont’d.) For counter = start To end [Step increment] …statements Next counter -------------------------------For Each element In group …statements Next element VBA & Excel.ppt 15
Slide 16: Exiting Control Structures For J = 1 To 10 Step 2 [statement block] Exit For Optional statements to [statement block] allow early exit from the Next J loop before the termination condition ----------------------Do Use with caution, [statement block] reluctantly. Exit Do [statement block] Loop Until Check = False VBA & Excel.ppt 16
Slide 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. VBA & Excel.ppt 17
Slide 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 VBA & Excel.ppt 18
Slide 19: Procedures • Sub name(arguments) (i.e., subroutine) – no value returned in the sense of a “function” – Called when needed Call mySub(param1, param2) • Function name(arguments) AS type (i.e., function) – value returned – assign return value to function name X = myFunction(2, 7, Z) VBA & Excel.ppt 19
Slide 20: Subs • Subs can also have Public or Private scope (default is Public) • Basic syntax {Public|Private} Sub name(arguments) [statements…] Optional way to leave the Sub Exit Sub before reaching the End statement [statements…] End Sub VBA & Excel.ppt 20
Slide 21: Functions • Functions can also have Public or Private scope (default is Public) • Basic syntax {Public|Private} Function fname(arguments) AS type [statements…] Fname returned to module fname = valu with value “valu” Exit Function [statements…] End Function Optional way to leave the Function before reaching the End statement VBA & Excel.ppt 21
Slide 22: Arguments for Procedures • Pass by Reference (default) means that changes to the value of the variable will be returned Sub stuff(item As String, price as Integer) • Pass by Value means only the value is passed so the original variable is unchanged Sub stuff(ByVal item As String, ByVal price as Integer) VBA & Excel.ppt 22
Slide 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. VBA & Excel.ppt 23
Slide 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 VBA & Excel.ppt This is how you address VBA intrinsic functions 24
Slide 25: Ways to use .Range Method Range("A1") Range("A1:B5") Range("C5:D9,G9:H16") Range("A:A") Range("1:1") Range("A:C") Range("1:5") Range("1:1,3:3,8:8") Range("A:A,C:C,F:F") Cell A1 Cells A1 through B5 A multiple-area selection Column A Row 1 Columns A through C Rows 1 through 5 Rows 1, 3, and 8 Columns A, C, and F VBA & Excel.ppt 25
Slide 26: Reading from a Worksheet • To do 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” VBA & Excel.ppt 26
Slide 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) VBA & Excel.ppt 27
Slide 28: Running the Code • Your modules will as appear as Macros that can be run from Excel under Tools  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) VBA & Excel.ppt 28
Slide 29: Useful tools in the Debug menu Debugging Setting break points causes code to stop when the point is reached (F5 to continue) Passing the cursor over variables shows their current value VBA & Excel.ppt 29
Slide 30: IN-CLASS EXAMPLE An apostrophe indicates a comment These variables are global since they are declared before any Sub or Function 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 < NextRepair Then Timer = "Failure" Clock = NextFailure NextFailure = 1000000 Else Timer = "Repair" Clock = NextRepair NextRepair = 1000000 End If End Function VBA & Excel.ppt Notice that Function must be typed Value "Failure" is returned as Timer, the name of the function. 30
Slide 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 NextEvent is local to this Sub since it is declared within the Sub Note use of an Excel function Do Until S = 0 NextEvent = Timer Select Case NextEvent Case "Failure" An Until loop and a Select Case Call Failure statement Case "Repair" Call Repair End Select Loop MsgBox ("System failure at time " _ & Clock & " with average # components " & Area / Clock) End Sub VBA & Excel.ppt 31
Slide 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 VBA & Excel.ppt 32
Slide 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 VBA & Excel.ppt 33
Slide 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. VBA & Excel.ppt 34
Slide 35: FYI –useful solution to exercise Finding/Creating a Sheet 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").Select ActiveSheet.UsedRange.Clear Else Worksheets.Add ActiveSheet.Name = “mySheet" End If VBA & Excel.ppt 35