Excel VBA Loop Thru All Defined Names

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.

Leave a Reply