Hi Huy,

I'm not sure what you mean by drag a string into 1 cell. But you can of
course drag or drag-copy a formula in one cell to another, whether it's
arguments to other cells change would depend on relative addressing.

After entering the single formula, why not array enter into other cells in
the normal way. Then these cells will be populated with results of the array
function.

If you want an option for the function either to return either a full array
or a particular element of the array in a single cell, perhaps something
like this

Function myArrayFunc(arg1, Optional arg2)
Dim darr(1 To 1, 1 To 3)
On Error GoTo errH
darr(1, 1) = arg1 * 2
darr(1, 2) = arg1 * 4
darr(1, 3) = arg1 * 6

If Not IsMissing(arg2) Then
myArrayFunc = darr(1, arg2)
Else
myArrayFunc = darr
End If

Exit Function
errH:
myArrayFunc = CVErr(xlValue)
End Function

Note a vertical array is always 2D.

Regards,
Peter T

If you only want the array function in a single cell, why not add an extra
optional argument
"Huy" wrote in message
...
Hi,

I am new to VBA and I am looking for a way to drop a function call String
(e.g. "=MyArrayFunction(...)") into a single Excel cell, and the effect
should be that the returned array (1-dimension) is fed into a range of
Excel
cells (either horizontal or vertical).
In brief:
1. Drag a string into 1 cell
2. My function will compute and return an array
3. A range of cells, starting from the dropped target, will be filled with
values from the returned array

The biggest problem is that a function is restricted to manipulation of
only
the Application.Caller. As a result, only the first element of the
returned
array is seen in the dropped target (just 1 cell); the remaining elements
of
the array are simply discarded.

Please suggest me a way to resolve or perhaps work around this problem.

Sincerely,
Huy