Excel VBA Array - Getting #Value! error when trying to dump array into spreadsheet

Question

I would be grateful if someone could help me with this code. - Objective: I am trying to populate an array with only the match between the two arrays (one array is the reference data I am looking for in the second array).

The code and arrays looks good in the Watches and Locals screens in the developement screen, but when I check the function in the excel spreadsheet it returns a #value! error.

Many thanks in advance.

Regards 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

First, I select the range GH183:GH215
Then press F2 and paste the formula =FTE_detail(GG183,GG215,DP17424,'2013PlanfromBex'!P3,'2013PlanfromBex'!P2369)
Returns #value! in all the cells I selected before.
I have done this procedure with other data before and worked. Not sure why is not working in this case.

p.s.: It seems that it builds all the arrays fine in the debug/watches window, only when the functions ends it fails pasting the data from "dumper" to the spreadsheet.

How are you using/calling this function to assign the data it returns to a range? — shahkalpesh
I am selecting a range and writing the function and pressing crtl+shift+enter. tkns :) — rodneijr
It is not possible to use a function to return a value to a range of cells, see here for details. You will have to use a subroutine to manipulate a range of cells. — David Zemens
@shahkalpesh it is not possible to write an array of values to a multi-cell range, using a UDF. This appears to be what OP is trying to do. — David Zemens
@shahkalpesh I understood from the OP question that he is calling this function from a worksheet cell. Perhaps I misunderstand, that he says things like, "I am selecting a range of cells..." etc. — David Zemens

Tim Williams Tim Williams · Accepted Answer · 2013-06-03T21:18:47

EDIT: I see @shahkalpesh beat me to it...

Not really an answer, but to address some of the comments. There's no doubt you can use a UDF to return an array of values. See the example below: select a 2x2 range and enter

=GetData()

and use Ctrl+Shift+Enter to enter it as an array formula.

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

Excel VBA Array - Getting #Value! error when trying to dump array into spreadsheet

3 Answers