Replace Defined Names In Formulas With Actual Cell References

Ease of Use


Version tested with

2000, 2002 

Submitted by:



Replaces any worksheet cells that are using named ranges in a formula to the actual ranges they refer to. 


Some people prefer to use lots of named ranges in their formulas because they feel it is clearer and easier to read, and understand. Some people take a different point of view and feel that this method is unclear and would rather see the actual range references used. This code will change any formulas using named ranges with the actual range references. NOTE: THIS CODE WILL PRODUCE INACCURATE RESULTS WHEN USING THE SAME STRING AND A STRING NOT REFERING TO A RANGE IN A FORMULA. PLEASE SAVE A BACKUP OF YOUR WORK BEFORE USING ON YOUR OWN PROJECTS. 


instructions for use


Option Explicit Public Sub ReplaceNamesWithRefs() Dim rng As Excel.Range Dim wks As Excel.Worksheet Dim nme As Name Dim nCt As Integer Dim szWkbName As String Dim szSwitchString As String nCt = 1 With Application .EnableEvents = False On Error Resume Next 'Loop through all names in the workbook For Each nme In ThisWorkbook.Names 'Store each name object in a variable szWkbName = ThisWorkbook.Names(nCt).Name .StatusBar = "Checking Defined Name " & CStr(szWkbName) 'Store each names RefersTo value in a variable that trims the (=)&($) szSwitchString = Replace$(ThisWorkbook.Names(nCt).RefersTo, "=", "") szSwitchString = Replace$(szSwitchString, "$", "") '=================================================================== 'Loop through the sheets collection, replacing the names with 'the actual range references For Each wks In ThisWorkbook.Worksheets With wks.Cells.SpecialCells(xlCellTypeFormulas) .Replace$ szWkbName, szSwitchString, xlPart, , True .Replace$ wks.Name & "!", "", xlPart, , True End With Next wks '=================================================================== nCt = nCt + 1 Next nme 'This code portion was created by Jim Rech For Each wks In ThisWorkbook.Worksheets With wks .TransitionFormEntry = True For Each rng In .Cells.SpecialCells(xlFormulas) rng.Formula = rng.Formula Next rng .TransitionFormEntry = False End With Next wks .EnableEvents = True .StatusBar = False End With End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes

Test the code:

  2. When the dialog appears, select (ReplaceNamesWithRefs)
  3. Press Run

Sample File:

ReplaceNamesWithReferences.zip 9.7KB 

Approved by mdmackillop

This entry has been viewed 266 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express