Lesson 3 Arrays

When we work with a single item in Excel VBA 365, we only need to declare one variable. However, if we need to deal with a list of items, we need to declare an array of variables instead of using a variable for each item. For example, if we need to enter 100 names, instead of declaring 100 different variables, we need to declare only one array.

By definition, an array is a group of variables with the same data type and name. We differentiate each item in the array by using subscript, the index value of each item. For example Studentname (1), Studentname (2), Studentname (3) ……..Studentname(n)

3.1 Declaring an Array

We use the Dim statement to declare an array just as the way we declare a single variable. In Excel VBA 365 we can have a one-dimensional array, two-dimensional array or even a multidimensional array (up to 60)

3.2 One-Dimensional Array

The statement to declare a one-dimensional array in Excel VBA 365 is as follows:

Dim arrayName(index) as dataType or Dim arrayName(first index to last index) as dataType

For example, we can use the following statement to declare an array that comprises 10 elements.

 Dim StudentName(10) as String
 Dim StudentName(1 to 10) as String
 Dim StudentMark(10) as Single
 Dim StudentMark( 1 to 10) as Single
 

Example 3.1

In this example, we define an array StudentName of five strings using the Dim keyword. We include an InputBox to accept input from the user. We also use the For …Next loop to accept the input five times and display the five names from cell A1 to cell E1. The code is as follows:

Private Sub Button1_Click( )
Dim StudentName(1 to 5) As String
 For i = 1 To 5
  StudentName(i) = InputBox(“Enter student Name”)
  Cells(i, 1) = StudentName(i)
 Next
End Sub

* You can also declare the array using Dim StudentName(5) As String When we run the program, an input box will appear, as shown below. This input box will repeat five times and let the user enter five names.

The five names will be displayed in the spreadsheet as shown below:

You can also declare more than one array on a single line. In this example, we declare three arrays in a single line, separated by commas.

Private Sub CommandButton1_Click( )
Dim StudentName(3) As String, StudentID(3) As String, StudentMark(3) As Single
 For i = 1 To 3 StudentName(i) = InputBox(“Enter student Name”)
  StudentID(i) = InputBox(“Enter student ID”)
  StudentMark(i) = InputBox(“Enter student Mark”)
  Cells(i, 1) = StudentName(i)
  Cels(i, 2) = StudentID(i)
  Cells(i, 3) = StudentMark(i)
 Next
End Sub

When we run the program, three input boxes will appear consecutively to let the user enter the student name, the student ID and then the student mark. The process will repeat three times until the particulars of all three students have been entered. The three input boxes and the output images are shown below:

The Output display is shown in the following figure:

3.3 Two-Dimensional Aarray

Multidimensional arrays are often needed when we are dealing with a more complex database, especially those that handle a large amount of data. Data are usually organized and arranged in table form, this is where the multidimensional arrays come into play. However, in this tutorial, we are dealing only with the two-dimensional array. A two-dimensional array can be represented by a table that contains rows and columns, where one index represents the rows and the other index represent the columns.

The statement to declare a two-dimensional array is

Dim arrayName (num1, num2) as datatype

Where num1 is the suffix of the first dimension of the last element and num2 is the suffix of the second dimension of the last element in the array. The suffixes of the element in the array will start with (0, 0) unless you set the Option Base to 1. In the case when the Option Base is set to 1, then the suffixes of the element in the array will start with (1, 1). For example,

Dim Score (3, 3) as Integer

will create a two-dimensional array consists of 16 elements. These elements can be organized in a table form as shown in the table below: