0

I'm trying to use Application.Caller inside a Function (code below), but Excel returns a #VALUE and the background color is not set.

The personal function is called from an Excel cell. The idea is to map RGB values to color display in a "synchronous" fashion (i.e. without having to press a button).

When I run the following function through the debugger and step just before the instruction vCaller.Interior.Color = RGB(rlev, glev, blev), I can manually set the background color to green by pasting the exact same instruction in the execution console. So I'm puzzled as to why Excel is failing but VBA isn't.

Any clue ?

Public Function RGB_print(rlev As Integer, glev As Integer, blev As Integer) 
As String

Dim vCaller As Variant

Set vCaller = Application.Caller
If TypeName(vCaller) = "Range" Then
    vCaller.Interior.Color = RGB(rlev, glev, blev)
End If
RGB_print = ""

End Function
Community
  • 1
  • 1
titus
  • 452
  • 4
  • 17
  • 4
    A function called from a cell cannot change that cell's colour (or any other cell's) – Rory Apr 10 '17 at 15:19
  • 1
    Where & how are you calling this code? – Absinthe Apr 10 '17 at 15:39
  • I am trying to map RGB values to color display in a "synchronous" way (i.e. without having to press a button). So maybe I can change another cell's color if it sounds less dangerous – titus Apr 10 '17 at 15:49
  • @Absinthe : I'm calling the function from an Excel cell – titus Apr 10 '17 at 15:49
  • @Rory : can I execute anything that read like "someCell.Interior.Color = RGB(...)" ? It seems that I can't – titus Apr 10 '17 at 15:59
  • No you can't do that. – Rory Apr 10 '17 at 16:01
  • 1
    https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 but it's worth pointing out that using code like this may well have unforseen side-effects – Tim Williams Apr 10 '17 at 16:12
  • @TimWilliams : wow thanks this is the answer I was looking for. If you want to post this as an answer I'll mark it. Or maybe I should juste delete my question – titus Apr 10 '17 at 16:18

2 Answers2

2

I completely agree with the comment from @Rory - I'd never use this code in my own projects, but I wanted to see anyway....

If in a normal module you create this function: 

Public Function RGB_print(rlev As Integer, glev As Integer, blev As Integer)
    Application.Volatile
End Function

Then in your sheet add this code: 

Private Sub Worksheet_Calculate()
    Dim rFormula As Range
    Dim vForm As Variant
    Dim sArguments As String
    Dim sFormula As String
    Dim rgblev As Variant

    Set rFormula = Sheet1.Cells.SpecialCells(xlCellTypeFormulas)
    For Each vForm In rFormula
        If InStr(vForm.FormulaLocal, "RGB_print") <> 0 Then
            sFormula = vForm.FormulaLocal
            sArguments = Mid(sFormula, InStr(sFormula, "(") + 1, InStr(sFormula, ")") - InStr(sFormula, "(") - 1)
            rgblev = Split(sArguments, ",")
            vForm.Interior.Color = RGB(Evaluate(rgblev(0)), Evaluate(rgblev(1)), Evaluate(rgblev(2)))
        End If
    Next vForm
End Sub

This worked for formula such as:
=RGB_print(255,0,255) and =RGB_print(A5,B5,C5)

But again, find another way - this code has so many pitfalls I'll probably lose 100 reputation just for posting it.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Ok, thanks I was looking for a way to map RGB to color display without having to press a button. So if you think of another way I'm all ears – titus Apr 10 '17 at 15:47
  • 1
    Conditional formatting? Not sure what you mean by 'map RGB to colour display'. – Darren Bartrup-Cook Apr 10 '17 at 15:50
  • Yes, I mean conditional formatting the actual formula I'm using is "=RGB_print(E9;F9;G9)" and the calling cell is I9 – titus Apr 10 '17 at 15:54
  • @DarrenBartrup-Cook - Nice answer. What is the vForm variant actually picking up please? (For Each vForm In rFormula) – Absinthe Apr 10 '17 at 17:28
  • `rFormula` returns a reference to all cells on the sheet containing formula. `vForm` is each cell held in `rFormula`. Maybe vForm isn't the best naming convention - makes it sound like a user form. The code will no doubt slow down the more formula you have on the sheet. – Darren Bartrup-Cook Apr 10 '17 at 18:44
0

Ok, as an alternative to Darrent's very precise reply, I'm reposting Tim Williwam's comment : whether one may/should mix functions and macros is an important question and it has been discussed here. Bottom line is : you can but don't do it unless you know what you are doing and are prepared to face the consequences.

titus
  • 452
  • 4
  • 17