Microsoft Excel: Writing a function to pass through a value and ALSO change the background color of a cell fails

Question

Public Function Ecell(value As Double) As Double
    Ecell = value
    'Call xtest
End Function
Public Sub xtest()
        Range("A1").Interior.ColorIndex = 11
End Sub

If I use the function Ecell in a cell and give it a value - with the call to xtest commented out - it works fine. The value is just moved into the cell.

If I call the subroutine xtest on its own, it works fine. The color in cell A1 is correctly set.

If I uncomment the call to xtest in the function and use the function I get a VALUE error "A value used in the formula is of the wrong data type".

Any idea why? Anyone know a workaround?

I tried many combinations of function call and setting the color in the cell. These two routines indicate the problem in the simplest way.

Answer 1

I don't know where this is going, but you can try using the Calculate event. When you call your function, you set the value of the public variable, and the procedure is executed by the Calculate event.
Put this code in a standard module:

Public flag As Long
Public Function Ecell(value As Double) As Double
    Ecell = value
    flag = 1
    ' Call xtest
End Function
Public Sub xtest()
    Range("A1").Interior.ColorIndex = 11
End Sub

Public Function Fcell(value As Double) As Double
    Fcell = value
    flag = 0
    ' Call ztest
End Function
Public Sub ztest()
    Range("A1").Interior.ColorIndex = xlColorIndexNone
End Sub

And in the sheet module:

Private Sub Worksheet_Calculate()
   Select Case flag
      Case 1: Call xtest
      Case 0: Call ztest
   End Select
End Sub