VBA Command Button array

Question

I'm currently working on a project where I'll be selecting up to 5 items to compare to each other, with the results being displayed in up to a 5x5 dynamic grid. My objective is to have this grid be composed of command buttons such that the caption of each button is the percent similarity between the row and column items, and on clicking the button, the units that are common between the row and column items will be displayed in a message box.

I more or less know how to generate the actual array of buttons. However, everything I've read suggests that I need to create a class to handle the button clicks, since I don't feel like making 20 subroutines that all have the same code in them. I have not been able to get this class to work properly, and I could use some tips. Here's what I have so far.

In a class module named DynButton:

Public Withevents CBevents as MSForms.CommandButton
Private Sub CBevents_Click()
    DisplayOverlappedUnits 'Sub that will display the units that are the same
                           'between items i and j- may use Application.Caller
End Sub

And in the userform itself:

      Private Sub Userform_Initialize()
        Dim NumItems as integer
        Dim ComparisonArray() as DynButton
        Dim ctlButton as MSForms.CommandButton
        'QuestionList() is a public type that stores various attributes of the 
        'items I'm comparing.

       'This code determines how many items were selected for comparison
       'and resets the item array accordingly.
       NumItems=0
       For i=1 to 5 
           If QuestionList(i).Length>0 Then
              NumItems=Numitems+1
              QuestionList(NumItems)=QuestionList(i)
           End If
       Next

Redim ComparisonArray(1 to NumItems, 1 to NumItems)
For i = 1 to NumItems
    For j=1 to NumItems
        Set ctlButton=Me.Controls.Add("Forms.CommandButton.1", Cstr(i) & Cstr(j) & cb)
        With ctlButton
            .Height= CB_HEIGHT 'These are public constants defined elsewhere.
            .Width= CB_WIDTH
            .Top= TOP_OFFSET + (i * (CB_HEIGHT+ V_PADDING))
            If i = j Then .visible = False
            .Caption= CalculateOverlap(i,j) 'Runs a sub that calculates the overlap between items i and j
            End With
        Set ComparisonArray(i,j).CBevents = ctlButton
        Next
    Next
End Sub

Currently, I get a "Object with or Block variable not set" when I hit the Set ComparisonArray line, and I'm stymied. Am I just missing something in the class module? Thanks in advance for the help.

Edited to add: I tried to model the class code in part off of this article, but again I haven't got it to work yet. http://www.siddharthrout.com/index.php/2018/01/15/vba-control-arrays/

Answer 1

Your code seems correct and interesting. The only (bug) I could see is:

Redim ComparisonArray(1 to NumItems, 1 to NumItems)
...
Set ComparisonArray(i,j).CBevents = ctlButton

The problem is that your array holds null references. You have not created your DynButton objects yet. You must explicitly creat the objects in your array.

Redim ComparisonArray(1 to NumItems, 1 to NumItems)
For i = 1 to NumItems
    For j = 1 to NumItems
       Set ComparisonArray(i,j) = new DynButton
    Next
Next        
...
Set ComparisonArray(i,j).CBevents = ctlButton

Also, declare the array ComparisonArray as a member object of the form, not as a local variable in Form_Initialize.