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.

excel vba name manager in vba

The following code shows how to create Named lists in Excel.

  1. Sub Namemgr()
  2. Dim nmsht As Worksheet
  3. Dim fn As String
  4. Dim incom As String
  5. fn = InputBox("Enter a name for First names list")
  6. incom = InputBox("Enter a name for Incomes")
  7. Set nmsht = Sheets("Q93")
  8. ActiveWorkbook.Names.Add _
  9. Name:=fn, _
  10. RefersTo:=nmsht.Range("A13:A20")
  11. ActiveWorkbook.Names(fn).Comment = "demo"
  12. ActiveWorkbook.Names.Add _
  13. Name:=incom, _
  14. RefersTo:=nmsht.Range("B13:B20")
  15. ActiveWorkbook.Names(incom).Comment = "demo"
  16. 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.

excel vba name manager in vba

excel vba name manager in vba

 

You can find similar Excel Questions and Answer hereunder

1) How to create Pivot table in excel VBA

2) How can I prevent users from seeing / accessing my macro code?

3) How can I extract file name from a full path?

4) Line break in vba message box in Excel

5) How do i put double quotes in a string in vba in Excel

6) How can I hide Formula Bar and Headings using VBA?

7) How to print a worksheet in Excel VBA

8) How can I set FreezePanes in a certain range using VBA?

9) How can I set non-contiguous print area using VBA?

10) How can I save a WorkSheet as a new WorkBook using VBA?

 

Here the previous and next chapter