Suggestions  Arrays 
The structure

1. What is an array An array is a collection of separate items. An array variable is a variable in which you can store those separate items. Strings, numbers, dates, VBAobjects, arrays, etc. can be stored in an array. You can get access to those items in an array for reading, adapting, replacing, storing. The array is in the memory of the computer; that improves the speed to access it. Compared to working with a worksheet in Excel it can be much faster. Adapting a worksheet can trigger Screenupdating, Calculation, Event procedures, that might slow down performance. 2. Array variable names You can assign any valid variable name to an array that contains an array. It can be practical to use a distinguishable name convention for array variables (e.g. 'a_sn', 'a_sp', 'a_sq', etc.). If you use an underscore _ in a variable name you can be sure it won't interfere with a 'reserved name' in VBA or in an application. 3. Kind of arrays VBA distinguishes 1dimensional and multidimensional arrays 3.1 1dimensional array In the array several items/elements are being stored in a certain order. You can consider a 1dimensional array as a pile of equally sized coins. If you look at it from above you only see one coin but you know it conceals several coins beneath it. An element in the array can be read using its serial number: e.g. a_sn(2), a_sn(7) 3.2 multidimensional array A 2dimensional array can be compared to a table or a worksheet in Excel. Each element is being positioned in the array, according to 2 dimensions. You can retrieve an element from a 2dimensional array using the serial number in each dimension; e.g. a_sn(2,4), a_sn(10,1) You can compare a 3dimensional array to a cube. Each element has its position in the array based on the 'row', 'column' and 'depth'dimension. Retrieve an element from a 3dimensional array by its serial number in each dimension; e.g.. a_sn(2,4,5), a_sn(10,1,10) An array in VBA can have 60 dimensions maximally. How to imagine multidimensional arrays ? Take Excel as an example: 2dimensional array A worksheet is a 2dimensional array. You can find a cell specifying its horizontal dimension (row) and its vertical dimension (column): cells(4,10) is equivalent to cell J4. 3dimensional array A third dimension is the number of worksheets in a workbook: sheets(3).cells(4,10) Written as a 3dimensional array: a_sn(4,10,3) The first dimension represents the rows, the second dimension represents the columns and the third dimension the worksheets. 4dimensional array The number of opened workbooks can be considered as a fourth dimension workbooks(7).sheets(3).cells(4,10) Written as a 4dimensional array: a_sn(4,10,3,7) 5dimensional array and more  Imagine: the number of files in a folder as a fifth dimension  The number of subfolders in a folder as a sixth dimension  The number of folders in a Drive as seventh dimension  The number of drives as an 8th dimension, etc. 4 Array properties 4.1 The lower limit: Lbound The default lower limit of an array is 0. Although it might seem counter intuitive mathematically speaking it's the most logical value. In a 10digit system 0 is the first number. The first element  in a 1dimensional array: a_sn(0),  in a 2dimensional array: a_sn(0,0)  in a 3dimensional array: a_sn(0,0,0). You can arrange that the default lower limit is 1 (Option base 1). To retrieve the lower limit an array, use Lbound: x=Lbound(a_sn) In a multidimensional array you have to specify the dimension you want its lower limit to be shown  the lower limit of the first dimension: Lbound(a_sn,1) or Lbound(a_sn)  the lower limit of the second dimension: Lbound(a_sn,2)  the lower limit of the third dimension: Lbound(a_sn,3) 4.2 The upper limit: Ubound Retrieve the upper limit (Ubound) of a 1dimensional array x=Ubound(a_sn) Since the default Lbound is 0, an array contains Ubound(a_sn)+1 elements. In multidimensional arrays you have to specify the dimension you want its upper limit to be shown The upper limit  of the first dimension: Ubound(a_sn,1) of Ubound(a_sn)  of the second dimension: Ubound(a_sn,2)  of the third dimension: Ubound(a_sn,3) The first dimension with lower limit 0 contains Ubound(a_sn,1) + 1 (of Ubound(a_sn)+1) elements The second dimension with lower limit 0 contains Ubound(a_sn,2) + 1 elements The third dimension with lower limit 0 contains Ubound(a_sn,3) + 1 elements 4.3 The array type: Vartype Vartype shows whether a variable is an array or contains an array, and if so what kind of items it holds. The Vartype number of an array is 8192. Vartype is the sum of the Vartype of an array (8192) and the Vartype of its content type. De Vartypenumbers of the distinct content types; for a normal variabele and after () for an array variable.
a_sn= Array(1, 2, 3, 4) a_sp= Split("1,2,3,45", ",") a_sq= Range("A1:F5") dim a_st As Shape x = VarType(a_sn) ' 8204 = 8192 (array) + 12 (variant) x = VarType(a_sp) ' 8200 = 8192 (array) + 8 (string) x = VarType(a_sq) ' 8204 = 8192 (array) + 12 (variant) x = VarType(a_st) ' 8201 = 8192 (array) + 9 (object) 4.4 The Typename of an array Typename shows the kind of array in a variable. a_sn= Array(1, 2, 3, 4) a_sp= Split("1,2,3,45", ",") a_sq= Range("A1:F5") x = TypeName(a_sn) ' Variant() x = TypeName(a_sp) ' String() x = TypeName(a_sq) ' Variant() x = TypeName(Range("A1:F5")) ' Range x = TypeName(Range("A1:F5").Value) ' Variant Unlike VarType Typename doesn't interpret a Range as an array. If you assign a Range to a variable its default property  Value  is stored in the variable; so this variable will be interpreted as an array. 4.5 Check whether a variable is an array: IsArray a_sn= Array(1, 2, 3, 4) a_sp= Split("1,2,3,45", ",") a_sq= Range("A1:F5") a_sr = Range("F5") a_st="aaa" y = IsArray(a_sn) ' True y = IsArray(a_sp) ' True y = IsArray(a_sq) ' True y = IsArray(a_sr) ' False y = IsArray(a_st) ' False IsArray interprets a Range in a worksheet as an array, unless the range consists of only 1 cell. 4.6 Redim For the use of Redim see 5.3.1.3 and 5.3.1.4 4.7 Erase MS calls Erase a 'Statement'. 4.7.1 Variant variable containing an array Erase removes the size of an array that has been assigned to a variant variabele. a_sn=Array(2,55,73,822,200) msgbox Ubound(A_sn) Erase a_sn After the application of Erase the variable a_sn, with Ubound(a_sn)=4, has no longer a Ubound or lbound. Erase removes the size of an array that has been declared as Variant. Dim a_sn a_sn=Array(2,55,73,822,200) msgbox ubound(A_sn) erase a_sn Aftr the application of Erase the variabele a_sn, with Ubound(a_sn)=4, has no longer a Ubound or lbound. 4.7.2 Dynamic array Erase removes the size of a dynamic array that has been declared by Dim. Dim a_sp() ReDim a_sp(2, 2) For j = 0 To UBound(a_sp) For jj = 0 To UBound(a_sp, 2)
Nexta_sp(j, jj) = j * jj
NextMsgBox VarType(a_sp) & vbTab & UBound(a_sp) & vbTab & UBound(a_sp, 2) Erase a_sp After the application of Erase variable a_sp, with Ubound(a_sp)=2 and Ubound(a_sp,2)=2, has no longer a Ubound for both dimensions. Erase removes the size of a dynamic array that has been declared by ReDim. ReDim a_sp(2, 2) For j = 0 To UBound(a_sp) For jj = 0 To UBound(a_sp, 2)
Nexta_sp(j, jj) = j * jj
NextMsgBox VarType(a_sp) & vbTab & UBound(a_sp) & vbTab & UBound(a_sp, 2) Erase a_sp After the application of Erase variable a_sp, with Ubound(a_sp)=2 and Ubound(a_sp,2)=2, has no longer a Ubound for both dimensions. 4.7.3 Fixed size Array Erase removes the content (not the size !) of a fixed size declared array. Dim a_sq(5, 10) For j = 0 To UBound(a_sq) For jj = 0 To UBound(a_sq, 2)
Nexta_sq(j, jj) = j * jj
NextMsgBox VarType(a_sq) & vbTab & UBound(a_sq) & vbTab & UBound(a_sq, 2) MsgBox a_sq(0, 0) & vbLf & a_sq(UBound(a_sq), UBound(a_sq, 2)) Erase a_sq MsgBox a_sq(0, 0) & vbLf & a_sq(UBound(a_sq), UBound(a_sq, 2)) MsgBox VarType(a_sq) & vbTab & UBound(a_sq) & vbTab & UBound(a_sq, 2) After the application of Erase the variabele a_sq has no contents, but the Ubound of each dimension is still intact. 5. How to create an array ? VBA has 5 methods to create an array  the VBA method 'Array'  the VBA method 'Split'  declare an array variable  assign the content of an object (in VBA, Excel of Word) with array like properties to a variable  pass arguments to a macro or functions, that stores the arguments in a paramarray. 5.1 VBA method 'Array' This method does 2 things simultaneously: it defines an array and fills it with items. You can only produce 1dimensional arrays with this method. With this method you can store strings, numbers, dates, objects, arrays and every combination of these. VBAtechnically The resulting array variable TypeName: Variant() Vartype: 8204 = 8102 (array) + 12 ( variant) Examples: a_sn=Array(1,2,4,6,8) ' 5 elements: Ubound(a_sn)=4 a_sp=Array("qq","ww","ee","rr","tt","yy") ' 6 elements: Ubound(a_sp)=5 a_sq=Array(date,date+1,date+2,date+3) ' 4 elements: Ubound(a_sq)=3 a_st=Array("text",3,Date+12,sheet1,Range("A10:K30"),array(1,2,3,4,5)) ' 6 elements: Ubound(a_st)=5 The first item of these arrays is a_sn(0), a_sp(0), a_sq(0) and a_st(0) respectively. 5.2 VBA method Split This method does 2 things simultaneously: it defines an array and fills it with items. You can only produce 1dimensional arrays with this method. You can only use a string to split into an array. The resulting elements in the array are strings exclusively. The string will be split by a separator; e.g. a comma. a_sp=split("aaa,fff,45,date,89,,ppp",",") The resulting array contains 7 elements: a_sp(0) t/m a_sp(6) a_sp(0)="aaa" a_sp(1)="fff" a_sp(2)="45" a_sp(3)="date" a_sp(4)="89" a_sp(5)="" a_sp(6)="ppp" The lower limit of this array is 0. The upper limit is 6. VBAtechnically: TypeName: String() VarType: 8200 = 8102 (array) + 8 (string) If you assign a number to 1 of this array's element e..g. a_sp(5)=2430 the number will be converted to a string automatically. 5.3 Declare the arrayvariable The declaration of an array determines its size. The array will be filled later on. The declaration determines the amount of memory it needs. Simultaneously you can determine the scope of the variable (cfr 5.3.2). The same applies to its content.(cfr. 5.3.3) 5.3.1 How to declare an array variable ? The instruction Dim. 5.3.1.1 Declare a 1dimensional fixed size array variable Dim a_sp(5) The variable a_sp lower limit is 0, upper limit is 5 and it contains 6 elements.  The method 'Dim' only accepts positive integers as argument to demarcate its size.  A variable, a calculation or a property isn't allowed.  You can only use a variable or a calculation to determine an array's size in combination with the method 'Redim'. If you prefer another lower limit than 0 you will have to specify it. Dim a_sp(1 to 12) Variable a_sp's lower limit is 1, upper limit 12 and it contains 12 elements. You can choose another lower limit. Whether that makes sense I wouldn't know. So this is also a valid declaration Dim a_sp(4 to 20) Variable a_sp's lower limit is 4, its upper limit is 20 and it can contain 17 elements/items.. 5.3.1.2 Declare a multidimensional fixed size array variable Dim a_sp(500,20)  The lower limit of both dimensions is 0  The upper limit of the first dimension is 500, the upper limit of the second one is 20  The variable contains 501 * 21 elements.  The method 'Dim' only accepts positive integers as argument to demarcate its size.  A variable, a calculation or a property isn't allowed.  You can only use a variable or a calculation to determine an array's size in combination with the method 'Redim'. If you prefer another lower limit than 0 for any dimension you will have to specify it. Dim a_sp(1 to 500,1 to 20) Variable a_sp hat  first dimension: lower limit 1; upper limit 500  second dimension: lower limit 1; upper limit 20  it contains 500*20 elements You can choose another lower limit. Even for every dimension. Whether that makes sense I wouldn't know. So also this is a valid declaration Dim a_sp(4 to 500,2 to 20) Variable a_sp  first dimension: lower limit 4; upper limit 500  second dimension: lower limit 2; upper limit 20  it contains 497*19 elements 5.3.1.3 Declare a 1dimensional dynamically sized arrayvariable The method Dim method determines the size of an array before it is being used of filled. The size will be fixed as long as the variable exists. It's not always possible to know the desired array size beforehand. In that case you can use the method Redim. Unlike the method Dim the method ReDim accepts variables, expressions, etc. to indicate the array's size. On top of that the method Redim allows you to ReDimension the array variable anytime. Valid daclaration with teh method ReDim ReDim a_sn(thisworkbook.sheets.count) y=12 Redim a_sq(y) Redim a_st(date) The upper limit is dependent of the value of the argument. Don't forget: because of the Lbound 0 the number of elements is 1 element greater than the Ubound of the variable. After it's declaration the variable can be filled ReDim a_sn(thisworkbook.sheets.count1) for j=0 to Ubound(a_sn) a_sn(j)=thisworkbook.sheets(j+1).name
nextIf you want 1 as LBound ReDim a_sn(1 to thisworkbook.sheets.count) y=12 ReDim a_sq(1 to y) Redim a_st(1 to date) The filling process ReDim a_sn(1 to thisworkbook.sheets.count) for j=1 to Ubound(a_sn) a_sn(j)=thisworkbook.sheets(j).name
next5.3.1.4 Declare a multidimensional dynamically sized arrayvariable The method Dim method determines the size of an array before it is being used or filled. The size will be fixed as long as the variable exists. It's not always possible to know the desired array size beforehand. In that case you can use the method Redim. Unlike the method Dim the method ReDim accepts variables, expressions, etc. to indicate the array's size. On top of that the method Redim allows you to ReDimension the array variable anytime. Valid declarations using ReDim ReDim a_sn(thisworkbook.sheets.count,thisworkbook.sheets.count) y=12 x=20 Redim a_sq(y,x) Redim a_st(date,weekday(date,2)) The Lbound of each array variable is 0 The Ubound corresponds to the values of the argument. Don't forget: because of the Lbound 0 the number of elements is 1 element greater than the Ubound of the variable. After its declaration the array can be filled with ReDim a_sn(thisworkbook.sheets.count1, thisworkbook.sheets.count1) for j=0 to Ubound(a_sn) for jj=0 to Ubound(a_sn,2)
nexta_sn(j,jj)=choose(jj+1,thisworkbook.sheets(j+1).name,thisworkbook.sheets(j+1).hyperlinks.count)
nextIf you prefer 1 as Lbound ReDim a_sn(1 to thisworkbook.sheets.count,1 to thisworkbook.sheets.count) y=12 x=20 ReDim a_sq(1 to y, 1 to x) Redim a_st(1 to date, 1 to weekday(date,2)) The number of elements now collides with the array's Ubound. The filling ReDim a_sn(1 to thisworkbook.sheets.count, 1 to thisworkbook.sheets.count) for j=1 to Ubound(a_sn) For jj=1 to Ubound(a_sn,2)
nexta_sn(j,jj)=choose(jj,thisworkbook.sheets(j).name,thisworkbook.sheets(j).hyperlinks.count)
next5.3.2 Scope and location of the declaration A variable's scope can  apply to 1 macro/function,  apply to 1 codemodule: workbook, worksheet, macromodule, classmodule or userform  apply to the whole workbook and all its VBA objects: the VBA project. 5.3.2.1 Scope 'Local': 1 macro or function The variable can only be used in the macro or function in which the variable has been declared. The declaration can be positioned anywhere in the macro/function. From another macro/function you can't get access to this variable: nor reading nor writing. To declare a local variable you can use the instruction 'Dim' or 'Redim'. If you use Private or Public in a macro or function you get a syntax error. For a decent overview starting the macro with declarations can be practical. For example sub M_example() Dim a_sn(5) ' VBA code end sub sub M_example() Redim a_sn(5) ' VBA code end sub 5.3.2.2 Scope 'Private' The scope 'Private' makes the variable available for all macros / functions in the same codemodule (workbook, worksheet, userform, macromodule or classmodule). Every codemodule has a separate declarations area at the beginning of the codemodule. All declarations there are available for the whole codemodule. In the declaration area the instruction 'ReDim' isn't allowed. You can use the keyword 'Private', or use the Instruction 'Dim'. The result is identical. Enter in the declaration area (before any macro/function) of the codemodule. Private a_sp(5) Dim a_sp(5) All declarations in the declaration area are Private per se. Macros/functions in other codemodules can't use these variables. 5.3.2.3 'Public' scope The scope 'Public' enables that all macros / functions in the workbook have access to a variable. The declaration of a 'Public' variable always has to reside in the declaration area of a macromodule. Use the keyword 'Public' to make the variable public. Enter in the declaration area of a macromodule Public a_sp(5) 5.3.3 Declare as Variantvariable or as arrayvariable If an array has to be accessible for several macros/functions declaring the variable is unavoidable. VBA offers two options: to declare the array as a Variant variable or as an array variable. A Variant variable is the only variable that can contain an array. 5.3.3.1 Declare as Variant variable You can store all kinds of elements in a variant variable; amongst them an array. If you declare a variant variable, you will have to fill it later with an array (using Dim , ReDim or Array or a method in 5.4) To declare a Variant variable Dim a_sn as Variant Since Variant is the default type is for any variable also this code suffices Dim a_sn Examples Dim in the macro Dim a_sn Sub M_snb() Dim a_sn(7)
end suba_sn(UBound(a_sn)) = 12 MsgBox a_sn(UBound(a_sn)) ReDim in the macro Dim a_sn Sub M_snb() ReDim a_sn(9)
end suba_sn(UBound(a_sn)) = 312 MsgBox a_sn(UBound(a_sn)) Array in the macro Dim a_sn Sub M_snb() a_sn= Array(12,45,36)
end subMsgBox a_sn(UBound(a_sn)) Split in the macro Dim a_sn Sub M_snb() a_sn= split("aaa,450," & date,",")
end subMsgBox a_sn(UBound(a_sn)) NB. the method 'Split' converts all values into strings. 5.3.3.2 Declare as Array variable You can declare a variable as an array from the start. That can be an array with a fixed size or a variable/dynamic size. At the same time you can determine the array's content: see 5.3.4. If you do not specify the array's content it will be a Variant by default. 1dimensional array: fixed size Dim a_sn(5) Dim a_sn(1 to 22) Private a_sn(40) Public a_sn(6) 1dimensional array: dynamic size Dim a_sn() Private a_sn() Public a_sn() multidimensional array: fixed size Dim a_sp(5,12,4) Dim a_sn(1 to 22,1 to 60) Private a_sn(40,10) Public a_sn(6,6,6) multi dimensional array: dynamic size Dim a_sn() Private a_sn() Public a_sn() In all these examples the variable is an array variable and it's content type Variant. 5.3.4 Declare the content of the Array You can specify the content of an array at 'declaration time'. If the array is restricted to hold strings only dim a_sn(10) as String dim a_sn() as String If the array is restricted to hold integers only dim A_sn(27) as Integer dim A_sn() as Integer All types of variables are available for arrays. Dim a_sn() As Integer Dim a_sn() As Byte Dim a_sn() As Boolean Dim a_sn() As Integer Dim a_sn() As Long Dim a_sn() As LongPtr Dim a_sn() As Single Dim a_sn() As Double Dim a_sn() As Currency Dim a_sn() As Date Dim a_sn() As Object Dim a_sn() As String Dim a_sn() As Variant The Typename for string variables: String The Typename for a string array: String() dim a_sn() As String MsgBox TypeName(a_sn) Vartype is the sum of the Vartype of an array (8192) + the variable type (string =12) So the Vartype of a string Array is 8192 +12 = 8204 dim a_sn(5,9) As String MsgBox VarType(a_sn) 5.4 Use objects / properties to create arrays Several objects in Excel or VBA libraries and their properties result in a 1dimensional or multi dimensional array. An Excel worksheet is a 2dimensional array 5.4.1 Range in Excel worksheet 5.4.1.1 A multi dimensional array a_sn=range("A1:K6").value The result:  An array a_sn of the type 'Variant', containing the values in Range A1:K6.  Vartype(a_sn)=8204 (8192 (array) + 12 (variant))  Typename((a_sn) is Variant().  First dimension: Lbound is 1; Ubound 6.  Second dimension: Lbound 1; Ubound 11. Since 'Value' is the default property of a Range alternative syntaxes are a_sn=range("A1:K6") a_sn=[A1:K6] This code assumes a static Range. If you want to be more flexible in defining the size of an array you can use 'Resize'. Based on 1 cell you can 'Resize' the array, using its rows and columns arguments x=6 y=11 a_sn=cells(1,1).resize(6,11) a_sn=cells(1).resize(6,11) a_sn=[A1].resize(6,11) a_sn=cells(1,1).resize(x,y) a_sn=cells(1).resize(x,y) a_sn=[A1].resize(x,y) 5.4.1.2 1dimensional array Every 'Range' in an Excel worksheet has a row and a column dimension. Also 1 row only or 1 column only is a multi dimensional array. Excel has 2 functions that can convert a multidimensional array into a 1dimensional array. All data have to be in 1 (part of a) row or in 1 (part of a) column. The lower limit (Lbound) of an array based on a Range is always 1. Data in one row Application.Index a_sn = Application.Index(Range("A1:M1").Value, 1, 0) a_sn = Application.Index(Range("A1:M1"), 1, 0) Application.Transpose a_sn = Application.Transpose(Application.Transpose(Range("A1:M1").Value)) a_sn = Application.Transpose(Application.Transpose(Range("A1:M1"))) a_sn = [transpose(transpose(A1:M1))] Data in one column Application.Transpose a_sn = Application.Transpose(Range("A1:A10").Value) a_sn = [transpose(A1:A10)] Result  An array a_sn of the type 'Variant', contains the values in the Range.  Vartype(a_sn)=8204 (8192 (array) + 12 (variant))  Typename(a_sn) is Variant().  First dimension: Lbound is 1; Ubound is 10 (the number of cells in the Range). 5.4.2 ActiveXcontrol A ComboBox and a ListBox is/contains an array. That can be a 1dimensional array or a 2dimensional array. The Lbound of each dimension is 0. To fill a ComboBox/ListBox with an array a_sn=array(22,44,77,88) ComboBox1.list=a_sn ListBox1.list=a_sn Write the ComboBox/ListBoxdata to an array a_sn=ComboBox1.List a_sn=ListBox1.list Take due notice that Lbound of both dimensions in a ComboBox/ListBox is 0. The number of items in the ListBox/ComboBox (property .Listcount) is always 1 more than the Ubound of a dimension a_sn=array(22,44,77,88) ComboBox1.list=a_sn Result ComboBox1.Listcount: 4 Ubound(ComboBox.list)=3 to retrieve the first item c00=ComboBox1.list(0) A ComboBox/ListBox has array properties and also some collection/dictionary properties. So a ComboBox/ListBox can be useful to adapt, add or remove items in an array. 5.4.3 The method 'Evaluate' You can create arrays with the method 'Evaluate' in Excel. Evaluate creates arrayformulae that you do no populate the worksheet with but store in memory. 5.4.3.1 1dimensional array Data in 1 column a_sn = [transpose(A1:A20)]  an array holding 20 elements  its content the values in Range A1:A20  Lbound: 1 Data in 1 row a_sn = [transpose(transpose(A1:K1))]  an array holding 11 elements  its content the values in Range A1:K1  Lbound: 1 If you need an empty array based on a column a_sn = [transpose(iferror(A1:A20/0,""))] or a_sn = [transpose(if(A1:A20="","",""))] Based on a row (NB its more elaborate than based on a column, though the result is identical). a_sn = [transpose(transpose(if(A1:K1="","","")))] a_sn = [transpose(transpose(iferror(A1:K1/0,"")))] You can fill the resulting array with the result of any calculations. The calculation can be based on data in a worksheet or independent of any worksheet. Add the row number to every value in Range A1:A20 a_sn = [transpose(A1:A20&"_"&row(1:20))] Multiply every value in Range A1:A20 by 12 a_sn = [transpose(12*A1:A20)] Worksheet independent results a_sn= [transpose(row(A1:A20))] result Declare  an array holding 20 elements  the numbers 1 t/m 20  Lbound: 1 a_sn = [transpose(text(today()+row(1:365),"ddmmyyyy"))] result  an array holding 365 elements  the dates from tomorrow to today next year  Lbound: 1 Sub M_Permutations_of_5() sn = [index(int((row(1:125)1)/25)+1 & int(mod((row(1:125)1),25)/5)+1& mod(row(1:125)1,5)+1,)]
End Sub an array holding 125 elements  all permutations of 3 elements from the numbers 1 to 5  Lbound: 1 5.4.3.2 2dimensional array The simplest: a Range in a worksheet a_sn=[A1:K10]  an array holding 11 * 10 elements  all values in Range(A1:K10) of the active worksheet  Lbound 1 for each dimension If you need an empty array a_sn=[if(A1:K10="","","")]  an array holding 11 * 10 empty elements  Lbound 1 for every dimension A calculated 2dimensional array, independent of any worksheet Sub unique_combinations_2letters_outof5() a_sn = [if(row(1:5)<transpose(row(1:5)),mid("ZFHKN",row(1:5),1) & transpose(mid("ZFHKN",row(1:5),1)),"")]
End Sub an array holding 5 * 5 elements  all unique combinations of 2 character out of a group of 5 (ZFHKN)  Lbound 1 for both dimensions Sub exponential_series() a_sn = [row(A1:D10)^column(a1:D10)]
end sub an array holding 10 * 4 elements  every row number raised to the power of the column number  Lbound 1 for both dimensions 5.4.4 Dictionary 5.4.4.1 1dimensional array A dictionary can contain independent data. Every Item gets a unique key. The dictionary creates a 1dimensional array containing all keys. That array can be assigned to a variant variable Sub M_snb() with createobject("scripting.dictionary")
end sub x0=.item("one")
end withx0=.item("two") x0=.item("three") a_sn=.keys  an array holding 3 elements: "one", "two" en "three"  Lbound: 0 If the items in a dictionary consists of separate strings, numbers or dates you can use the 1dimensional array that contains all items. Sub M_snb() with createobject("scripting.dictionary")
end sub.item("it_" & .count)="one"
end with.item("it_" & .count)="two" .item("it_" & .count)="three" a_sn=.items  an array a_sn holding 3 elements: "one", "two" en "three"  Lbound: 0 5.4.4.2 2dimensional array A dictionary can store 1dimensional arrays. With the Excel function 'index' you can concatenate those into a 2dimensional array. The 1dimensional arrays have to have an identical Ubound. Sub M_snb() with createobject("scripting.dictionary")
end sub.item("it_" & .count)=array("one","two","three","four")
end with.item("it_" & .count)=array("four","five","six","seven") .item("it_" & .count)=array("seven","eight","nine","ten") a_sn=application.index(.items,0,0) result:  a 2dimensional array holding 3 * 4 elements  Lbound: 0 for both dimensions NB. You can't concatenate 2dimensional arrays using the function Index. 5.5 The argument ParamArray In VBA you can pass arguments to a macro or function. All kinds of data can be passed as argument and be used in the receiving macro/function: strings, numbers, dates, arrays and VBA objects. There is no restriction to the use of names for the variables in which the argument will be stored (in the following example 'c00'). Sub M_snb() M_snb_000 "Illustration"
End subSub M_snb_000(c00) msgbox c00
end subIf you want to pass several arguments you can do so separating them by commas. Sub M_snb() M_snb_000 "Illustration",4,date
End subSub M_snb_000(c00,y,d01) msgbox c00
end submsgbox y msgbox d01 In the receiving macro you can indicate the kind of data that can be passed for each variable. Sub M_snb() M_snb_000 "Illustration",4,date
End subSub M_snb_000(c00 As String, y As Integer, d01 As Date) msgbox c00
end submsgbox y msgbox d01 Instead of passing arguments to several receiving variables you can use a paramarray. Paramarray is a dynamic 1dimensional Variant array that can contain all passed arguments/parameters. The paramarray is 'optional': if you don't pass any argument no error will be triggered. The paramarray is dynamic: you can pass 1 argument/parameter or an unlimited amount. The paramarray is 1dimensional: its Lbound is 0, its Ubound is dependent of the amount of passed arguments/parameters. The paramarray is a Variant: you can't restrict the paramarray to a certain type of data (e.g. string or integer). The 'lifecycle' of the paramarray is restricted to the receiving macro/function. Sub M_snb() M_snb_param "aa1", 34, Date, Array(1, 2, 3, 4, 5), Range("A1:K10"), Range("A1:K10").Value
End SubSub M_snb_param(ParamArray a_sn()) MsgBox LBound(a_sn) & vbLf & UBound(a_sn)
End SubMsgBox a_sn(0) ' string MsgBox a_sn(1) ' number MsgBox a_sn(2) ' date MsgBox a_sn(3)(4) ' 1dimensional array MsgBox a_sn(4).Address ' Object MsgBox a_sn(5)(2, 2) ' 2dimensional array 6. Working with arrays An array isn't a VBAobject with properties or methods or events. If you want to manipulate the contents of arrays or arrays itself you will have to 'lend' methods/tools from several VBA libraries. An important tool is the Excel worksheetfunction Index. Since this isn't the least complicated Excel function I will devote special attention to it here. Every time the method wil be employed there will be a reference to this place for further clarification. 6.0 Excel function Index The Excel function Index has 3 arguments  the range range/array it is referring to  the row(s) in the range  the column(s) in the range/array 6.0.1 Retrieve 1 value form an array Specify the row number and the column number to retrieve a value c00=application.index(a_sn,3,5) c00=a_sn(3,5) 6.0.2 Retrieve 1 row from a 2dimensional array Retrieve row 3 from array a_sn a_sp=application.index(a_sn,3,0) 6.0.3 Retrieve 1 column from a 2dimensional array Retrieve column 5 from array a_sn. a_sp=application.index(a_sn,0,5) Result Array a_sp is a 2dimensional array. Dimension 1: Lbound 1, Ubound = Ubound(a_sn). Dimension 2: Lbound 1, Ubound 1 (ergo: a_sp contains only 1 'column') 6.0.4 Retrieve several 'rows'/'columns' from an array In the second argument of the Index function you can indicate which 'rows' to retrieve. If you want more than 1 row you can use a 2dimensional array containing all row numbers you want to 'filter'. The result of the Index function is a 2dimensional array. In the third argument of the Index function you can indicate which 'columns' to retrieve. If you want more than 1 column you can use a 1dimensional array containing all column numbers you want to 'filter' How to create a 2dimensional array containing the rows to retrieve ? a_sr=[row(1:10)] Result: a_sr(1,1)=1, a_sr(2,1)=2,a_sr(3,1)=3 .... a_sr(10,1)=10
a_sr=application.transpose(Array(1,2,3,4,5,6,7,8,9,10)) Result: application.transpose converts the 1dimensional array array(..,..,) to a 2dimensional array: a_sr(1, 1) .... a_sr(10, 1)
a_sr= application.transpose(split("1,2,3,4,5,6,7,8,9,10",",)) Result: application.transpose converts the 1dimensional array that is the result of split() to a 2dimensional array: a_sr(1, 1) ... a_sr(10, 1)
c00="1,3,5,7" a_sr= application.transpose(split(c00,",)) Result: application.transpose converts the 1dimensional array that is the result of split() to a 2dimensional array: a_sr(1, 1) ... a_sr(4, 1) How to create a 1dimensional array containing the columns to retrieve ? a_sc=array(1,3,5,6) Result: 1dimensional array a_sc(0)=1, a_sc(1)=3, a_sc(2)=5, a_sc(3)=6
a_sc=split("1,3,5,6",",") Result: 1dimensional array a_sc(0)=1, a_sc(1)=3, a_sc(2)=5, a_sc(3)=6
c00="1,3,5,6" a_sc=split(c00,",") Result: 1dimensional array a_sc(0)=1, a_sc(1)=3, a_sc(2)=5, a_sc(3)=6
a_sc=[transpose(row(1:8))] Result: transpose converts the 2dimensional array that is the result of row( .. ) to a 1dimensional array: a_sc(1)=1, ... a_sc(8)=8 6.0.4.0 All values in an array Assuming a 2dimensional array containing 10 'rows' / 'records' and 8 'columns': retrieve all data. a_sp=application.index(a_sn,[row(1:10)],application.transpose([row(1:8)])) Alternative syntax a_sp=application.index(a_sn,application.transpose(array(1,2,3,4,5,6,7,8,9,10)),application.transpose([row(1:8)])) a_sp=application.index(a_sn,application.transpose(array(1,2,3,4,5,6,7,8,9,10)),array(1,2,3,4,5,6,7,8)) a_sp=application.index(a_sn,application.transpose(split("1,2,3,4,5,6,7,8,9,10",",")),split("1,2,3,4,5,6,7,8",",")) 6.0.4.1 Filter certain 'rows' / 'records' Filter records 3, 5 and 7 a_sp=application.index(a_sn,application.transpose(array(3,5,7)),application.transpose([row(1:8)])) 6.0.4.2 Filter certain 'columns' Filter columns 2, 4 and 6 a_sp=application.index(a_sn,[row(1:10)],array(2,4,6)) 6.0.4.3 Filter 'rows' / 'records' and change their order Filter records 2, 5 and 7 and revert their order a_sp=application.index(a_sn,application.transpose(array(7,5,2)),application.transpose([row(1:8)])) 6.0.4.4 Filter columns and change their order Filter columns 1, 4 en 6 and revert their order a_sp=application.index(a_sn,[row(1:10)],array(6,4,1)) 6.1 Resize an array 6.1.1 1dimensional array If the size of an array has been declared by the use of the method Dim, no resizing is possible. So it's impossible to resize the array with Dim a_sp(5) Redim a_sp(12) If the array hasn't been declared but has been assigned to a variable the array can't be resized either. This code errors out a_sn=Array(23,44,36,47,82) Redim a_sn(12) Resizing an array is possible if  the array has been declared as a dynamic array variable: Dim a_sn()  the array has been declared as a Variant variable: Dim a_sn  the array has been declared using the method Redim: Redim a_sn(5) The size of an array can be reduced or be enlarged. The VBA method to perform this is ReDim. The method ReDim can only change the upper limit (Ubound) of an array; the Lbound is static. Only the last dimension of an array can be resized. Resizing an array is wiping out its contents simultaneously. Resizing an array that has been declared as a dynamic array variable Dim a_sn() Redim a_sn(12) Resizing an array that has been declared as a variant variable Dim a_sn Redim a_sn(12) Resizing an array that has been declared by Redim ReDim a_sn(6)
msgbox UBound(a_sn)
ReDim a_sn(20)
msgbox Ubound(a_sn)
The combination of Redim and the Array method Resizing an array that has been declared as a dynamic array and has been populated by the method Array Dim a_sn() a_sn=Array(23,44,36,47,82) Redim a_sn(12) Resizing an array that has been declared as a variant variable and has been populated by the method Array Dim a_sn a_sn=Array(23,44,36,47,82) Redim a_sn(12) Resizing an array that has been declared by the method Redim and has been populated by the method Array Redim a_sn(0) a_sn=Array(23,44,36,47,82) Redim a_sn(12) The combination of Redim and the method Split It is not possible to use the method split if the array has been declared an a dynamic array This code errors out Dim a_sn() a_sn=split("a23,b44,c36,d47,e82",",")) It is not possible to use Split to populate a variable that has been declared by the method Redim This code errors out Redim a_sn(0) a_sn=split("23,44,36,47,82",",") It is possible to use Split if the variable has been declared as variant variable and has been populated by the method split Dim a_sn a_sn=split("23,44,36,47,82",",") Redim a_sn(12) 6.1.2 multidimensional array If the size of an array has been declared, using the method Dim, the array can't be resized. This code errors out Dim a_sp(5,10) ReDim a_sp(12,7) If the array has been assigned to a variant variable it can't be resized either. This code errors out a_sn=Range("A1:K10") Redim a_sn(12,7) Resizing a multidimensional array is possible if  the array has been declared as a dynamic array: Dim a_sn()  the array has been declared as a Variant variable: Dim a_sn  the array has been declared by the method Redim: Redim a_sn(5,4) The size of an array can be reduced or be enlarged. The VBA method to perform this is ReDim. The method ReDim can only change the upper limit (Ubound) of an array; the Lbound is static. Only the last dimension of an array can be resized. Resizing an array is wiping out its contents simultaneously. Resizing the array: dynamic array variable Dim a_sn() Redim a_sn(12,10) Resizing an array: array in variant variable Dim a_sn Redim a_sn(12,10) Resizing a multidimensional array: array variable declared by Redim ReDim a_sn(thisworkbook.sheets.count1, thisworkbook.sheets.count1) for j=0 to Ubound(a_sn) for jj=0 to Ubound(a_sn,2)
nexta_sn(j,jj)=choose(jj+1,thisworkbook.sheets(j).name,thisworkbook.hyperlinks.count)
nextRedim a_sn(Ubound(a_sn)+3,Ubound(a_sn,2)+5) 6.1.3 Resize a 1dimensional array and keeping its content The VBA method Redim resizes an array; the keyword 'Preserve' indicates that the contents shouldn't be wiped out. 1dimensional array ReDim a_sn(0) a_sn=array("aa1","aa2","aa3","aa4") ReDim preserve a_sn(Ubound(a_sn)+4) MsgBox a_sn(3) & vbLf & UBound(a_sn) 6.1.4 Resize a multidimensional array keeping its contents The VBA method Redim resizes an array; the keyword 'Preserve' indicates that the contents shouldn't be wiped out. The method ReDim can only change the upper limit (Ubound) of an array; the Lbound is static. Only the last dimension of an array can be resized. 2dimensional array. ReDim a_sn(3, 3) For j = 0 To UBound(a_sn) For jj = 0 To UBound(a_sn, 2)
Nexta_sn(j, jj) = j & "_" & jj
NextReDim Preserve a_sn(3, 7) MsgBox a_sn(2, 3) & vbLf & UBound(a_sn) & vbTab & UBound(a_sn, 2) A 3dimensional array. ReDim a_sn(3, 3, 3) For j = 0 To UBound(a_sn) For jj = 0 To UBound(a_sn, 2)
NextFor jjj = 0 To UBound(a_sn, 3)
Nexta_sn(j, jj, jjj) = j & "_" & jj & "_" & jjj
NextReDim Preserve a_sn(3, 3, 7) MsgBox a_sn(2, 3, 3) & vbLf & UBound(a_sn) & vbTab & UBound(a_sn, 2) & vbTab & UBound(a_sn, 3) 6.2 Check the existence of a value in an array 6.2.1 1dimensional array 6.2.1.1 Excel function 'Match' a_sn = Array("aa1", "aa2", "aa3", "aa4") msgbox Not IsError(Application.Match("aa3", a_sn, 0)) msgbox Not IsError(Application.Match("aa7", a_sn, 0)) The function match is case insensitive: "aa3" as well as "AA3" will be 'found'. 6.2.1.2 The Excel formula 'Find' The Excel formula 'Find' results in an array that has the same size as the array is which is being looked for, but lbound 1. If a value can't be found in the array the resulting array contains an error code in the corresponding 'cell'. If a value has been found the corresponding 'cell' contains a 1. a_sn=Array("aa1", "aa2", "aa3", "aa4") a_sr=application.find("aa2",a_sn) The elements in the resulting array: sr(1)=error 2015, sr(2)=1, sr(3)=error 2015, sr(4)=error 2015 Because of the error values this 1dimensional array can't be transformed into a string by 'join'. On the other hand you can apply the Excelformula 'Sum'; the result is comparable to 'Countif'. Whether an item is part of an array: a_sn=Array("aa1", "aa2", "aa3", "aa4") msgbox application.sum(application.find("aa2",a_sn))>0 The frequency of the item 'aa2' in array a_sn: a_sn=Array("aa1", "aa2", "aa3", "aa4") msgbox application.sum(application.find("aa2",a_sn)) 6.2.1.3 VBA method 'filter' In a 1dimensional array you can look for an item using the method 'filter' a_sp=filter(a_sn,"abcde")  the method Filter filters all items in the array that contain the filter criterion.  the method Filter inherently 'wilcard' oriented: it filters no only exact matches, but all item that contain the filer criterion: in this case "abcde" as well as "234abcde88". you can consider the filter criterion to be equivalent to "*abcde*"  if no item matches the filer criterion the result is an empty array 'a_sp', recognisable by its upper limit (Ubound) value 1 Check the occurrence of an item in a 1dimensional array MsgBox "Item 'abcde' does " & IIf(UBound(Filter(a_sn, "abcde")) = 1, "not ", "") & "appear in array a_sn" Exact matches To use the Filter method to obtain exact matches requires the marking of every item by unique delimiters a_sp=filter(split("~" & join(a_sn,"~~") & "~",""),"~abcde~") Case sensitive filtering The method Filter's argument 'compare' indicates whether the filtering is binary (case sensitive) or textual (case insensitive). MsgBox "Item 'abcde' does " & IIf(UBound(Filter(a_sn, "abcde",,0)) = 1, "not ", "") & "appear in array a_sn"
MsgBox "Item 'abcde' does " & IIf(UBound(Filter(a_sn, "abcde",,vbBinaryCompare)) = 1, "not ", "") & "appear in array a_sn" Case insensitive filtering The method Filter's argument 'compare' indicates whether the filtering is binary (case sensitive) or textual (case insensitive). MsgBox "Item 'abcde' komt " & IIf(UBound(Filter(a_sn, "abcde",,1)) = 1, "niet ", "") & "voor in array a_sn"
MsgBox "Item 'abcde' does " & IIf(UBound(Filter(a_sn, "abcde",,vbTextCompare)) = 1, "not ", "") & "appear in array a_sn" 6.2.1.4 A loop by indexnumber All items can be inspected successively if they match a criterion. If the item matches the criterion the loop will be interrupted. Case sensitive a_sn = Array("aa1", "aa2", "aa3", "aa4") for j=0 to Ubound(a_sn) if a_sn(j)="Aa3" then exit for
nextmsgbox j<(Ubound(a_sn)+1) Case insensitive a_sn = Array("aa1", "aa2", "aa3", "aa4") for j=0 to Ubound(a_sn) If Lcase(a_sn(j))="aa3" then exit for
nextmsgbox j<(Ubound(a_sn)+1) 6.2.1.5 A loop by element All items can be inspected successively if they match a criterion. If the item matches the criterion the loop will be interrupted. An array resembles a 'Collection' since you can step through its conctent with a For Each .. Next loop. Case sensitive a_sn = Array("aa1", "aa2", "aa3", "aa4") c00="not " For Each it In a_sn if it="Aa3" then c00=""
nextmsgbox "Item Aa3 has " c00 & "been found" Case insensitive a_sn = Array("aa1", "aa2", "aa3", "aa4") c00 = "not" For Each it In a_sn If Lcase(it)="aa3" then c00 = ""
nextmsgbox "Item aa3 has " c00 & "been found" 6.2.2 2dimensional array 6.2.2.1 A loop To check the occurrence of an item in a 2dimensional array For Each it In a_sn If it = "aa33" Then
Nextc00 = " "
End IfExit For MsgBox c00 <> "" 6.2.2.2 Excel function 'Match' The Excel function 'match' is only applicable to a row or a column (i.e. a 2dimensional array with either a Ubound=1 of the first or the second dimension). check row by row for j=1 to Ubound(a_sn) If Not IsError(Application.Match("abcde", Application.Index(a_sn, j, 0), 0)) Then Exit For
nextmsgbox j< (Ubound(a_sn)+1) check column by column a_sn = Cells(1).CurrentRegion For j = 1 To UBound(a_sn, 2) If Not IsError(Application.Match("aa33", Application.Index(a_sn, 0, j), 0)) Then Exit For
NextMsgBox j < (UBound(a_sn, 2) + 1) 6.2.2.3 The Excelobject 'Names' Assign the array to a Name'. Excel creates an arrayformula, consisting of a string. The item can be searched for in that string. Embed the search string in quotation marks. Embed numerical values in commas. Names.Add "snb_002", a_sn msgbox InStr(Names("snb_002").Value, """abcde""")>0 msgbox InStr(Names("snb_002").Value, ",1245,")>0 6.3 Determine the position of an item in an array 6.3.1 1dimensional array 6.3.1.1 Excel function 'match' Match determines the first occurrence of an item in an array. a_sn = Array("aa1", "aa2", "aa3", "aa4") msgbox Application.Match("aa3", a_sn, 0) NB. Match returns the position of the item. "aa3" is the third item in the array, but because of the lower bound 0, it matches a_sn(2) 6.3.1.2 VBA: join & split The combination of 'join' and 'split' can return an item's position in the array. a_sn = Array("aa1", "aa2", "aa3", "aa4") c00="~" & join(a_sn,"~~") & "~" MsgBox UBound(Split(Split(c00, "~aa3~")(0), "")) 6.3.2 2dimensional array 6.3.2.1 A loop for j=0 to Ubound(a_sn) for jj=0 to Ubound(a_sn)
next if a_sn(j,jj)="abcde" then exit for
nextif jj< (Ubound(a_sn)+1) then exit for Msgbox " 'row' : " & vbtab & j & vbtab & " 'column' : " & jj 6.3.2.2 Excel object 'Name' a_sn = Cells(1).CurrentRegion Names.Add "snb_002", a_sn Names("snb_002").RefersTo = [if(snb_002="a25","#","~")] c00 = Names("snb_002").RefersToLocal c00 = Left(c00, InStr(c00, "#")) x = UBound(Split(c00, ";")) MsgBox " 'row' : " & vbtab & x + 1 & vbtab & " 'column' : " & UBound(Split(Split(c00, ";")(x), "\")) + 1 6.4 Copy an item within an array 6.4.1 1dimensional array 6.4.1.1 VBA methods join en split copy an item immediately after/before the item to be copied a_sn=array("aa1","aa2","aa3","aa4","aa5") a_sn(3)=a_sn(3) & "," & a_sn(3) a_sn=split(join(a_sn,","),",") copy an item immediately after the last item in the array a_sn=array("aa1","aa2","aa3","aa4","aa5") a_sn=split(join(a_sn,",") & "," & a_sn(3),",") 6.4.2 2dimensional array 6.4.2.1 Excel function 'Index' See: Function Index: detailed explanation Copy an item immediately before/after the item yo be copied Assign the values of Range A1:D9 to an array. Copy the third item ('row' / 'record'). a_sn=cells(1).currentregion a_sn=application.index(a_sn,transpose(array(1,2,3,3,4,5,6,7,8,9),array(1,2,3,4)) Alternative syntax a_sn = Application.Index(a_sn, Application.Transpose(Array(1,2,3,3,4,5,6,7,8,9)), [transpose(row(1:4))]) a_sn = Application.Index(a_sn, Application.Transpose(Split("1,2,3,3,4,5,6,7,8,9", ",")), [transpose(row(1:4))]) Copy the third item after the last item in the array a_sn=cells(1).currentregion a_sn=application.index(a_sn,transpose(array(1,2,3,4,5,6,7,8,9,3),array(1,2,3,4)) 6.5 Filter items from an array 6.5.1 1dimensional array 6.5.1.1 VBA method 'filter' The method 'filter' filters all items from an array that contain a certain string. The resulting array only contains strings. a_sn=array(2,4,1250,"aa12","aa14","aa50") a_sp=filter(a_sn,"1") Result  a_sp is a 1dimensional array containing 3 string elements  array a_sp contains the elements "1250", "aa12" and "aa14"  Lbound: 0; Ubound: 2 6.5.2 2dimensional array A 2dimensional has no filter method (like e.g. autofilter or advancedfilter in Excel) With the use of a loop and the Excel function Index it's possible to get the same result. 6.5.2.1 Excel function Index See: Function Index: detailed explanation Purpose: filter all items that contain the string "abcde" in the fist 'column'. a_sn = Sheet1.Cells(1).CurrentRegion For j = 1 To UBound(a_sn) If a_sn(j, 1) = "abcde" Then c00 = c00 & "_" & j Next a_sp = Application.Index(a_sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(1, 2, 3, 4)) A multiple filter Filter all items that contain the string "abcde" in the first 'column' and the number 12 in the 4th 'column'. a_sn = Sheet1.Cells(1).CurrentRegion For j = 1 To UBound(a_sn) If a_sn(j, 1) = "abcde" and a_sn(j,4)=12 Then c00 = c00 & "_" & j Next a_sp = Application.Index(a_sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(1, 2, 3, 4)) 6.6 Filter 'rows'/'records' from a 2dimensional array A multidimensional array has no filter method. With the use of the Excel function Index you can get the same result. 6.6.1 Excel function Index See: Function Index: detailed explanation Filter the 'rows' 1,3 and 5 of a 2dimensional array. a_sn=range("A1:H10") a_sp=application.index(a_sn,application.transpose(array(1,3,5)),application.transpose([row(1:8)])) Change the order of the resulting 'records'/'rows' a_sn=range("A1:H10") a_sp=application.index(a_sn,application.transpose(array(5,3,1)),application.transpose([row(1:8)])) Filter rows on its content: create the 2dimensional array argument in a loop. a_sn=range("A1:H10") for j=1 to Ubound(a_sn) if instr("yeardateday",a_sn(j,1)) then c00=c00 & "_" & j
nexta_sp=application.index(a_sn,application.transpose(split(mid(c00,2),"_")),application.transpose([row(1:8)])) 6.7 Filter 'columns' from a 2dimensional array A multidimensional array has no filter method. With the use of the Excel function Index you can get the same result. 6.7.1 Excel function Index See: Function Index: detailed explanation Filter the columns 1,3 and 5 of a 2dimensional array. a_sn=range("A1:H10") a_sp=application.index(a_sn,[row(1:10)],array(1,3,5)) Change the order of the resulting columns. a_sn=range("A1:H10") a_sp=application.index(a_sn,[row(1:10)],array(5,3,1)) Filter columns based on a condition: create the 1dimensional array argument in a loop. a_sn=range("A1:H10") for j=1 to Ubound(a_sn,2) if instr("yeardateday",a_sn(1,j)) then c00=c00 & "_" & j
nexta_sp=application.index(a_sn,[row(1:10)],split(mid(c00,2),"_")) 6.8 Delete items in an array 6.8.1 Delete 1 item by index number 6.8.1.1 1dimensional array 6.8.1.1.1 VBA method filter a_sn=array("aa1","aa2","aa3","aa4","aa5") You could suppose you can delete 1 item using a_sn(3)="" That's not the case.The item stays intact, the size of the array hasn't been diminished, only the item's content has been deleted. After clearing an item's content it's imaginable to filter all items that have some content using the VBA method 'Filter'. The method 'Filter' doesn't provide that option. As an alternative you can assign a unique value to the item that has to be deleted. After having done so you can filter all elements that do not contain that value. a_sn=array("aa1","aa2","aa3","aa4","aa5") a_sn(3)="~~~" a_sn=filter(a_sn,a_sn(3),false) Now the resulting array has been reduced by 1 item. 6.8.1.1.2 ActiveX control You can assign an array to an ActiveXcontrol: a ComboBox or a ListBox. Keep in mind that the lower limit (LBound) of a ComboBox/ ListBox is 0. A ComboBox/ ListBox has array properties as well as collection properties. You can remove elements from a ComboBox/ListBox by index number using the method .Removeitem After removing you can assign the contents of the ListBox / ComboBox to a variable a_sn=array("aa1","aa2","aa3","aa4","aa5") ListBox1.List=a_sn ListBox1.removeitem 2 a_sn=ListBox1.list The resulting array is a 2dimensional array. If you need an 1dimensional resulting array: a_sn=array("aa1","aa2","aa3","aa4","aa5") ListBox1.List=a_sn ListBox1.removeitem 2 a_sn = Application.Transpose(ListBox1.List) 6.8.2 2dimensional array VBA has no method to delete an item by index number in a 2dimensional array. With the use of an ActiveXcontrol it can be accomplished. 6.8.2.1 ActiveXcontrol Assign the array to an ActiveXcontrol: a ComboBox or a ListBox. Keep in mind that the lower limit (LBound) of each dimension in a ComboBox/ ListBox is 0. A ComboBox/ ListBox has array properties as well as collection properties. You can remove elements from a ComboBox/ListBox by index number using the method .Removeitem After removing you can assign the contents of the ListBox / ComboBox to a variable a_sn=range("A1:K10") ListBox1.List=a_sn ListBox1.removeitem 2 a_sn=ListBox1.list 6.8.3 Delete an item by content 6.8.3.1 1dimensional array 6.8.3.1.1 VBA method Filter The method Filter has an argument/switch to keep the items that do not match a certain condition. The array can contain string, numbers and/or dates. The filter condition can be a string, a number or a date. The resulting array only consists of string elements. Case insensitive a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") a_sp=filter(a_sn,"a4",false) Result  a 1dimensional array a_sp consisting of 4 elements:  the elements: "aa1", "aa2", "aa3" en "aa5"  all elements that contain the string "a4" (i.c. "aa4" en "aa4a") have been 'deleted'. Alternative syntax a_sp=filter(a_sn,"a4",false,1) a_sp=filter(a_sn,"a4",false,vbTextCompare) Case sensitive a_sn=array("aa1","aa2","aa3","aa4","aA4a","aa5") a_sp=filter(a_sn,"A4",false,0) Result  a 1dimensional array a_sp  consisting of 5 elements  "aa1","aa2", "aa3", "aa4" and "aa5";all elements that contain the string "A4" (i.c. "aA4a") has been 'deleted' Alternative syntax a_sp=filter(a_sn,"a4",false,vbBinaryCompare) Exact Match The method 'Filter' filters as if the filter condition is wrapped in wild characters "*": "*" & filter condition & "*" All items that contain the filter condition somewhere will be deleted. To ensure only exact matches will be deleted, you can embed all items in unique markers. After filtering those markers have to be removed. Example: use the tilde ~ as unique marker. a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") a_sn=split("~"&join(a_sn,"~~)&"~","") a_sp=split(replace(join(filter(a_sn,"~aa4~",false),""),"~"),"") Only the item 'aa4' has been deleted. 6.8.3.1.2 ActiveX Assign the array to an activeX control (ComboBox or ListBox). The method .Removeitem can remove an item by index number. To obtain that index number you assign the value to the value property of the ComboBox/ListBox. The index number in a ListBox/ComboBox is the property .ListIndex. Now you can remove this item by its index number (= .ListIndex) Assign the property 'List' of the ComboBox/ListBox to the array variable. a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") ComboBox1.list=a_sn ComboBox1.value="aa4" if ComboBox1.ListIndex>1 then ComboBox1.removeitem ComboBox1.ListIndex a_sn=application.transpose(ComboBox1.list) The item 'aa4' has been deleted 6.8.4 Delete several items by content 6.8.4.1 1dimensional array 6.8.4.1.1 VBA method filter The VBA method filter deletes all items in a 1dimensional array that do not contain a certain filter string a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") a_sn=filter(a_sn,"a4",false) Items 'aa4' en 'aa4a' have been deleted. The method Filter is inherently a 'wild card' method; so you can't use special characters like ? or *. The method allows the use of nested filters a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") a_sn=filter(filter(a_sn,"a4",false),"aa2",false) The items 'aa4', 'aa4a' en 'aa2' have been deleted. 6.9 Delete 'columns' in a 2dimensional array VBA has no method to delete 'columns' in arrays. With the aid of the Excel function 'Index' you can filter the columns you want to preserve. See: Function Index: detailed explanation The result is an array that only contains the indicated columns; all the other columns have been 'deleted'. In a 1dimensional array you can specify which columns should be preserved a_sn=range("A1:F14") a_sp=application.index(a_sn, [row(1:14)],Array(1,3,5))  a_sp is a 2dimensional array consisting of 20 'rows' and 3 'columns'  a_sp contains the values in the columns 1,3,5 of array a_sn  the columns 2,4 en 6 have been 'deleted' from array a_sn. Alternative syntax the 'column' argument a_sp = Application.Index(a_sn, [row(1:14)], [{1, 3, 5}]) a_sp = Application.Index(a_sn, [row(1:14)], Split("1 2 3")) the 'row' argument a_sp = Application.Index(a_sn, Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)), Array(1,2,3)) a_sp = Application.Index(a_sn, Application.Transpose(Split("1 2 3 4 5 6 7 8 9 10 11 12 13 14")), Split("1 2 3")) a_sp = Application.Index(a_sn, Evaluate("row(1:" & UBound(a_sn) & ")"), [{1, 3, 5}]) Names.Add "snb_003", Rows(1).Resize(UBound(a_sn)) a_sp = Application.Index(a_sp, [row(snb_003)], [{1, 3, 5}]) 6.10 Delete rows in a 2dimensional array VBA has no method to delete 'rows' in arrays. With the aid of the Excel function 'Index' you can filter the rows you want to preserve. See: Function Index: detailed explanation The result is an array that only contains the indicated rows; all the other rows appear to have been 'deleted'. In a 2dimensional array you can specify which rows have to be preserved. a_sn=range("A1:F14") a_sp = Application.Index(a_sn, Application.Transpose(Array(1, 5, 10, 14)), [transpose(row(1:6))])  a_sp is a 2dimensional array consisting of 4 'rows' and 6 'columns'  a_sp contains the values in in the rows 1,5, 10 and 14 in array a_sn  the rows 24, 69 and 1113 appear to be deleted from array a_sn. Alternative syntax a_sp = Application.Index(a_sn, Application.Transpose(Split("1 5 10 14")), [transpose(row(1:6))]) a_sp = Application.Index(a_sn, [transpose({1,5,10,14})], [transpose(row(1:6))]) 6.11 Sort the items in an array 6.11.1 1dimensional array VBA has no array sorting method. You can use sorting methods in other VBA libraries (arraylist, sortedlist, ADODB) or in an Excel worksheet. 6.11.1.1 Arraylist a_sn = Array("aa5", "aa2", "aa4", "aa3", "aa1") With CreateObject("system.collections.arraylist") For j = 0 To UBound(a_sn)
End With.Add a_sn(j)
Next.Sort a_sp = .toarray()  an 'arraylist' only contains values, no 'keys' (like a dictionary or a collection or a sortedlist)  assign the items to sort to the arraylist  after sorting the property .toarray() contains the sorted items. 6.11.1.2 Sortedlist In a sortedlist new items will be sorted directly in the existing list. No instruction '.sort' is needed. The items in a sortedlist are being sorted by their key. Put the value of an item that has to be sorted in the keys. The items can be read in the sorting order of the key. a_sn = Array("aa5", "aa2", "aa4", "aa3", "aa1") With CreateObject("System.Collections.SortedList") For j = 0 To UBound(a_sn)
End With .Item(a_sn(j)) = ""
NextFor j = 0 To .Count  1 a_sn(j) = .getkey(j)
Next6.11.1.3 ADODB The ADODB library provides the tools to convert an array into a virtual database. Create as many fields in the database as the array has 'columns' (its 2nd dimension): .fields.append Define each field as a string of variable length. adVarChar ( 200) Create as many 'records' as the array a_sn has 'rows': .Addnew. Fill record by record each field with the corresponding values in array a_sn: .fields("item" & jj)=sn(j,jj) Specify the sorting field: .Sort = "item1" The result is in the property .Getrows The property .GetRows has to be transposed to get the same dimensions of array a_sn. a_sn = Array("aa5", "aa2", "aa4", "aa3", "aa1") With CreateObject("ADODB.recordset") .Fields.Append "item", 200, 30
End With.Open For j = 0 To UBound(a_sn) .AddNew
Next.Fields("item")= a_sn(j) .Update .Sort = "item" a_sp = split(.GetString,vbCr) 6.11.1.4 Excel worksheet Write the array to an unused part of a worksheet. Sort that range by the 'field' (i.c the cell) of that range. Read the values of the sorted range in an array variable. a_sn = Array("aa5", "aa2", "aa4", "aa3", "aa1") cells(1,200).Resize(Ubound(a_sn)+1)=application.transpose(a_sn) cells(1,200).currentregion.sort cells(1,200),,,,,,,0 a_sp=cells(1,200).currentregion cells(1,200).currentregion.clearcontents 6.11.2 2dimensional array VBA has no sorting method for a 2dimensional array. You can use sorting methods in other VBA libraries (arraylist, sortedlist, ADODB) or in an Excel worksheet. 6.11.2.1 Arraylist Create a 1dimensional sorted list of row numbers. Create the sorted array with the use of the Excel function Index, in which the sortedlist will be used as argument for the rows. a_sn = Cells(1).CurrentRegion With CreateObject("system.collections.arraylist") For j = 1 To UBound(a_sn) .Add a_sn(j, 1) & "~_" & j Next .Sort a_sp = Filter(Split(Join(.toarray(), "_"), "_"), "~", False) End With a_st = Application.Index(a_sn, Application.Transpose(a_sp), Application.Evaluate("transpose(row(1:" & UBound(a_sn, 2) & "))"))  an arraylist can only contain values, no 'keys' (like in an dictionary, collection or sortedlist)  the Excel function index requires a 2dimensional array to indicate in which order 'rows' have to be distilled from the array.  add to the arraylist the value of each item a_sn(j,1) and concatenate it with its row number: a_sn(j,1) &"_" & j  after sorting the sorted array contains not only the sorted values but also the corresponding row numbers.  remove the sorted values form the sorted list, so only the sorted row numbers will remain.  convert the resulting array to a 2dimensional array using transpose, and use it as row argument in the Excel function Index 6.11.2.2 Sortedlist In a sortedlist new items will be sorted directly in the existing list. No instruction '.sort' is needed. The items in a sortedlist are being sorted by their key. Put the value of an item that has to be sorted in the keys. Put the row number in the item of the sortedlist The items (=row numbers) can be read into a 2dimensional array in the sorting order of the key. a_sn = Range("A1:K14") ReDim a_sr(UBound(a_sn), 0) With CreateObject("System.Collections.SortedList") For j = 1 To UBound(a_sn)
End With.Item(a_sn(j, 1)) = j
NextFor j = 0 To .Count  1 a_sr(j,0) = .Item(.getkey(j))
Nexta_sp = Application.Index(a_sn, a_sr, Array(1, 2, 3, 4, 5)) 6.11.2.3 ADODB The ADODB library provides the tools to convert an array into a virtual database. Create as many fields in the database as the array has 'columns' (its 2nd dimension): .fields.append Define each field as a string of variable length. adVarChar ( 200) Create as many 'records' as the array a_sn has 'rows': .Addnew. Fill record by record each field with the corresponding values in array a_sn: .fields("item" & jj)=sn(j,jj) Specify the sorting field: .Sort = "item1" The result is in the property .Getrows The property .GetRows has to be transposed to match the dimensions of array a_sn. a_sn = Range("A1:K14") With CreateObject("ADODB.recordset") For j = 1 To UBound(a_sn, 2)
End With .Fields.Append "item" & j, 200, 30
Next.Open For j = 1 To UBound(a_sn) .AddNew
NextFor jj = 1 To UBound(a_sn, 2) .Fields("item" & jj) = a_sn(j, jj)
Next.Update .Sort = "item1" a_sp = Application.Transpose(.GetRows) 6.11.2.4 Excel worksheet Write the array to an unused part of a worksheet. Sort that range by the 'field' (i.c the cell) of that range. Assign the values of the sorted range to an array variable. a_sn=Range("A1:K12") cells(1,200).Resize(Ubound(a_sn),Ubound(a_sn,2))=a_sn cells(1,200).currentregion.sort cells(1,200),,,,,,,0 a_sp=cells(1,200).currentregion cells(1,200).currentregion.clearcontents 6.12 2dimensional array: insert empty rows/records On a regular basis the question arises to add empty rows/records in a worksheet. Strictly spoken that's not possible: each worksheet has a static number of rows, since Excel 2007: 1048576 (2^20). Adding rows in a worksheet can trigger many event procedures and calculations; that may slow down the code considerably. It's more practical to assign the values to an array and to insert empty 'rows' in the array, after which the array can be written to the worksheet in one go.  assign the range to array a_sn  enlarge the range with 1 empty row; if you employ currentregion you can be sure the first row 'below' the range is an empty one.  in the Excel function Index you can indicate in a 2dimensional array in which order rows from an array have to be read. See: Function Index: detailed explanation  the result is a new 2dimensional array Assume  the Range("A1:E6") contains values  after each row 2 empty rows have to be inserted Read Range("A1:E6") + 1 empty row: Range("A7:E7") With cells(1).currentregion a_sn = .Resize(.Rows.Count + 1)
End WithThis method copies the array a_sn: a_sp = Application.Index(a_sn,Application.Transpose(Array(1, 2, 3, 4, 5, 6,7)), Array(1, 2, 3, 4, 5)) The empty 'row' in array a_sn is row '7' This method inserts 2 time the empty row 7 after each row a_sp = Application.Index(a_sn, Application.Transpose(Array(1, 7, 7, 2, 7, 7, 3, 7, 7, 4, 7, 7, 5, 7, 7, 6, 7, 7)), Array(1, 2, 3, 4, 5)) Check: write the resulting array in the workbook Cells(20, 1).Resize(UBound(a_sp), UBound(a_sp, 2)) = a_sp A more robust syntax y is the number of empty rows y = 2 With Sheet1.Cells(1).CurrentRegion a_sn = .Resize(.Rows.Count + 1)
End Witha_sp = Application.Index(a_sn, Application.Transpose(Split(Join(Evaluate("transpose(row(1:" & UBound(a_sn) & "))"), Replace(String(y, ","), ",", "," & UBound(a_sn)) & ","), ",")), Evaluate("transpose(row(1:" & UBound(a_sn, 2) & "))")) 6.13 Combine arrays VBA has no method to combines arrays. VBA has some tools with which you can accomplish a combination 6.13.1 1dimensional array 6.13.1.1 VBA methods Join & Split If two 1dimensional arrays contian solely strings/number/dates you can use join & split to combine them. a_sn=array("aa1","aa2","aa23","aa4","aa5") a_sp=array("bb11","bb22","bb33","bb44","bb55") a_sq=split(join(a_sn,"") & "" & join(a_sp,""),"") 6.13.1.2 VBA method Redim If two 1dimensional arrays contain numbers and dates and you want to prevent them from being converted into strings you will have to define a new array that can contain all values of both arrays. a_sn = Array("aa1", "aa2", "aa23", "aa4", "aa5") a_sp = Array("bb11", 220, "bb33", Date, "bb55") ReDim a_sq(UBound(a_sn) + UBound(a_sp) + 1) For j = 0 To UBound(a_sq) If j > UBound(a_sn) Then
Nexta_sq(j) = a_sp(j  UBound(a_sn)  1)
Elsea_sq(j) = a_sn(j)
End If6.13.1.3 Excel worksheet Write both 1dimensional arrays to 1 row in a worksheet. Assign the values in that row to a variable. Convert the resulting 2dimensional variable to a 1dimensional variable. a_sn = Array("aa1", "aa2", "aa23", "aa4", "aa5") a_sp = Array("bb11", "bb22", "bb33", "bb44", "bb55") Sheet1.Cells(1).Resize(, UBound(a_sn, 1) + 1) = a_sn Sheet1.Cells(1).Offset(, UBound(a_sn) + 1).Resize(, UBound(a_sp, 1) + 1) = a_sp a_sq = Application.Transpose(Application.Transpose(Cells(1).Resize(, UBound(a_sn) + UBound(a_sp) + 1))) 6.13.2 2dimensional array 6.13.2.1 Excel worksheet Write both (or more) arrays contiguously in a worksheet. Assign the resulting contiguous range to a new array variable. a_sn=sheet5.Range("A1:K10") a_sp=sheet4.Range("A1:F17") sheet1.cells(1).resize(Ubound(a_sn),Ubound(a_sn,2))=a_sn sheet1.cells(Ubound(a_sn)+1,1).resize(Ubound(a_sp),Ubound(a_sp,2))=a_sp a_sq=sheet1.cells(1).currentregion 6.13.2.2 Dictionary A dictionary can combine 1dimensional arrays to one 2dimensional array. If you add two (or more) 2dimensional arrays 'rowbyrow' to a dictionary you can combine them using the Excel function Index. An identical amount of 'columns' (Ubound(,2) is a prerequisite. a_sn = Sheet4.Range("A1:F10") a_sp = Sheet5.Range("A1:F28") With CreateObject("scripting.dictionary") For j = 1 To UBound(a_sn)
End With.Item("nr" & .Count) = Application.Index(a_sn, j, 0)
NextFor j = 1 To UBound(a_sp) .Item("nr" & .Count) = Application.Index(a_sp, j, 0)
Nexta_sq = Application.Index(.items, 0, 0) 6.14 Convert 1dimensional array to 2dimensional array v.v. 6.14.1 convert 1dimensional array to a 2dimensional array Only a 2dimensional array can be written vertically to a worksheet. A 1 dimensional array needs to be converted. A 1dimensional array contains 1 item more than its upper limit Ubound, since the lower limit  Lbound  is 0. 6.14.1.1 Excel function 'Transpose' a_sn=Array("aa1","aa2","aa3","aa4","aa5") a_sp=application.transpose(a_sn) Alternative syntax a_sp=application.transpose(Array("aa1","aa2","aa3","aa4","aa5"))  a_sp is a 2dimensional array; first dimension: Ubound 5; second dimension: Ubound 1.  first dimension: Lbound 1; second dimension Lbound 1.  the function Transpose implicitly uses lower limit 1 (Option Compare 1) for both dimensions. 6.14.1.2 ActiveXcontrol The assigning of a 1dimensional array to a ComboBox or ListBox converts the 1dimensional array into a 2dimensional array. The assigning of the contents of a ComboBox or ListBox to a variable results in a 2 dimensional array. a_sn = Array("aa1","aa2","aa3","aa4","aa5") ComboBox1.List = a_sn a_sn = ComboBox1.List Result  an array consisting of 5 elements a_sn(0,0), a_sn(1,0), a_sn(2,0), a_sn(3,0) and a_sn(4,0)  dimension 1: Lbound 0, Ubound 4  dimension 2: Lbound 0, Ubound 0 6.14.1.3 Dictionary A dictionary can combine several 1dimensional arrays to a 2dimensional array. With CreateObject("scripting.dictionary") .Item(.Count) = Array("aa1", "aa2", "aa3", "aa4", "aa5") .Item(.Count) = Array("bb1", "bb2", "bb3", "bb4", "bb5") .Item(.Count) = Array("cc1", "cc2", "cc3", "cc4", "cc5") a_sn = Application.Index(.items, 0, 0) End With Result  an array consisting of 15 elements  The Excel function Index creates Lbound=1 for all dimensions.  dimension 1: Lbound 1 , Ubound 3  dimension 2: Lbound 1 , Ubound 5 6.14.2 Convert a 2dimensional array to a 1dimensional array The conversion of two special 2dimensional arrays: the 'row' array and the 'column' array. 6.14.2.1 Convert a 'row' A 'row' array has 1 row and several columns. You can create one using a_sn=range("A1:K1")  dimension 1: Lbound 1, Ubound 1  dimension 2: Lbound 1, Ubound 11 6.14.2.1.1 Excel function Index See: Function Index: detailed explanation a_sn=range("A1:K1") a_sn=application.index(a_sn,1,0) Result  a 1dimensional array a_sn  with Lbound(a_sn) =1  with Ubound(a_sn) = 11 If you need a 1dimensional array and an Lbound 0 a_sn=range("A1:K1") a_sn=filter(application.index(a_sn,1,0),"") Result  a 1dimensional array a_sn  with LLbound(a_sn) =0  with Ubound(a_sn) = 10  the method 'Filter' converts all values to strings 6.14.2.1.2 Excel function Transpose a_sn = Range("A1:K1") a_sn = Application.Transpose(Application.Transpose(a_sn)) Result  a 1dimensional array a_sn  with Lbound(a_sn) =1  with Ubound(a_sn) = 11 If you need a 1dimensional array and an Lbound 0 a_sn=range("A1:K1") a_sn=filter(Application.Transpose(Application.Transpose(a_sn)),"") Result  a 1dimensional array a_sn  with Lbound(a_sn) =0  with Ubound(a_sn) = 10 6.14.2.2 Convert a 'column' A 'column' array has 1 column and several rows. You can create it using a_sn=range("A1:A12") Result  a 2dimensional array a_sm  dimension 1: Lbound 1, Ubound 11  dimension 2: Lbound 1, Ubound 1 6.14.2.2.1 Excel function Transpose a_sn=range("A1:A12") a_sn = Application.Transpose(a_sn) Result  a 1dimensional array a_sn  dimension 1: Lbound 1, Ubound 12 If you need a 1dimensional array ans Lbound 0 a_sn=range("A1:A12") a_sn = filter(Application.Transpose(a_sn),"") NB. The method 'Filter' converts all values to strings. 6.14.2.2.2 Excel function Index See: Function Index: detailed explanation The function Index can do the conversion; compared to the function Transpose it's unnecessarily complicated. a_sn=range("A1:A12") a_sn = Application.Index(a_sn, [transpose(row(1:12))], 0) Result  a 1dimensional array a_sn  Lbound(a_sn) =1  Ubound(a_sn) = 12 6.15 Convert an array to a string 6.15.1 1dimensional array 6.15.1.1 VBA method Join Any 1dimensional array without arrays can be converted to a string using the method join. All values will be converted to strings. a_sn=array("aa1",dateserial(2014,10,20),1250,30.6) msgbox join(a_sn) Result  string: "aa1 20102014 1250 30,6" The method join uses the space as separator between items by default. If you do not want any separator msgbox join(a_sn,"") Any character or string can be used as a separator. msgbox join(a_sn,"") msgbox "This is item " & join(a_sn,vbLf & "This is ") 6.15.2 2dimensional array 6.15.2.1 A loop a_sn=Range("A1:K12") for each it in a_sn c00=c00 & "" & it
nextmsgbox c00 6.15.2.2 Excel function Index See: Function Index: detailed explanation The function Index can convert any row in a 2dimensional array to a 1dimensional array. The method Join can convert that 1dimensional array to a string. a_sn=range("A1:K12") for j=1 to Ubound(a_sn) c00=c00 & vblf & join(application.index(a_sn,j,0),"_")
next6.15.2.3 The clipboard Write a 2dimensional array to a worksheet. Copy the resulting range. Retrieve the data in the clipboard as string, using the DataObject in the MSForms 2.0 library. 'Late' binding sheets(1).cells(1).resize(Ubound(a_sn),Ubound(a_sn,2))=a_sn sheets(1).cells(1).currentregion.copy With GetObject("New:{1C3B4210F44111CEB9EA00AA006B1A69}") .GetFromClipboard
End Withc00 = .GetText 'Early' binding ' reference to Microsoft Forms 2.0 Object Library sheets(1).cells(1).resize(Ubound(a_sn),Ubound(a_sn,2))=a_sn sheets(1).cells(1).currentregion.copy With New DataObject .GetFromClipboard
End Withc00 = .GetText 6.15.2.4 'Name' in Excel Assign an array to a Name. The content of the array will be written as a string to the Name's property '.Refersto'. The string can be retrieved, after the "={ ... }" has been removed. Restriction: the combination of row delimiters, column delimiters and content shouldn't exceed 8221 characters. a_sn=range("A1:K10") Names.Add "proof", a_sn CreateObject("Scripting.FileSystemObject").CreateFile("G:\OF\example.csv").write mid(Names("proof"),3,len(Names("proof"))3) 6.16 Arrays and Excel formulae You can do calculations in arrays using Excel formulae. In VBA Excel formulae are being called 'Worksheetfunctions'. 6.16.1 Writing conventions In VBA you can invoke formulae in three different ways ( e.g. the formula MAX) :  Application.Worksheetfunction.Max( .. )  Worksheetfunction.Max( .. )  Application.Max( .. ) The format Application.formula differs essentially from the format (Application.)Worksheetfunction.formula (thanks to R. Ceulemans who drew my attention to this). 6.16.1.1 Error handling Application.worksheetfunction.formula / Worksheetfunction.formula If the .worksheetfunction.formula generates an error, e.g. because a certain value has not been found, the VBAcode will be ended. Example a_sp=array(2,4,6,8,10) a_sr=application.worksheetfunction.match(6,sp,0) a_sr=worksheetfunction.match(6,sp,0) a_sp=array(2,4,6,8,10) a_sr=application.worksheetfunction.match(5,a_sp,0) a_sr=worksheetfunction.match(5,a_sp,0) Application.formula The Application.formula version registers the error and stores it in the resulting variable a_sr. a_sp=array(2,4,6,8,10) a_sr=Application.match(5,a_sp,0) VBA doesn't generate a message box, the VBA code continues. You can use the error code in the variable a_sr, e.g.: if iserror(a_sr) then msgbox "not found" 6.16.1.2 The use as if it's an arrayformula Application.WorksheetFunction.formula / WorksheetFunction.formula The format Application.worksheetfunction and application.worksheetfunction format can't act like arrayformulae. They generate error messages and stop the VBA code. Example a_sp=array(2,4,6,8,10) a_sn=array(2,6,10) a_sr=application.worksheetfunction.match(a_sn,a_sp,0) a_sr=worksheetfunction.match(a_sn,a_sp,0) Application.formula The Application.formula handles the formula arguments as if it were an array formula. It seems as if this method contains an implicit 'Evaluate' method. a_sp=array(2,4,6,8,10) a_sn=array(2,6,10) a_sr=application.match(a_sn,a_sp,0) The resulting variable is a 1dimensional array; it's size identical to the size of the argument array a_sn, but with Lbound 1. In this example the resulting values are: a_sr(1)=1, a_sr(2)=3 and a_sr(3)=5 If a value can't be found it's no problem: a_sp=array(2,4,6,8,10) a_sn=array(2,6,12) a_sr=application.match(a_sn,a_sp,0) You can use it e.g. in if iserror(a_sr(3)) then msgbox a_sn(2) & " has not been found" this is also applicable to 2dimensional arrays: a_sn=range("A1:K10") a_sr=application.find("abc",a_sn) The resulting array a_sr is a 2dimensional array with the same size (10*11). If the string is being found a 1 will be stored in the matching 'cell' in the resulting array a_sr. If the string is not being found 'error 2012' will be stored in the matching 'cell'. How you can use this formula for several purposes, cfr.6.2.1.1 The Excel formula 'Find' Conclusion The use of Application.formula offers much more access to the use of Excel formulae than then (Application.)WorksheetFunction.formula variant. Besides for Application.formula you don't need an extra error handling procedure to guarantee the continuation of the code. Arguments In VBA arguments have to be separated by a comma, regardless of any International setting in Office or Windows. E.g. msgbox application.date(2016,6,21) Formula syntax in a worksheet vs. VBA syntax Sometimes formulae contain 'dots' when entered in a worksheet like MODE.SNGL. In VBA the dot has to be replaced by an 'underscore' _ e.g. msgbox application.mode_sngl(a_sn) Evaluate and Formulae In 'Evaluate' you can't use variables, so arrays won't do either. This code errors out: a_sn=sheet1.range("A1:A10") y=[sum(a_sn)] a_sn=sheet1.range("A1:A10") y=Evaluate("sum(a_sn)") A workaround is the use of a Named Range: a_sn=sheet1.range("A1:A10") Application.names.add "snb_01", a_sn msgbox [sum(snb_01)] msgbox Evaluate("sum(snb_01)") In 'Evaluate' an array can be evaluated only if the array has been transformed into a string first: You can convert a 1dimensional array into a string using 'transpose' and 'join'. a_sn=sheet1.range("A1:A10") msgbox Evaluate("sum(" & join(application.transpose(a_sn),",") For a 2dimensional array the workaround of a Named Range is an option: a_sn=sheet1.range("A1:A10") Application.names.add "snb_01",a_sn msgbox Evaluate("sum(" & mid(application.names("snb_01").value,2) & ")") 6.16.2 Speed The use of Excel formulae in VBA is not faster than the use of 'original' VBAmethods. You can retrieve the position of a value in a 1dimensional array using then formula 'match'. A loop in VBA is 'faster'. The difference in speed is so tiny that a user won't notice it: a nondiscernible difference is equal to 'no difference'. In extreme cases you can take this 'difference' into account. The simplicity of the code is an advantage of the use of Excel formulae: msgbox application.match("abc",a_sn,0) compared to for j=1 to ubound(a_sn) if a_sn(j,1)="abc" then exit for
nextMsgbox j 6.16.3 Excel formulae: an inventory Not every formula can be applied to an Array. A necessary condition is the availability of an argument to assign the array to; e.g. RAND(), TODAY() and NOW() have no arguments. If arguments are being restricted exclusively to numbers  e.g. DATE(..,..,..), MOD(..,..)  or strings  e.g. DATE(..), Clean(..)  they are not applicable to Arrays. If arguments require a Range, as is the case with AGGREGATE(number,Range) or RANK(Range,Range), the formula isn't available for arrays. I wasn't able to distil a common criterion which formulae accept arrays as argument. Nor the worksheet input help, nor Intellisense in VBA gave much clues. So it's also a question of 'trial & error'. The list that follows has been tested and proven to be applicable to arrays. Probably the list isn't complete. The result of a formula can be a value or an array. The examples make use of a 2dimensional array unless indicated otherwise. If the result is a value I use the variable 'y'. A resulting array has the name 'a_sr'. Some formulae do not calculate anything but convert/transform the array like: TRANSPOSE(..) and INDEX(..). If a formula in Excel 2010 is 'obsolete', and only is available because of compatibility with previous versions it's indicated with (2007). In the examples I use two 2dimensional arrays (a_sn and a_sq). The VBAcode for 1dimensional arrays is identical. a_sn = Range("A1:D10") a_sq = Range("A11:D20")
6.17 Find and replace in an array 6.17.1 Excelformula 'Substitute' The VBAfunction 'replace' doesn't affect an array. The Excel method 'Range.replace' is only applicable to ranges and not to arrays. The Excelformula 'Substitute' changes ranges in arrayformulae. It appears to change arrays too. Keep in mind that the replacement takes place as if all elements in the array consist of strings. Only strings are the result of the replacement. Every string in the array ( at the start, inside, at the end or as a entire element in the array) is being replaced by the replacement string. Wildcards like * or ? have no meaning in combination with 'Substitute'. 'Substitute' is case sensitive. The argument which stringinstance should be replaced is being ignored. sn = Array("aa1", "aa2", "cc", "aa4") sr = Application.Substitute(sn, "aa", "bb") Result : sr(1) ="bb1", sr(2)="bb2", sr(3)=" 