arrays - Excel VBA 数组 - 获取#Value!尝试将数组转储到电子表格时出错

Question

如果有人可以帮助我使用此代码，我将不胜感激。
- 目标:我试图用两个数组之间的匹配填充一个数组(一个数组是我在第二个数组中寻找的引用数据)。

代码和数组在开发屏幕的 Watches 和 Locals 屏幕中看起来不错，但是当我在 excel 电子表格中检查该函数时，它返回一个 #value!错误。

提前谢谢了。

问候
罗德内

Function FTE_Detail(sref As Range, eref As Range, esource As Range, bplan As Range, eplan As Range) As Variant

    'Application.Volatile

    Dim rreference(34, 0) As String, dumper(150, 6) As String, vsource(17424, 11) As String, k As Integer, j As Integer
    Dim b As Integer, c As Integer, month As Integer, a As Integer
    Dim IDNUMBER As Integer, name As String, empID As String, fromCC As String, tocc As String

    month = Worksheets("Introduction").Cells(7, 6).Value


    For k = 0 To (eref.Row - sref.Row)
        rreference(k, 0) = Worksheets("data").Cells(sref.Row + k, sref.Column).Value
    Next k
    k = 0
    j = 0

    For k = 0 To 11
        For j = 0 To esource.Row
        If Len(Worksheets("data").Cells(70 + j, esource.Column + k).Value) > 250 Then
            vsource(j, k) = Left(Worksheets("data").Cells(70 + j, esource.Column + k).Value, 250)
        Else
            vsource(j, k) = Worksheets("data").Cells(70 + j, esource.Column + k).Value
        End If
        Next j
    Next k

    i = 0
    k = 0
    j = 0
    c = 0
    IDNUMBER = 0

    'hire array
        Do While i <= (eref.Row - sref.Row + 1)
                Do While k <= esource.Row

                    If InStr(vsource(k, month - 2), rreference(i, 0)) Then
                        If vsource(k, month - 3) = "" Then

                            IDNUMBER = IDNUMBER + 1
                            name = Worksheets("data").Cells(70 + k, 1).Value 'Employee name
                            empID = Worksheets("data").Cells(70 + k, 2).Value 'Employee ID

                            dumper(j, 0) = "hire"
                            dumper(j, 1) = Str(IDNUMBER)
                            dumper(j, 2) = name
                            dumper(j, 3) = Str(empID)
                            dumper(j, 4) = "-"
                            dumper(j, 5) = vsource(k, month - 2)
                            dumper(j, 6) = Worksheets("data").Cells(70 + k, 133).Value 'Employee Country

                            j = j + 1

                        Else
                        End If
                    Else
                    End If
                    k = k + 1

                Loop
                k = 0
                i = i + 1
        Loop
    FTE_Detail = dumper()
End Function

首先，我选择范围GH183:GH215

然后按 F2 并粘贴公式 =FTE_detail(GG183,GG215,DP17424,'2013PlanfromBex'!P3,'2013PlanfromBex'!P2369)

返回 #value!在我之前选择的所有单元格中。

我之前已经用其他数据完成了这个过程并且工作过。不知道为什么在这种情况下不起作用。

p.s.:似乎它在调试/监 window 口中构建了所有数组，只有当函数结束时，它才会将数据从“转储器”粘贴到电子表格中失败。

Answer 1

编辑 :我看到@shahkalpesh 打败了我......

不是真正的答案，而是解决一些评论。毫无疑问，您可以使用 UDF 返回值数组。请参见下面的示例:选择一个 2x2 范围并输入
=GetData()
并使用 Ctrl+Shift+Enter将其作为数组公式输入。

Function GetData()
Dim arr(1 To 2, 1 To 2)

    arr(1, 1) = "1,1"
    arr(1, 2) = "1,2"
    arr(2, 1) = "2,1"
    arr(2, 2) = "2,2"

    'Err.Raise 13      'uncomment to demonstrate #VALUE in all cells    
    GetData = arr

End Function