1004 Error Has Me Stumped

[Micron]

err.Description: Application-defined or object-defined error
For code context review last post in this thread (it's virtually the same as what I have now)

What I don't understand is no matter what syntax I use to set a cell interior colour it raises that error. BUT if I step through the code and in the immediate window (iw) I use that exact line, I can set the interior colour to whatever value I choose. That works regardless of whether or not I comment out the line that raises the error. The easiest approach for verifying this is to put a break point on Exit Function and use the iw to issue the command before I lose the variables. When the code is finished, the interior colour is whatever value I used in the iw but only if I use the iw. When the code errors, it does not set the interior colour.

Why can I set the value in the iw but not in code when using the exact same code line?
I have tried Do Events, cycling EnableEvents and passing the range address and colour value to an outside function yet the error persists. I am stumped. I could trap 1004 and ignore it but not only does that seem to be a hack, then the colour still cannot be set. Can anyone explain this behaviour?

 

[Fluff]

You cannot change the fill colour of a cell, with a function called from the worksheet.

 

[Micron]

Thanks for the reply. The function call was just another attempt to eliminate the problem; anything I tried in the main code didn't work either. Or were you not referring to the udf that was being called? There certainly is a lot of posted code examples that suggest this should be doable in the main procedure.

 

[Micron]

OK, I think I got what you're saying. You're referring to the function call being made from the formula bar? That means even that function calling another function or sub won't allow it either? If that's the case, I'd have to think of a sheet event if it were that important to affect the interior colour.

 

[Fluff]

A UDF has pretty much the same limitations as a normal formula, although there are some workarounds if you use evaluate.

 

[Jaafar Tribak]

If you have only a couple of cells with the UDF, you could use the following hack. I have used it before w/o problems.

For more than a couple of cells with the UDF , this hack is not viable as it can make the application unstable.

Example:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
#Else
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
#End If

Dim oCell As Range

'UDF
Public Function AddOne(ByVal X As Single) As Single
    Set oCell = Application.Caller
    Call SetTimer(Application.hwnd, 0&, 0&, AddressOf ChangeCellColor)
    AddOne = X + 1
End Function
 
Private Sub ChangeCellColor()
    Call KillTimer(Application.hwnd, 0&)
    If oCell.Value Mod 2& Then
        oCell.Interior.Color = vbRed
    Else
        oCell.Interior.Color = vbGreen
    End If
End Sub