Put in Excel Spreadsheet from a Function

  • I am trying to put arr1 onto Sheet1 but it is giving me this error:


    Quote

    Sub or function not defined.


    In module:


    Code
    Function func(ByRef arr As Variant) As Variant
        Dim arr1(8) As Variant
        For k = 1 To 8
            arr1(k) = arr(7 - k)
        Next k
        Call printarr(arr1)
        func = arr1(8) & arr1(7) & arr1(6) & arr1(5) & arr1(4) & arr1(3) & arr1(2) & arr1(1)
    End Function


    In Sheet1:


    Code
    Sub printarr(ByRef arr As Variant)
        With Sheets("Sheet1")
            For k = 1 To Len(arr)
                .Cells(7, k + 3) = arr(k)
            Next k
        End With
    End Sub


    Code
    =func(D3:K3)


    'In cell 5,3

  • Re: Put in Excel Spreadsheet from a Function


    Moved subroutine to module:



    The function call "=func(D3:K3) "is now having an error: "#VALUE!""

  • Re: Put in Excel Spreadsheet from a Function


    I think that is the problem "=func(D3:K3) is returning "#VALUE!". The function returned the values from "D3:K3" ok without the "Call printarr(arr)" but when I added it it went to the "#VALUE!" error and no printout in row 7.

  • Re: Put in Excel Spreadsheet from a Function


    The problem, or at least one problem, is the use of Len(arr).


    Just realised another possible problem, if you pass D3:K3 to the function arr will not be an array, it wil be a range.


    So you'll need to treat it as such.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!