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:

  1. Activate the worksheet containing the named range.
  2. Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Graphical user interface, application

Description automatically generated

Alternatively, you can press Alt + F11.

  1. In the Visual Basic Editor click Insert >> Module to create a new module.
  1. Type the following sub-procedure in the module.
  1. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
  2. 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:

  1. Activate the worksheet containing the named range.
  2. Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Graphical user interface, application

Description automatically generated

Alternatively, you can press Alt + F11.

  1. In the Visual Basic Editor click Insert >> Module to create a new module.
  1. Type the following sub-procedure in the module.
  1. Save the procedure and save the workbook as a Macro-Enabled Workbook.
  2. 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.

  1. Select cell D2 and type in the following formula:
Graphical user interface, application, table, Excel

Description automatically generated
  1. 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.

Posted in vba