Return to VBA Code Examples

VBA Multi-Dimensional Array (2D Arrays)

This tutorial will discuss 2-d and multi-dimensional arrays in VBA.

Multi-Dimensional Array (2D Arrays)

Multi-dimensional Arrays are arrays that contain more than one dimension, usually two or three dimensions, but arrays can have up to 32 dimensions.

Declare a 2D Array

To create an array with more than one dimension, use commas to define each separate dimension:

Populating a 2D Array

The code below will populate a 2D array, and then populate the rows and columns of a worksheet with the values in the array.

Your Excel spreadsheet should then be populated as follows.

vba 2D array

Populating a 2D Array from Excel data

The code below will populate a 2D array from an Excel worksheet and then populate a different sheet with the data.

Resizing using ReDim and Re-Dim Preserve

You can resize an array using ReDim (learn more).

When you redeclare the array, you will lose any data previously held in the array unless you use the ReDim Preserve Statement.

You can only resize the last dimension of an Array if want to keep the original data in the Array with Re-Dim Preserve.

vba resize array

When you click debug, the error will be highlighted showing that the first dimension in the array is not the same as the first dimension when the array size was originally declared.

vba resize array error

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!