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!!

This `Private variables() As Variable` should be `Private variables() As Variant` - and that same change needs to be made all throughout the code. There is no such thing as a `Variable` type unless you define it yourself. — braX, Aug 07 '17 at 17:27
I created a class called "Variable". Is that not enough to define it as a type? — Ari Levisohn, Aug 07 '17 at 17:29
Ah - and you just dont have that shown. I see where you mention it now tho. No one here is going to be able to test any of your code without that being defined as well. — braX, Aug 07 '17 at 17:32
Right now it is just a code module with these to declarations: `Public name As String` `Public value As Long` — Ari Levisohn, Aug 07 '17 at 17:35
What are you trying to do? With an array, you can't just do (AFAIK) `Variables`. You're going to need to loop through the variables array and assign the units one at a time. ...if I understand correctly. ...What's `Units` anyways? You should also add `Option Explicit` to the very top of your code, to make sure you declare all variables. — BruceWayne, Aug 07 '17 at 17:47
1) Do I need to declare all variables? Isn't that not necessary? 2) The reason I want to pass the whole array at once is because the I don't know ahead of time how big it is going to be. — Ari Levisohn, Aug 07 '17 at 17:52
Also `units` is an array of `Equipment`. I added a deceleration for it above. — Ari Levisohn, Aug 07 '17 at 17:56
Where do you set NumVars? If NumVars is 0, the ReDim won't do anything, neither will the for next loop do anything. — Rob Anthony, Aug 07 '17 at 18:00
Remove the parentheses --- Instead of `units(atRow).setVariables (variables)`, `units(atRow).setVariables variables`. If that doesn't work, try `Public Sub setVariables(vars As Variant)` — cxw, Aug 07 '17 at 18:01
The extra parentheses are messing it up. `DoSomething (foo)` isn't the same as `DoSomething foo`. Drop the parens, they're forcing the parameter to be evaluated as a value and passed `ByVal`, which is illegal for an array. — Mathieu Guindon, Aug 07 '17 at 19:26

cxw · Accepted Answer · 2017-08-07T18:10:51.310

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.

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

1 Answers1