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.

What You Will Learn?

Types of Arrays in VBA

VBA does have two types of arrays.

  • StaticArray – 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 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.
Интересно почитать:  Excel поиск последнего значения в строке

Static Array in VBA

Assigning Values to an Array

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.

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.

Ссылка на основную публикацию