Join Curt Frye for an in-depth discussion in this video Defining the Stations class and adding stations, part of Excel VBA: Process Modeling (2016).
- [Voiceover] In this course of simulations, we are modeling the flow of customers through a sandwich shop. This process has two main elements: Customers and Stations. In this movie, I will review the stations class that is included with the workbook and also show you how to create station objects using a VBA code. Because each station has properties, such as the station ID, average processing time, and standard deviation of processing time, I’ll read the values from the Sim Setup worksheet into the objects.
My sample file is the Chapter02_04 workbook, which you can find in the Chapter two folder of the exercise files archive. Before I switch over to the Visual Basic Editor, let's review what information we have here on this Sims Setup worksheet. First, we have the number of stations and then we also have information for each of the stations that we're going to create. So we have Stations 1, 2, and 3 and we have the Mean or average processing time for each station as well as the standard deviation.
So with that information in place, let's switch over to the Visual Basic Editor by pressing Alt + F11. The main code that we’ll be working with is in module one, but what I'd like to do is take a quick look at the Station to Class module so that we can see the properties that are available there and also get a reminder of how they were created. So, in the Visual Basic Editor, I’ll go to the Window menu and click CStation.
If you did not see CStation as one of the open windows, then you can go over to the Project Viewer, go under Class Modules and double-click CStation there. In this case, stations have four properties. Those are the station ID, the mean processing time, the standard deviation processing time, and the next station and all of those values are defined as longs. And below, we have the property declarations, which we've seen several times before, so I won't go into any detail here.
Now I’ll go back to the main code module. So I’ll do Window, Module1 and now I can create a new collection. I already have object S as type station and again, the C in front of station indicates that it’s a class I created, and then Stations is also available as a collection. We defined those variables earlier. So, I’ll scroll down below Set Customers = New Collection and I will do the same thing.
I'll Set Stations = New Collection, so that's in place and now I can scroll down to the comment that says “Create station objects and initialize values” and I will tell my code where to find the values for the number of stations. First thing I need to do is to activate the Sims Setup worksheet and there's only one worksheet, so I really don't have to do it, but it's good practice to get into, activating the worksheet explicitly.
So I’ll type Worksheets(“SimSetup”).Activate. Now that I have activated the Sims Setup worksheet, I can identify the cell that I want to start reading values for my stations from later in this module. So I'll type Range(“C4”).Activate.
Great, so cell C4 is currently the active cell on the Sims Setup worksheet. Now I’ll go down below Add a station. This is within the loop that creates the stations that will be used in the simulation and also reads in their property values. So for Add a station, I’ll type Set objS = New CStation, the station class we created earlier, and now I’ll add objS to the stations collection so, \Stations.Add objS and we’re done.
Now we need to read in the values for the station ID, the mean or average processing time, and standard deviation. So, I’ll scroll down a bit and click under Assign Station ID and read Mean and StdDev from the worksheet and just to remind us of what we're going for, I’ll press Alt + F11 and we can see that starting in cell C4, I have the headers for Station, Mean, and StdDev and then in cells B5 through D7, we’ll be reading in the values.
Now, one reason that we started in cell C4 is that we can use our loop counter to bring in the station ID. So, we don't have to read in values here and that's why we started in C4 instead of B4. So, I’ll press Alt + F11 to go back and I'm ready to type in the code and that will bring in the values that we need. So the first, we have Stations and then which stations object do we want to use? Well, that will be the one that we're currently on, so we’re using ours station counter variable, which goes from one to the number of stations.
So we have Stations and that would be lStCntr the counter variable SCID which is the property for the station ID equals, and that is also the station counter variable, so, lSrCntr then return and now we can do the similar action for the mean and standard deviation but we need to read those in from the worksheet.
So, I'll type in the first ones Stations. We're still working at the same variable: (lStCntr). That's in parentheses, then a period. Then STA mean equals. And, now we need to use the active cell and offset it by rows and columns representing its position within the worksheet. We can use the lSt counter or the station counter variable to indicate the number of rows that we need to go down.
So, to remember, pressing Alt + F11 again, we're starting in cell C4, that's currently the active cell. If we offset by one row, were in cell C5, which has a value for Station 1 Offset by two with got the mean for Station 2, offset by three, we've got the mean for Station 3. So, this is a handy trick that you can use. Use active cell and offset in combination. So, Alt + F11 to go back and it is the ActiveCell.Offset.
Then in left parenthesis we need to indicate how many rows and how many columns. The row is simply of the station counter so .lStCntr, then a comma, and the number of columns is 0, 'Cause we're going to be looking in column C and enter. Now, I will copy this code, so I don't have to retype all of it. So, I highlighted it, press Ctrl + C, and Ctrl + V to Paste. I'll edit the property value from StaMean or station mean to station standard deviation, and I'll change the column offset from 0 to 1.
So, I have read in those values, and I now have the mean standard deviation and station ID. However if you're reading ahead You see the Next comment that says I need to read in the Next Station property. The idea behind Next Station is that I am creating a basically a single-length list of stations. What I want is for a customer that has done with Station 1 to look at the station Next Property, and know that it needs to move to Station 2 and the code can handle that for it.
So, how do we do that? Well, we can use an IF THEN statement. What I want to do is check to see whether the station that were looking at is the last station in the list. In this case, Station 3. If it's not, then use this current station idea plus one, as the next station, the station to move to. If it is the last station, in this case 3, then we want to set the next station value to -1. And we can use that -1 value as a check, as we get into more and more complex coding.
So I'll click below Set NextSta property, and I'll start with my If statements. So, If stations, and we're looking at LStCntr the station counter again, .StaID < the total number of stations, which is held in the variable LNmbrStas Then we can assign that station’s next station property.
The current counter variable plus one, so have Stations(lStCntr).NextSta = lStCntr +1 So, in other words, if the condition is true that the station ID is less than the number of stations, which we read from the worksheet, then the next station is the counter plus one.
Then Enter and then Else: Stations(lStCntr), again the counter variable, .NextSta = -1 and then finally we close out the if then statements with End If.
So that was a lot and we got a lot accomplished in this movie and what we've done is to define stations, made them part of a collection and then read end variables from the worksheet so that we know how they operate within our simulation.
- Creating a class module in VBA
- Defining class properties
- Creating collections
- Describing process flow and programming goals
- Creating loops
- Increasing capacity of a model
- Running simulations
- Analyzing simulation results