Function to check to see if cell has hard coded data in it

[ExcelMonkey]

I want to write a function that will check to see if a cell has har
coded data in it. For example in the formula below, you would want t
check and see that all the operators (=,+,-,*,/) are followed by eithe
a $ or a letter. So below the 2 would be picked up because it follows
sign.

=$C$10/E10+G10+2/C10*E10

I want to do this so that I can run a check on a spreadsheet to mak
sure their are no hidden easter eggs in it.

Thanks

 

[Ron Rosenfeld]

I want to write a function that will check to see if a cell has hard
coded data in it. For example in the formula below, you would want to
check and see that all the operators (=,+,-,*,/) are followed by either
a $ or a letter. So below the 2 would be picked up because it follows +
sign.

=$C$10/E10+G10+2/C10*E10

I haven't tested it but, if you are only concerned about numeric constants,
perhaps something like:

===============
Function FormulaHasConstant(rg As Range) As Boolean
Dim FormulaString As String
Dim Operators As String
Dim FormulaCharacter As String
Dim i As Integer, j As Integer

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

FormulaHasConstant = False

If rg.HasFormula = False Then Exit Function

FormulaString = rg.Formula

For i = 1 To Len(FormulaString)
FormulaCharacter = Mid(FormulaString, i, 1)
If InStr(1, Operators, FormulaCharacter) > 0 Then
If IsNumeric(Mid(FormulaString, i + 1, 1)) Then
FormulaHasConstant = True
Exit Function
End If
End If
Next i

End Function
====================

If you are also concerned about string constants, or array constants, you could
also test to see if the following character (at i+1) is a quote mark, or a
brace.


--ron