At times we may need to convert a named range into an array in Excel VBA so that we can access the more powerful options available to arrays and not Excel ranges. This tutorial shows you two methods of how to do it.
Method #1: Use a Sub-procedure
The following data range is named Tablets.
We want to convert this named range into a two-dimensional array in Excel VBA.
We use the following steps:
- Activate the worksheet containing the named range.
- Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Alternatively, you can press Alt + F11.
- In the Visual Basic Editor click Insert >> Module to create a new module.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 6 7 8 |
Sub Convert_Named_Range_To_Two_Dimensional_Array() Dim myArray As Variant Dim iNum1 As Integer myArray = Range("Tablets") For iNum1 = 1 To UBound(myArray) Debug.Print myArray(iNum1, 1) & " " & myArray(iNum1, 2) Next iNum1 End Sub |
- Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
- Place the anywhere in the procedure and press F5 to run the code.
The results of the code are displayed in the Immediate window.
The Tablets range has been converted into a two-dimensional array.
Explanation of the Sub-procedure
- The myArray variable of the Variant data type is declared.
- The iNum1 variable of the Integer data type is declared.
- The For…To…Next loop is used to generate elements of the array and print them to the Immediate window.
Method #2: Use a User Defined Function
The following data range is named Tablets.
We can use a User Defined Function to convert this named range into a two-dimensional array.
We proceed as follows:
- Activate the worksheet containing the named range.
- Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Alternatively, you can press Alt + F11.
- In the Visual Basic Editor click Insert >> Module to create a new module.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 |
Public Function NAMEDRANGETOARRAY(Range1 As Range) As Variant Dim arr1 As Variant arr1 = Range1 NAMEDRANGETOARRAY = arr1 End Function |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the named range.
Alternatively, you can press Alt + F11.
- Select cell D2 and type in the following formula:
|
1 |
=NAMEDRANGETOARRAY(Tablets) |
- Click the Enter button on the formula bar to enter the formula.
The formula returns a two-dimensional array as shown below:
Conclusion
This tutorial has looked at two methods of how to convert a named range into an array in Excel VBA. We hope you found the tutorial helpful.