Put in Excel Spreadsheet from a Function

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


    Sub or function not defined.

    In module:

    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:

    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


    '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!