Excel Name Manager create syntax for large number of Names using VBA (no voiceover)
HTML-код
- Опубликовано: 16 сен 2024
- Name Manager in Excel (CTRL+F3)
One can use the Name Manager dialog box and create Names click by click.
But when the number of Names becomes larger the operation becomes tedious.
You create a Sub in VBA to create those Names.
But that is tedious as well.
Therefore, you create an UDF for that syntax (see code below).
That function is to have 2 Arguments.
Argument 1: Name
Argument 2: RefersTo
Save file as XLSM
CTRL + Shift + A
(to see the arguments of the UDF)
Public Function Syntax_for_Name_Manager(Name, RefersTo As Range) As Variant
'Name Manager dialog box is accessed with CTRL+F3
Dim text_1, text_2, text_3 As Variant
text_1 = " ActiveWorkbook.Names.Add Name:="""
text_2 = """, RefersToR1C1:=""="
text_3 = """"
Syntax_for_Name_Manager = text_1 & Name & text_2 & RefersTo & text_3
End Function