User Defined Function - Can we identify the cell it's called from

[Guest]

I have a UDF and I want to identify the cell that the function is called
from. I need this because I want to be able to "conditionally" format the
cell based upon the function result and there are more than 3 conditions.
Can someone assist? I know what I need to do for the conditions, just need
to find the cell address.

Thanks,
Barb Reinhardt

 

[Guest]

Application.caller:


Function barb() As String
barb = Application.Caller.Address
End Function

 

[Guest]

Thanks. Let's say I do something like this

function barb()

Set rngWhere = Application.Caller
'do the calculations to get the value for barb
if barb = 1 then
rngwhere.interior.colorindex = 5
end if

I can see what the interior.colorindex is, but it doesn't redefine it. I'm
guessing I can't do this in a function. Can I create a sub and call it to
redefine the colors?

Thanks,
Barb Reinhardt

 

[Guest]

I just found that UDF's are not able to do what I want to do. It would have
been so easy if they could.

Oh well.

Thanks for your assistance.
Barb Reinhardt

 

[Guest]

You are correct, but there is still a way!. As I remember, UDFs can create
and insert comments. Not a clean as background color, but still a visible
indicator that something noteworthy has occurred.

 

[Robert Bruce]

I just found that UDF's are not able to do what I want to do. It
would have been so easy if they could.

Oh well.

Here's a post made recently to another group. It doesn't deal with colour
formatting, but with number formats, but the principle still applies:

This idea comes up from time to time and its origins are in the way that
excel responds to the user entering a worksheet function such as NOW(). Try
it. Excel not only returns the current date and time, but also reformats the
cell. This leads some people to think that a) the formattring is being done
by the function and b) it might be possible to do something similar with a
UDF. In fact, the function is not doing the formatting. Excel is registering
that the function has been entered and is responding afterwards by
'helpfully' changing the number format. An internal list, to which we have
no access, is maintained of the functions which Excel thinks might benefit
from such reformatting.

Maybe this can be done with a global sheet change event hook. We need to
check if any cell changing was unformatted ("General") before we entered our
UDF. If it is, we change the formatting to however we want it. If the UDF is
in an add-in, the whole thing can be encapsulated within the add-in
workbook.

Say we have a function called MyNow in a regular module in an Add-In:

Option Explicit

Function MyNow()
Application.Volatile
MyNow = Now()
End Function

Now (ahem!) we put the following event code in the add-in's Thisworkbook
module:

Option Explicit

Private WithEvents oApp As Excel.Application

Private Sub Workbook_Open()
Set oApp = Application
End Sub

Private Sub oApp_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.NumberFormat = "General" And _
UCase(Target.Formula) Like "=MYNOW(*)" Then _
Target.NumberFormat = "mm:ss"
End Sub

This should do what we want subject to further testing (which I'm not going
to do since I don't have any use for this!).

Rob