Finding Comparable Objects in Class Modules

Here’s the made up scenario (that resembles a real one, kind of): I have 200 companies with their city, state, and a sales figure. I want to find the companies whose sales are near my focus company, but only if they’re in the same state.

In Excel’s UI, the steps would be 1) Sort the list 2) Autofilter on state 3) Find my company and look whose above and below.

If I care about Monarch Playing Cards, and I want to know the three closest companies with less sales and the three with more sales, I can see that in the above list. And I can see that there are only two with more.

In code, the steps are pretty much same, but I filter before I sort. Here’s the code.

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

Over the next few posts, I’ll go through each step in this code. For this post, I’ll look at setting up the data and the classes.

The first step is to generate some sample data using Quick Sample. I generated 200 entries with FakeCompanyName, US Cities, US States, and Sales. Sales is a new sample I created that looks like this

From $500k to $5m in sales with two decimals. If you create your own sample data, don’t make that upper sales figure too high (there’s a bug I need to fix).

Next, I create a class module called CCompany and put this code in it.

Option Explicit

Public CompanyID As Long
Public CompanyName As String
Public City As String
Public State As String
Public Sales As Double

Using my VBHelpers Add-in, I convert those Public Properties to Property Get/Let statements.

Private mlCompanyID As Long
Private msCompanyName As String
Private msCity As String
Private msState As String
Private mdSales As Double

Public Property Get Sales() As Double: Sales = mdSales: End Property
Public Property Let Sales(ByVal dSales As Double): mdSales = dSales: End Property
Public Property Get State() As String: State = msState: End Property
Public Property Let State(ByVal sState As String): msState = sState: End Property
Public Property Get City() As String: City = msCity: End Property
Public Property Let City(ByVal sCity As String): msCity = sCity: End Property
Public Property Get CompanyName() As String: CompanyName = msCompanyName: End Property
Public Property Let CompanyName(ByVal sCompanyName As String): msCompanyName = sCompanyName: End Property
Public Property Get CompanyID() As Long: CompanyID = mlCompanyID: End Property
Public Property Let CompanyID(ByVal lCompanyID As Long): mlCompanyID = lCompanyID: End Property

Back to VBHelpers to create a parent class module, CCompanies. Here’s the code that VBHelpers generates for that module:

Option Explicit

Private mcolCompanies As Collection

Private Sub Class_Initialize()
    Set mcolCompanies = New Collection
End Sub

Private Sub Class_Terminate()
    Set mcolCompanies = Nothing
End Sub

Public Property Get NewEnum() As IUnknown
    Set NewEnum = mcolCompanies.[_NewEnum]
End Property

Public Sub Add(clsCompany As CCompany)
    If clsCompany.CompanyID = 0 Then
        clsCompany.CompanyID = Me.Count + 1
    End If

    Set clsCompany.Parent = Me
    mcolCompanies.Add clsCompany, CStr(clsCompany.CompanyID)
End Sub

Public Property Get Company(vItem As Variant) As CCompany
    Set Company = mcolCompanies.Item(vItem)
End Property

Public Property Get Count() As Long
    Count = mcolCompanies.Count
End Property

It also does other stuff as described in the above linked post, like give me For Each and default property capabilities.

Tomorrow, we’ll look at the Fill method for CCompanies and the FindByName property.

You can download

Posted in Uncategorized

3 thoughts on “Finding Comparable Objects in Class Modules

  1. Why not use a formula in the column to the right that ranks using an array function. eg
    In Column E enter:

    Then combine this value with column C into Column F: =C2&E2

    You can then build a separate table where you select the Company you want from a dropdown list

    Then use the rank you get in column C to identify the ranks below and above required.
    Combine these with the State and then use match to selct those companies with the otehr values being identified using vlookup.

    I think this is much simpler than lots of code in this case. You could also write this approach in code quite easily

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

Leave a Reply

Your email address will not be published.