How to Force a Formula to Calculate

[Cyberdude]

I wrote the following function in VBA:

Sub ISFORMULA_Test()  ‘<-Macro to test “ISFORMULA” function below
Dim CellAddr$
CellAddr = "T25"
MsgBox "Value of ISFORMULA = """ & ISFORMULA(CellAddr) & """" & vbCr & _
"Cell contents = """ & Range(CellAddr).Formula & """"
End Sub
    
Function ISFORMULA(CellAddr$)
ISFORMULA = -(Left(Range(CellAddr).Formula, 1) = "=") + 0
End Function

My problem is that for the value of CellAddr I want to use a cell’s defined name. Instead of writing CellAddr = “T25” I want to write CellAddr = “BrkEven”. Using the test macro shown above, the function works. It returns “1” if a formula is in the cell and it returns “0” if a constant is in the cell. It does not work when it’s on a worksheet. No error occurs … it just doesn’t do anything. On the sheet, I write the formula = ISFORMULA(“BrkEven”). If a formula is present in the cell BrkEven, then ISFORMULA returns a “1”. Next I enter a constant into cell BrkEven, and nothing happens in the ISFORMULA formula … the value stays “1”.

I don’t know why this works, but if I then do the following:
1. Click on the cell containing the ISFORMULA formula, which makes the formula = ISFORMULA(“BrkEven”) appear in the formula bar, then
2. Click to the left of the equal sign in the formula bar, which makes a red “X” and a green check mark appear on the formula bar to the left of the formula, then
3. Click on the green check mark, this causes the formula = ISFORMULA(“BrkEven”) to execute, and it produces the value “0” as it should.

I have no idea why this works, but it does. “Ah”, you say, “you should press F9 to force a calculation to occur.” Well you would think so, but F9 doesn’t do anything, i.e., it does not do what clicking the green check mark does. I’ve tried using Volatile = True and Volatile = False, and neither have any effect. What’s going on here??

[Bob Phillips]

Try This

Function ISFORMULA(CellAddr As Range)
    ISFORMULA = -(Left(CellAddr.Formula, 1) = "=") + 0
End Function

and use like this

=ISFORMULA(BrkEven)

[Cyberdude]

Hey, Bob, nice save. Your suggestion works great! Thanx for the help.

Sid