From the course: Excel VBA: Process Modeling
Declare variables used in the simulation
From the course: Excel VBA: Process Modeling
Declare variables used in the simulation
- [Instructor] Almost every VBA code module requires variables to represent values used within the module's procedures. In this case we will use quite a few variables to store values that include the number of stations in the simulation, the average processing time for each station, and the total time that an individual customer spends in the system. In this movie, I will declare the variables that we will use to represent all those values and more. My sample file is Chapter02_02, and that's an Excel workbook you can find in the chapter two folder of the exercise files collection. We will use the data that is displayed on the sim setup worksheet later on in this chapter, but for now I'll press Alt + F11, and move to the Visual Basic editor. In the Visual Basic editor, I have my code module, which will have the code that will run to create the simulation. Also in the background, I have two other windows, and those are the customers class and the stations class. So if I go to the window menu, and click CCustomer, that gives me the definition of and the properties for the customer class, and if I go back to window and click CStation, then we'll see the same thing for the station class. I haven't really talked about the station class that much in detail, but if you want to see the definitions for the objects it contains and their properties, it's right here. Now I'll go ahead and switch back to code module one, so go to window, Module 1, and we can start defining our variables. At the very top above the Sub MainTest statement, at the top of the Sub routine, you'll see that I have Option Explicit. Option Explicit requires you to declare a variable before you use it in your code. Let's say for example that I want it to store the name of an employee. That could be strFirstName, so it's a string and I represent the first name of that employee. If I were to misspell that variable anywhere in the routine, Option Explicit would catch it and unless I accidentally typed the name of another variable, then I would get an error saying that the variable was undefined. Option Explicit will save you from hours of work chasing down errors, because the variable that you think you named correctly displays a zero when in fact you assigned a value to a different variable, a different-spelled variable earlier in the routine. So believe me, Option Explicit seems like a lot of work, but it will actually save you a lot of work over the long run. All right, with all that in mind, let's go ahead and declare our variables. So go under the first comment, and I will declare four variables. So Dim, and then o-b-j-C, which will be our customers, that's as CCustomer, which is the customer class we created before. Next we'll have Dim o-b-j-s as CStation, so we have our customer and our station object variables. Now we need to define collections to contain them. So Dim, Customers as Collection, and enter, and finally, Dim Stations as Collection. Right, that's all of the collections and objects that we need. Next we can do our loop, random, and processing time variables. So I click under the second comment, and I'll enter in four more variables. So Dim, l-S-t Counter as Long, so that'll be one of our counter variables. Next we'll do the customer ID counter, so it will be Dim, l, customer ID counter as Long, we'll have one more variable of type Long, and that will be Dim, l-S-t-a Time, so the time spent in the station, as Long, and enter. And finally, we will have a random value that will be a single value, so it'll be a single precision floating point number. So that'll be Dim, s-n-g Rand as Single. There we go. Now we need to declare one more variable and assign values to two of the ones that we have available. So I'll declare the variable for the number of stations, that will be Dim, l-N-m-b-r-S-t-a-s. Using this type of abbreviation allows us to retain readability but save typing, as Long. And finally we need to assign the value for the number of stations, and initialize the customer counter variable. So I click under assign value for number of stations, that is l-N-m-b-r-S-t-a-s equals, and we will draw the value from the sim setup worksheet cell B2. So that'll be Worksheets, then left parentheses, the name of the worksheet is SimSetup, so we'll put that in double quotes, and then close the right parentheses, a period, Range, so we identify the cell range the value's coming from, in this case in double quotes, it's B2, period, Value. So we're drawing the value from cell B2. Now we can initialize the customer ID counter variable, so I will go under the initialize comment, l-C-u-s-t-I-D-C-n-t-r equals one, and we're done. Just to make sure there are no errors, I will press F5 to ensure the code runs. We won't see any output, but if something's wrong, we will see an error. So I'll press F5, everything ran, so that means everything is either working correctly or something went wrong and we don't know about it yet.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
(Locked)
Define process flow and programming goals55s
-
Declare variables used in the simulation6m
-
(Locked)
Define the Customers class and add a customer4m 48s
-
(Locked)
Define the Stations class and add a station8m 56s
-
(Locked)
Add code to calculate and write out times6m 2s
-
(Locked)
Delete unneeded objects1m 50s
-
(Locked)
Run the simulation1m 55s
-
(Locked)
Adapt the code for multiple runs4m 8s
-
(Locked)
-
-
-
-
-
-