Names in Excel VBA makes our job more easier. We can save lot of time using Names. It is easy to maintain the formulas, Cells,Ranges and Tables. You can define the names once in the workbook and use it across the workbook. The following examples will show some of the Names daily operations.
Adding Names in Excel VBA:
Sometimes you may need to Add name to a particular range in excel worksheet. We can manage range names in excel vba with using names collection.
Adding Names in Excel VBA – Solution(s):
We can use Names.Add method or Name property of a range for adding names in excel VBA.
We can create range name in the following way. It contains several properties.We must define Name and the Refers To property.please find the following example.The following code creates a name “MyData” and referring to sheet1 of a range(“$A$1:$E$10”)
Code:
'Naming a range Sub sbNameRange() 'Adding a Name Names.Add Name:="myData", RefersTo:="=Sheet1!$A$1:$E$10" 'OR 'You can use Name property of a Range Sheet1.Range("$A$1:$E$10").Name = "myData" End Sub
Output:
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5
- GoTo Sheet1 and Select Range A1 to D10
- You should see the following example
Example File
Download the example file and Explore it.
Analysistabs – Adding Name to Range in excel Workboobk
Deleting Names in Excel VBA:
Sometimes you may need to Delete range name in existing excel workbook for cleaning broken references in excel workbook.
Deleting Names in Excel VBA – Solution(s):
You can use Delete method for deleting existing names in excel workbook.We can delete range name in the following way.please find the following example.The following code Deletes a name “MyData”.
Code:
'Deleting Names Sub sbDeleteName() 'myData=Sheet1.range("A1:E10") Names("myData").Delete End Sub
Output:
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5
- GoTo Sheet1 and Select Range A1 to D10
- You should see the following example
Example File
Download the example file and Explore it.
Analysistabs – Deleting Name to Range in excel Workboobk
Hide UnHide Names in Excel VBA:
Sometimes you may need to Hide UnHide names in Excel VBA.
Hide UnHide names in Excel VBA – Solution(s):
You Can Hide UnHide Names in Excel VBA using Visible Property in the following way. when we set visible proprty to false, We dont see defined range name . when we set visible proprty to true, We can see defined range name .
Code:
'Hiding a Name Sub sbHideName() Names("myData").Visible = False End Sub 'UnHide aName Sub sbUnHideName() Names("myData").Visible = True End Sub
Output:
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5
- GoTo Sheet1 and Select Range A1 to D10
- You should see the following example
Example File
Download the example file and Explore it.
Analysistabs – Hide UnHide Names in excel Workboobk
Premium Project Management Templates
50+ Professional Project Management Templates!
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates