Table of Contents
To reference a named range in VBA, you can use the Range object and specify the name of the range within the parentheses. This allows you to easily access and manipulate the data within the named range without having to use specific cell references. Using named ranges in VBA can help streamline your code and make it more readable and efficient. Additionally, you can also use the Names collection to loop through all the named ranges in a workbook and perform actions on each one. Overall, referencing a named range in VBA is a useful technique for working with data in Excel and can improve the functionality of your code.
VBA: Reference a Named Range
To reference a named range in VBA, you simply need to use the Range() function with the named range in double quotes.
For example, you could create the following macro to assign a value of “Team” to every value in the named range called teams:
Sub ModifyNamedRange()
Range("teams").Value = "Team"
End Sub
The following example shows how to use this syntax in practice.
Example: How to Reference Named Range Using VBA
Suppose we have a named range called teams that represents the range A2:A11 in a particular sheet in Excel:
We can create the following macro to assign a text value of “Team” to each cell in this named range:
Sub ModifyNamedRange()
Range("teams").Value = "Team"
End Sub
When we run this macro, we receive the following output:
Notice that each cell in our named range now contains “Team” instead of the original team name.
We could also use the following macro to assign a numeric value of 100 to each cell in the teams named range:
Sub ModifyNamedRange()
Range("teams").Value = 100
End Sub
When we run this macro, we receive the following output:
Or we could even use the following macro to make each cell in the named range teams to have a bold font and a green background color:
Sub ModifyNamedRange()
Range("teams").Interior.Color = vbGreen
Range("teams").Font.Bold = True
End Sub
When we run this macro, we receive the following output:
Each cell in the named range teams now has a bold font and a green background color.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: