Arrays Basic – Part 2

In this post we will understand how to store values in the array for our programming purpose. Two scenarios we will go through during this operation, one is we will have only a small set of values and another scenario is we will have huge list of values.

For a small set of values we can store them in the array, by directly assigning to them.

For a large set of values, it is better to keep them in the range of cells and take it from them using FOR – NEXT loop.

Scenario 1

When you have less number of values for storing in an array, assigning values to them is a good choice.

Assigning values to a single dimensional array

Here we are assigning values to a string array which was declared as

Dim usernames(3) As String

usernames(0) = “Hari”

usernames(1) = “Antony”

username(2) = “Inayath”

username(3) = “Sukhwinder”

Assigning values to a multi dimensional array

Array we have declared in this example is a string array which holds 2 rows and 3 columns of data

Dim credentials(1,2) As String

credentials(0,0) = “HR”

credentials(0,1) = “Hari”

credentials(0,2) = “Jackson”

credentials(1,0) = “Finance”

credentials(1,1) = “admin123”

credentials(1,2) = “user123”

Scenario 2

When you have a huge list of values in a row or column to store in an array or big table data to store in array, below mentioned method is a better choice.

Feeding values to an array from range of cells

Single Dimensional Array

Dim usernames(3) As String

Dim i As Byte

For i = 0 to 3

usernames( i ) = Range(“A2”).Offset( i , 0 ).Value

Next i

Multi Dimensional Array

Dim credentials(1 , 2) As String

Dim i as Byte, j as Byte

For i = 0 to 1

For j = 0 to 2

credentials(i , j) = Range(“A2”).Offset(i , j).Value

Next j

Next i

In the next posting we will understand how to retrieve/read the values stored inside an array


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.