Names in Excel VBA – Explained with Examples!

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
• Deleting Names in Excel VBA
• Hide UnHide Names in Excel VBA

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.

• Solution
• Code
• Output
• Example File

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:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5
6. GoTo Sheet1 and Select Range A1 to D10
7. 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.

• Solution
• Code
• Output
• Example File

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:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5
6. GoTo Sheet1 and Select Range A1 to D10
7. 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.

• Solution
• Code
• Output
• Example File

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:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5
6. GoTo Sheet1 and Select Range A1 to D10
7. You should see the following example

Example File

Download the example file and Explore it.
Analysistabs – Hide UnHide Names in excel Workboobk

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource

Excel VBA Reference	Project Management Reference
VBA Reference: VBA Code Explorer VBA Excel Application VBA Excel Workbook VBA Excel Worksheet VBA Excel Range VBA ActiveX Controls VBA Userforms VBA Projects Excel Reference: Excel Tutorial Microsoft Excel 2003 Microsoft Excel 2007 Microsoft Excel 2010 Microsoft Excel 2013 Microsoft Excel 2016	What is a Project? Project Appraisal Project Management Project Plan Project Resource What is Gantt Chart? Excel Templates Excel Project Management Templates PowerPoint Project Management Templates MS Word Project Management Templates Excel Basics Tutorials Excel VBA Tutorials Excel Functions and Formulas

External VBA Reference

Excel VBA Reference

Project Management Reference

VBA Reference: Getting Started with VBA in Office VBA for MS Office Application: Complete References Excel VBA Reference VBA Tutorials VBA Macros VBA Functions Excel Reference: Microsoft Excel Reference Excel Formulas Excel 2016 Excel Functions Excel Quick Tutorials

Project Management Methodologies MS Project Templates Excel, Word, PowerPoint Timeline Templates Excel, Word, PowerPoint Planners and Trackers Microsoft Excel Templates MS PowerPoint Excel Templates MS Word Templates MS Office Schedules Templates Excel, Word, PowerPoint Agenda Templates Excel, Word, PowerPoint Invoices Templates Excel, Word, PowerPoint Business Plans Templates Excel, Word, PowerPoint Financial Management Templates Excel, Word, PowerPoint Minutes Templates