Array in VBA UFT Excel

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.

‘Method 1: Using Dim
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

We can declare and assign values to a static array in VBA in the following way.

Output of the code:

The value stored in Array at index 1 is : Apple

 

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.

2D array Two dimensional array in vba uft

Example of Two Dimensional (2D) Array in VBA

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

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])

Arguments Description


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.

 
Example: The following example uses the Join function to join the substrings of myArray:

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]])

Arguments
 

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.

ConstantValueDescription
vbBinaryCompare0Perform a binary comparison.
vbTextCompare1Perform a textual comparison.

Example

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)

Example
The following example will check whether var1 and var2 are an array.

Output

Checking whether var1 is an Array : True
Checking whether var2 is an Array : False

Erase Function

The Erase function re-initializes the elements of fixed-size arrays and deallocates dynamic-array storage space.
 
Syntax

 

 

Erase ArrayName

The effect of Erase on fixed-array elements
  • 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

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.

Recommended Posts

Leave a Reply