Array
An array is a data structure that contains a group of elements. Typically these elements are all of the same data type, such as an integer or string. index of an array start from the 0, that mean if you declare an array of size 10 , you can store 11 values in that array because first value will store on 0 index and so on.Declaration of Array
an array is declared same way as variable, there is minor change in declaration of an array, to declare a variable as array you need to use parenthesis.
|
'Method 1 : Using Dim
Dim vari() 'Without
Size of array
'Method 2 : with the Size of the array
Dim vari(5) 'Declared with
size of array Is 5
'Method 3 : using 'Array' Parameter
Dim arr3
vari = Array("red","Orange","Green")
|
Single dimension array
Single dimension array
|
Sub demo_array()
Dim arr(5)
arr(0) = "2" 'Number as String
arr(1) =
"define" 'String
arr(2) = 6 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #02/28/1989# 'Date
arr(5) = #12.45 PM# 'Time
for i= 0 to 6
debug.print ("Value stored
in Array index 5 : " & arr(i))
End Sub
|
|
Value stored in Array index 0: 2
Value stored in Array index 1: define
Value stored in Array index 2: 6
Value stored in Array index 3: 2.45
Value stored in Array index 4: 2/28/1989
Value stored in Array index 5: 12:45:00 PM
|
Two or Multi Dimension array
An array of arrays is known as 2D array. The two dimensional (2D) array in VBA it is also known as matrix. A matrix can be represented as a table of rows and columns.
|
Sub demo_multidim_array()
Dim arr(2, 2) As Variant
' Which has 3 rows and 3
columns size is 2 but index start with 0, hence 3 value will be stored
arr(0, 0) =
"Apple"
arr(0, 1) =
"Orange"
arr(0, 2) =
"Grapes"
arr(1, 0) =
"cucumber"
arr(1, 1) =
"beans"
arr(1, 2) =
"carrot"
arr(2, 0) =
"potato"
arr(2, 1) =
"sandwitch"
arr(2, 2) =
"coffee"
Debug.Print "Value in
Array index 0,1 : " & arr(0, 1)
Debug.Print "Value in
Array index 2,2 : " & arr(2, 2)
End Sub
|
|
Value in Array index 0,1 : Orange
Value in Array index 2,2 : coffee
|
ReDim Statement
ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space. it is declare later when we know what size we required for our array
Syntax
|
ReDim [Preserve] varname(subscripts) [, varname(subscripts)]
|
Parameter Description
- Preserve − An optional parameter used to preserve the data in an existing array when you change the size of the last dimension.
- Varname − A required parameter, which denotes the name of the variable, which should follow the standard variable naming conventions.
- Subscripts − A required parameter, which indicates the size of the array
|
Sub demo_redim()
Dim a() As Variant ' size is
not declared
i = 0
ReDim a(3) ' we are redim the
array with size of 2
a(0) = "XYZ"
a(1) = 41.25
a(2) = 22
Debug.Print "value of
arry 0 is " & a(0)
Debug.Print "value of
arry 1 is " & a(1)
Debug.Print "value of
arry 2 is " & a(2)
End Sub
|
|
value of arry 0 is XYZ
value of arry 1 is 41.25
value of arry 2 is 22
|
Array Methods
There are various inbuilt functions within VBA which can help us to handle arrays effectively.
|
Function
|
Description
|
|
LBound
|
A Function, which returns an integer that corresponds to the
smallest subscript of the given arrays.
|
|
UBound
|
A Function, which returns an integer that corresponds to the
largest subscript of the given arrays.
|
|
Split
|
A Function, which returns an array that contains a specified
number of values. Split based on a delimiter.
|
|
Join
|
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.
|
|
Filter
|
A Function, which returns a zero based array that contains a
subset of a string array based on a specific filter criteria.
|
|
IsArray
|
A Function, which returns a boolean value that indicates whether
or not the input variable is an array.
|
|
Erase
|
A Function, which recovers the allocated memory for the array
variables.
|
0 Comments