Testing for formula or value (constant)

[JH]

Can someone please show me how to test whether the cell is a formula or a
constant in VBA?

say in A1, if I entered a formula, '=D4*E1', msgbox will pop up and say it
is formula, and if I entered a value '1000', it pops up and say it is a value.


Thanks!

 

[Rick Rothstein]

Try this structure...

If Range("A1").HasFormula Then
MsgBox "A1 has a formula"
ElseIf Range("A1").Value <> "" Then
MsgBox "A1 has a constant value in it"
Else
MsgBox "A1 is empty"
End If

 

[JH]

Thanks, it works, learnt something new today.

try this
Function ISFORMULA(cel As Range) As Boolean
ISFORMULA = cel.HasFormula
End Function
Sub testforformula()
If ISFORMULA(Range("A1")) Then
MsgBox "Has formula"
Else
MsgBox "Has no formula"
End If
End Sub