Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for Hard Coded Constants
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box / Hard Coded List | Excel Discussion (Misc queries) | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
hard coded text | Excel Programming | |||
vba(SOX): ftp connection & hard coded password | Excel Programming | |||
Find Hard Coded Cells | Excel Programming |