How to refer to the cell containing the formula?

[Faraz A. Qureshi]

Hi all!

Sometimes, I usually end up in a problem while creating UDFs which refer to
the cell actually containing the same. I usually use ActiveCell technique but
in a loop in a sub procedure its OK while when used in a UDF the ActiveCell
is always different where the cursor is place.

Furthermore, sure would oblige if one would kindly provide the code for
simple formula of reversing the column values like the following, as a UDF:

=OFFSET($D$1,ROWS($D$1:$D$20)-ROWS($A$1:A1),0)

 

[Jan Karel Pieterse]

Hi Faraz,

Sometimes, I usually end up in a problem while creating UDFs which refer to
the cell actually containing the same. I usually use ActiveCell technique but
in a loop in a sub procedure its OK while when used in a UDF the ActiveCell
is always different where the cursor is place.

Application.Caller returns the range which called the UDF.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Faraz A. Qureshi]

Thanx,

Can u kindly present a clear sample code?

 

[Jan Karel Pieterse]

Hi Faraz,

Can u kindly present a clear sample code?

This function writes the addresses of the cells that call it in the immediate
window (control+g form the VBE):

Public Function Demo(r As Variant)
Dim oRng as Range
On Error Resume Next
Set oRng =Application.Caller
On Error Goto 0
If oRng Is Nothing Then
'Function called from VBA perhaps?
Else
'Function called from Worksheet cell
'Write address in immediate window (control+g)
Debug.print oRng.Address
End If

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com