Apply Range Names to your worksheet

June 9, 2009 by JP ·

If you have a worksheet with lots of cells used in formulas, then proceeded to name those cells, you'll need to apply those names to their dependent formulas to prettify them. Typically I just go to Insert » Name » Apply and do it that way, but I came up with this code to automate the process and thought I'd share.

I didn't test it on worksheet vs. workbook level names, it appears to just apply whatever names it can to the used range of the worksheet. And I'm fine with that.

Sub ApplyNames()
' programmatically apply named ranges to worksheet
On Error GoTo ErrorHandler

' create an array and populate it with workbook names
Dim namesList() As String
ReDim namesList(0 To Names.Count - 1)

Dim nm As Name
Dim i As Long ' i = 0

For Each nm In Names
  namesList(i) = nm.Name
  i = i + 1
Next nm

' if no names can be applied, error msg will appear
ActiveSheet.UsedRange.ApplyNames namesList
MsgBox "Names were applied to worksheet " & ActiveSheet.Name

ErrorExit:
  Exit Sub

ErrorHandler:
  MsgBox Err.Description
  Resume ErrorExit
End Sub

May 13, 2008 -- Outlook Automated File Request System
April 9, 2010 -- Building a string from an array
March 22, 2010 -- The Census is coming!

Filed Under: Excel, VBA ·

4 Comments:

Billy Gee writes:

June 11, 2009 at 3:00 pm

Very clean code, but I find that even using the apply names internal feature, my copying flexibility diminishes due to the inherent removal of relative cell referencing. More of a personal preference.
JP writes:

June 11, 2009 at 8:38 pm

Not sure what you mean, unless you mean copying the formula to another workbook. Otherwise, naming ranges and applying them to your formulas not only makes formulas easier to read, but makes them more portable within a worksheet. I can copy and paste them anywhere and not have to worry about references at all.
Steve writes:

January 20, 2010 at 10:06 pm

does this macro apply names defined on all pages of a wookbook to all of the cells of the page being acted upon?
- JP writes:
  
  January 21, 2010 at 6:16 pm
  
  @Steve
  
  From Excel Help:
  
  For an Application object, returns a Names collection that represents all the names in the active workbook. For a Workbook object, returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names). For a Worksheet object, returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix).
  
  Since we're calling Names.Count in the procedure, we're using the Application-level Names collection, which should consist of all workbook and worksheet level names.