HasFormula() vs. has a "numeric" entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mark kubicki - 25 May 2006 23:04 GMT

How can I distinguish if a cell has formula entry (ex: =sum(A1:A25) ) vs. a
simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString, 1) = "="

thanks in advance,
-mark

Reply to this Message

Chip Pearson - 25 May 2006 23:28 GMT

Try

Dim F As String
Dim S As String

F = ActiveCell.Formula
S = Application.ConvertFormula(F, xlA1, xlR1C1)
If F = S Then
Debug.Print "No cell references"
Else
Debug.Print "cell references"
End If

> How can I distinguish if a cell has formula entry (ex:
> =sum(A1:A25) ) vs. a simple "numeric entry (ex: 123)?
[quoted text clipped - 4 lines]
> thanks in advance,
> -mark

Reply to this Message

witek - 26 May 2006 00:08 GMT

what about formulas which use named ranges.
like
=MyRange +1
?

> Try
>
[quoted text clipped - 17 lines]
>>thanks in advance,
>>-mark

Reply to this Message

Dana DeLouis - 26 May 2006 00:23 GMT

> what about formulas which use named ranges.

Maybe one could include something like this:

Dim nPrecedents As Long

On Error Resume Next 'In case there are none
nPrecedents = ActiveCell.Precedents.Count
If n > 0 Then
' There are cell references...
End If

Signature

HTH. :>)
Dana DeLouis
Windows XP, Office 2003

> what about formulas which use named ranges.
> like
[quoted text clipped - 21 lines]
>>>thanks in advance,
>>>-mark

Reply to this Message

Joerg - 26 May 2006 04:29 GMT

> > what about formulas which use named ranges.
>
[quoted text clipped - 9 lines]
>HTH. :>)
>Dana DeLouis

Maybe I missed something, but why not::

if ActiveCell.HasFormula then
'has formula with or without cell references (example for 'without
reference': =10+5)
else
'no formula
end if

Joerg

Reply to this Message

keepITcool - 26 May 2006 12:24 GMT

Dana,

precedents EXCLUDE references to cells other than activesheet.

[sheet1!a1].formula = "=sheet2!a1"
msgbox [sheet1!a1].precedents.count '<No CellsFound>

Testing .Formula <> .Value appears similar to .HasFormula
surprisingly it is 3x faster.

Signature

keepITcool

| www.XLsupport.com | keepITcool chello nl | amsterdam

> > what about formulas which use named ranges.
>
[quoted text clipped - 7 lines]
> ' There are cell references...
> End If

Reply to this Message

Chip Pearson - 26 May 2006 15:08 GMT

Very good point.

> what about formulas which use named ranges.
> like
[quoted text clipped - 22 lines]
>>>thanks in advance,
>>>-mark

Reply to this Message