CODEWITHSUNDEEP
arraysexcelvba
rodneijr
rodneijr

Reputation: 41

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

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
  1. First, I select the range GH183:GH215
  2. Then press F2 and paste the formula =FTE_detail(GG183,GG215,DP17424,'2013PlanfromBex'!P3,'2013PlanfromBex'!P2369)
  3. Returns #value! in all the cells I selected before.
  4. 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.

Upvotes: 2

Views: 1480

Answers (3)

rodneijr
rodneijr

Reputation: 41

I figured out what was wrong. The array content, and function result of 175 lines, surpassed the size of the dumper array, which is 150 lines. If that happens, the result in the excel spreadsheet returns #value!. I've increased the size of the dumper array and the code worked beautifully. Thanks for all your help ! Looking forward to contribute more in the future. Regards Rodnei

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166341

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

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

It is not possible to use a UDF, called from the Worksheet object, to manipulate a range of cells on the worksheet.

Reposted:

More information here:

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

And here:

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

generally, subroutines can manipulate the worksheet, and functions cannot.

The exception is that functions called from within a subroutine can, however this is probably a bad habit to use a function for anything other than returning values to the Subroutine.

Upvotes: 0

Related Questions