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)

--
Thanx in advance,
Best Regards,

Faraz

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

Thanx,

Can u kindly present a clear sample code?
--
Thanx in advance,
Best Regards,

Faraz


"Jan Karel Pieterse" wrote:

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

.

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