How to check for hard-coded values in Excel formulas?

Posted on January 14th, 2015 in VBA Macros - 34 comments

Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .

It was as if Homer Simpson created it while Peter Griffin oversaw the project.

So how do you go about detecting all cells containing formulas with hard-coded values?

Finding hardcoded formula values in Excel - how to?

Alas, the usual methods fail

The usual methods to audit formulas are of no help here. Let’s see:

Show formulas (CTRL+`): Since we have way too many formulas, this approach requires a lot of squinting and gallons of coffee.

Go to special > Constants: This will only detect constant cells (ie input cells), but not cells containing formulas like =IF(2=2, Budget2014!A2, Budget2015!A2)

Trace Precedents: This can be used only for formulas that contain all hard-coded values (ex: SUM(1,2,3) will have no arrows, where as SUM(A1,A2, 7) will have some arrows

FORMULATEXT(): There is a new function called as FORMULATEXT() introduced in Excel 2013. This can tell us what is the formula in a cell. But we still need to develop additional logic to see if the formula text contains any constants.

Let’s build ‘Detect hard-coded formulas’ feature for Excel

The beauty of Excel is that, if there is something you can’t do with on screen features, you can build it. This is where VBA comes handy.

So we can create a hasConstants() user defined function that takes a cell as input and tells us TRUE or FALSE. True if the cell has constants (or hard-coded values) as formula parameters and False otherwise.

But what should be the logic for hasConstants()?

The process for detecting hard-coded values can be defined like this:

  1. Read the formula from left to right
  2. For each argument of the formula
    1. See if the argument is a valid reference or name
    2. If not, break the loop and return TRUE
  3. Return FALSE

How do we detect only the parameters?

There is no direct way to extract only the parameters of a formula. So what we do is we split the formula in to an array using the delimiter COMMA.

And we check each item of this array to see if it is

  • a function call (like SUM, COUNT, VLOOKUP)
  • a valid name or reference

What about nested functions?

The approach works the same way.

What about arithmetic, text or comparison operations?

For example, a formula like =A1+A2+17 should throw TRUE as it has hard-coded value.

So what we do is, we replace all such operators with delimiter (COMMA) before splitting the formula text.

We can consider +-*/%&><= as operators.

So how does the code look like?

Here is how it looks like:


Const COMMA = ","
Const OPERATORS = "+-*/%^&><="

Public Function hasConstants(thisCell As Range) As Boolean
    'finds out if thisCell has a formula with constants in it
    'i.e. hardcoded values
    
    Dim formula As String, args As Variant, i As Long
    Dim testRange As Range
    
    formula = replaceOperators(Mid(thisCell.formula, 2))
    
    args = Split(formula, COMMA)
    
    For i = LBound(args) To UBound(args)
        If Not (Len(args(i)) = 0 Or Right(args(i), 1) = "(" Or args(i) = ")") Then
            'not a function or null, must be one of the parameters
            'see if it is a valid name or reference
            If Not nameExists(CStr(args(i))) Then
                'name or reference doesn't exist, must be a constant / hard-coded value
                hasConstants = True
                Exit Function
            End If
       End If
    Next i
End Function

Function replaceOperators(formula As String) As String
    'replace operators such as +-/%^&>< with COMMA
    Dim char As Long
    
    For char = 1 To Len(OPERATORS)
        formula = Replace(formula, Mid(OPERATORS, char, 1), COMMA)
    Next char
    formula = Replace(formula, "(", "(" & COMMA)
    formula = Replace(formula, ")", COMMA & ")")
    replaceOperators = formula
    
End Function

Function nameExists(name As String) As Boolean
    'Check if a name or reference is valid
    Dim testR As Range
    
    On Error GoTo last
    
    Set testR = Range(name)
    nameExists = True
    Set testR = Nothing
last:

End Function

How to use this code?

Simple. Copy this code and add it to your personal macros workbook. (Tip: how to setup personal macros workbook?)

Conditional formatting to check hardcoded formula valuesThen use it in your complex workbook like this:

  • To check if a cell contains hardcoded formulas, write =hasConstants(A1)
  • To check if an entire range has hardcoded values,
    1. Select the range
    2. Go to home > conditional formatting > new rule
    3. Select formula type rule
    4. Type =hasConstants(top-left-cell relative reference)
    5. Format by filling a color or changing font style to detect easily
    6. Done

Does it work in all cases?

For most normal formulas this approach should work. I have tested it with various combinations and it seems to hold up good. I suggest you to double check the results for any type II errors (ie missed hard coded formulas) during initial few rounds.

Also, please share your observations in the comments so that we can improve this code.

Download Example Workbook

Click here to download this VBA codeAfter downloading the file, go to Module 1 (press ALT+F11) to see the code. Copy it or modify it as you see fit.

Your comments please?

I never had the need to check for hard-coded values until recently. But once I had that need, I found there is no simple way to do it. I believe this kind of check can be very useful for people in modeling, risk management or auditing positions.

What about you? How do you check for hard coded formulas? What methods do you use? Please share your thoughts and tips in the comments section.

More on spreadsheet auditing & risk management:

Check out below articles to learn more about how to audit spreadsheets and prevent risk of miscalculation:


Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

34 Responses to “How to check for hard-coded values in Excel formulas?”

  1. Vad says:

    Very good article and makes even better the conditional formatting trick. Thanks for sharing.

    P.S: VBA may needs little alteration for array formulas. Invariably for subtotal function, function number is hard coded. And also for lookup functions, the exact match condition is hard coded.

    • Jeff Weir says:

      Good pickup. Same goes with VLOOKUP - either the Columns argument, or using 1 or 0 instead of True or False.

    • Chandoo says:

      Good catch. Implementing these nuances means adding more code to check individual functions. The macro would become very complicated when considering nested formulas. So I chose the happy medium.

      • Vad says:

        Yeah. It may become lengthy cumbersome to cover all the cases. I just put my thoughts on this great forum.

        On another note, is there any way to check the each substring EVAL with its text. If they both are same, they should be constant. Not sure how it works though 🙂

  2. Jeff Weir says:

    Interesting implementation. I like the way you substitute all operators with a comma.

    Note you can replace this:
    For i = LBound(args) To UBound(args)
    ...with this:
    For i = 0 To UBound(args)
    ...which saves VBA having to evaluate the LBound, as it's always going to be zero (unless the user has set it to 1 in the declarations section).

    I wonder if using the Evaluate method would be a quicker test of whether something is a range or not.

    • Chandoo says:

      Good suggestions Jeff. I have not thought about Evaluate approach. We could also invoke any of the simple application.worksheetfunctions like N(), T(), COUNTA() to check validity of names / references. (some of them only work if the name points to a cell, not to a value)

      You can let us know if the EVALUATE approach is faster. To me the whole thing about setting a variable to range seems a little slow.

  3. Elias says:

    Chandoo,

    None of the Worksheetfuntion you mention will work because they can use constant. However, what about counting the number of Row/Columns of the range.

    Function nameExists(name As String) As Boolean
    'Check if a name or reference is valid
    Dim testR As Long
    On Error GoTo last
    testR = Range(name).Rows.Count
    nameExists = True
    last:
    End Function

    Regards

  4. Gary Lundblad says:

    I apologize for my limited experience with VBA, but I copied the code into my personal workbook, as I've done successfully with other VBA code many times. Then I added the conditional formatting to a range that I knew had formulas with constants in it, but nothing happened. The one part that I may be misunderstanding is step 4 under "How to use this code?" It says "Type =hasConstants(top-left-cell relative reference)." Does that mean I should type "=hasConstants(C1)" if the upper left cell of the range I selected is C1? If so, it isn't working for some reason. I checked my Developer tab and my formulas were not set to use relative reference, but I changed it and it still doesn't work.

    This was something I was just wondering yesterday how to do, so the solution is very timely and valuable to me.

    Thank you!

    Gary

    • Vad says:

      Though I couldn't visualize your situation well, following may be of help.
      1>Check the user defined function is working well. Just use “=hasConstants(C1)” and shall return true if C1 has some constants. If it is giving right answer, then VBA part is fine.
      2>Conditional formatting. Select the cell in which you want to format. As shown in the figure above posted in the article, add conditional formatting with reference to same cell. When you select the cell to give reference in conditional formatting, by default it fixes the reference (like $C$1) which needs to be unfixed. Then use format painter to apply this conditional formatting to all cells.

  5. Jeff Weir says:

    I'd be interested to hear from any readers about whether they routinely have to identify cells with constants. We could include this in an add-in that my business partner and I are working on, but I don't want too many 'niche' functions cluttering up the interface. I don't know if this is a common requirement or not. If it is, please let me know.

  6. Adam says:

    Is there a way to make such a custom function available in the personal workbook? I love this idea, but I'd hate to have to copy and paste the code into each workbook I'm using that I want to use it in.

  7. NickC says:

    Great concept!

    It seems like the conditional formatting trick only works when the UDF is in the workbook (not in Personal macro workbook), but still very handy to have.

    Cheers

  8. Kuldeep says:

    This is a good area if something can be developed. Many time this will be useful when you work on the workbooks you received from the external world.

    Rather opposite to this is also a good idea.

    if the file is using number of references within the sheet or across sheets, one may want to get all those cell (controlling element) at one sheet and control from there. we generally found this problem in workbooks where formula is build with many sheet reference to check. i generally have one dedicated sheet for all variables (reference) used in formula.

  9. Oscar says:

    I found this udf in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach's website: http://spreadsheetpage.com/index.php/site/eee/issue_no_20_july_8_2001/

    by John Walkenbach and John Green

    ---How can I locate cells containing formulas with literal values?---

    Use the following UDF as your conditional formatting formula.

    Function CellUsesLiteralValue(Cell As Range) As Boolean
    If Not Cell.HasFormula Then
    CellUsesLiteralValue = False
    Else
    CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
    End If
    End Function

    It accepts a single cell as an argument. It returns True if the cell's
    formula contains an operator followed by a numerical digit. In other words,
    it identifies cells that have a formula which contains a literal numeric
    value.

    You can test each cell in the range, and highlight it if the function
    returns True.

    • Kyle McGhee says:

      Wow, so simple!
      You could put that in conditional formatting or add a Sub that calls the function to handle ranges with a 'For Each Cell in Selection' loop

    • Chandoo says:

      Amazing find Oscar. It works for all numeric literals flawlessly and is far simpler than my macro. Thanks for sharing it with all of us.

  10. Roan says:

    Chandoo - nice work. As usual, you've lit a fire and shown people how to do it. Now they'll be off using it to do things ranging from lighting a scented candle to stoking massive infernos that can melt metal.

    Oscar - nice find. Sort, succinct and does the job (for the most part).

    Jeff - your add-in sounds like a great idea. Like you say, catering for every exception on every function will get unwieldy, so maybe you can give the user a menu option to add their commonly-used functions to an exceptions list where they choose how each argument is dealt with (eg. check/ignore/NA). If you take VLOOKUP for example, whether you use TRUE/FALSE or 1/0 for the fourth argument, it should not affect the outcome of the UDF, since VLOOKUP can't work properly without it, so the fourth argument would be in the NA category (unless of course you get someone who does use a formula to determine the fourth argument!). For the third argument, many people want to use a constant as the column identifier, so would not want the UDF to highlight this. They would then choose Ignore for the third argument. I'd submit that most people would only ever add a handful of functions to the exceptions list, so it shouldn't slow down performance too much, but it will be of great benefit.

  11. Gary Lundblad says:

    Vad,

    Thank you for your response. That was a good way to check the VBA code, and it seems there is a problem with the code. I copied it exactly as it is above into my Personal.xlsb workbook, under the Modules folder. In that folder I have a file called "MyFavoriteMacros," and I put all of my macros in it. When I did the test you mentioned on a cell that had "=149513.4+27.4" in it I got a "#NAME?" error.

    Gary

  12. David Brom says:

    I've used a non-VBA solution in the past, though not sure if it covers every possibilities, but haven't had it come up wrong yet.

    Assuming Cell A1 is the cell you're checking:
    =IF(AND(CELL("prefix",A1)="",CELL("type",A1)"b"),TRUE,FALSE)

  13. Gary Lundblad says:

    Oscar,

    I copied the code into VBA and it highlighted one of the lines in red. Then I realized you said to use the code as your CF formula, so I created a new rule, and copied the code in as my formula, but nothing. This would be really helpful, but for some reason nothing seems to be working.

  14. Gary Lundblad says:

    Chandoo I don't quite understand your reply. Is something missing, and who is this a reply to?

    Thank you!

  15. Neeraj says:

    Hi Chandoo,

    Thanks for the great code. I was wondering where in your code can we add Oscar's input?

    Thanks,
    Neeraj

  16. Bob says:

    I saved the John Walkenbach CellUsesLiteralValue code as an addin. It works fine when tested directly on a Excel 2010 worksheet. However, when I tried to use it on the same worksheet as a conditional formatting formula

    for example, =CellUsesLiteralValue(C46)

    I got this error message: This type of reference cannot be used in a Conditional Formatting formula. Anyone have thoughts on am I doing wrong?

  17. Bob says:

    Now I copied the code into a module in the workbook I have been testing. Now the conditional formatting works fine - no error message and formulas with literal values are highlighted. Having the code in the active workbook seems to be the only way to make this work - or am I missing something?

  18. jimm says:

    Thanks for the instructions, Chandoo. I'm a bit new to this, but have everything setup properly and it does not seem to be working. Did anyone else have any challenges followed by a break-through?

  19. brian says:

    I also cannot get this to work and am also new to this. I tried saving in personal workbook and using PERSONAL.XLSB! in conditional formatting formula and i was not allowed to use that type of formula. Chandoo were you able to use this functional saved in personal workbook without typing the prefix in the formula? It seems above you alluded to saving in personal but your formula does not use the personal prefix. I am simply trying to highlight an entire column with say 1,000 rows and look into all formulas at once for a hard coded value so that when I copy the column over to the next, I know which cells I (someone) made a manual adjustment to. Any help would be greatly appreciated as this is being done by me looking into each cell at the moment for a constant. thank you.

  20. Jared Powell says:

    Could this formula be modified to not highlight a cell if the only hardcoded number is a 1? would "1" just be added to the Operators?

  21. Bhavik says:

    I was thinking one liner can be used too.

    'True means its a Formula
    'False means not formula
    Function IsFormulaBK(cell As Range) As Boolean
    IsFormulaBK = Not (IIf(cell.formula Like "*[=^/*+-/()><, ]#*[0-9]*", True, False))
    End Function

  22. FreddyK says:

    Thanks for sharing this tip. I tried using this, my only concern is that the function seems to return TRUE for a cell containing a formula that links to another worksheet in the same workbook, even though there is no constant within that formula. Any idea on how to fix this? I'd like to be able to identify constants only.
    Thanks in advance

Leave a Reply