Printing an array from a function

    I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.


    In Module1:

    Code
    Function func1(ByRef arrayA())
    Dim arrayB(8)
    For k = 0 To 7
        arrayB(k + 1) = arrayA(k) * 2
    Next k
    func1 = arrayB
End Function


    In sheet1:


    Code
    =func1(D3:K3)


    The input is D3:K3, arrayA.


    How does the resultant arrayB get populated onto the spreadsheet?

    Re: Printing an array from a function


    You will need to array enter your function into all the output cells at once.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Printing an array from a function


    Quote from rory;626249

    You will need to array enter your function into all the output cells at once.


    How do you enter the array output in more than one cell?

    Re: Printing an array from a function


    Select 1 row x 8 columns, same size as the source range.
    Enter
    =func1(D3:K3,8)
    then confirm with Ctrl + Shift + Enter (Array formula entry)

    Code
    Function func1(rng As Range, mult As Double)
    func1 = rng.Parent.Evaluate(rng.Address & "*" & mult)
End Function

Participate now!

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

Register Yourself Login