1 / 34

Primer on VBA Excel for Discrete-Event Simulation

VBA

Ava
Download Presentation

Primer on VBA Excel for Discrete-Event Simulation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. VBA & Excel.ppt 1 Primer on VBA & Excel for Discrete-Event Simulation

    2. VBA & Excel.ppt 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.ppt 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. VBA & Excel.ppt 4 Accessing VBA in Excel Tools ? Macros ? Visual Basic Editor Enter VBA through the navigation buttons in the top toolbars

    5. VBA & Excel.ppt 5 VB Edit Window

    6. VBA & Excel.ppt 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. VBA & Excel.ppt 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. VBA & Excel.ppt 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)

    9. VBA & Excel.ppt 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

    10. VBA & Excel.ppt 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

    11. VBA & Excel.ppt 11 Control Structures Decisions If anyDate < Now Then anyDate = Now End If Next, consider If … Then … Else

    12. VBA & Excel.ppt 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

    13. VBA & Excel.ppt 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

    14. VBA & Excel.ppt 14 Loops/Iterations Do {While|Until} …condition statements… Loop ------------------------------------------- Do statements… Loop {While|Until} …condition

    15. VBA & Excel.ppt 15 Loops(cont’d.) For counter = start To end [Step increment] …statements Next counter -------------------------------- For Each element In group …statements Next element

    16. VBA & Excel.ppt 16 Exiting Control Structures For J = 1 To 10 Step 2 [statement block] Exit For [statement block] Next J ----------------------- Do [statement block] Exit Do [statement block] Loop Until Check = False

    17. VBA & Excel.ppt 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. VBA & Excel.ppt 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. VBA & Excel.ppt 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. VBA & Excel.ppt 20 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 syntax {Public|Private} Sub name(arguments) [statements…] Exit Sub [statements…] End Sub Subs

    21. VBA & Excel.ppt 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 syntax {Public|Private} Function fname(arguments) AS type [statements…] fname = valu Exit Function [statements…] End Function

    22. VBA & Excel.ppt 22 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) Arguments for Procedures

    23. VBA & Excel.ppt 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. VBA & Excel.ppt 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.

    25. VBA & Excel.ppt 25 Ways to use .Range Method

    26. VBA & Excel.ppt 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. VBA & Excel.ppt 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

    28. VBA & Excel.ppt 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)

    29. VBA & Excel.ppt 29 Debugging

    30. VBA & Excel.ppt 30

    31. VBA & Excel.ppt 31

    32. VBA & Excel.ppt 32

    33. VBA & Excel.ppt 33

    34. VBA & Excel.ppt 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. VBA & Excel.ppt 35 Finding/Creating a Sheet

More Related