Testing for formula or value (constant)

J

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!
 
R

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
 
J

JH

Thanks, it works, learnt something new today.


Mike said:
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
 

Ask a Question

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.

Ask a Question

Top