Sometimes you need to find formulas containing literals (hard coded values) in a workbook.
I found this excellent UDF in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach’s website.
Function CellUsesLiteralValue(Cell As Range) As Boolean If Not Cell.HasFormula Then CellUsesLiteralValue = False Else CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*" End If End Function
---How can I locate cells containing formulas with literal values?---
Use the UDF as your conditional formatting formula.
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.
by John Walkenbach and John Green
It is a small efficient UDF
The UDF with conditional formatting highlights cells containing formulas with literals, column C. You can download an excel file at the end of this post, if you are interested in this technique.
I have bolded all literal values in the formulas, column E. It seems to work with nested formulas also. Boolean values can be expressed as TRUE/FALSE but also 1/0 in excel. Remember, some formulas are built to have hard coded arguments, like col_index_num in VLOOKUP function.
I have a large workbook and I am in a hurry!
The authors want you to use it as conditional formatting formula to spot cells containing formulas with literal values, that can be tedious work with a large workbook. I would like to show you how to use the udf to build a list of all cells containing literal values, from an entire workbook.
I have reused some parts of Rick Rothstein´s macro.
Sub FindLiteralsInWorkbook() Dim C As Range, A As Range, Addresses As String, i As Single Dim cell As Range Sheets.Add Set x = ActiveSheet x.Range("A1") = "Link" x.Range("B1") = "Formula" i = 1 For Each sh In ActiveWorkbook.Worksheets On Error Resume Next Set C = sh.Cells.SpecialCells(xlConstants) If C Is Nothing Then Set C = sh.Cells.SpecialCells(xlFormulas) Else Set C = Union(C, sh.Cells.SpecialCells(xlFormulas)) End If For Each A In C.Areas For Each cell In A If CellUsesLiteralValue(cell) = True Then i = i + 1 x.Hyperlinks.Add Anchor:=x.Range("A" & i), _ Address:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _ SubAddress:=sh.Name & "!" & cell.Address, _ TextToDisplay:=sh.Name & "!" & cell.Address x.Range("B" & i) = "'" & cell.Formula End If Next cell Next A On Error GoTo 0 Next sh x.Columns("A:E").AutoFit End Sub
How to use this macro
- Copy UDF CellUsesLiteralValue and macro FindLiteralsInWorkbook
- Go to VB Editor (Alt + F11)
- Go to the menu and click Insert
- Click Module
- Paste UDF and macro to your workbook´s code module
- Return to excel
- Go to tab Developer on the ribbon
- Click "Macros" button
- Click "FindLiteralsInWorkbook" and then click "Run"