Search code examples
excelcellnamesvba

How to select all names in Excel with VBA

I am working with a large Excel file, where I have defined around 2000 names for cells. In addition the excel contains formulas, these formulas are already entered with references to cells (A23-B24). I want to change the cells references by the defined names (instead of A23-B24 having VARIABLE_100-VARIABLE_120).

I know this is possible by selecting "Apply names" and then select the defined names from the list. Because I have around 2000 defined names, I would like to select all the names at once from the menu, but I cannot find an option, so I have to select one by one. I have been looking if there was an option for enabling the multiple selection on the menu, but I have not found such an option. A work around for me would be to create a macro that applies to the selected range of formulas the selected names. Something like this:

Sub Macro1()

' Macro1 Macro

Selection.ApplyNames Names:=Array  ("ATL_BV_XP", "ATL_BV_XP..EUN", "ATL_PK_XP", _
    "ATL_PK_XP..EUN", "CHN_PK_IM", "CHN_PK_IM..EUN", "CHN_PK_IM..SHREUN", _
    "E15_AG_AH", "E15_AG_EPA", "E15_AG_SFP", "E15_AG_SFP..CF" _
    , "E15_APF_FE"), IgnoreRelativeAbsolute:=True, UseRowColumnNames:=True, _
    OmitColumn:=True, OmitRow:=True, Order:=1, AppendLast:=False
End Sub

My problem is that because I have a large number of defined names (already wrote like twice that around 2000), listing all names in the macro code becomes really complex. I thought that probably a workaround would be to create a list including all defined names within the code. Does someone has an idea how to do this? I have looking around and some codes suggest a loop while other say it is possible to extract a list. Nonetheless I have not been able to find a way to do it.

Solution

  • The documentation at msdn.microsoft.com says

    Names: An array of the names to be applied. If this argument is omitted, all names on the sheet are applied to the range.

    see https://msdn.microsoft.com/en-us/library/office/ff196578(v=office.15).aspx

    Following on that try this:

    Selection.ApplyNames _
    IgnoreRelativeAbsolute:=True, UseRowColumnNames:=True, _
    OmitColumn:=True, OmitRow:=True, Order:=1, AppendLast:=False

    This should work depending on the Scope of the Defined Names and their visibility. Try that and let us know the results.