PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

ARRAYS IN VBA

INTRODUCTION

The array is a group of 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 requirement of the case.

An array is helpful where we need to club 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 upto 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 upto one less than the size.

 

DECLARING ARRAYS

SYNTAX TO DECLARE ARRAYS IN VBA

Arrays are just like variable, the only different is that they are a group of more than one. So we can declares 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 scopes decision remains same.

 

MULTIDIMENSIONAL ARRAYS IN VBA

If you remember the MATRICES studied in high school, same is the case with multidimensional arrays. We can define the arrays in 2 dimensions and 3 dimensions too. VBA can hold upto 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 upto 1000 values.

We have one more type of arrays

The dynamic arrays.

 

 

DYNAMIC ARRAYS IN VBA

Dynamic arrays are the ones which does 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 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 Example

ReDim Students(1 to 100) as Integer
ReDim 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 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 few examples of using arrays.

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.

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
SINGLE ARRAY EXAMPLE IN VBA
EXCEL:SINGLE ARRAY EXAMPLE 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: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.

In this example we will create an array of Weekdays and Max temperature on that day. After thisput 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.

'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
2D ARRAY EXAMPLE CODE IN VBA
EXCEL:2D ARRAY EXAMPLE 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.

RUNNING 2D ARRAY EXAMPLE CODE IN VBA
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.

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

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

'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
RUNNING 3D ARRAY EXAMPLE CODE IN VBA
EXCEL:3D ARRAY EXAMPLE 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.

3D ARRAY EXAMPLE CODE IN VBA
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 array.

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

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: