 # 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

Hi

the syntax will be

Code
``````Function func1(rng As Variant) As Variant
Set arrayA = rng
ReDim arrayB(1 To 8, 1 To 1)
For k = 1 To 8
arrayB(k, 1) = arrayA(k) * 2
Next k
func1 = arrayB
End Function``````
• 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!