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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for Hard Coded Constants
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for Hard Coded Constants
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 : 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for Hard Coded Constants
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 "keepITcool" wrote: 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 : 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 |
Reply |
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 |