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?