Excel Function returning array

Posted on

QUESTION :

I want a UDF that accepts a parameter and returns an array.

Here is my function:

Function UnitSales(ToAdd As Long) As Long()
     UnitSales = Array(1 + ToAdd, 2 + ToAdd, 3 + ToAdd)
End Function

I am trying to use the array by selecting 3 cells and typing =unitsales(3) then pressing Ctrl-Shift-Enter

All I get is 3 cells that say #VALUE!
I am expecting 3 cells to contain 4,5,6

What am I doing wrong?

ANSWER :

Change the Long() to Variant()

Function UnitSales(ToAdd As Long) As Variant()
     UnitSales = Array(1 + ToAdd, 2 + ToAdd, 3 + ToAdd)
End Function

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *