How to check for hard-coded values in Excel formulas?
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?
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:
- Read the formula from left to right
- For each argument of the formula
- See if the argument is a valid reference or name
- If not, break the loop and return TRUE
- 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?)
Then 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,
- Select the range
- Go to home > conditional formatting > new rule
- Select formula type rule
- Type =hasConstants(top-left-cell relative reference)
- Format by filling a color or changing font style to detect easily
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 code. After 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:
- Spreadsheet risk management – 4 part series
- Show all names & references
- Go to special, a powerful way to navigate your workbooks
Leave a Reply
|8 Reasons you must get better at Excel in 2015||How can I help you become awesome this year?|