EM,

Try this version of the function, below.

HTH,
Bernie
MS Excel MVP

Function FormulaHasConstant(inCell As Range) As Boolean
Dim strForm As String
Dim Parts As Variant
Dim i As Integer
Dim myDbl As Double
Const Operators As String = "=+-*/^()"

FormulaHasConstant = False

strForm = inCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Parts = Split(strForm, "*")
For i = LBound(Parts) To UBound(Parts)
On Error GoTo NotNumber
myDbl = CDbl(Parts(i))
FormulaHasConstant = True
Exit Function
NotNumber:
Resume GoOn
GoOn:
Next i

End Function



"ExcelMonkey" wrote in message
...
I am using a function which searches formulas for hard coded contants
(+1,-6^8,*9,/7). There are two issues with this:

1) It does not find constants at the beginning of a fomula (i.e. can't find
=1+SUM(A1:A10) but will find =SUM(A1:A10) +1

2) Can be very slow on spreadsheets with large number of cells with large
complex formulas.

Can anyone recommend a more robust version which is faster.

Thanks

EM






Private Function FormulaHasConstant(rn As Range) As Boolean
Dim FormulaString As String
Dim Operators As String
Dim FormulaCharacter As String
Dim i As Integer, j As Integer
Dim Constraints As String
Dim FoundOperator As String
Dim FoundNumber As Integer
Dim FoundCombination As String

Operators = "=" & "/" & "+" & "-" & "*" & "^"

FormulaHasConstant = False

'If cell is not a formula then check to see that
'it is not a paste specialed number
If rn.HasFormula = False Then
'If not formula but empty, exit
If Not IsEmpty(rn) Then
If IsNumeric(rn) Then
FormulaHasConstant = True
Else
FormulaHasConstant = False
End If
Else
Exit Function
End If
Exit Function
End If

'Pass the cell formula to a string variable

FormulaString = rn.Formula

'Searh each character in string
For i = 1 To Len(FormulaString)
FormulaCharacter = Mid(FormulaString, i, 1)
'Search for operators in string
If InStr(1, Operators, FormulaCharacter) 0 Then
'Add "Or" Condition
If IsNumeric(Mid(FormulaString, i + 1, 1)) Then
FormulaHasConstant = True
FoundOperator = FormulaCharacter
FoundNumber = Mid(FormulaString, i + 1, 1)
'Pass operator and number to variable so that you know
'what has been found
FoundCombination = FoundOperator & FoundNumber
Exit Function
End If
End If
Next i
End Function