Array in VBA – In this tutorial, I will show you how to use arrays in VBScript. Let me first explain you what is an array in VBScript. An array in VB is a collection of elements, in another way we can say that it is a special type of variable that is used to store more than one value in a single variable. An example of an array could be storing all month names in a single array variable.
Types of Arrays in VBA
VBA does have two types of arrays.
- Static Array – The length of a static array is fixed. It can store a predetermined number of elements in it. Once the size of the array is defined, it can’t be changed later on. A static array is very beneficial when we’ve got an estimation of what number of elements could be stored in it.
- Dynamic Array – Unlike a static array, we can change the size of the dynamic array at runtime. We will learn more about dynamic arrays in VBA at later part of this tutorial.
Declare an Array in VBA
We can declare an array in VBA in the following ways.
Dim myArray1() ‘Without defining the Size of the array
‘Method 2: Array with predefined size
Dim myArray2(10) ‘Declared with the size of 10
‘Method 3: Declaring and assigning values to Array
Dim myArray3
myarray3= Array(“India”, “Japan”, “USA”, “Russia”)
- The index of an array starts from 0. It means if the size of an array is 10, it can store 11 elements.
- Arrays in VBA can store values of different data types. Hence, an array in VBScript can store an integer, a string, a floating in a variable and etc, in a single array variable.
Static Array in VBA
Assigning Values to an Array
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Dim arrVar(3) 'Declaring array of size 3 'Storing values to array arrVar(0) = "Apple" 'String arrVar(1) = 25 'Integer arrVar(2) = 2.45 'Decimal Value arrVar(3) = #15/8/2021# 'Date MsgBox ("Value stored in Array at Index 0 is : " & arrVar(0)) MsgBox ("Value stored in Array at Index 1 is : " & arrVar(1)) MsgBox ("Value stored in Array at Index 2 is : " & arrVar(2)) MsgBox ("Value stored in Array at Index 3 is : " & arrVar(3)) |
Output of the code:
The value stored in Array at index 1 is : 25
The value stored in Array at index 1 is : 2.45
The value stored in Array at index 1 is : 15/8/2021
You will get error “Subscript out of Range” if you try to access a location that does not exist in the array
Two Dimensional Array in VBA
There could be a multidimensional array out of which, two-dimensional are most commonly used. The following image shows how elements are stored in a two-dimensional in VBA.
Example of Two Dimensional (2D) Array in VBA
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Dim arrCountry(2, 3) ' Declaring 2D array having 3 rows and 4 columns arrCountry(0, 0) = "India" arrCountry(0, 1) = "United States" arrCountry(0, 2) = "Greece" arrCountry(0, 3) = "Spain" arrCountry(1, 0) = "Australia" arrCountry(1, 1) = "Turkey" arrCountry(1, 2) = "France" arrCountry(1, 3) = "Greece" arrCountry(2, 0) = "Germany" arrCountry(2, 1) = "Malaysia" arrCountry(2, 2) = "Russia" arrCountry(2, 3) = "Japan" 'Displaying all elements of two-dimensional array using for loop For i = 0 To 2 For j = 0 To 3 MsgBox ("The value stored in arrCountryay at index (" & i & "," & j & ") : " & arrCountry(i, j)) Next Next 'Displaying specific array elements MsgBox ("The value stored in arrCountryay at index 0,3 : " & arrCountry(0, 3)) 'Dispalys Spain MsgBox ("The value stored in arrCountryay at index 2,0 : " & arrCountry(2, 0)) ''Dispalys Germany |
Dynamic Array in VBA
Dynamic arrays do not have a pre-defined number of elements. These are quite useful when we can’t predict the exact size of the array well in advance. Dynamic arrays can be resized at runtime by using the “ReDim” statement.
Declaring a Dynamic Array in VBA
Dynamic arrays are declared without providing the initial size of the array. The syntax for declaring dynamic arrays is as follows:
Dim myArr() ‘Declares an array without specifying its size
ReDim myArr(5) ‘Sets the size of array after the array has been defined
ReDim Statement in VBA
The ReDim statement declares dynamic array variables and allocate or reallocate storage space at runtime. We can increase or decrease the size of an array using ReDim statement. If we use the ReDim statement along with Preserved keyword and increase the size the array the existing elements of the array remain intact in it. However, if we decrease the size of the array than it was originally, data in the eliminated elements is lost. So be careful whenever you decrease the size of the array.
If the ReDim statement is used without Preserved keyword, all previous elements of the array will be lost.
Example of ReDim Preserved
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Dim arr() ReDim arr(2) arr(0) = "VBScript" arr(1) = "Java" arr(2) = "Python" ReDim Preserve arr(5) arr(3) = "4th-Element in extended array" arr(4) = "5th-Element in extended array" arr(5) = "6th-Element in extended array" For i = 3 To 5 MsgBox arr(i) 'Display elements that are added after resizing the array Next |
Traverse an array using For Each Loop: VBScript Loops: Do Loop, For Loop, For Each, and While Loop
We can reinitialize the size of the same array using ReDim statement multiple times whenever it is required.
Array Functions in VBA
Join Function: Joining Elements of an Array to a String
The Join function returns a string created by joining a number of substrings contained in an array.
Syntax
Join(List[, delimiter])
List
Required Parameter. A one-dimensional array containing substrings to be joined.
Delimiter
Optional Parameter. String character used to separate the substrings in the returned string. The default delimiter is Space (” “). If delimiter is a zero-length string, all items in the list are concatenated with no delimiters.
|
1 2 3 4 5 6 7 |
myArray = Array("Onion", "Potato", "Tomato") myString = Join(myArray) MsgBox ("The value of myString variable is: " & myString ) ' Joining array using the delimeter ; myString = Join(myArray, ";") MsgBox ("The value of myString variable is: " & myString ) |
Output:
The value of myString variable is: Onion Potato Tomato
The value of myString variable is: Onion;Potato;Tomato
Split Function: Create an array by splitting a String based on a delimiter
Refer: Most Useful VBScript Functions – Split Function
Ubound Function: Get largest subscript of the specified array
Refer: Most Useful VBScript Functions – Ubound Function
Filter Function: Get certain elements from an array by specific filter criteria
The Filter function returns a zero-based array containing a subset of a string array based on a specified filter criteria.
Syntax
Filter(InputStrings, Value[, Include[, Compare]])
InputStrings
Required Parameter. One-dimensional array of strings to be searched for filtering.
Value
Required Parameter. String to search against the Inputstrings parameter.
Include
Optional Parameter. This is a Boolean value indicating whether to return substrings that include or exclude Value. If Include is True, Filter returns the subset of the array that contains Value as a substring. If Include is False, Filter returns the subset of the array that does not contain Value as a substring.
Compare
Optional Parameter. Numeric value indicating the kind of string comparison to use.
| Constant | Value | Description |
|---|---|---|
| vbBinaryCompare | 0 | Perform a binary comparison. |
| vbTextCompare | 1 | Perform a textual comparison. |
Example
|
1 2 3 4 5 6 |
myArr1 = Array("Subhas", "Karan", "Suman", "Simran", "Samadra") myArr2 = Filter(myArr1, "Su") 'Creating new array with elements starting with "Su" characters For Each x In myArr2 MsgBox ("Flitered Array Element starting with characters <<Su>> is: " & x) Next |
Output:
Flitered Array Element starting with <Su> is: Subhas
Flitered Array Element starting with <Su> is: Suman
IsArray Function: Check whether a variable is an Array
The IsArray function returns a Boolean value indicating whether a variable is an array.
Syntax
IsArray(varname)
|
1 2 3 4 5 6 |
Dim var1, var2 var1 = Array("Apple", "Mango", "Orange", "Grapes") var2 = "Hello" MsgBox ("Checking whether var1 is an Array : " & IsArray(var1)) MsgBox ("Checking whether var2 is an Array : " & IsArray(var2)) |
Output
Checking whether var1 is an Array : True
Checking whether var2 is an Array : False
Erase Function
Erase ArrayName
- Fixed numeric array – Sets each element of the Array to zero.
- Fixed string array – Sets each element to zero-length (“”).
- Array of objects – Sets each element to the special value Nothing.
Example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Dim numArray(1), stringArray(1) stringArray(0) = "Excel" stringArray(1) = "Word" numArray(0) = 10 numArray(1) = 20.50 Dim DynamicArray() ReDim DynamicArray(4) ' Allocate storage space. Erase numArray ' Each element is reinitialized. Erase stringArray Erase DynamicArray ' Free memory used by array. ' All values of the array would be erased. MsgBox ("The value at 0 index of numArray is: " & numArray(0)) MsgBox ("The value at 1 index of numArray is: " & numArray(1)) MsgBox ("The value at 0 index of stringArray is: " & stringArray(0)) MsgBox ("The value at 1 index of stringArray is: " & stringArray(1)) |
Output
The value at 0 index of numArray is:
The value at 1 index of numArray is:
The value at 0 index of stringArray is:
The value at 1 index of stringArray is:
Conclusion
We should use arrays inefficient way to store a list of items on the same variable. We can access an array element directly using its index also knows as the subscript. We have also seen two types of arrays: Static and Dynamic. Arrays are very important kind of variable in all programming languages and one should learn it thoroughly. Hope you will get most of the things out of it. If you find this article useful, please don’t forget to share it and like it.
