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

Question

如果有人能帮助我处理这段代码，我将不胜感激。-目标:我试图填充一个数组，只有两个数组之间的匹配(一个数组是我在第二个数组中寻找的参考数据)。

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

提前感谢。

的问候rodnei

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)
1. 返回我之前选择的所有单元格中的#value!。
2. 我以前用其他数据做过这个过程，并且成功了。不知道为什么在这种情况下不工作

注。:似乎它在调试/观察窗口中构建了所有数组，只有当函数结束时，它才会将数据从"转储器"粘贴到电子表格中。

Answer 1

EDIT:我看到@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

Answer 2

不能使用从工作表对象调用的UDF来操作工作表上的单元格范围。

转发:

更多信息在这里:

https://stackoverflow.com/a/15647054/1467082

:

http://www.excel-it.com/UDF.htm

一般来说，子程序可以操作工作表和函数不能。

例外是从子例程中调用的函数可以，然而，这可能是一个坏习惯，使用一个函数的任何东西除了返回值给子程序。

Answer 3

我明白是怎么回事了。175行的数组内容和函数结果超过了转储数组的大小(150行)。如果发生这种情况，excel电子表格中的结果将返回#value!我增加了转储数组的大小，代码运行得很好。谢谢你的帮助!期待未来能贡献更多。问候Rodnei