HOW TO DEAL WITH ARRAYS IN VBA

Table of Contents

INTRODUCTION

An array is a group of the same type of elements. Almost all the programming languages support Arrays. Arrays are very helpful in structured programming and managing large data of one type.

An array can be operated upon altogether or one by one, as per the requirement of the case.

An array is helpful where we need to club the same kind of things such as if we have to manage the days of a week.

We know that there are seven days of the Week. i.e. Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.

If we need to manage some data of these seven days, we have to use separate names for all the seven days or we can create an Array as Days(6).

“By default, VBA takes 0 as lower index and the index given in parenthesis is upper index”

Days(6) means that an array named DAYS can hold up to 7 values and we can use these values by the use of their index number. For example

Days(0)…Days(3) upto Days(6).

Remember the index of arrays starts from 0 so it goes up to one less than the size.

DECLARING ARRAYS IN VBA

SYNTAX TO DECLARE ARRAYS IN VBA

Arrays are just like variables, the only difference is that they are a group of more than one. So we can declare Arrays as

DIM DAYS(0 TO 6) AS INTEGER or

DIM DAYS(6) AS INTEGER

DIM DAYS ( 1 TO 7) AS INTEGER

All contain 7 elements.

The scope decision remains the same.

MULTIDIMENSIONAL ARRAYS IN VBA

If you remember the MATRICES studied in high school, the same is the case with multidimensional arrays. We can define the arrays in 2 dimensions and 3 dimensions too. VBA can hold up to 60-dimensional arrays.

Higher-dimensional arrays help us to manage a large number of data effortlessly.

Let us define a 2-D array

Dim Students(1 to 100, 1 to 100) as Integer

This statement declares an array of 10000 elements.

To refer any element, we need to use the double index as

Students(3,4) and so on.

Similarly, a 3-dimensional array will be declared as

Dim Students( 1 to 10, 1 to 10, 1 to 10) as Integer

This array can hold up to 1000 values.

We have one more type of arrays

The dynamic arrays.

DYNAMIC ARRAYS IN VBA

Dynamic arrays are the ones that do not have a predefined size.

Many times in applications we come to know the actual need of the size only during the application is being executed. So this kind of declaration is much helpful in that scenario as it saves us the memory which is going to be wasted otherwise.

Here is the standard declaration of the DYNAMIC ARRAYS.

Dim Students() as Integer

However, the statement is not yet ready for us.

Before we put this array in use, we need to tell the VBA about the size which is going to be used.

For telling the size of the Array the following statement is used

ReDim Students(1 to 100) as Integer

After this declaration, we can use the array named Students.

REDIM can be used any number of times and the values can also be changed.

For ExampleReDim Students(1 to 100) as IntegerReDim Students(1 to 80) as Integer and so on. 

PRESERVE DYNAMIC ARRAYS

There is a keyword as PRESERVE which if used, fixes the lower bounds of the arrays. If this keyword is used, we can only change the upper bound of the arrays.

The syntax for the preserve keyword is

Redim preserve Students(2,5) as Integer

Now we can only change 5 i.e. upper bound in this array else it’ll cause an error.

EXAMPLE 1:USING ARRAYS

Let us discuss a few examples of using arrays.

USING SINGLE DIMENSION ARRAY

In this example, we will create an array of Weekdays and put all the days in the array.

After filling up the array we’ll display the days using a message box one by one using a loop.

code :single array example

Sub ArrayExample()

          Dim Weekdays(7) As String 'Declaring Array

          Weekdays(0) = "Sunday" 'Putting Values
          Weekdays(1) = "Monday"
          Weekdays(2) = "Tuesday"
          Weekdays(3) = "Wednesday"
          Weekdays(4) = "Thursday"
          Weekdays(5) = "Friday"
          Weekdays(6) = "Saturday"

          For i = 0 To 6 'Displaying days

             MsgBox ("The day is " & Weekdays(i))

          Next i

End Sub
EXCEL: SINGLE ARRAY EXAMPLE CODE

EXAMPLE 2: RUNNING THE CODE

After the coding is complete, save the code and Run the example by any ways which you have already learned.

The following picture shows the execution.

EXCEL: SINGLE ARRAY EXAMPLE RUNNING THE CODE

EXAMPLE 2:USING 2-D ARRAYS

This example will discuss about the 2D arrays. How to implement and use them.

EXAMPLE 2: USING 2-D ARRAYS

In this example we will create an array of Weekdays and Max temperature on that day. After this, put all the days in the array.

After filling up the array we’ll display the days using a message box one by one using a loop.

CODE :2 DIMENSION ARRAY EXAMPLE

'GYANKOSH.NET/MSEXCEL

Sub Array2DExample()

Dim Weekdays(6, 1) As String 'Declaring Array

Weekdays(0, 0) = "Sunday" 'Putting Values
Weekdays(1, 0) = "Monday"
Weekdays(2, 0) = "Tuesday"
Weekdays(3, 0) = "Wednesday"
Weekdays(4, 0) = "Thursday"
Weekdays(5, 0) = "Friday"
Weekdays(6, 0) = "Saturday"

Weekdays(0, 1) = 25 'Putting Values temperature
Weekdays(1, 1) = 26
Weekdays(2, 1) = 25
Weekdays(3, 1) = 26
Weekdays(4, 1) = 23
Weekdays(5, 1) = 23
Weekdays(6, 1) = 24

For i = 0 To 6

      MsgBox ("The day of the week is " & Weekdays(i, 0)
      & " and the maximum temp. is " & Weekdays(i, 1))

Next i

End Sub
EXCEL:2D ARRAY EXAMPLE CODE

EXAMPLE 1: RUNNING THE CODE

After the coding is complete, save the code and Run the example by any ways which you have already learnt.

The following picture shows the execution.

EXCEL:2D ARRAY EXAMPLE RUNNING THE CODE

EXAMPLE 3:USING 3-D ARRAYS

This example will discuss about the 3D arrays. How to implement and use them.

EXAMPLE 2: USING 3-D ARRAYS

In this example, we will create an array of volumes of 8 cubes.

After filling up the array we’ll display the days using a message box one by one using a loop.

CODE: 3 DIMENSION ARRAY EXAMPLE

'GYANKOSH.NET/MSEXCEL

Sub Array3DExample()

Dim Cube(2, 2, 2) As Double 'Declaring Array to capture volume of 8 cubes

Cube(0, 0, 0) = 25.24 'Putting Values
Cube(0, 0, 1) = 23.24
Cube(0, 1, 0) = 22.24
Cube(0, 1, 1) = 26.24
Cube(1, 0, 0) = 22.24
Cube(1, 0, 1) = 26.24
Cube(1, 1, 0) = 22.24
Cube(1, 1, 1) = 21.24


For i = 0 To 1
    For j = 0 To 1
         For k = 0 To 1
              MsgBox ("The volume of the cube is " & Cube(i, j, k))
         Next k
     Next j
Next i

End Sub
EXCEL:3D ARRAY EXAMPLE CODE

EXAMPLE 3: RUNNING THE CODE

After the coding is complete, save the code and Run the example by anyways that you have already learned.

The following picture shows the execution.

EXCEL:3D ARRAY EXAMPLE RUNNING THE CODE

So these are the ways we can use different Arrays. The examples were meant to explain the input and displaying of the elements of the array.

In practical applications, these are very helpful for managing large data.

These are also helpful when we need to take the different inputs which are related to each other. For example, we can take name as a two dimensional array which can hold the first name and second name.