Creating Dynamic Runtime Lists

Creating Dynamic Runtime Lists

I work in a world of lists. Lists that need to be reported on. Often these lists are implicit, in that they’re contained in the data that’s being processed and I don’t know in advance what, or how many, items they contain. In these situations, I determine the list by extracting it from the data right before processing it. That’s what I mean by creating dynamic runtime lists.

I first stumbled upon this concept when creating my FaceIdViewer. It was unique in that instead of hard-coding the number of FaceIDs in different versions of Excel, it just counted them up at runtime. Something like this code, which still works in Excel 2010.

Sub CountFaceids()
Dim cbar As Office.CommandBar
Dim ctl As Office.CommandBarControl
Dim FaceIdNumber As Long
Dim NoMoreFaceIds As Boolean

'the cbar probably doesn't exist, but if it does let's delete it
On Error Resume Next
On Error GoTo 0
Set cbar = Application.CommandBars.Add(Name:="TempForFaceiIdCount", temporary:=True)
With cbar
    Set ctl = cbar.Controls.Add(Type:=msoControlButton)
    'FaceId index is zero-based
    FaceIdNumber = 0
    Do Until NoMoreFaceIds
        On Error Resume Next
        'Loop through the FaceId numbers, assigning them to the button,
        'until we error at the upper limit plus one
        ctl.FaceId = FaceIdNumber
        If Err.Number <> 0 Then
            NoMoreFaceIds = True
        End If
        On Error GoTo 0
        FaceIdNumber = FaceIdNumber + 1
End With
MsgBox Format(FaceIdNumber, "#,##0") & " FaceIds found"
End Sub

Excel 2010 FaceId count

(As an aside, it’s interesting that Excel 2010 has 22,716 FaceIds, more than double the 10,040 in Excel 2003, and thousands more than 2007, which has 16,210. I wonder why there’s such a big increase in something with such decreased use?)

Ice cream sales

Another example of creating lists on the fly is splitting worksheets into multiple ones, based on the values in a certain column. It can be really hard to know beforehand what values will be in the column, as in this example of an ice cream store with multiple salespeople and, of course, many delicious flavors.

In addition to getting a list of salespeople in column A to report on, I want the list to contain unique entries. Each salesperson should only be listed once.

To get this unique list I use something like the function below. It stores the list in a collection, taking advantage of the fact that collections can’t contain duplicate keys. The function takes a range and adds each cell’s value to the collection, provided the value is unique. We surround the attempt with On Error statements, so that the code continues merrily along if trying to add a duplicate value causes an error. In this example it will quietly error many more times than not, generating a list of about 10 salespeople from the dozens of rows containing their names.

Function GetUniqueCellValues(rng As Excel.Range) As Collection
Dim collUniqueCellValues As Collection
Dim CellArray As Variant
Dim i As Long, j As Long

'assign all the cell values to a variant array
CellArray = rng.Value
Set collUniqueCellValues = New Collection
'cycle through the two dimensions of the array
For i = LBound(CellArray, 1) To UBound(CellArray, 1)
    For j = LBound(CellArray, 2) To UBound(CellArray, 2)
        'if we try to add a duplicate, ignore the error
        On Error Resume Next
        collUniqueCellValues.Add CStr(CellArray(i, j)), CStr(CellArray(i, j))
        On Error GoTo 0
    Next j
Next i
Set GetUniqueCellValues = collUniqueCellValues
End Function

The code above could just cycle through each cell in the range. Instead it loads the range’s values into a variant array and processes that array, which greatly speeds things up with long lists.

Below is an ice cream sales report subroutine that uses this GetUniqueCellValues function. It creates a collection containing each salesperson’s name. It then creates a new workbook to contain the individual reports. The source worksheet is copied to this workbook, once for each name in the collection. Each copied worksheet is filtered to show all names except the one being reported on. The visible rows are then deleted and the filter turned off. The result is a workbook with one report per salesperson. Note that, because we used a filter, no sorting is required:

Sub CreateSalesReports()
Dim wsSales As Excel.Worksheet
Dim wbSalesReport As Excel.Workbook
Dim ListLastRow As Long
Dim collSalesPeople As Collection
Dim i As Long
Dim wsSalesPerson As Excel.Worksheet

Set wbSalesReport = Workbooks.Add
Set wsSales = ThisWorkbook.Sheets("Sales")
With wsSales
    ListLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    'create the unique list/collection of salespeople
    Set collSalesPeople = GetUniqueCellValues(.Range("A2:A" & ListLastRow))
End With
'cycle through the salespeople, creating a worksheet/report for each
For i = 1 To collSalesPeople.Count
    wsSales.Copy after:=wbSalesReport.Sheets(wbSalesReport.Sheets.Count)
    Set wsSalesPerson = ActiveSheet
    'filter to all other salespeople
    With wsSalesPerson
        .Range("A1").AutoFilter Field:=1, Criteria1:="<>" & collSalesPeople(i)
        'delete the visible rows, leaving just those for the salesperson
        .Range("2:" & ListLastRow).EntireRow.Delete
        'name the worksheet
        .Name = collSalesPeople(i)
        .AutoFilterMode = False
    End With
Next i
End Sub

Here’s the resulting workbook:

If you’re interested, Dennis Wallentin explains a different way to get unique values from a range, using Advanced Filter.

2 thoughts on “Creating Dynamic Runtime Lists

  1. thx a lot for the note about the max 22716 face ID’s in the newest Excel version. I increased my “max” from 10100 to 23000 now in my faceID viewer userform. I still use the old faceID’s a lot for Addin command bars.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>