List Defined Names Excel VBA Macro

Excel allows to define names that can be used in formulas instead of cell references. This macro example lists all defined names and formulas in a workbook.

 

Macro/VBA code:

  Sub ListNamesAndFormula()

      Dim rngName As Name, r As Integer

      For Each rngName In ActiveWorkbook.Names

          r = r + 1

          ActiveSheet.Range("A" & r).Value = rngName.Name

          ActiveSheet.Range("B" & r).Value = rngName

      Next rngName

  End Sub

Macro explained:

• First we declare a Name object variable (rngName) that will hold each named range or formula in the workbook.
• Then we use a For Each loop to go through all defined names in the workbook one by one.
• The variable r will increment the row number while adding each name to the list.
• We add the name and reference for each defined name in the workbook to columns A and B of the active sheet respectively.

This is how we list all defined names and formulas in Excel with VBA macros.

Other examples:

• How to Highlight Duplicates with Excel VBA macros
• How to List Unique Entries with Excel VBA macros
• How to Sort Sheets Alphabetically with Excel VBA macros