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.
Dim clsCompanies As CCompanies
Dim clsWisky As CCompanies
Dim vaOutput As Variant
Dim clsFocus As CCompany
‘Fill all companies
Set clsCompanies = New CCompanies
‘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
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.
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 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:
Private mcolCompanies As Collection
Private Sub Class_Initialize()
Set mcolCompanies = New Collection
Private Sub Class_Terminate()
Set mcolCompanies = Nothing
Public Property Get NewEnum() As IUnknown
Set NewEnum = mcolCompanies.[_NewEnum]
Public Sub Add(clsCompany As CCompany)
If clsCompany.CompanyID = 0 Then
clsCompany.CompanyID = Me.Count + 1
Set clsCompany.Parent = Me
mcolCompanies.Add clsCompany, CStr(clsCompany.CompanyID)
Public Property Get Company(vItem As Variant) As CCompany
Set Company = mcolCompanies.Item(vItem)
Public Property Get Count() As Long
Count = mcolCompanies.Count
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 SortFilterClass.zip