Searching for Hard Coded Constants

[ExcelMonkey]

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

 

[Bernie Deitrick]

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

 

[keepITcool]

Hi EM,
just saw that while i was working Bernie posted a similar solution.

IMPORTANT!
I suppose you run your function against all cells
in a worksheet.

I'd run it against the result of
Range(x).SpecialCells(xlCellTypeFormulas)
(note SpeciallCells returns a multiarea range!)

To find the "pasted numbers" use
SpecialCells(xlCellTypeConstants,xlNumbers)

Further:

I'm not sure that making it more robust improves speed :
and I've not thoroughly tested (and still slow on large quantities)

Function FormulaHasConstant(rngCell As Range) As Boolean
Dim sFml$, aFml$(), i&, bQuoted As Boolean

'If cell is not a formula then check to see that
'it is not a paste specialed number
With rngCell
If Len(.Formula) = 0 Then
'empty cell
ElseIf Not .HasFormula Then
'no formula
If IsError(.Value) Then
'it's an error constant
Stop
ElseIf Not IsNumeric(.Value) Then
'it's a string string constant
Else
'------------------------
'it's a numeric constant!
FormulaHasConstant = True
End If
Else
'Pass the cell formula to a string variable
'ignore the starting "="
sFml = Mid$(.Formula, 2)

'Replace operators with separator, but skip quoted strings
For i = 1 To Len(sFml)
Select Case Mid$(sFml, i, 1)
Case """"
bQuoted = Not bQuoted
Case "=", "+", "-", "/", "*", "^", "&", "{", "}"
If Not bQuoted Then
sFml = Left$(sFml, i - 1) & "," & Mid$(sFml, i + 1)
End If
End Select
Next
'Split the string into components
aFml = Split(sFml, ",")
'Check each component to see if it's numeric
For i = 0 To UBound(aFml)
If IsNumeric(aFml(i)) Then
FormulaHasConstant = True
Exit Function
End If
Next

End If
End With

End Function


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


ExcelMonkey wrote :

 

[ExcelMonkey]

Thanks both. Will take a look. I am having a general speed issue with my
routine. It gets slower as it evaluates more cells. I may have to do with
the overall stucture of my code. I posted a question called "Routine running
slow (memory leak?)" yesterday/today. Have not really got to be bottom of
why. Was assuming it may have something to do with Set statemtents.

You may be able to help there too. See my second post as the first one was
confusing.

Thanks
EM