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