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
– braX, Commented Aug 7, 2017 at 17:27
I created a class called "Variable". Is that not enough to define it as a type? — Ari Levisohn
– Ari Levisohn, Commented Aug 7, 2017 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
– braX, Commented Aug 7, 2017 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
– Ari Levisohn, Commented Aug 7, 2017 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
– BruceWayne, Commented Aug 7, 2017 at 17:47

cxw · Accepted Answer · 2017-08-07 18:10:51Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related