In this final installment, I look at the code to write a class module to a range. That is represented by the last two lines of this controlling procedure:
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
A quick and efficient way to write data to a worksheet, typically a slow process, is to assign an array to the Value property of a Range. This is done in two steps in the above procedure. The first step is to pull certain data out of the class into the Variant Array vaOutput. The second step is to assign that Variant Array to the Value property of a range.
To get the data into an array, I wrote the WriteComparables property. It returns a Variant (a data type that can be anything), which in this case will be an array. The second argument, lRange, is how I tell the property how many companies before and after the focus company I want to return, in this case 3.
Dim aReturn() As Variant
Dim lFocus As Long
Dim i As Long
Dim clsCurr As CCompany
Dim lCount As Long
Dim lStart As Long, lEnd As Long
lFocus = clsFocus.Index
If lFocus > lRange Then
lStart = -lRange
lStart = -lFocus + 1
If lFocus < Me.Count – (lRange – 1) Then
lEnd = lRange
lEnd = Me.Count – lFocus
ReDim aReturn(1 To (lEnd – lStart) + 2, 1 To 4)
aReturn(1, 1) = “Company”
aReturn(1, 2) = “City”
aReturn(1, 3) = “State”
aReturn(1, 4) = “Sales”
lCount = 1
For i = lStart To lEnd
Set clsCurr = Me.Company(lFocus + i)
lCount = lCount + 1
If clsCurr.CompanyID = clsFocus.CompanyID Then
aReturn(lCount, 1) = “*” & clsCurr.CompanyName
aReturn(lCount, 1) = clsCurr.CompanyName
aReturn(lCount, 2) = clsCurr.City
aReturn(lCount, 3) = clsCurr.State
aReturn(lCount, 4) = clsCurr.Sales
WriteComparables = aReturn
The basics of this procedure is to loop through a certain number of companies and fill the array with certain properties. That seems easy enough. But it gets slightly more complicated if there are not enough companies before or after my focus company. For instance, if I have eight companies in my class and my focus company is number seven on the list, then I will only have one company that comes after the focus company.
To account for that, I set lStart and lEnd to determine how far I should go. The first step is to find out where my focus company is. The Index property returns my focus company’s position in the order.
Dim i As Long
For i = 1 To Me.Parent.Count
If Me.Parent.Company(i).CompanyID = Me.CompanyID Then
Index = i
This simply loops through every company in the Parent (a CCompanies class) until it finds the focus company. Then it returns i, which is the position in the list. The next several lines determine lStart and lEnd. The easy case is when my focus company is in the middle with plenty of companies before and after it. In that case, I loop from -lRange to lRange (in this example, -3 to 3). If lFocus is too close to the start of the list, I set lStart equal to -lFocus + 1 (the first company in the class). If lFocus is too close to the end of the list, I set lEnd to Me.Count-lFocus (the last company in the class). What I’m actually getting in those two situations is where the first and last companies are compared to where lFocus is.
In this example, there are plenty of companies before Monarch but only two after. I will be looping from -3 to 2 (as offset from lFocus).
Next I establish the dimensions of aReturn. The first “row” of the array will be a header, so I create one more row than I need to hold the companies. In this case, -3 to 2 is six companies (0 is my focus company), so my “row” dimension is 1 to (2 – (-3)) + 2 or 1 to 7.
The next few lines put my header information in to the first element of aReturn and set the lCount (the counter I use to determine what row I’m on) to 1 so as to skip the first row where the header is.
Finally, we get to the loop. Remember we’re looping from -3 to 2. I set clsCurr to the company that’s at lFocus + i. For the first pass through the loop, that will get (6 + (-3)) or the third company in the list. The If block in the loop appends an asterisk in front of the company name if it’s the focus company. The rest of the For loop puts certain data (City, State, and Sales) into the proper “column” dimensions of the array.
The last line assigns the array to the property name so that’s it’s returned to the calling procedure.
Back in the controlling procedure, I want to start my output in cell G1. I resize that array for the number of dimensions in the array. If the array has 6×4 dimensions, then my range is resized to six rows and four columns. Assigning vaOutput to the Value property writes the whole array to the Range at once.
The result shows the three companies whose sales are less than Monarch’s and the two (because there are only two) companies whose sales are greater.
To recap, here are the steps we took to get this list: First, we filled a collection class with all the companies. Next, we found our focus company with a property of the collection class that returns a company given a name or partial name. Then we created a new collection class with only those companies with the same state. We sorted that class on the Sales property. Next, we created an array with the companies a certain number away (both before and after) our focus company. Finally, we assigned that array to the Value property of a Range to write it to the worksheet.
You can download SortFilterClass.zip