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.

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

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

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.