Microsoft Excel is a powerful software application that you can use to create, track, and analyze data. In addition to the standard features of Excel, you can also create VBA macros to automate certain tasks.
Are you looking to learn about creating Named Ranges and using VBA macros in Excel? If so, you have come to the right place. In this blog post, we will discuss how to create Named Ranges in Excel using VBA.
We will also provide some examples of how you can use Named Ranges in your own work. Let's get started!
What Is a Named Range in Microsoft Excel?
Using a named range instead of a column/row reference, you can refer to a cell or group of cells with a custom name. The ability to specify the data inside your cells is a huge benefit of using Named Ranges.
Here's a quick example of how you can use Named Ranges to tell if the shipping costs are charged with the product price:
- = (B7 + B5 * C4) * (1 + A3)
- =(ShippingCharge + ProductPrice * Quantity) * (1 + TaxRate)
Option number two, as you can see, gives you an immediate indication of whether the price of the products includes shipping expenses. This makes it simple for the user to comprehend how the formula works without having to waste time looking through cells to figure out what is what.
How To Use Named Ranges in Excel?
As a financial analyst, you might be expected to experiment with various rates. Anything from a tax rate to a projected inflation rate might be used as an example. You can use Named Ranges to organize variables that are either modified seldom (such as Month or Year) or will remain static for a long period (like the inflation rate).
Here's a list of common names you may use often when working with Named Ranges in Excel:
- ReportDate
- Year
- Month
- FcstID
- TaxRate
- RawData
These are only some of the possibilities - you can name a range of anything you want that adheres to Excel's rules for Named Ranges.
Create Unique Names
Creating a Named Range is a breeze. All you have to do now is select the cell(s) you wish to refer to and type a name in the Name Box.
Because your name cannot contain any spaces, you must capitalize the first letter of each new word or use an underscore (_) to separate terms. To finalize the establishment of the Named Range, press the ENTER key after you've done typing the name.
Note: Any Named Range established with the Name Box has a Workbook scope. This means that any worksheet in your Excel file can access the named range.
Creating Names Using the Name Manager
You can change and create new Named Ranges in the Name Manager (Formulas tab > Defined Names group > Name Manager button) if you wish to customize your Named Ranges even more.
You should be aware that the Name Manager allows you to:
- Change the name of a Named Range that already exists,
- Make a reference formula change,
- Define the scope of which worksheets the name can be accessed.
Now that you understand what a named range is and how to create one, let's look at some practical examples of how you can use them in your work.
Practical Examples of How To Use Named Ranges
Example 1: Creating a Dynamic Range
A dynamic range is a named range that automatically adjusts to include new data that is added to the worksheet. This is a great way to save time when your data changes often.
Example 2: Creating a 3D Range
A 3D range is a named range that refers to data in multiple worksheets. This can be a great time-saver if you work with large amounts of data that are organized in a similar fashion across multiple worksheets.
Example 3: Creating a Named Array
A named array is a type of dynamic range that refers to an array of values. This can be a great way to reference data that is organized in a table format.
Automate Named Ranges With VBA Macros
Now that we've gone over a quick review of Named Ranges, let's look at some VBA macros that can help you automate your use of them.
Add a Named Range
The VBA code below demonstrates how to construct various sorts of Named Ranges.
|
Sub NameRange_Add() 'PURPOSE: Various ways to create a Named Range 'SOURCE: SoftwareKeep.com Dim cell As Range Dim rng As Range Dim RangeName As String Dim CellName As String 'Single Cell Reference (Workbook Scope) RangeName = "Price" CellName = "D7"
Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell 'Single Cell Reference (Worksheet Scope) RangeName = "Year" CellName = "A2"
Set cell = Worksheets("Sheet1").Range(CellName) Worksheets("Sheet1").Names.Add Name:=RangeName, RefersTo:=cell 'Range of Cells Reference (Workbook Scope) RangeName = "myData" CellName = "F9:J18"
Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell 'Secret Named Range (doesn't show up in Name Manager) RangeName = "Username" CellName = "L45"
Set cell = Worksheets("Sheet1").Range(CellName) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell, Visible:=False End Sub |
Loop Through Named Ranges
The next VBA macro code demonstrates how to cycle through your spreadsheet's Named Ranges.
|
Sub NamedRange_Loop() 'PURPOSE: Delete all Named Ranges in the Active Workbook 'SOURCE: SoftwareKeep.com Dim nm As Name 'Loop through each named range in workbook For Each nm In ActiveWorkbook.Names Debug.Print nm.Name, nm.RefersTo Next nm
'Loop through each named range scoped to a specific worksheet For Each nm In Worksheets("Sheet1").Names Debug.Print nm.Name, nm.RefersTo Next nm End Sub |
Delete All Named Ranges
This VBA function will help you clean out a large chunk of Named Ranges in Excel that you no longer need in your project.
|
Sub NamedRange_DeleteAll() 'PURPOSE: Delete all Named Ranges in the ActiveWorkbook (Print Areas optional) 'SOURCE: SoftwareKeep.com Dim nm As Name Dim DeleteCount As Long 'Delete PrintAreas as well? UserAnswer = MsgBox("Do you want to skip over Print Areas?", vbYesNoCancel) If UserAnswer = vbYes Then SkipPrintAreas = True If UserAnswer = vbCancel Then Exit Sub 'Error Handler in case Delete Function Errors out On Error GoTo Skip 'Loop through each name and delete For Each nm In ActiveWorkbook.Names If SkipPrintAreas = True And Right(nm.Name, 10) = "Print_Area" Then GoTo Skip
'Error Handler in case Delete Function Errors out On Error GoTo Skip
'Delete Named Range nm.Delete DeleteCount = DeleteCount + 1
Skip: 'Reset Error Handler On Error GoTo 0 Next
'Report Result If DeleteCount = 1 Then MsgBox "[1] name was removed from this workbook." Else MsgBox "[" & DeleteCount & "] names were removed from this workbook." End If End Sub |
Delete Named Ranges With Errors
Only Named Ranges with faults will be deleted with this VBA code. These problems might be triggered by the deletion of worksheets or rows/columns.
|
Sub NamedRange_DeleteErrors() 'PURPOSE: Delete all Named Ranges with #REF error in the ActiveWorkbook 'SOURCE: SoftwareKeep.com Dim nm As Name Dim DeleteCount As Long 'Loop through each name and delete For Each nm In ActiveWorkbook.Names If InStr(1, nm.RefersTo, "#REF!") > 0 Then 'Error Handler in case Delete Function Errors out On Error GoTo Skip
'Delete Named Range nm.Delete DeleteCount = DeleteCount + 1 End If Skip: 'Reset Error Handler On Error GoTo 0 Next
'Report Result If DeleteCount = 1 Then MsgBox "[1] errorant name was removed from this workbook." Else MsgBox "[" & DeleteCount & "] errorant names were removed from this workbook." End If
End Sub |
TL;DR
- Named ranges are a great way to organize your data in Excel.
- You can use them to create dynamic ranges, 3D ranges, and named arrays.
- You can also automate your use of Named Ranges with VBA macros.
Conclusion
Excel is an amazing tool that can make even the most mundane tasks exciting to complete with vast automation features. If you want to make your life easier, learning how to work with advanced tools like Named Ranges with VBA is a great way to start.
We hope this article has helped you learn a little bit more about how to use Named Ranges in Excel. As always, if you have any questions, feel free to leave us a comment below!