custom function - finding cell address

[extrnh]

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?

 

[Per Jessen]

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

 

[extrnh]

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?

 

[Dave Peterson]

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

 

[extrnh]

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

Dave:

Thank you very much, this is what I needed.