Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can 1 excel's DROP target change MULTIPLE cells?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can 1 excel's DROP target change MULTIPLE cells?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
can 1 excel's DROP target change MULTIPLE cells?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Date Cell when any of target cells change | Excel Worksheet Functions | |||
Change the default color of Excel's selected cells | Excel Discussion (Misc queries) | |||
change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
Deny change of value of two target cells / enforce use of macros. | Excel Programming | |||
Is there a way to change Excel's pointer from a + to an arrow (li. | New Users to Excel |