Hi Peter,

Thanks for your prompt reply. I should have made it clear like this: "my
drop source is of data type vbCFText, e.g. `=MyArrayFunction(...)`". I know
this as it is part of an OLE application.

Since we need to drag such a "string" and drop it in a cell, we have no way
to either (1) array-enter the function call string into a series of cells, or
(2) modify/add additional arguments into the drop source's string value.

My research suggests me that there is no way a function (mine is in VBA) can
alter/affect cells other than those cells that triggered the function call. I
doubt the only way to work around this problem is to multithreading.

I am thinking of creating a thread that runs once every few seconds, to
check if any cells have the content of `=MyArrayFunction(...)". The thread
would then activate a series of cells, and programmatically array-enter the
function call into those cells.
Cells that have been activated will be filled by the returned array. The
thread can always activate more (or less) cells on the go, so as to fit the
returned array into just the right number of cells in the worksheet.

Could this be a right solution in this case?

Regards,
Huy


"Peter T" wrote:

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