Join Curt Frye for an in-depth discussion in this video Creating collections, part of Process Modeling in Excel Using VBA.
- [Voiceover] In Excel VBA, classes let you create instances of an object you use in your simulation. Those objects could be customers or stations within a process. One useful way to manage instances of a class is to create a collection. If you've programmed in VBA before, then you know the worksheets collection contains all individual worksheets within a workbook. In this movie, I'll show you how to create collections of objects in your own classes. My sample file is Chapter 01_04.
That's an Excel macro-enabled workbook that you can find in the Chapter 01 folder of the exercise files collection. We're working exclusively in VBA, so the worksheet itself is blank. I'll move to the Visual Basic editor by pressing Alt F11. I have a code module called Module1 that has a little bit of code already in it. Basically this just covers what we've done previously in this chapter.
So I've already defined a variable called objC as type Customer. What I need to do now is to define a variable as a collection type so that I can assign individual customers to that collection. So I'll go up to just below the previous dimension statement and I'll type Dim, then a space, and I'll call the collection Customers as, and then it will be type Collection.
So I'll type Col and I see Collection appear in the autocomplete list so I'll press Tab, and then press Enter twice. So now I have variables that can represent an individual customer and also a collection of customers, but I haven't created the collection yet, or at least I haven't defined a variable as a collection. To do that, I need to use the Set command, or keyword to create customers as a collection. So I'll type Set, then a space, and then Customers = and then I'm creating a new object of type Collections so New, space, Collection, and press Tab to accept it from the formula autocomplete list.
If you're running into errors where you're trying to assign a particular object variable to a collection, this is the most common step to leave out. So if you're having that type of issue, make sure you have a Set statement where you actually define the collection. They don't come in automatically. I'll press Enter twice. Now I need to create a For Next loop that allows me to assign values to multiple customers without doing the same commands over and over. So the first thing I'll do is I will define a counter variable.
So I'll type Dim lCustIDCntr as long, and Return. So I have my counter variable for the loop, and now I can create the For Next loop itself. So I'll type For, space, lower case lCustIDCntr = and I'll just do two, so I'll make it 1 To 2. I'll press Enter, and just so I don't forget the next, I'll hit Return a couple of times and type it.
So now I'm two lines below the For statement. I'll press Tab to move in. Now I can use the Set keyword to define a new customer each time we go through the loop. So I'll type Set objC = New, then a space, and I want to create a customer, and I name the class CCustomer. So I'll press Tab and then Enter. Now I need to add the object I just created to the customer's collection.
So I'll type Customers.Add, then a space, and the name of the variable, which is objC. I don't need to set any other arguments or parameters for this particular command. I'll press Enter. So what I've done so far is to define a new customer and to add it to the customer's collection. Now I can use the counter ID and property names to assign values.
So I'll type Customers, C-U-S-T-O-M-E-R-S, and then I need to identify which member of the customer's collection I'm working with. That is identified by the loop counter, lCustomer ID counter. So I'll type a left parentheses and then lCustIDCntr. Then I'll close the parentheses. So the first time through this loop, I'll be referring to the first, or number one customer within the customer's collection and the second time when the ID counter goes to two, will be referring to the second member.
Now I need to identify the property I want to use. So I'll type period and then CustID = and then lCustIDCntr. So what I'm doing is I am assigning, again, the counter variable to customer number one, the first time through the loop, an ID equal to that number. So in other words, every time I create a new customer, it will get an ID and it will be equal, will reflect the order in which it was created.
That's all I will do here. I'll just add one property. So I'll press Delete a couple times to even up the spacing. I'll click below Next and above End Sub. Give myself a little space again. Now what I want to do to verify that my code works is to print out the values of each customer ID in the collection. So I'll make a comment to that effect.
So I'll print out values of each customer ID in the collection, and it's in green because it's a comment. So now I want to loop through my customer's collection and print out the customer ID of each customer. Rather than use Customers.Count to discover the number of objects in the customer's collection, and then use a For Next loop, I can instead use the For Each command to move through each element of the collection and I don't have to worry how many there are.
So I'll type For Each, two separate words, and a space, and objC, and again that is a variable of type Customer. So I'm looking for each customer within the collection of customers. Then Return twice, a Tab, Debug.Print, a space, and then objC.CustID.
So what I'm doing is printing to the immediate window the customer ID for each of the customers in the customer's collection. So I have those lines there. Press Return twice and then type Next to complete the command. What we've done so far is to once again, assign customer ID's to two new customers that we just created and the For Each loop will move through every member of the customer's collection, which we know to be two, and print their customer ID here in the immediate window.
If you don't see the immediate window, you can open the view menu and click Immediate Window, or press Ctrl G. So here we go. I have the insertion point blinking within the code module. I'll press F5 and in the immediate window, I have customer ID one, customer ID two, which reflects what we did earlier when we created our code.
- 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