Join Curt Frye for an in-depth discussion in this video Running the simulation, part of Excel VBA: Process Modeling (2016).
- [Voiceover] We have done a lot of preparatory work in this chapter. Extending the clock base simulation we created in chapter three to add new customers as the simulation runs. Those customers' arrival times were calculated by using the Poisson distribution, and they're time at each station was calculated using the normal distribution. In this movie, I will show you how to run your simulation, examine the results, and as it turns out, correct an aspect of station timing that I initially overlooked. My sample file is chapter 0405, and you can find it in the chapter four folder of the exercise files collection.
I'm not going to spend time reviewing what we've done. Our code is ready to go, and we're ready to write our results to the results worksheet. Which is here. So, I'll press alt F11 to move to the main code module. And with the insertion point blinking inside of our subroutine, I'll press F5 and run the simulation. All right. Well, I didn't see anything here, but I'll press alt F11 to move back.
And here we are in the results worksheet. If we start at the top, we see that the first customer entered the system at one and left at 46. It completed all stations, so it's current station is set to minus one. It is idle. It's idle time was zero. Not surprising for the first customer going to the station, and it spent 46 ticks in the station. That value is calculated using an if function.
It looks at cell C2 to see if the customer did in fact leave the system. If it did, then it subtracts 2881, which is total ticks plus one, minus the time it entered the system. And if it didn't, then it calculates the value based on C2 minus D2. The time it left the system, minus the time it entered the system, plus one. So, in other words, if the customer successfully made it through the system, we find out how long it was in there. And if it didn't, then we use a different method to get the same result.
All right. So, let's go ahead and scroll down through the worksheets tab, or worksheets page, and we'll see that everything worked as expected. When we get down to 144, we see that this customer here, which entered at 2836 out of 2880, didn't make it out. And the same for customer 145 and 146. And we don't have values of total idle time plus percent idle time calculated.
So, I'll just go ahead and select cells G146 and G147 and pull down, and we can see the customer results there. Because of randomization, your values might be different. But, now, let's see what happens, and what can happen some times. So, I'm going to rerun simulations until I get the results that I want. So, I'm going to pause the video for a moment, and then I'll come back to the results worksheet to show you what I mean. All right. It took me a few tries, but I was able to generate a simulation that shows the programming mistake that I've made in the code as it currently stands.
So, everything that we see here looks good. At least to start. We see that we have customers entering and leaving the system. And that they're current stations are moving along. So far everyone has completed everything. But if we scroll down, we can see that customer 42 never advanced beyond station two. In other words, it never left the system, and it never left station two. But it's not idle.
And you'll see that, because that customer was blocking station two, none of the other customers were able to move forward either. So, the question is what causes that result? And the answer is that if you look at the mean and standard deviation for our particular stations, you'll see that station number two has a mean of 10 and a standard deviation of three. That means that there is a reasonable probability that calculating a number with that mean and standard deviation using the normal distribution, will generate a zero or negative value.
So, that means that testing the customer's end time will never result in a positive check, because the end time is in the past. So, what we need to do is to correct our code by making sure that each customer spends, at least one tick of the clock in each station. So, I'll press alt F11 to go back, and I'll scroll down to where we calculate the amount of time that a customer spends in a station.
That's right here. So, I am at the OBJC dot end time equals L ticker plus the random number that we generate. I'll press enter. And line everything up. Give myself a little white room. And I'll type in the code indicating that the customer needs to spend at least one tick of the clock in each station. So, I'll add a comment, "Make sure the customer spends "at least one tick in a station." Enter.
And here's how we do that just using a single line if then statement. So, if OBJC dot end time is less than or equal to L ticker, so in other words, if we got zero or a negative value, we don't want that because it generates an error. Then OBJC dot end time equals L ticker plus one.
So, in other words, we are setting the end time to at least the current clock value plus one. Now, I can press alt tab to move to the main workbook. I'll switch to the results worksheet, and we have results all the way down to row 148. So, I'll delete everything from column F over.
And press control home to move back to cell A1. So, A1 is currently the active cell. Although we have code to set it anyway. I'll press alt F11 to go back. And press F5 to run the simulation. Alt tab, and when we scroll down, we can see that everything is fine. We don't have any customers that got stuck in any of the stations. And because of randomization, we do have some results rows that don't have any calculations, but that's perfectly fine.
You should also rerun the simulation a few times to verify that you have no errors. This movie provided the culmination of a lot of work. We were able to add customers on the fly based on randomly generated Poisson distributed times, and, also, to fix a programming error. So, if you see an error, such as the one I pointed out, always make sure that your assumptions about timing are correct. When you're working with clocks and checking to see whether a certain value is equal to another value, make sure that you're not generating negative values, as I was, with the normal distribution with a standard deviation of three based on a mean of ten.
- 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