Excel List All Defined Names with Formula
Defined names are using to simply the long formula into a short & meaningful name. These names can be defined in Excel from Menu -> Formula -> Name Manager -> New
More information on creating Defined name can be found here.
If you would like to Loop through all the available names in the Workbook, here is the code to do that.
Loop thru all names using Excel VBA
This code will also get the defined formula for each name.
Sub List_All_Defined_Name()
Dim dName As Name, iRow As Double
'Get All Defined Names
iRow = 1
For Each dName In ThisWorkbook.Names
'Get the Name of Formula Definition
ThisWorkbook.Sheets(1).Cells(iRow, 1) = dName.Name
'Get the Value or Formula
ThisWorkbook.Sheets(1).Cells(iRow, 2) = "'" & dName.RefersTo
'Get Comments
ThisWorkbook.Sheets(1).Cells(iRow, 3) = "'" & dName.Comment
'Get Value
ThisWorkbook.Sheets(1).Cells(iRow, 4) = "'" & dName.Value
iRow = iRow + 1
Next
End Sub
On executing the above code, Excel will fetch all the details of the complete list of defined names for the workbook.
This will also list the value & the formula defined for each name. This is a good way to have a look at all the defined names rather than seeing it in Names manager.
In Names manager there is no option to search for a formula. Here it can be done.
