VBA Array Length : How to Efficiently Assign and Use Array Lengths

Visual Basic for Applications is an important object oriented and event driven programming language. Arrays form an important part of VBA. If you want to handle large amount of data arrays are required. Today, in this intermediate level tutorial, we walk you through the concept of array length. We assume that you are familiar with VBA and basics of programming. If not, you can take this introductory course on Excel VBA or go through this excellent tutorial that will help give you an overview of VBA

What is an Array

An array is a group of variables which share the same data type and name.When we work with a single item only one variable is needed. However, if we have a list of items of similar types it does not make sense to declare a variable for each item. It’s better if we can declare an array of variables. For example, if we need one hundred names of employees, instead of declaring 100 different variables, it’s better to declare only one array.By default, the subscripts of VBA arrays start at 0.This is known as the lower bound of the array. It runs up to the number you specify in the Dim statement and is known as the upper bound of the array. Here are a few things that you need to know about arrays.

• Array size

Array size limit depends on its data type and available memory. The available memory depends on your system. Common data types include Integer, long Integer and Byte. An interesting bit of information for you – if you’re into programming details – integer data types are automatically converted to long integers by VBA.

• Array Length

The Array.Length property gives the number of elements that the array contains. It is different from the amount of memory storage required by the array. You can learn more about array handling in VBA with this awesome course.

Array Types

• Single Dimensional Array

The array which represents and stores data in a linear form is known as single or one dimensional array.The syntax of VBA array looks like this:

Dim arrayName(index) as dataType
Dim EmpName(10) as String
Dim EmpName(1 to 10) as String

• Multidimensional Array

It is an array containing one or more arrays. Two dimensional arrays can be represented by a table having rows and columns. Here one index represents the rows and the other represents the columns. The syntax of two dimensional array looks like this. The image below makes this concept clear.

Dim arrayName (num1, num2) as datatype
For example,   Dim Score (3, 3) as Integer

Why Use Dynamic Arrays?

What should you do if you’re not sure how many subscripts you would need in an array? You could guess at the correct number, but that will almost always make you face one of the following problems.  If your guess is too low and you try to access a subscript higher than the array’s upper bound, VBA will generate an error message.    If your guess is too high, VBA will still allocate memory to the unused portions of the array, so you’ll waste precious system resources. The solution is to use dynamic arrays.

• Dynamic Arrays– Dynamic arrays can change in dimension when a function runs. In other words, it can change the number of variables.
• Static Arrays-A static array is allocated memory at compiled time and exists for the entire duration of the program.

Let’s take a look at a simple single dimensional Array program. If you’d like a peak into other examples, you can always refer to this Excel VBA course by Mr Excel.

Example 1:

Sub CommandButton1_Click( )

 Dim EmpName(5) As String

 For i = 1 To 5

EmpName(i) = InputBox("Enter Employee Name")

Cells(i, 1) = EmpName(i)

 Next

End Sub

In this program, we declare EmpName to be an array of size 5 and data type string. Using the For loop, we iterate through the array and initialize it to different employee names.

Example: 2 Declaring More Than One Array in a Single Line.

It’s possible to declare more than one array in a single line. To see how that’s done, take a look at this program

 Sub CommandButton1_Click( )
   Dim EmpName(3) As String, EmpID(3) As String, Empsal(3) As Single
    For i = 1 To 3
EmpName(i) = InputBox("Enter Employee Name")
EmpID(i) = InputBox("Enter Employee ID")Empsal(i) = InputBox("Enter Employee Salary")
Cells(i, 1) = EmpName(i
Cells(i, 2) = EmpID(i)
Cells(i, 3) = EmpSal(i)
    Next
End Sub

In this program, three arrays are declared.  All of them have length three. EmpName and EmpIdare declared as string arrays.WhileEmpSal is a integer array. We use a For loop to iterate through the arrays and initialize the array elements to the respective values.

Example 3: Multi-Dimensional Array

If a company wants to track the performance of 5 newly joined employees over a period of 2 days, you can create a 5×2 array in Excel VBA, denoted by a 5X 2 table in a spreadsheet.Take a look at this program to make it clearer

   Sub CommandButton1_Click()
    Dim SalesVolume(2to 6, 2 to 3) as Single
    Dim Emp as Integer, Day as Integer
           For Emp=2 to 6
               For Day=2 to3
SalesVolume(Emp, Day)=inputbox("Enter Sales Volume")
Cells(Emp, Day)=SalesVolume(Emp,Day)
             Next Day
          Next Emp
    End Sub

In this program, twoFor loops are used accept the sales volumes. Then they are displayed in the spreadsheet next to the employee names.

Example 4: Program to Calculate Average in VBA Using Array

Function average(ListOfNumbers() as double, length as integer)
Dim x as integer
for counter1 = 0 to (Length - 1)
x = x + ListOfNumbers(counter1)
next
Average = x / Length
End Sub
Dim array (3) as double
array (0) = 3
array (1) = 4
array (2) = 5
A = Average(array, length)

In this program, we use the for loop to add the total value of the variables. Finally, we divide the total value by the length of the array. The result is the average. Of course, instead of using this program you could always directly use the Average function. Check out this Excel VBA course to see how it’s done.

Example 5:  Reading from a Worksheet to a VBA Array

Dim Arr() As Variant
Arr = Range("B1:C10")
Dim R1As Long
Dim C1As Long
For R1 = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C1 = 1 To UBound(Arr, 2) ' Second array dimension is columns.
Debug.PrintArr(R1, C1)
    Next C1
Next R1

This program takes data from a worksheet and uses a for loop to print out the values. If your data range is a single cell, you can use this program instead.

 Dim Arr() As Variant
Dim RangeNameAs String
Dim R1As Long
Dim C1As Long
Dim RR1As Range
RangeName = "TheRange"
Set RR1 = Range(RangeName)
If RR1.Cells.Count = 1 Then
ReDimArr(1 To 1, 1 To 1)
Arr(1, 1) = RR1.Value
Else
Arr = Range(RangeName)
End If

Example 6: Program to Store the Name of Worksheets in a Dynamic Array

Remember we spoke of dynamic arrays? When you do not know in advance, the size of the array you’ll need, it’s best to go for a dynamic array. In the next example, we show you how to use a dynamic array to store names.

 Sub StoreWorksheetNames1()
    Dim sheetNames() As String
    Dim totalSheets As Integer
    Dim sheet As Worksheet
    Dim i As Integer
    Dim strMessage As String
totalSheets = ActiveWorkbook.Worksheets.Count
ReDimsheetNames(totalSheets)
       For i = 1 TototalSheets
sheetNames(i - 1) = ActiveWorkbook.Worksheets(i).Name
    Next
strMessage = "Here are the worksheet names:" &vbCrLf
    For i = 0 TototalSheets - 1
strMessage = strMessage&sheetNames(i) &vbCrLf
    Next
MsgBoxstrMessage
End Sub

The best way to learn programming is by trying it out for yourself. So make sure you get lots of practice. If you get stuck at any point, you can always hop over to this Ultimate VBA course to help you out!