Names in Excel VBA – Explained with Examples!

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.

mongopono.ru – 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.
mongopono.ru – 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.
mongopono.ru – Hide UnHide Names in excel Workboobk

LIMITED TIME OFFER
By |June 30th, 2013|Excel VBA|5 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

5 Comments

  1. 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

    • 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!

  3. 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

    • 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


Related pages


dml commands in sql with syntax and exampleshow to protect selected cells in excel 2007vba send email to multiple recipientsstacked pyramid chart excelcountifs functionexcel autoexec macrohow to learn vba codeopen and activate workbook vbaunprotect excel sheet macrovba worksheetexcel vba cells rangename manager excel 2013graph chart in excelvba unprotectvba excel delete columnsleft function vbafilter pivot table based on cell valuearray in excel vbamicrosoft excel countif functionsas & sqlvba excel selectfrequently asked interview questions and answers for fresherssample construction schedule excelexcel vlookup functionproject task tracker excelinterview questions on sql queries with answers pdfhow to add command button in excel 2007merge excel columnsadd developer tab to the ribbon in word 2010how to do vlookup in excel 2007 step by stepvba application waitinterview questions in testing for freshersschedule template powerpointexcel vba additemhow to unhide the column in excelmicrosoft office gantt chart templatebasic excel macroexcel vlookup examplesexcel macros templatesplanner template excelvbscript create text filevba excel save workbookmacro to combine multiple workbooks into onehow to add macro in excel 2007ddl dml sqlmicrosoft outlook questions and answershow to do pivot table in excel 2010spotfire tutorialexcel vba forms examplestcs interview questions and answers pdfado odbc connection stringexcel vba hide columnhow to change the width of a column in excelchange column width in excelexcel vba commandsnested if in vbscripttick on excelexcel option button groupexcel case statementvlookup examples in excelexcel auto fitremove password protection excel 2010vba paste from clipboardhow to remove the duplicates in excelexcel workbook saveexcel checkbox macrotechnical aptitude questions and answersselect last row vbaexcel spreadsheet project managementexcel vba pivot table filtervba excel match functionconditional if statement excelwriting vba code in excel 2010hlookup in ms excel