Convert Named Range into Array in VBA

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.

Graphical user interface, application

Description automatically generated

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.

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

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.

Graphical user interface, text, application

Description automatically generated

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.

Public Function NAMEDRANGETOARRAY(Range1 As Range) As Variant
    Dim arr1 As Variant
    arr1 = Range1
    NAMEDRANGETOARRAY = arr1
End Function

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.

Graphical user interface, application, Word

Description automatically generated

Alternatively, you can press Alt + F11.

Select cell D2 and type in the following formula:

=NAMEDRANGETOARRAY(Tablets)

1	=NAMEDRANGETOARRAY(Tablets)

Graphical user interface, application, table, Excel

Description automatically generated

Click the Enter button on the formula bar to enter the formula.

The formula returns a two-dimensional array as shown below:

Table

Description automatically generated

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 356