Excel VBA“类型不匹配：预期为数组或用户定义的类型”

阿里·莱维森（Ari Levisohn）

我知道很多人都问过有关此错误的问题，但基于对这些错误的回答，我应该做正确的一切。

我创建了一个名为的类，Variable用于存储有关变量的多条信息。我还有另一个类Equipment，它存储这些变量的数组。以下是相关代码Equipment：

Public name As String
Private variables() As Variable

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

我也有一个模块，可以创建的实例Equipment。这是所有代码：

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

这里是我的问题：当我运行它，它给了我一个编译器错误“类型不匹配：数组或用户定义类型预期”这个词variables在units(atRow).setVariables (variables)。

我不明白我在做什么。variables被定义为Variable恰好setVariables要求的对象类型的数组。

谢谢！我非常感谢您的帮助！

您还有多余的括号。编译时不会出现错误：

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

关键问题是括号。但是，这也会Dim为您的变量添加语句。正如@BruceWayne所说，您应该始终使用Option Explicit。是的，这在每个模块和每个类模块中都有。否则，就是放弃编译器的调试帮助。

实际上，我还使用Option Base 0了每个模块的顶部，主要是提醒自己正在使用的系统:)。

编辑我添加了一些CStr，可以保护您免受奇怪的极端情况的影响。为了进一步开发此代码，我建议使用显式工作表变量，而不要依赖隐式变量ActiveSheet。参见例如这个答案。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。