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.
- 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.
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!