CODEWITHSUNDEEP
excelvba
rohrl77
rohrl77

Reputation: 3337

How to write a UDF that uses the cell it is written in as a refrence

I want to write a UDF that returns the RGB value of the background color of the cell it is written in. How does one refrence the cell in which the UDF is written without specifying the cell?

I'd like my function to work the same way the =COLUMN() worksheet function does.

Supplying ActiveCell does not work... because the activecell will something else when a recalc is done of the sheet.

Function BackgroundAsRGB(Optional rng As Range)
On Error GoTo Hell
    If rng Is Nothing Then
        Set rng = ActiveCell 'It isn't active cell...
    End If
    BackgroundAsRGB = ColorLongToRGB(rng.Resize(1, 1).Interior.Color)
Hell:
End Function

Upvotes: 2

Views: 89

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

Try:

    Function BackgroundAsRGB(Optional rng As Range)
Dim MyTarget As Range

If rng Is Nothing Then
    Set MyTarget = Application.Caller
Else
   Set MyTarget = rng
End If

BackgroundAsRGB = ColorLongToRGB(MyTarget.Resize(1, 1).Interior.Color)

End Function

Application.Caller returns the object that is invoking the UDF, so it returns the range where you execute this UDF. More info:

Application.Caller property (Excel)

Also, check:

Application.ThisCell property (Excel)

Upvotes: 4

Related Questions