In yesterday’s post, I discussed the setup of data and classes to find comparables. Today, I’ll discuss filling the classes with data and finding a specific instance of a class by a property that’s not the key to the collection.
Here’s my main procedure again
Dim clsCompanies As CCompanies
Dim clsWisky As CCompanies
Dim vaOutput As Variant
Dim clsFocus As CCompany
‘Fill all companies
Set clsCompanies = New CCompanies
clsCompanies.Fill Sheet1.Range(“A2:D201”)
‘identify the one I care about
Set clsFocus = clsCompanies.FindByName(“Monarch”)
‘get only those companies in the same state
Set clsWisky = clsCompanies.FilterByState(clsFocus.State)
‘sort them by sales
clsWisky.SortBySales
‘get an array of the 3 below and 3 above
vaOutput = clsWisky.WriteComparables(clsFocus, 3)
‘write it to a range
Sheet1.Range(“G1”).Resize(UBound(vaOutput, 1), UBound(vaOutput, 2)).Value = vaOutput
End Sub
In CCompanies, I have a Fill method. Methods are Public Subs in class modules, while properties use Public Property syntax.
Dim rCell As Range
Dim clsCompany As CCompany
‘Loop through all the cells in the first column
For Each rCell In rng.Columns(1).Cells
Set clsCompany = New CCompany
‘load up the class with the data
With clsCompany
.CompanyID = rCell.Row
.CompanyName = rCell.Value
.City = rCell.Offset(0, 1).Value
.State = rCell.Offset(0, 2).Value
.Sales = rCell.Offset(0, 3).Value
End With
‘add this instance of CCompany to the CCompanies
Me.Add clsCompany
Next rCell
End Sub
The Fill method accepts a Range argument that contains the Company data – one Company per row. I have a tendency, for good or bad, not to make my Fill classes too generic. If you pass a Range into this method and you want good information in the class, you have to make sure there’s good information in the range. My code does nothing to ensure that the first column is indeed the company name or that the third column is a valid state abbreviation. That’s not to say that it can’t do it, just that it doesn’t do it.
It also only accepts a Range, not a recordset, array, text file, or anything else. Any property or method that puts data into a class from an external source or gets data out of a class with the intention of writing it to an external source (like a spreadsheet, text file, or even a listbox) has to be re-written when the external source changes. If I start storing my companies in a database, I have to rewrite the Fill method. When I start displaying information on an HTML page, I have to write a new property that pulls the data out in the format I want.
I could make the Fill class more generic, say, by accepting an array, but I personally don’t see the value in it. If I do that, I need something else to convert my real input into an array. If I change my input source, I have to rewrite my conversion function. So what’s the difference if I rewrite that or rewrite the Fill class. I could also make the case that a Range argument is as generic as any other type of argument when my app is in Excel. I could, if wanted to, write a conversion function that will convert another data source into a Range object and pass it in with no changes to the Fill method by simply writing my data to a Range first. Of course I can think of reasons not to do that. So a Range argument it will stay and I won’t argue with anyone who says I should make it an array!
OK, back to the Fill method. I loop though all the cells in the first column, which I assume to be the company name. It doesn’t really matter which column I choose as long as I adjust my Offsets to match. I know that each Row in a Worksheet is a unique number, so I use that row number as my CompanyID. Every pass through the For Each loop creates a new CCompany instance, fills it with data, and adds it to Me (the instance of CCompanies that I’m working in). By adding it to the collection that’s internal to the class, that instance of CCompany will not lose scope as long as my CCompanies instance doesn’t lose scope. Even when I reassign my clsCompany variable to a new instance of CCompany on the second pass, the original instance (from the first pass) is safely stored in mcolCompanies. When I’m done, my instance of CCompanies will contain all of the CCompany instances I created and I can work with them individually (in the CCompany module) or collectively (in the CCompanies module).
The next part of the main procedure is to identify my focus company, the company against which I will be comparing its neighbors. I create a FindByName property that returns a specific instance of CCompany based on the CompanyName property. If I knew the order of the companies or the Key (the unique String I used to add it to the Collection), I wouldn’t need a separate property. I could call one of these
Set clsFocus = clsCompanies.Company(“5”)
If I pass in an Integer (like 4), it will return the fourth Company object added to the Collection. If I pass in a String (like “5?), it will return the Company in the Collection whose Key was defined as “5?. But I don’t want to find the Company that way. I want to find it using its CompanyName.
Dim clsReturn As CCompany
‘Loop through all the companies
For Each clsReturn In Me
‘See if the argument is contained in CompanyName
If clsReturn.CompanyName Like “*” & sName & “*” Then
‘A match was found, so no need to keep looking
Exit For
End If
Next clsReturn
Set FindByName = clsReturn
End Property
I start looping through all the Company instances in Me (the instances of CCompanies I’m working in). Each Company instance’s CompanName is compared to the argument that was passed in using the Like operator. Asterisks are put around the sName argument so that it finds the name if it exists anywhere in the CompanyName property. I could have looked for an exact match with If clsReturn.CompanyName = sName Then
. There are good and bad reasons to do it either way.
In most cases, I probably only need the exact match method. It’s likely I would be getting the company’s name from the user via a worksheet cell, listbox, or some other method. If it’s a controlled environment, like a listbox, the user will be forced to select a company name that I know is complete and accurate, so there’s no need for the Like operator. For this contrived example, I’m “inputting” the company name directly in my code, so I went with Like so I wouldn’t have to spell out the whole name.
For each pass of the loop, clsReturn is set to a different Company instance. When I find a match and exit the loop, clsReturn is still set to that instance. So I have only to set my Property name equal to clsReturn and it will get passed back to the procedure that called it.
In my main procedure, after the line Set clsFocus = clsCompanies.FindByName("Monarch")
is executed, I have a clsCompanies variable that contains all of my Company objects and I have a clsFocus variable that contains one Company object. Specifically, it contains the first Company object that has the String “Monarch” in it’s CompanyName property.
Monday we’ll look at filtering into a new instance of CCompanies and sorting that instance with a quick sort algorithm.
You can download SortFilterClass.zip
All of this time I’ve been writing my FindByName like this:
Dim clsReturn As CCompany, bln As Boolean
bln = False
For Each clsReturn In Me
If clsReturn.CompanyName Like “*” & sName & “*” Then
bln = True
Exit For
End If
Next clsReturn
If bln Then Set FindByName = clsReturn
End Property
I was concerned that if no matches were found, then the last item of the collection would remain against clsReturn.
So it was good to read your code because it prompted me to check my assumptions. Much better!