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.
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