When I do this in VBA, I look at the size of the cells holding the array formula
and pad with "".

For instance, if I select A1:A10 and array enter:
=myArr()

with this function:

Option Explicit
Function myArr() As Variant

Dim myVal As Variant
Dim HowManyCells As Long
Dim HowManyElements As Long
Dim iCtr As Long

'Application.caller is the cell(s)
'containing the array formula
'I'm using a single column area for this
'but you don't need to.
HowManyCells = Application.Caller.Cells.Count

myVal = Array("a", "B", "c", "D")

HowManyElements = UBound(myVal) - LBound(myVal) + 1
If HowManyElements < HowManyCells Then
ReDim Preserve myVal(1 To HowManyCells)
For iCtr = HowManyElements + 1 To HowManyCells
myVal(iCtr) = ""
Next iCtr
End If

myArr = Application.Transpose(myVal)

End Function

Maybe you can use this idea????


as?m ?enyuva wrote:

hello,
I Wrote a custom function on excel. My function is return an array. and its
runs OK. My problem is #N/A. Firstly. I wrote an my function command excel
function bar and after I select an area on worksheet and press CTRL + SHIFT +
ENTER.

if my selected range is bigger then function return value ( array - XLOPER -
size). I saw #N/A. how can i remove this. Anyone knows something about that ?

--

Dave Peterson