Way to spot constants in cell formulas?

[VBAjedi]

I'm writing a quick 'n dirty workbook analyzer that flags potential design weaknesses (beyond what the built-in Formula Auditing tools provide).

One of the things I want to do is loop through the formulas on a worksheet (I'm just using .SpecialCells(xlCellTypeFormulas)) to look for formulas that contain constants. For example, I'm not interested in formulas like:

=$A$1 + $B$1

but I AM interested in formulas like:

=$A$1 + 10
=Sum(A1:A10) * .25

because of the constants (in bold) they contain.

Any thoughts on how I could spot these? My initial thought was to pass the .Formula string for each cell to a Subroutine which would parse the string into segments based on any combination of adjacent spaces, parentheses or commas, then checking each segment with IsNumeric. But that seems clunky and would probably be painfully slow for the large workbooks I have to analyze (some containing over a million formulas!) - is there a more clever way to spot formulas that contain constant values? And if not, how can I make that parsing routine as efficient and comprehensive as possible?

Thanks!

VBAjedi

 

[SkipVought]

Hi,

Some constans are TEXT literals.

Your parsing must also identify as valid, any FUNCTION, which I suppose would be any value xxx(

I would parse and determine if the parsed value was a reference, ie evaluated to a range. All others would be literals.

Also the FIRST occurrence of a formula in a table, data row 1, ought to be representative of ALL the remaining formulas in that column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAjedi]

Good thoughts, Skip...

I hadn't thought of text literals... fortunately, I may be able to ignore them in this case, because other than Boolean operators, text values don't affect calculated values (right?).

Basically I'm in an environment where people sometimes go into random rows of data and insert fixed values into formulas to get the results to say what they want. HORRIBLE business practice that I'm working to expose by building this workbook analysis tool.




So sadly, I can't count on the first occurrence of a formula being representative of the rest of the table.

I'll work on building a parsing routine and when I've got something I'll post it back here for comment.



VBAjedi

 

[SkipVought]

So sadly, I can't count on the first occurrence of a formula being representative of the rest of the table.

A blight that Excel 2007 TABLES feature eliminates!

Parse the FIRST formula in column. Then just compare the FORMULA value to FORMULA value for the remainder of the column. That will catch the odd balls.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAjedi]

Oh, right, you actually MEANT tables when you said it. We still use Office 2003 here so I don't have access to those.

Here's the function I put together to spot numbers in formulas. I decided to go with my initial thought first and see how it ran:

Function ParseForConstants(FormulaToParse As String)
' Looks for any constant/hard-coded numbers in this formula
Dim x As Integer
Dim CurrChar As String
Dim Segment As String
Dim EvaluateIt As Boolean

For x = 1 To Len(FormulaToParse)
  CurrChar = Mid(FormulaToParse, x, 1)
  Select Case CurrChar
  Case "=", " ", "(", ")", ","
    If Len(Segment) > 0 Then EvaluateIt = True
  Case Else
    Segment = Segment & CurrChar
    If x = Len(FormulaToParse) Then EvaluateIt = True
  End Select
  If EvaluateIt = True Then ' evaluate substring we just finished building
    If IsNumeric(Segment) Then
      ParseForConstants = True
      Exit Function ' no need to continue parsing, finding one constant in the formula is enough!
    End If
    Segment = ""
    EvaluateIt = False
  End If
Next x

End Function

It took about 20 minutes for my underpowered PC to run my whole workbook analysis process (including this block of code) against a workbook containing 75 sheets, 87,000 constant values, and 1,500,000 formulas (!). I actually think that's reasonably fast given the size of the beast being analyzed.

The function does what I want it to do, although it does return some false positives (ex. for formulas containing functions with numeric arguments). Any thoughts on how to optimize it further?

VBAjedi

 

[SkipVought]

ex. for formulas containing functions with numeric arguments

For instance???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAjedi]

Oh, one example would be Round:

=ROUND(($C18*-E16),0)

Running that formula through my parser trips my IsNumeric flag because of that zero in the "number of digits" argument. Although that is technically a fixed number in the formula, it's not really in the spirit of what I'm trying to spot (I'm looking for fixed values inserted into formulas as a short-term adjustment that compromise future results if forgotten). But I think since this is a quick and dirty 90% analysis tool I can live with a few false positives...

VBAjedi

 

[mscallisto]

Hi VBAjedi

Have you tried using Reverse Polish Notation (RPN) as your parsing scheme?

Not sure if it will work in your case but maybe with (minimum modification) it's a methodology I would probable look at.

example:

http://babylon.acad.cai.cam.ac.uk/students/study/engineering/engineer02/cepolish.htm

sam

 

[mscallisto]

I should have added:

At the link I supplied, scroll down to the "railroad track with side track" picture for a simple visual explanation of how RPN works.

I'm thinking that along with "operators" and "functions" etc you could add cell pointers etc.

Anyway your method may be better as is but I thought you might want to explore an alternative method.

sam

 

[firefytr]

Take a look at the Explode add-in by Aaron Blood.

http://www.xl-logic.com/modules.php?name=Downloads&d_op=viewdownload&cid=3

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[xlbo]

I think what you need is a regular expression (regex)

You are looking for adjustments to a formula so a + - x or / followed by any number would indicate a hardcoded adjustment. This would also take care of your ROUND formula false positive...will post back later with an example if I get time



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

I'm not so hot on regular expressions but see if this works for you...

Sub Test()
dim IsAdj as string
Const strForm As String = "cell formula passed in here"
IsAdj = RegExTest(strForm)
If IsAdj <> "" then
msgbox "Formula manually adjusted"
Else
EndIf
End Sub

Function RegExTest(strTest As String) As String
Dim RE As Object, REMatches As Object

Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "[[\+-*/0-9]"
End With

Set REMatches = RE.Execute(strTest)
RegExTest = REMatches(0)

End Function


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

As promised!! This one should actually work

Would be interesting to see whether this takes less time to iterate through the formulae to get your results...should do as you are not needing to loop through the string of each formula

Sub test()
Dim IsAdj As Boolean
Dim strForm As String
    
    strForm = Selection.Formula
    
    IsAdj = RegExTest(strForm)

If IsAdj Then MsgBox "Formula manually adjusted"

End Sub

Function RegExTest(strTest As String) As Boolean
    Dim re As Object, REMatches As Object
    
    RegExTest = True
    
    Set re = CreateObject("vbscript.regexp")
    
    With re
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[+\-*/][0-9]"

    End With
    
    Set REMatches = re.Execute(strTest)
    
    If REMatches.Count = 0 Then RegExTest = False
        
End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question