Arrays
We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as an array variable.
Array Declaration
Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis. In the following example, the size of the array is mentioned in the brackets.
Method 1 : Using Dim
Dim arr1() 'Without Size
Method 2 : Mentioning the Size
Dim arr2(5) 'Declared with size of 5
Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
arr3 = Array("apple","Orange","Grapes")
Assigning Values to an Array
The values are assigned to the array by specifying an array index value against each one of the values to be assigned. It can be a string.
Example:-
Private Sub Constant_demo_Click()
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript" 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("Value stored in Array index 0 : " & arr(0))
msgbox("Value stored in Array index 1 : " & arr(1))
msgbox("Value stored in Array index 2 : " & arr(2))
msgbox("Value stored in Array index 3 : " & arr(3))
msgbox("Value stored in Array index 4 : " & arr(4))
msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript" 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("Value stored in Array index 0 : " & arr(0))
msgbox("Value stored in Array index 1 : " & arr(1))
msgbox("Value stored in Array index 2 : " & arr(2))
msgbox("Value stored in Array index 3 : " & arr(3))
msgbox("Value stored in Array index 4 : " & arr(4))
msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
Multi-Dimensional Arrays
Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most commonly used ones.
Example:-
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
ReDim Statement
ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space.
Syntax:-
ReDim [Preserve] varname(subscripts) [, varname(subscripts)]
Example:-
Private Sub Constant_demo_Click()
Dim a() as variant
i = 0
redim a(5)
a(0) = "XYZ"
a(1) = 41.25
a(2) = 22
REDIM PRESERVE a(7)
For i = 3 to 7
a(i) = i
Next
'to Fetch the output
For i = 0 to ubound(a)
Msgbox a(i)
Next
End Sub
Dim a() as variant
i = 0
redim a(5)
a(0) = "XYZ"
a(1) = 41.25
a(2) = 22
REDIM PRESERVE a(7)
For i = 3 to 7
a(i) = i
Next
'to Fetch the output
For i = 0 to ubound(a)
Msgbox a(i)
Next
End Sub
Arrays Method
VBA - LBound Function
The LBound Function returns the smallest subscript of the specified array. Hence, LBound of an array is ZERO.
Syntax:-
LBound(ArrayName[,dimension])
Example:-
Private Sub Constant_demo_Click()
Dim arr(5) as Variant
arr(0) = "1" 'Number as String
arr(1) = "VBScript 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("The smallest Subscript value of the given array is : " & LBound(arr))
' For MultiDimension Arrays :
Dim arr2(3,2) as Variant
msgbox("The smallest Subscript of the first dimension of arr2 is : " & LBound(arr2,1))
msgbox("The smallest Subscript of the Second dimension of arr2 is : " & LBound(arr2,2))
End Sub
Dim arr(5) as Variant
arr(0) = "1" 'Number as String
arr(1) = "VBScript 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("The smallest Subscript value of the given array is : " & LBound(arr))
' For MultiDimension Arrays :
Dim arr2(3,2) as Variant
msgbox("The smallest Subscript of the first dimension of arr2 is : " & LBound(arr2,1))
msgbox("The smallest Subscript of the Second dimension of arr2 is : " & LBound(arr2,2))
End Sub
VBA - UBound Function
The UBound Function returns the largest subscript of the specified array. Hence, this value corresponds to the size of the array.
Syntax:-
UBound(ArrayName[,dimension])
Example:-
Private Sub Constant_demo_Click()
Dim arr(5) as Variant
arr(0) = "1" 'Number as String
arr(1) = "VBScript 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("The smallest Subscript value of the given array is : " & UBound(arr))
' For MultiDimension Arrays :
Dim arr2(3,2) as Variant
msgbox("The smallest Subscript of the first dimension of arr2 is : " & UBound(arr2,1))
msgbox("The smallest Subscript of the Second dimension of arr2 is : " & UBound(arr2,2))
End Sub
Dim arr(5) as Variant
arr(0) = "1" 'Number as String
arr(1) = "VBScript 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("The smallest Subscript value of the given array is : " & UBound(arr))
' For MultiDimension Arrays :
Dim arr2(3,2) as Variant
msgbox("The smallest Subscript of the first dimension of arr2 is : " & UBound(arr2,1))
msgbox("The smallest Subscript of the Second dimension of arr2 is : " & UBound(arr2,2))
End Sub
VBA - Split Function
A Split Function returns an array that contains a specific number of values split based on a delimiter.
Syntax:-
Split(expression[,delimiter[,count[,compare]]])
Example:-
Private Sub Constant_demo_Click()
' Splitting based on delimiter comma '$'
Dim a as Variant
Dim b as Variant
a = Split("Red $ Blue $ Yellow","$")
b = ubound(a)
For i = 0 to b
msgbox("The value of array in " & i & " is :" & a(i))
Next
End Sub
' Splitting based on delimiter comma '$'
Dim a as Variant
Dim b as Variant
a = Split("Red $ Blue $ Yellow","$")
b = ubound(a)
For i = 0 to b
msgbox("The value of array in " & i & " is :" & a(i))
Next
End Sub
VBA - Join Function
A Function, which returns a string that contains a specified number of substrings in an array. This is an exact opposite function of Split Method.
Syntax:-
Join(List[,delimiter])
Example:-
Private Sub Constant_demo_Click()
' Join using spaces
a = array("Red","Blue","Yellow")
b = join(a)
msgbox("The value of b " & " is :" & b)
' Join using $
b = join(a,"$")
msgbox("The Join result after using delimiter is : " & b)
End Sub
' Join using spaces
a = array("Red","Blue","Yellow")
b = join(a)
msgbox("The value of b " & " is :" & b)
' Join using $
b = join(a,"$")
msgbox("The Join result after using delimiter is : " & b)
End Sub
VBA - Filter Function
A Filter Function, which returns a zero-based array that contains a subset of a string array based on a specific filter criteria.
Syntax:-
Filter(inputstrings,value[,include[,compare]])
Example:-
Private Sub Constant_demo_Click()
Dim a,b,c,d as Variant
a = array("Red","Blue","Yellow")
b = Filter(a,"B")
c = Filter(a,"e")
d = Filter(a,"Y")
For each x in b
msgbox("The Filter result 1: " & x)
Next
For each y in c
msgbox("The Filter result 2: " & y)
Next
For each z in d
msgbox("The Filter result 3: " & z)
Next
End Sub
Dim a,b,c,d as Variant
a = array("Red","Blue","Yellow")
b = Filter(a,"B")
c = Filter(a,"e")
d = Filter(a,"Y")
For each x in b
msgbox("The Filter result 1: " & x)
Next
For each y in c
msgbox("The Filter result 2: " & y)
Next
For each z in d
msgbox("The Filter result 3: " & z)
Next
End Sub
VBA - IsArray Function
The IsArray Function returns a boolean value that indicates whether or NOT the specified input variable is an array variable.
Syntax:-
IsArray(variablename)
Example:-
Private Sub Constant_demo_Click()
Dim a,b as Variant
a = array("Red","Blue","Yellow")
b = "12345"
msgbox("The IsArray result 1 : " & IsArray(a))
msgbox("The IsArray result 2 : " & IsArray(b))
End Sub
Dim a,b as Variant
a = array("Red","Blue","Yellow")
b = "12345"
msgbox("The IsArray result 1 : " & IsArray(a))
msgbox("The IsArray result 2 : " & IsArray(b))
End Sub
VBA - Erase Function
The Erase Function is used to reset the values of fixed size arrays and free the memory of the dynamic arrays. It behaves depending upon the type of the arrays.
Syntax:-
Erase ArrayName
Example:-
Private Sub Constant_demo_Click()
Dim NumArray(3)
NumArray(0) = "VBScript"
NumArray(1) = 1.05
NumArray(2) = 25
NumArray(3) = #23/04/2013#
Dim DynamicArray()
ReDim DynamicArray(9) ' Allocate storage space.
Erase NumArray ' Each element is reinitialized.
Erase DynamicArray ' Free memory used by array.
' All values would be erased.
msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
msgbox("The value at First index of NumArray is " & NumArray(1))
msgbox("The value at Second index of NumArray is " & NumArray(2))
msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub
Dim NumArray(3)
NumArray(0) = "VBScript"
NumArray(1) = 1.05
NumArray(2) = 25
NumArray(3) = #23/04/2013#
Dim DynamicArray()
ReDim DynamicArray(9) ' Allocate storage space.
Erase NumArray ' Each element is reinitialized.
Erase DynamicArray ' Free memory used by array.
' All values would be erased.
msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
msgbox("The value at First index of NumArray is " & NumArray(1))
msgbox("The value at Second index of NumArray is " & NumArray(2))
msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub