How to Declare Array in Excel VBA Easily (3 Types Array)

Welcome to Excel Avon

Array in Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

Arrays in Excel VBA are an important feature of VBA Array is very useful and powerful feature. A VBA array in Excel is a storage unit or a variable which can store multiple data values, these values must necessarily be on the same data types, or an array is a memory location capable of storing some value or more than one value in it. There are 3 types of arrays in VBA, one dimension array, multiple dimension array and, in dynamic arrays, we can change the size of the array. In dynamic array if we want to resize the array again, we use Redim Function.

To understand array in Excel VBA, we have to go to VBE – Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Model has to be inserted, as can be seen in the image.

Single Dimension Array in Excel VBA

The one-dimensional array in Excel VBA consists of list of items of same data type. It consists of either single row or column data. We read values from an array or into an array using index value.

First, we need to write subroutine for single dimension.

Sub Declare_SingleDimension()

End Sub

After writing the subroutine, we will define the array. myArray(1 to 100) as variant

Sub Declare_SingleDimension()
Dim myArray(1 to 100) As Variant
End Sub

• • The reason for defining in the variant is that we have to store the value in text.

So, let’s see if we store Integer 1 in ABC, will it be stored.

Sub Declare_SingleDimension()
Dim myArray(1 to 100) As Variant

myArray(1) = "abc"
End Sub

After writing the code, click the run button

It is Stored because ‘integer 1’ into the define range.

Now we will store 0, but we haven’t defined 0 in the variant

Sub Declare_SingleDimension() 
Dim myArray(1 to 100) As Variant 

myArray(1) = "abc"
myArray(0) = "abc"
End Sub

After writing the code, click the run button

But 0 was not defined in the variant so it will give error page

then we will store 101, But we haven’t even defined 101 in the variant.

Sub Declare_SingleDimension() 
Dim myArray(1 to 100) As Variant 

myArray(1) = "abc" 
myArray(101) = "abc" 
End Sub

After writing the code, click the run button

It will give this error page, because 101 is not defined in the variant.

The reason for getting error is that we have defined 1 for lower range and 100 for upper range.

And if the lower take is not defined then the default will be considered as zero. And if we assume 100 for the upper range, if we do not define the lower range, then the default will be zero.

Sub Declare_SingleDimension() 
Dim myArr1(100) As Variant
 
myArray1(0) = "xyz"
End Sub

After writing the code, click the run button

and Now integer 0 is stored

Now we’ll store 99

Sub Declare_SingleDimension() 
Dim myArr1(100) As Variant

myArray1(0) = "xyz"
myArray1(99) = "xy1"
End Sub

After writing the code, click the run button

Now integer 99 is stored.

Multi-Dimension array in Excel VBA

In Multi Dimension array in Excel VBA, the length is determined in two dimensions, and it works.

First, we need to write subroutine for multi-dimension array in Excel VBA.

Sub Declare_MultiDimension() 

End Sub

Now I will define myArr(1 to 10, 1 to 20, 1 to 20) as variant for multi-Dimension Array in Excel VBA.

Sub Declare_MultiDimension() 
Dim myArr(1 to 10, 1 to 20, 1 to 20)
End Sub

Now I will store dimension (1, 1, 1) store as text.

Sub Declare_MultiDimension() 
Dim myArr(1 to 10, 1 to 20, 1 to 20)

myArr(1, 1, 1) = "abc"
End Sub

After writing the code, click the run button

Multi-Dimension array stored

Now I will store dimension (10, 20, 30) store as text.

Sub Declare_MultiDimension() 
Dim myArr(1 to 10, 1 to 20, 1 to 20)

myArr(1, 1, 1) = "abc"
myArr(10, 20, 30) = "xyz"
End Sub

After writing the code, click the run button

Multi-Dimension array stored.

Now I will store dimension (11, 20, 30) store as text.

Sub Declare_MultiDimension() 
Dim myArr(1 to 10, 1 to 20, 1 to 20) As Variant

myArr(1, 1, 1) = "abc"
myArr(10, 20, 20) = "xyz"
myArr(11, 20, 20) = "xya"
End Sub

After writing the code, click the run button

But error page will come because the range of first upper dimension is 10 defined but we are storing 11 which is more than the defined range.

Now I will redefine the range but without the lower range. And Store Integer (0, 0, 0) as variant.

Sub Declare_MultiDimension() 
Dim myArr(1 to 10, 1 to 20, 1 to 20) as Variant
Dim myArr1(10, 20, 20) As Variant

myArr(1, 1, 1) = "abc"
myArr(10, 20, 20) = "xyz"
myArr(0, 0, 0) = "xya"
End Sub

After writing the code, click the run button.

Dimension will be stored.

Dynamic Array in Excel VBA

First, we need to write subroutine for Dynamic array in Excel VBA.

Sub Declare_DynamicArray()

End Sub

Define myArr() as variant for Dynamic dimension array.

Sub Declare_DynamicArray()
myArr() as variant

End Sub

Now I will store 0 as text.

Sub Declare_DynamicArray()
myArr() as variant

myArr(0) = "xyz"
End Sub

After writing the code, click the run button

Dimension will be stored.

Redefine myArr(1 to 100) with preserve function as variant for Dynamic dimension array in excel VBA And with this we will store zero as well.

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)

myArr(0) = "xyz"
End Sub

After writing the code, click the run button

Error occurred, Because the lower value is 1 redefine and we are storing 0.

Now we will store Integer 1 as text.

Sub Declare_DynamicArray()
myArr() as variant
myArr1() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

End Sub

After writing the code, click the run button

Dimension will be stored.

Once again, we will define myArray1 as variant, use Redim function and now we can resize the array size

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)
End Sub

Now we will store Integer 1

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)
myArr1(1) = "abc"
End Sub

After writing the code, click the run button

Dimension will be stored.

Now I will Redim function and resize the array size.

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)
myArr1(1) = "abc"

Redim myArr1 (10 to 101)
End Sub

Now I will store Integer 10 as ABC

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)

myArr1(1) = "abc"
Redim myArr1 (10 to 101)
myArr1(10)="abc"
End Sub

After writing the code, click the run button

Integer Stored.

Now we will store Integer 1

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)
myArr1(1) = "abc"

Redim myArr1 (10 to 101)
myArr1(1)="abc"
End Sub

After writing the code, click the run button

When we click run button the code will be error because 1 we didn’t define because lower value is 10 only

Now I will store Integer 101

Sub Declare_DynamicArray()
myArr() as variant
Redim preserve myArr(1 to 100)
myArr(1) = "xyz"

Redim preserve myArr(1 to 100)
myArr1(1) = "abc"

Redim myArr1 (10 to 101)
myArr1(101)="abc"

End Sub

After writing the code, click the run button to test array in excel vba code

Now Integer Stored Because 101 in the defined range.