Excel VBA "Type mismatch:array or user-defined type expected"

Question

I know a lot of people have asked questions about this error, but based on the answers to those, I should be doing everything right.

I created a class called Variable to store multiple pieces of information about a variable. I have another class called Equipment which stores an array of those variables. Here is the relevant code in Equipment:

Public name As String
Private variables() As Variable

Public Sub setVariables(vars() As Variable)
    variables = vars
End Sub

I also have a module which creates instances of Equipment. Here is all the code for that:

Public Sub fillEquipment()

    'figure out how many units of equipment there are
    numUnits = 0
    atRow = 1
    Do Until Range("A" & atRow).value = ""
        numUnits = numUnits + 1
        atRow = atRow + 1
    Loop

    'create array for equipment units
    Dim units() As Equipment
    ReDim units(0 To numUnits)

    'figure out how many variables there are
    numVars = 0
    For Each col In Range("A1:ZZ1")
        If col.value <> "" Then
            numVars = numVars + 1
        End If
    Next col

    'create an array of equipment one row at a time
    atRow = 1
    Do Until Range("A" & atRow).value = ""
        'create and name equipment
        units(atRow) = New Equipment
        units(atRow).name = Range("A" & atRow).value

        'create an array of vars
        Dim variables() As Variable
        ReDim variables(0 To numVars)
        For atCol = 1 To numVars
            variables(atCol) = New Variable
            variables(atCol).name = Cells(1, atCol).value
            variables(atCol).value = Cells(atRow, atCol).value
        Next atCol

        'add variables to equipment
        units(atRow).setVariables (variables)
        atRow = atRow + 1

    Loop

    'print for testing
    For atRow = 1 To numUnits
        Cells(atRow, 1).value = Equipment(atRow).name
        For atCol = 1 To numCols
            Cells(atRow, atCol + 1).value = Equipment(atRow).getVariables(atCol)
        Next atCol
    Next atRow

End Sub

Here's my problem: when I run the program it gives me a compiler error "Type mismatch:array or user-defined type expected" on the word variables in units(atRow).setVariables (variables).

I don't get what i am doing wrong. variables is defined as an array of the object type Variable which is exactly what setVariables is asking for.

Thank you! I really appreciate the help!!

Answer 1

You have extra parentheses. This compiles without the error:

Sub make(numUnits As Long, numVars As Long)
    Dim units() As Equipment
    ReDim units(0 To numUnits)
    Dim atRow As Long, atCol As Long    ' <-- new Dim, because of Option Explicit

    'create an array of equipment one row at a time
    atRow = 1
    Do Until Range("A" & atRow).value = ""
        'create and name equipment
        units(atRow) = New Equipment
        units(atRow).name = CStr(Range("A" & CStr(atRow)).value)   ' <-- use CStr() anytime you need a string

        'create an array of vars
        Dim variables() As Variable
        ReDim variables(0 To numVars)
        For atCol = 1 To numVars
            variables(atCol) = New Variable
            variables(atCol).name = Cells(1, atCol).value
            variables(atCol).value = Cells(atRow, atCol).value
        Next atCol

        'add variables to equipment
        units(atRow).setVariables variables
        atRow = atRow + 1       ' ^^^^^^^^^ not (variables) - no parens

    Loop
End Sub

The key issue was the parentheses. However, this also adds Dim statements for your variables. As @BruceWayne said, you should always use Option Explicit. Yes, that is in every module and every class module. To do otherwise is to throw away debugging assistance from the compiler.

I actually also use Option Base 0 at the top of every module, mostly to remind myself which system I'm working in :) .

Edit I added some CStrs, which protect you from weird corner cases. Should develop this code further, I would recommend using explicit worksheet variables rather than relying on the implicit ActiveSheet. See, e.g., this answer.