Filling Classes and Finding Specific Instances

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

Sub GetComperablesByState()
    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
    ‘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.

Public Sub Fill(rng As Range)
    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(4)
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.

Public Property Get FindByName(sName As String) As CCompany
    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

Posted in Uncategorized

One thought on “Filling Classes and Finding Specific Instances

  1. All of this time I’ve been writing my FindByName like this:

    Public Property Get FindByName(sName As String) As CCompany
        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!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.