One Dimensional Array in Excel

Enter your search terms Submit search form




Today we will deal with VBA in Excel once more. This time I will show you how to create a one dimensional array. We declare an array using the following format:

  Dim ArrayName(N)  as String, where N+1  is the number of elements that the array can hold in the memory.

For example, Dim  CarName(9)  as String  will reserve 10 elements in the memory.  The elements are  CarName(0), CarName(1),CarName(2).............CarName(9).

However, if we add he Option Explicit statement to the general procedure, the the elements in the array will start with index 1.

For example      Option Explicit

                       Dim  StudentName(10) as String  will create elements

StudentName(1), StudentName(2),.............StudentName(10)

Example on the right illustrates the above concepts.

Private Sub CommandButton1_Click()
Dim row As Integer
Dim country(10) As String
country(1) = "USA"
country(2) = "Russia"
country(3) = "China"
country(4) = "UK"
country(5) = "France"
country(6) = "India"
country(7) = "Germany"
country(8) = "Japan"
country(9) = "Italy"
country(10) = "Canada"
For row = 1 To 10
Cells(row, 1).Value = country(row)
End Sub






 [Back to VBToday]