VBA array usage case details

'''''''''''' Directly define assign an array 'Definition of a one-dimensional constant array Sub arrDemo1() Dim arr(2) As Variant' array arr(0) = "vba" arr(1) = 100 arr(2) = 3.14 MsgBox arr(0) End Sub 'Sub arrDemo2() Dim arr(1, 1) As Variant 'Dim arr(0 To 1, 0 To 1) As Variant arr(0, 0) = "apple" arr(0, 1) = "banana" arr(1, 0) = "pear" arr(1, 1) = "grape" For i = 0 To 1 For j = 0 To 1 MsgBox arr(i, j) Next Next End Sub '''''''''''' Create constant array with Array function 'one-dimensional array Sub arrayDemo3() Dim arr As Variant' array arr = Array("vba", 100, 3.14) MsgBox arr(0) End Sub 'Array Sub arrayDemo4() Dim arr As Variant' Array arr = Array(Array(" array three ", 100), Array(" Lie four ", 76), Array (" detective ", 80)) MsgBox arr (1) (1) End Sub 'calls to Excel worksheets memory Array one-dimensional Array [{" A ", 1, "C"}]' two-dimensional Array [{" A ", 10; "b", 20; "c",30}] Sub mylook() Dim arr arr = [{"a",10;"b",20; "c",30}] Range("a1:b3") = arr MsgBox Application.WorksheetFunction.VLookup("b", arr, 2, 0) 'can be called vlookup as the second argument End Sub' dynamic array definition method Sub arrDemo5() Dim arr1() 'declares a dynamic array (dynamic means not fixed size) Dim arr2' declares a variable of Variant type arr1 = Range(" A1: B2 ") 'Puts the values of cell Range A1: B2 into the array arr1 arr2 = Range("a1:b2")' Puts the values of cell range A1: The value of B2 is loaded into the array arr2 MsgBox arr1(1, 1) 'Reads the value of row 1, column 1 in the arr array MsgBox arr2(2, 2)' reads the value of row 2, column 2 in the arr1 array End Sub

'Reads cell data into array, calculates it, and assigns it to cell Sub arr_calculate() Dim arr' Declares a variable to hold cell data Dim i% arr = Range("a2:d5") 'Carries cell data into arr, It has 4 columns and 4 rows For i = 1 To 4 'and loops through the arr array arr(i, 4) = arr(i, 3) * arr(i, 2) 'Column 4 (Amount) of array = Column 3 * Example 2 Next i Range("a2:d5") = arr' Put the array back into the cell End Sub

Sub join_demo() Dim a As Variant Dim b As Variant 'Join using spaces a = Array("Red", "Blue", sub join_demo() dim a as variant dim b as variant' Join using Spaces a = array (" red ", "blue ", "Yellow") b = Join(a, "") MsgBox ("The value of b is :" & b) 'Red Bule Yellow ' Join using $ b = Join(a, "$") 'Red$Bule$Yellow MsgBox ("The Join result after using delimiter is : "&b) End Sub Sub split_demo() Dim a As Variant Dim b As Variant a = Split("Red$Blue$Yellow", "$") 'a = Array("red", "blue", "yellow") b = UBound(a) For i = 0 To b MsgBox a(i) Next End Sub

Sub arr_filter() arr = Array("ABC", "F", "D", "CA", "ER") arr1 = VBA.Filter(arr, "A", True) 'Filters all values containing A to form A new array arr2 = VBA.Filter(arr, "A", False)' Filters all values without a to form a new array MsgBox Join(arr1, ",") 'View the result of the filter End Sub

Sub arr_tranpose1() arr = Array(10, "vba", 2, "b", 3) arr1 = Application.Transpose(arr) MsgBox arr1(2, 1) 'Converted array is a 2-D array with 1 column and many rows End Sub' 2-D array to 1-D 'Note: Only an array with 1 column and N rows can be directly converted to a 1-D array when transposed Sub arr_tranpose2() arr2 = Range("A1:B5") arr3 = Application.Transpose(Application.Index(arr2, , 2)) 'Get arr2 column 2 data and transpose into a 1-dimensional array MsgBox arr3(4) End Sub' Connect the contents of the cell with "-" Sub join_transpose_demo() arr = Range("A1:C1") arr1 = Range("A1:A5") MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-") MsgBox Join(Application.Transpose(arr1), "-") End Sub

'Custom Function to get all sheet names from an array Function getSheetsname(id) Dim i%, arr() k = Sheets.Count ReDim arr(1 To k) For i = 1 To k arr(i) = Sheets(i).Name Next getSheetsname = Application.Index(arr, id) End Function

'Array assignment, Improve computing efficiency '2.03 seconds Sub dataInput() Dim start As Double start = Timer Dim i& For i = 1 To 30000 Cells(i, 1) = i Next MsgBox "Program run time is" & Format(Timer-start, "0.00") & "seconds" End Sub '0.12 seconds Sub dataInputArr() Dim start As Double start = Timer Dim i&, arr(1 To 30000) As String For i = 1 To 30000 arr(i) = i Next Range("a1:a30000").Value = Application.Transpose(arr) MsgBox "program run time is" & Format(Timer-start, "0.00") & "seconds" End Sub '0.09 seconds Sub dataInputArr2() Dim start As Double start = Timer Dim i&, arr(1 To 30000, 1 To 1) As String For i = 1 To 30000 arr(i, 1) = i Next Range("a1:a30000").Value = arr MsgBox "The program run time is" & Format(Timer-start, "0.00") & "seconds" End Sub