Macro to Identify Hardcoded numbers in formulas ....

[actjfc]

Hi dear Excel gurus!

I would like somebody to help me to develop a macro (or do it for me ) that makes a list of all cell addresses that contain hardcoded numbers that are used within an entire workbook. An example will be:


Cell B28 = A1*3*C14 => Hardcoded number is = 3

Cell B30 = (1+.08)^B15 => Hardcoded number is = 0.08


Cell B40 = (1+B3)^2 => Hardcoded number is = 3

An so on…


The macro output must list in sheet "n" (n>3)

Sheet 1 - B28 Text= A1*3*C14
Sheet 2 – B30 Text = (1+.08)^B15
Sheet 3 – B40 Text = (1+B3)^2

It would be ideal if it actually shows the cell address and the text formula with the hardcoded number in it.

Thanks!

actjfc

 

[pgc01]

Hi actjfc

Before running the macro you have to create a worksheet in your workbook named Summary.

The code
- erases all data in worksheet Summary
- creates a new list with all cells within the entire workbook that have hardcoded numbers.

Please test it and post
- cells that should be recognised but weren't
- cells that weren't recognised and should have been

Hope this helps
PGC

Option Explicit
Const sNumber As String = "(^|[=,;\+\-\/\*\^\(])(\d+(?:\.\d+(?:E[\-\+]?\d+)?)?)($|[%=,;\+\-\/\*\^\)])" ' Number
Const sDoubleSingleQuotedString As String = "(\""[^\""]*\"")|('[^'\""]*')" ' Double- and single quoted strings

Sub FormulaHasNumber()
Dim ws As Worksheet, rFormula As Range
Dim RegExQS As Object, RegExN As Object

Set RegExQS = CreateObject("VBSCRIPT.REGEXP")
Set RegExN = CreateObject("VBSCRIPT.REGEXP")
RegExQS.Pattern = sDoubleSingleQuotedString
RegExN.Pattern = sNumber
RegExQS.Global = True
Worksheets("Summary").UsedRange.ClearContents
Worksheets("Summary").Range("A1") = "Cells with numbers hardcoded"

On Error Resume Next
For Each ws In Worksheets
    For Each rFormula In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        If RegExN.test(RegExQS.Replace(rFormula.Formula, "")) Then _
            Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1) = rFormula.Worksheet.Name & _
                    " - " & rFormula.Address & "  Text: " & rFormula.Formula
    Next
Next
End Sub

 

[Richard Schollar]

Hey PGC

That's rather cool. I can definitely make use of it.

Thanks

Richard

 

[pgc01]

Thank you Richard.

If you find cases I overlooked, or mistakes, please let me know.

I just found one.
In the tests I made I had strings starting or ending with a number, hence the start and end line codes in the expression.
However we know that excel always starts the formula with an equal sign and so the start of line code as left delimeter is not necessary.
Since this does not cause any error and adds to the simmetry of the expression I will not erase now. I will wait a bit until I have other modifications to make.

Cheers
PGC

 

[actjfc]

Test Results

pgco1, thanks a lot for stepping in!

I tested the macro and these are my findings:

1) The list of cell addresses also must show any cells containing only a number, regardless how the cell is defined. For example the address of a cell defined as =20 or just 20 (without the equal sign) should be in the list. The list should also show any cell containing =0 or just 0. This "identification" feature of the macro should be optional, I mean it could be turned off, if needed.

2) If I have a cell formula defined as =B4+B5, the list of addresses should NOT show this cell.

I think you really understood what I need . However, I believe the current macro as it is defined is actually listing all cells, but those indentified in point "1)".

Thanks for your help!

PS: The underlined section was added when the post was edited. Thanks

 

[pgc01]

Hi actjfc

Woops! You are right.

Let me explain: I divided this problem in 2:

1 - List all the cells with a constant number

2 - List all cells with a formula that contains a hardcoded number.

For the first point I have to do nothing, since vba already gives that with the method SpecialCells.

The second point is the real problem. That's where I concentrated my efforts.

At the end, I forgot to include the first point.

To make it easier to test, I list the formulas that contain hardcoded numbers in column A and constants in column B. Later on we can choose a different layout.

Please test again and let me know the result.

Kind regards
PGC

Option Explicit
Const sNumber As String = "([=,;{\+\-\/\*\^\(])(\d+(?:\.\d+(?:E[\-\+]?\d+)?)?)($|[%=,;}\+\-\/\*\^\)])" ' Number
Const sDoubleSingleQuotedString As String = "(\""[^\""]*\"")|('[^'\""]*')" ' Double- and single quoted strings

Sub FormulaHasNumber()
Dim ws As Worksheet, rFormula As Range, rConst As Range
Dim RegExQS As Object, RegExN As Object

Set RegExQS = CreateObject("VBSCRIPT.REGEXP")
Set RegExN = CreateObject("VBSCRIPT.REGEXP")
RegExQS.Pattern = sDoubleSingleQuotedString
RegExN.Pattern = sNumber
RegExQS.Global = True
Worksheets("Summary").UsedRange.ClearContents
Worksheets("Summary").Range("A1") = "Cells with formulas with numbers hardcoded"
Worksheets("Summary").Range("B1") = "Cells with number constants"

On Error Resume Next
For Each ws In Worksheets
    For Each rFormula In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        If RegExN.test(RegExQS.Replace(rFormula.Formula, "")) Then _
            Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1) = rFormula.Worksheet.Name & _
                    " - " & rFormula.Address & "  Text: " & rFormula.Formula
    Next
    For Each rConst In ws.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)
        Worksheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1) = rConst.Worksheet.Name & _
                " - " & rConst.Address & "  Text: " & rConst.Formula
    Next
Next
End Sub

 

[Richard Schollar]

Hi PGC

Very minor suggestion, but it might be nice to include in the worksheet the actual hardcoded number (split from the formula for easier review) that is included within the formula since a hardcoded number of 1,000 say will often (but by no means always) be used to represent millions as thousands, and so the user may well not be so worried about these as they are about an addition of say 3801.

Now, you can assist me here with my understanding of regular expressions: I assume the above mentioned adjustment would be reasonably easy since you are already using back references?

I have a few thoughts on how I'd like to implement your checking system on a spreadsheet, and will be investigating this aspect as soon as I can.

It really does have the potential to be incredibly useful in my workplace.

Richard

 

[pgc01]

Hi Richard

You are right. With some adjustments we can extract the hardcoded number, or, in fact, the list of hardcoded numbers, since a formula may have several.

I will look into it and try to post a version still today.

Best regards
PGC

 

[pgc01]

Hi Richard

I now extract the hardcoded numbers from the formulas

The Regex for the number is

Left_Delimeter Number Right_Delimeter

In cases like "=3+4" the right delimeter of a number may be the left delimeter of the next, that's why I cannot capture the right delimeters, I just do a lookahead. I tried to write all possible left and right delimeters but I may have missed some. The number can be just an integer (\d+), can have or not decimal part (\.\d+) ? and can be or not in scientific notation (E[\-\+]?\d+)?.

Before I scan for the number I delete all double- and singlequotedstrings, so that numbers inside them are not extracted.

I changed the layout. Now I write the information in 4 columns, A - Worksheet name, B - Cell address, C - Formula if there is one, D - list of hardcoded numbers. I think this will make it easier to manipulate the list.

Please test it and let me know about your impressions and about errors you find.

Cheers
PGC

Const sNumber As String = "(?:[=,;{\+\-\/\*\^\(])(\d+(?:\.\d+)?(?:E[\-\+]?\d+)?)(?=$|[%=,;}\+\-\/\*\^\)])" ' Number
Const sDoubleSingleQuotedString As String = "(\""[^\""]*\"")|('[^'\""]*')" ' Double- and single quoted strings

Sub FormulaHasNumber()
Dim ws As Worksheet, rFormula As Range, rFormulas As Range
Dim rConstant As Range, rConstants As Range, rResult As Range
Dim RegExQS As Object, RegExN As Object, oMatches As Object, iMatch As Integer

Set RegExQS = CreateObject("VBSCRIPT.REGEXP")
RegExQS.Pattern = sDoubleSingleQuotedString
RegExQS.Global = True
Set RegExN = CreateObject("VBSCRIPT.REGEXP")
RegExN.Pattern = sNumber
RegExN.Global = True

Worksheets("Summary").UsedRange.ClearContents
Worksheets("Summary").Range("A1:D1") = Array("Worksheet", "Address", "Formula", "Hardcoded Numbers")

On Error Resume Next
For Each ws In Worksheets
    Set rFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
    If Err Then
        Err.Clear
    Else
        For Each rFormula In rFormulas
            Set oMatches = RegExN.Execute(RegExQS.Replace(rFormula.Formula, ""))
            If oMatches.Count > 0 Then
                Set rResult = Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
                rResult = rFormula.Worksheet.Name
                rResult.Offset(, 1) = rFormula.Address
                rResult.Offset(, 2) = "'" & rFormula.Formula
                rResult.Offset(, 3) = "'" & oMatches(0).submatches(0)
                For iMatch = 1 To oMatches.Count - 1
                    rResult.Offset(, 3) = rResult.Offset(, 3) & ", " & oMatches(iMatch).submatches(0)
                Next
            End If
        Next
    End If
    Set rConstants = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)
    If Err Then
        Err.Clear
    Else
        For Each rConstant In rConstants
            Set rResult = Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
            rResult = rConstant.Worksheet.Name
            rResult.Offset(, 1) = rConstant.Address
            rResult.Offset(, 3) = "'" & rConstant.Value
        Next
    End If
Next
End Sub

 

[actjfc]

Your are getting there!

Thanks pgc01!

The second section or point 2 of your macro does what I need. However, please also add the actual formula to the cell in output list. Also, I would like to exclude from the list any hardcode cell with Time or Date format. When I am revising an excel model I highlight cells that contain a hard coded number or a constant by changing the cell to bold blue font color. Then, I also would like to exclude of the list any cells that have been already identify by the user as hard coded by changing its color font to bold blue.

I would like the macro to create the output sheet by itself. It could use an odd name such as “ZZZ”, most of my models already have a summary page, but I will never use a sheet named ZZZ.

A bit of history about the need of this macro:
I develop some financial models in excel as a part of my daily work, and I include a huge amount of formulas in them. My models only have formulas linked to each other and to blue hard coded cells that are the model input to all formulas within the workbook model. However, some of the users that receive the models modified the formulas or add new formulas and include hard coded numbers inside these formulas. Other times, I receive a model developed by others that have a bunch of hard coded numbers in them that should be hard coded bold blue variables within the model and outside the actual formulas, not “hidden” as I receive them. This macro that you are developing will save me a lot of time in my search for improving the models. I believe it has the potential to become an icon to some excel users. So, I bow to your Excel knowledge. THANKS! This compliment goes to both, pcg01 and Richard. Assign a cute name to your macro and be very pleased with your contribution to the forum, and the excel world. I think the issue solved by your macro is something that happen everyday in the life of thousands of Excel warriors out there.

I believe the first section of the macro (point 1) list almost all cells, since for example =B4+B5 have a number in them, then is listed, but for my models this cell only contains links to other cells, so I do not need them in the list output.

Thanks again!

actjfc

PS: Richard, I also like your approach, but I could not make your macro to run properly. I believe the macro output include all or most of the cells in the workbook.