Vba name manager in vba in Excel
Name Manager feature of Excel enables an user to define a name to a range of cells containing value or formula.
Generally, a data is excel is understood with the help of header under which the data is present.
For example, a sheet may contain hundreds of First Names in column A with the heading as "First Name".
But, when a name or group of names has to be accessed in some other sheet or area, then it cannot be done with the help of header.
The header is just for the user to identify the column and for excel, its also a text like other names in the column.
But, What if the column A is assigned a name that can be refered in other parts of the workbook, whenever any information about the names are required?
The above mentioned facility is made possible in Excel with the help of "Name Manager".
The following example shows the benefits offered by Name Manager.
The following code shows how to create Named lists in Excel.
- Sub Namemgr()
- Dim nmsht As Worksheet
- Dim fn As String
- Dim incom As String
- fn = InputBox("Enter a name for First names list")
- incom = InputBox("Enter a name for Incomes")
- Set nmsht = Sheets("Q93")
- ActiveWorkbook.Names.Add _
- Name:=fn, _
- RefersTo:=nmsht.Range("A13:A20")
- ActiveWorkbook.Names(fn).Comment = "demo"
- ActiveWorkbook.Names.Add _
- Name:=incom, _
- RefersTo:=nmsht.Range("B13:B20")
- ActiveWorkbook.Names(incom).Comment = "demo"
- End Sub
The program demands names of First name column from A13:A20 and Income details from B13:B20.
Once entered, the named lists are created.
In our example, we have created two named lists "vbafirstnames" and "vbaincome" for illustration purpose.
The users are free to create a list with names of their choice.
The screenshots of the editor and the output is as shown below.