Getting The Cell Index For A Function Not The Cells Value

I am trying to get the cell index from a cell reference passed to a function and I have no idea how.

eg. In excel

=UDF(D4,...,...,)

What I want is to get is the row and column index for cell D4, not whatever is in cell D4, so I can just click the cell in excel, and drag the formula about etc.

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks, the problem is they are worksheet functions that are not available to VBA, so I cant use them. Anyone got some bright ideas?

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks Andy, but all I can get out of that is the cell the formula is in, I dont know how to set a cell passed to the function as the active cell to use activecell.xxxx which would be one way of doing it if its possible.

What I want to do is have in an Excel cell;

=testfunction(B6, X, Y)

And be able to drag it down a column, across a row etc, and have B6 change accordingly. But what I want is the row and column index of the cell as i would get in Excel by typing

=row(B6)
&
=column(B6)

as the result depends on the values X cells above it and Y cells below it. See example below;

Option Explicit
'// Test Function
Public Function testfunction(targetcell As Variant, par1 As Integer, par2 As Integer) As Double
Dim row%, column%


row= ???????????????.targetcell
'//Equivalent of =row(targetcell)
column = ??????????????.targetcell
'//Equivalent of =column(targetcell)




testfunction = Cells(row, column) / (Cells(row - par1, column) + Cells(row + par2, column))


End Function

Re: Getting The Cell Index For A Function Not The Cells Value

Thanks very much Andy :).

& the Okk, thanks, yes the the function shown could be done with a formula, but it is just a grossly simplified example .