Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Macro to Identify Hardcoded numbers in formulas ....

This is a discussion on Macro to Identify Hardcoded numbers in formulas .... within the Excel Questions forums, part of the Question Forums category; Hi dear Excel gurus! I would like somebody to help me to develop a macro (or do it for me ...

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Location
    Miami, FL
    Posts
    292

    Default Macro to Identify Hardcoded numbers in formulas ....

    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

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,046

    Default

    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

    Code:
    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

  3. #3
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default

    Hey PGC

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

    Thanks

    Richard
    Richard Schollar

    Using xl2013

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,046

    Default

    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

  5. #5
    Board Regular
    Join Date
    Jun 2003
    Location
    Miami, FL
    Posts
    292

    Default 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

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,046

    Default

    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

    Code:
    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

  7. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default

    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
    Richard Schollar

    Using xl2013

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,046

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,046

    Default

    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


    Code:
    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

  10. #10
    Board Regular
    Join Date
    Jun 2003
    Location
    Miami, FL
    Posts
    292

    Default 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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com