can 1 excel's DROP target change MULTIPLE cells?

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

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" <Huy@discussions.microsoft.com> wrote in message
news:63C95851-5112-411E-B91C-706C6312CA76@microsoft.com...
> 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

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" <Huy@discussions.microsoft.com> wrote in message
> news:63C95851-5112-411E-B91C-706C6312CA76@microsoft.com...
> > 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
>
>
>

I vaguely follow what you are trying to do but not the overall scenario. But
at the end of the day if the formula that returns an array is not array
entered only the first element will be returned to the cell, the rest will
be lost.

I can only see the two possibilities I mentioned previously, namely array
enter (which can be done programmatically) or adapt the function to return
individual elements.

Does a Drop event occur in the OLE app that is exposed to you. If so maybe
you can incorporate some code to 'array-enter' the cells that had the
formula dropped into. Or perhaps a worksheet change event. Could get messy!

Regards,
Peter T


"Huy" <Huy@discussions.microsoft.com> wrote in message
news:331B4353-56F5-46A2-B073-A8FFB1575DF3@microsoft.com...
> 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" <Huy@discussions.microsoft.com> wrote in message
> > news:63C95851-5112-411E-B91C-706C6312CA76@microsoft.com...
> > > 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
> >
> >
> >