i have a custom function that returns an array of values based on
several inputs. to implement this, a range of cells are highlighted,
the function is typed, and then ctrl + shift + enter keys are
entered. i would like to calculate the number of cells that are
required for the output so the user does not have to input this.
cannot use activecell, that only works when the function is created.
if the any of the arguments point to a cell, the function is
recaclulated when that cell changes. the activecell is then the
argument cell and not the cell that is being calculated. is it
possible to find what cell is being calculated during the
recalculation process?

I am not sure, exactly what you need, but maybe this can help you:

Selection.Cells.Count
Selection.address

Maybe you should post your code for further comments.

Per

On 27 Nov., 02:38, extrnh <ext...@yahoo.com> wrote:
> i have a custom function that returns an array of values based on
> several inputs. *to implement this, a range of cells are highlighted,
> the function is typed, and then ctrl + shift + enter keys are
> entered. *i would like to calculate the number of cells that are
> required for the output so the user does not have to input this.
> cannot use activecell, that only works when the function is created.
> if the any of the arguments point to a cell, the function is
> recaclulated when that cell changes. *the activecell is then the
> argument cell and not the cell that is being calculated. *is it
> possible to find what cell is being calculated during the
> recalculation process?

On Nov 26, 8:38*pm, extrnh <ext...@yahoo.com> wrote:
> i have a custom function that returns an array of values based on
> several inputs. *to implement this, a range of cells are highlighted,
> the function is typed, and then ctrl + shift + enter keys are
> entered. *i would like to calculate the number of cells that are
> required for the output so the user does not have to input this.
> cannot use activecell, that only works when the function is created.
> if the any of the arguments point to a cell, the function is
> recaclulated when that cell changes. *the activecell is then the
> argument cell and not the cell that is being calculated. *is it
> possible to find what cell is being calculated during the
> recalculation process?

Here is the code;

Public Function samplefunction(N As Long, A As Double, B As Double, G
As Double, _
D As Double, P As Long) As Variant()

' execute the samplefunction function and return the output as
column array

Dim dblX() As Double
Dim i As Long
Dim lngIerr As Long
Dim varTemp As Variant
'
ReDim dblX(0 To N - 1)
On Error Resume Next
Call samplefunction_vba(N, dblX(0), A, B, G, D, P, lngIerr)
If lngIerr <> 0 Then ' error in dll - fill in output array with
error value
ReDim varTemp(1 To 1)
varTemp(1) = lngIerr
Else
ReDim varTemp(1 To N)
For i = 1 To N
varTemp(i) = dblX(i - 1)
Next i
End If
samplefunction = Application.Transpose(varTemp)

End Function


Again this is a custom function that returns an array of values. So a
FormulaArray is used when entering the function on the worksheet. N
is the number of cells in the output, which is the number of cells
that are highlighted when the FormulaArray is entered. I do not want
to require the user to input N, I want to be able to determine the
number of cells for the FormulaArray. Any ideas?

Application.caller will return the range of cells that contain the formula.

If you use a multi-cell range (say A1:C9) and use an array formula like:

=myfunc(x99)
and array enter it, you could use:

Option Explicit
Function myFunc(rng As Range) As Variant

Dim HowManyRows As Long
Dim HowManyCols As Long

HowManyCols = Application.Caller.Columns.Count
HowManyRows = Application.Caller.Rows.Count

'some tests to see how to rearrange your output.
myfunc = ...

End Function



On 11/26/2010 19:38, extrnh wrote:
> i have a custom function that returns an array of values based on
> several inputs. to implement this, a range of cells are highlighted,
> the function is typed, and then ctrl + shift + enter keys are
> entered. i would like to calculate the number of cells that are
> required for the output so the user does not have to input this.
> cannot use activecell, that only works when the function is created.
> if the any of the arguments point to a cell, the function is
> recaclulated when that cell changes. the activecell is then the
> argument cell and not the cell that is being calculated. is it
> possible to find what cell is being calculated during the
> recalculation process?

--
Dave Peterson

On Nov 30, 7:45*am, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> Application.caller will return the range of cells that contain the formula.
>
> If you use a multi-cell range (say A1:C9) and use an array formula like:
>
> =myfunc(x99)
> and array enter it, you could use:
>
> Option Explicit
> Function myFunc(rng As Range) As Variant
>
> * * *Dim HowManyRows As Long
> * * *Dim HowManyCols As Long
>
> * * *HowManyCols = Application.Caller.Columns.Count
> * * *HowManyRows = Application.Caller.Rows.Count
>
> * * *'some tests to see how to rearrange your output.
> * * *myfunc = ...
>
> End Function
>
> On 11/26/2010 19:38,extrnhwrote:
>
> > i have a custom function that returns an array of values based on
> > several inputs. *to implement this, a range of cells are highlighted,
> > the function is typed, and then ctrl + shift + enter keys are
> > entered. *i would like to calculate the number of cells that are
> > required for the output so the user does not have to input this.
> > cannot use activecell, that only works when the function is created.
> > if the any of the arguments point to a cell, the function is
> > recaclulated when that cell changes. *the activecell is then the
> > argument cell and not the cell that is being calculated. *is it
> > possible to find what cell is being calculated during the
> > recalculation process?
>
> --
> Dave Peterson

Dave:

Thank you very much, this is what I needed.