REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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:

Adding Names in Excel VBA

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.

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:

Deleting Names in Excel VBA

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.

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:

Hide UnHide Names in Excel VBA

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

Effortlessly Manage Your Projects and Resources
120+ 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.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

5 Comments

  1. Eric W April 30, 2015 at 11:17 AM - Reply

    I am having a problem copying Ranges from one point to another in Excel using VBA.
    I have tried several methods and get the same result any way I’ve tried
    When the copy action is triggered, the destination has the copy from the header row in the destination
    Example: Range(“U14:AO14”).Copy Destination:=Range(“A40”)
    I am receiving the data from U2:AO2.
    I have named the range by selection using the spreadsheet name window. The array that shows in the window shows the correct value. The destination is correct.

    Do you have a clue what is happening, or what I am doing wrong?
    Thank You

  2. Amar September 9, 2015 at 5:23 PM - Reply

    Can this defined name be used for coding or in vba editor

  3. PNRao September 11, 2015 at 11:51 PM - Reply

    Hi Amar,
    Yes! we can use the defined names in VBA. Example:

    Sheet1.Range("$A$1:$E$10").Name = "myData"
    Range("myData").Interior.ColorIndex = 3
    

    here, myData is a user defined name and the above code will change the background color of the defined range.
    Thanks-PNRao!

  4. Silke Flink September 12, 2015 at 12:35 AM - Reply

    Thank you for this post. I was wondering if it is possible to address a dynamic name range in VBA. I have a named range (though I use a lookup-function in the name manager) that is visible in the name manager and can be used in formulas in the Excel sheet.

    I tried this

    dim Taxes_from_Sales as Range
    Set Taxes_from_Sales = Range(“Taxes_from_Sales”)

    whereas “Taxes_from_Sales” is a excel Lookup defined name range. VBA doesn’t recognize it cause of the formula.

    Any idea how to approach this without writing Lookup in VBA?

    Thanks for help

    silke

  5. PNRao September 13, 2015 at 1:48 AM - Reply

    Hi,
    It works for dynamic range. In your case, Lookup functions always returns a value, and we need a range to use in VBA.

    I have tried the below example and its working fine:

    Sub DynamicNamedRange()
    'Adding a dynamic Name
    Names.Add Name:="test", RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),1)"
    
    'Entering sample data
    For i = 1 To 10
    Cells(i, 1) = i
    Next
    
    'Formatting dynamic range
    Range("test").Interior.ColorIndex = 3
    End Sub
    

    Thanks-PNRao!

Leave A Comment