Arrays Basics – Part 1

What is an array?

Array is a collection of same data type elements.

How array used in VBA?

Array is used like a variable which can store a collection of similar data type elements.

When will I need an array in my VBA programming?

When I need to use a set of values for any iterations or calculations again and again instead of taking it from individual variables it can stored under one variable.

For example, if I need to store 200 usernames for a login system automation, instead of creating 200 variables, single array variable can hold it.

This in turn saves a lot of memory during the program execution. Secondly improves the execution time of the macro.

What are the different types of array?

There are two types of arrays. They are

  • Single dimensional array
  • Multi-dimensional array

Example of single dimensional array

Employee numbers, Order Numbers, Invoice Numbers etc

Example of multi-dimensional array

Usernames and Passwords, Product Codes and Product Names, Product Codes and Product Prices

How to declare the array variable?

While declaring array variable we need to decide how many items are going to get stored inside. There are two possibilities, number of items are known, and other possibility is number of items are not known or it might be dynamic.

In an array the item stored inside is referred by its index. Imagine array like a stack, in a stack the values are stored with the position, so when we want to take a particular item from the array it can be extracted using its index. Indexing in an array by default starts from 0 or it can start from 1 based on the user need. Maximum limit is up to the user’s requirement, however each value limitation still has to follow the respective data type limitation.

Declaring a single dimensional array

Dim emp_ids (10) As Integer

The above-mentioned array will store 11 items, because by default the indexing starts from 0

Dim emp_ids (9) As Integer (or)

Dim emp_ids (1 To 10) As Integer

The above-mentioned array will store 10 items, because by default the indexing starts from 0

Declaring a multi-dimensional array

While declaring a multi-dimensional array consider like the values are stored in a table. In a table the values are stored in the form of rows and columns.

Dim emp_credentials (199,1) As String (or)

Dim emp_credentials (1 To 200, 1 To 2) As String

The above-mentioned array will store 200 rows of usernames and passwords, in a 2 column format 1st column for username and 2nd column for password

In the next post we will discuss how to use it inside a code.

Leave a Reply

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