Suggestions | Arrays |

1. What is an array 2 Array names 3 Kinds of arrays 3.1 1-dimensional 3.2 multi-dimensional 4 Properties 4.1 Lower limit: Lbound 4.2 Upper limit: Ubound 4.3 Array type: Vartype 4.4 Array TypeName 4.5 IsArray 4.6 ReDim 4.7 Erase 4.7.1 Variant containing array 4.7.2 Dynamic array 4.7.3 Fixed size array 5. How to create an array ? 5.1 Method 'Array' 5.2 Method Split 5.3 Declare the variable 5.3.1 How to declare 5.3.1.1 1-dimensional: fixed size 5.3.1.2 2-dimensional: fixed size 5.3.1.3 1-dimensional: dynamic 5.3.1.4 2-dimensional: dynamic 5.3.2 Scope and location 5.3.2.1 Scope 'Local' 5.3.2.2 Scope 'Private' 5.3.2.3 Scope 'Public' 5.3.3 Variant or Array 5.3.3.1 Variant variable 5.3.3.2 Array variable 5.3.4 Declare the content 5.4 Object properties 5.4.1 Range in Excel 5.4.1.1 2-dimensional array 5.4.1.2 1-dimensional array 5.4.2 ActiveX-control 5.4.3 Method 'Evaluate' 5.4.3.1 1-dimensional array 5.4.3.2 2-dimensional array 5.4.4 Dictionary 5.4.4.1 1-dimensional array 5.4.4.2 2-dimensional array 5.5 The argument ParamArray 6. Working with arrays 6.0 Excel function Index 6.0.1 Read 1 value 6.0.2 Read 1 row 6.0.3 Read 1 column 6.0.4 Read several rows/columns 6.0.4.0 All data 6.0.4.1 Filter 'rows' / 'records' 6.0.4.2 Filter 'columns' 6.0.4.3 Filter 'rows' / 'records' in different order 6.0.4.4 Filter columns in different order 6.1 Resize an array 6.1.1 1-dimensional array 6.1.2 multi-dimensional array 6.1.3 1-dimensional: keep data 6.1.4 2-dimensional: keep data 6.2 Item occurrence 6.2.1 1-dimensional array 6.2.1.1 Excel function 'Match' 6.2.1.2 Excel formula 'Find' 6.2.1.3 VBA method 'filter' 6.2.1.4 Loop by index number 6.2.1.5 Loop by element 6.2.2 2-dimensional array 6.2.2.1 A loop 6.2.2.2 Excel function 'Match' 6.2.2.3 Excel-object 'Names' 6.3 Item position 6.3.1 1-dimensional array 6.3.1.1 Excel function 'match' 6.3.1.2 VBA 6.3.2 2-dimensional array 6.3.2.1 A loop 6.3.2.2 'Name' in Excel 6.4 Copy item 6.4.1 1-dimensional array 6.4.1.1 VBA: join and split 6.4.2 2-dimensional array 6.4.2.1 Excel function 'Index' 6.5 Filter items 6.5.1 1-dimensional array 6.5.1.1 VBA method 'filter' 6.5.2 2-dimensional array 6.5.2.1 Excel function Index 6.6 Filter 'rows'/'records' 6.6.1 Excel function Index 6.7 Filter columns 6.7.1 Excel function Index 6.8 Delete items 6.8.1 1 item by index number 6.8.1.1 1-dimensional array 6.8.1.1.1 VBA: Filter 6.8.1.1.2 ActiveX control 6.8.2 2-dimensional array 6.8.2.1 ActiveX-control 6.8.3 1 item by content 6.8.3.1 1-dimensional array 6.8.3.1.1 VBA: Filter 6.8.3.1.2 ActiveX 6.8.4 Several items by content 6.8.4.1 1-dimensional array 6.8.4.1.1 VBA: Filter 6.9 Delete 'columns' 6.10 Delete 'rows' 6.11 Sorting items 6.11.1 1-dimensional array 6.11.1.1 Arraylist 6.11.1.2 Sortedlist 6.11.1.3 ADODB 6.11.1.4 Excel worksheet 6.11.2 2-dimensional array 6.11.2.1 Arraylist 6.11.2.2 Sortedlist 6.11.2.3 ADODB 6.11.2.4 Excel worksheet 6.12 Insert empty rows 6.13 Join Arrays 6.13.1 1-dimensional array 6.13.1.1 VBA: Join & Split 6.13.1.2 VBA: Redim 6.13.1.3 Excel worksheet 6.13.2 2-dimensional array 6.13.2.1 Excel worksheet 6.13.2.2 Dictionary 6.14 Convert arrays 6.14.1 1 to 2-dimensional 6.14.1.1 'Transpose' 6.14.1.2 ActiveX-control 6.14.1.3 Dictionary 6.14.2 2 to 1-dimensional 6.14.2.1 Convert a 'row' 6.14.2.1.1 'Index' 6.14.2.1.2 'Transpose' 6.14.2.2 Convert a 'Column' 6.14.2.2.1 'Transpose' 6.14.2.2.2 'Index' 6.15 Convert to string 6.15.1 1-dimensional array 6.15.1.1 VBA: join 6.15.2 2-dimensional array 6.15.2.1 A loop 6.15.2.2 'Index' 6.15.2.3 Clipboard 6.15.2.4 'Name' in Excel 6.16 Arrays and Excel formulae 6.16.1 Writing conventions 6.16.1.1 Error handling 6.16.1.2 Use as array formulae 6.16.2 Speed 6.16.3 Excel formulae: inventory 6.17 Find and Replace in an array 6.17.1 Excel formula 'Substitute' |
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, VBA-objects, 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. 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. VBA distinguishes 1-dimensional and multi-dimensional arrays In the array several items/elements are being stored in a certain order. You can consider a 1-dimensional 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) A 2-dimensional 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 2-dimensional array using the serial number in each dimension; e.g. a_sn(2,4), a_sn(10,1) You can compare a 3-dimensional 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 3-dimensional 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 multi-dimensional arrays ? Take Excel as an example: 2-dimensional array A worksheet is a 2-dimensional array. You can find a cell specifying its horizontal dimension (row) and its vertical dimension (column): cells(4,10) is equivalent to cell J4. 3-dimensional array A third dimension is the number of worksheets in a workbook: sheets(3).cells(4,10) Written as a 3-dimensional array: a_sn(4,10,3) The first dimension represents the rows, the second dimension represents the columns and the third dimension the worksheets. 4-dimensional array The number of opened workbooks can be considered as a fourth dimension workbooks(7).sheets(3).cells(4,10) Written as a 4-dimensional array: a_sn(4,10,3,7) 5-dimensional 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. 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 10-digit system 0 is the first number. The first element - in a 1-dimensional array: a_sn(0), - in a 2-dimensional array: a_sn(0,0) - in a 3-dimensional 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) - 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) Retrieve the upper limit (Ubound) of a 1-dimensional array x=Ubound(a_sn) In multi-dimensional 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 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 Vartype-numbers 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) 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 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 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 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 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 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 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) - 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. This method does 2 things simultaneously: it defines an array and fills it with items. You can only produce 1-dimensional arrays with this method. With this method you can store strings, numbers, dates, objects, arrays and every combination of these. VBA-technically 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 You can only produce 1-dimensional 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. VBA-technically: 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 array-variable 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 1-dimensional fixed size array variable Dim a_sp(5) - 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) 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) 5.3.1.2 Declare a multi-dimensional fixed size array variable Dim a_sp(500,20) - 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) - 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) - 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 1-dimensional dynamically sized array-variable 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 the 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.count-1) for j=0 to Ubound(a_sn) a_sn(j)=thisworkbook.sheets(j+1).name
nextReDim 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 multi-dimensional dynamically sized array-variable 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 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.count-1, thisworkbook.sheets.count-1) 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)
nextReDim 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 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)
end sub' VBA code sub M_example() Redim a_sn(5)
end sub' VBA code 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) Macros/functions in other codemodules can't use these variables. 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 Variant-variable or as array-variable 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 Dim a_sn 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)) Dim a_sn Sub M_snb() ReDim a_sn(9)
end suba_sn(UBound(a_sn)) = 312 MsgBox a_sn(UBound(a_sn)) Dim a_sn Sub M_snb() a_sn= Array (12,45,36)
end subMsgBox a_sn(UBound(a_sn)) Dim a_sn Sub M_snb() a_sn= split ("aaa,450," & date,",")
end subMsgBox a_sn(UBound(a_sn)) 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. 1-dimensional array: fixed size Dim a_sn(5) Dim a_sn(1 to 22) Private a_sn(40) Public a_sn(6) Dim a_sn() Private a_sn() Public a_sn() 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) Dim a_sn() Private a_sn() Public a_sn() 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 dim A_sn(27) as Integer dim A_sn() as Integer 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 a string array: String() dim a_sn() As String MsgBox TypeName(a_sn) 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 1-dimensional or multi dimensional array.An Excel worksheet is a 2-dimensional array 5.4.1 Range in Excel worksheet 5.4.1.1 A multi dimensional array a_sn=range("A1:K6").value - 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] 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) Also 1 row only or 1 column only is a multi dimensional array. Excel has 2 functions that can convert a multi-dimensional array into a 1-dimensional 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) 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))] Application.Transpose a_sn = Application.Transpose(Range("A1:A10").Value) a_sn = [transpose(A1:A10)] - 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). A ComboBox and a ListBox is/contains an array. That can be a 1-dimensional array or a 2-dimensional 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 a_sn=ComboBox1.List a_sn=ListBox1.list 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 ComboBox1.Listcount: 4 Ubound(ComboBox.list)=3 to retrieve the first item c00=ComboBox1.list(0) So a ComboBox/ListBox can be useful to adapt, add or remove items in an array. 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. Data in 1 column a_sn = [transpose(A1:A20)] - its content the values in Range A1:A20 - Lbound: 1 Data in 1 row a_sn = [transpose(transpose(A1:K1))] - 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,""))] a_sn = [transpose(if(A1:A20="","",""))] a_sn = [transpose(transpose(if(A1:K1="","","")))] a_sn = [transpose(transpose(iferror(A1:K1/0,"")))] 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))] a_sn = [transpose(12*A1:A20)] a_sn= [transpose(row(A1:A20))] Declare - an array holding 20 elements - the numbers 1 t/m 20 - Lbound: 1 a_sn = [transpose(text(today()+row(1:365),"dd-mm-yyyy"))] - 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- all permutations of 3 elements from the numbers 1 to 5 - Lbound: 1 The simplest: a Range in a worksheet a_sn=[A1:K10] - 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="","","")] - Lbound 1 for every dimension A calculated 2-dimensional 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- 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- every row number raised to the power of the column number - Lbound 1 for both dimensions A dictionary can contain independent data. Every Item gets a unique key. The dictionary creates a 1-dimensional 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 - Lbound: 0 If the items in a dictionary consists of separate strings, numbers or dates you can use the 1-dimensional 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 - Lbound: 0 A dictionary can store 1-dimensional arrays. With the Excel function 'index' you can concatenate those into a 2-dimensional array. The 1-dimensional 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) - a 2-dimensional array holding 3 * 4 elements - Lbound: 0 for both dimensions NB. You can't concatenate 2-dimensional arrays using the function Index. 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 subSub M_snb() M_snb_000 "Illustration",4,date
End subSub M_snb_000(c00,y,d01) msgbox c00
end submsgbox y msgbox d01 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 Paramarray is a dynamic 1-dimensional 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 1-dimensional: 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) ' 1-dimensional array MsgBox a_sn(4).Address ' Object MsgBox a_sn(5)(2, 2) ' 2-dimensional array 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. 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 valuec00=application.index(a_sn,3,5) c00=a_sn(3,5) 6.0.2 Retrieve 1 row from a 2-dimensional array Retrieve row 3 from array a_sna_sp=application.index(a_sn,3,0) 6.0.3 Retrieve 1 column from a 2-dimensional array Retrieve column 5 from array a_sn.a_sp=application.index(a_sn,0,5) Array a_sp is a 2-dimensional 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 thesecond argument of the Index function you can indicate which 'rows' to retrieve.If you want more than 1 row you can use a 2-dimensional array containing all row numbers you want to 'filter'. The result of the Index function is a 2-dimensional 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 1-dimensional array containing all column numbers you want to 'filter' How to create a 2-dimensional 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 1-dimensional array array(..,..,) to a 2-dimensional 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 1-dimensional array that is the result of split() to a 2-dimensional 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 1-dimensional array that is the result of split() to a 2-dimensional array: a_sr(1, 1) ... a_sr(4, 1) How to create a 1-dimensional array containing the columns to retrieve ? a_sc=array(1,3,5,6) Result: 1-dimensional 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: 1-dimensional 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: 1-dimensional 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 2-dimensional array that is the result of row( .. ) to a 1-dimensional array: a_sc(1)=1, ... a_sc(8)=8
6.0.4.0 All values in an array Assuming a 2-dimensional 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)])) 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 7a_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 6a_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 ordera_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 ordera_sp=application.index(a_sn,[row(1:10)],array(6,4,1)) So it's impossible to resize the array with Dim a_sp(5) Redim a_sp(12) This code errors out a_sn=Array(23,44,36,47,82) Redim a_sn(12) - 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) Dim a_sn Redim a_sn(12) ReDim a_sn(6)
msgbox UBound(a_sn)
ReDim a_sn(20)
msgbox Ubound(a_sn)
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) Dim a_sn a_sn=Array(23,44,36,47,82) Redim a_sn(12) Redim a_sn(0) a_sn=Array(23,44,36,47,82) Redim a_sn(12) It is not possible to use the method split if the array has been declared an a dynamic arrayThis code errors out Dim a_sn() a_sn=split("a23,b44,c36,d47,e82",",")) not possible to use Split to populate a variable that has been declared by the method RedimThis code errors out Redim a_sn(0) a_sn=split("23,44,36,47,82",",") Dim a_sn a_sn=split("23,44,36,47,82",",") Redim a_sn(12) This code errors out Dim a_sp(5,10) ReDim a_sp(12,7) This code errors out a_sn=Range("A1:K10") Redim a_sn(12,7) - 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) Dim a_sn Redim a_sn(12,10) ReDim a_sn(thisworkbook.sheets.count-1, thisworkbook.sheets.count-1) 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 1-dimensional array and keeping its content The VBA method Redim resizes an array; the keyword 'Preserve' indicates that the contents shouldn't be wiped out.1-dimensional 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 multi-dimensional 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. 2-dimensional 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) 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.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)) 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) Because of the error values this 1-dimensional 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 a_sn=Array("aa1", "aa2", "aa3", "aa4") msgbox application.sum(application.find("aa2",a_sn)) a_sp=filter(a_sn,"abcde") - 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 1-dimensional array MsgBox "Item 'abcde' does " & IIf(UBound(Filter(a_sn, "abcde")) = -1, "not ", "") & "appear in array a_sn" 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~") 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" 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" 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) 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) 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" 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" 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 2-dimensional 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) 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 Excel-object '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.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) "aa3" is the third item in the array, but because of the lower bound 0, it matches a_sn(2) 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), "|")) 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 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.1 VBA methods join en split copy an item immediately after/before the item to be copieda_sn=array("aa1","aa2","aa3","aa4","aa5") a_sn(3)=a_sn(3) & "," & a_sn(3) a_sn=split(join(a_sn,","),",") a_sn=array("aa1","aa2","aa3","aa4","aa5") a_sn=split(join(a_sn,",") & "," & a_sn(3),",") 6.4.2.1 Excel function 'Index' See: Function Index: detailed explanationCopy 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)) 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))]) 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 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") - a_sp is a 1-dimensional array containing 3 string elements - array a_sp contains the elements "1250", "aa12" and "aa14" - Lbound: 0; Ubound: 2 A 2-dimensional 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. 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)) 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 2-dimensional array A multi-dimensional array has no filter method.With the use of the Excel function Index you can get the same result. See: Function Index: detailed explanation Filter the 'rows' 1,3 and 5 of a 2-dimensional array. a_sn=range("A1:H10") a_sp=application.index(a_sn,application.transpose(array(1,3,5)),application.transpose([row(1:8)])) a_sn=range("A1:H10") a_sp=application.index(a_sn,application.transpose(array(5,3,1)),application.transpose([row(1:8)])) 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 2-dimensional array A multi-dimensional array has no filter method.With the use of the Excel function Index you can get the same result. See: Function Index: detailed explanation Filter the columns 1,3 and 5 of a 2-dimensional array. a_sn=range("A1:H10") a_sp=application.index(a_sn,[row(1:10)],array(1,3,5)) a_sn=range("A1:H10") a_sp=application.index(a_sn,[row(1:10)],array(5,3,1)) 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.1 Delete 1 item by index number a_sn=array("aa1","aa2","aa3","aa4","aa5") 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) 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 If you need an 1-dimensional resulting array: a_sn=array("aa1","aa2","aa3","aa4","aa5") ListBox1.List=a_sn ListBox1.removeitem 2 a_sn = Application.Transpose(ListBox1.List) With the use of an ActiveX-control it can be accomplished. Assign the array to an ActiveX-control: 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 The method Filter has an argument/switch to keep the items that donot 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) - a 1-dimensional 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) a_sn=array("aa1","aa2","aa3","aa4","aA4a","aa5") a_sp=filter(a_sn,"A4",false,0) - a 1-dimensional 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) 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),"|"),"~"),"|") 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) 6.8.4 Delete several items by content The VBA method filter deletes all items in a 1-dimensional array that do not contain a certain filter stringa_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5") a_sn=filter(a_sn,"a4",false) 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) 6.9 Delete 'columns' in a 2-dimensional 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 1-dimensional 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 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")) 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 2-dimensional 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 2-dimensional 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 contains the values in in the rows 1,5, 10 and 14 in array a_sn - the rows 2-4, 6-9 and 11-13 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 VBA has no array sorting method.You can use sorting methods in other VBA libraries (arraylist, sortedlist, ADODB) or in an Excel worksheet. 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() - assign the items to sort to the arraylist - after sorting the property .toarray() contains the sorted items. 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)
NextCreate 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) 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 You can use sorting methods in other VBA libraries (arraylist, sortedlist, ADODB) or in an Excel worksheet. Create a 1-dimensional 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) & "))")) - the Excel function index requires a 2-dimensional 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 2-dimensional array using transpose, and use it as row argument in the Excel function Index 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 2-dimensional 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)) 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) 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 2-dimensional 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 2-dimensional array in which order rows from an array have to be read. See: Function Index: detailed explanation - the result is a new 2-dimensional 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 With a_sp = Application.Index(a_sn,Application.Transpose(Array(1, 2, 3, 4, 5, 6,7)), Array(1, 2, 3, 4, 5)) 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 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) & "))")) VBA has some tools with which you can accomplish a combination 6.13.1.1 VBA methods Join & Split If two 1-dimensional 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,"|"),"|") 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 IfAssign the values in that row to a variable. Convert the resulting 2-dimensional variable to a 1-dimensional 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))) 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 If you add two (or more) 2-dimensional arrays 'row-by-row' 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 1-dimensional array to 2-dimensional array v.v. 6.14.1 convert 1-dimensional array to a 2-dimensional array Only a 2-dimensional array can be written vertically to a worksheet.A 1- dimensional array needs to be converted. A 1-dimensional 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) a_sp=application.transpose(Array("aa1","aa2","aa3","aa4","aa5")) - a_sp is a 2-dimensional 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. The assigning of a 1-dimensional array to a ComboBox or ListBox converts the 1-dimensional array into a 2-dimensional 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 - 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 A dictionary can combine several 1-dimensional arrays to a 2-dimensional 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 - 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 2-dimensional array to a 1-dimensional array The conversion of two special 2-dimensional arrays: the 'row' array and the 'column' array. A 'row' array has 1 row and several columns.You can create one using a_sn=range("A1:K1") - dimension 2: Lbound 1, Ubound 11 6.14.2.1.1 Excel function Index See: Function Index: detailed explanationa_sn=range("A1:K1") a_sn=application.index(a_sn,1,0) - a 1-dimensional array a_sn - with Lbound(a_sn) =1 - with Ubound(a_sn) = 11 If you need a 1-dimensional array and an Lbound 0 a_sn=range("A1:K1") a_sn=filter(application.index(a_sn,1,0),"") - a 1-dimensional 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)) - a 1-dimensional array a_sn - with Lbound(a_sn) =1 - with Ubound(a_sn) = 11 If you need a 1-dimensional array and an Lbound 0 a_sn=range("A1:K1") a_sn=filter(Application.Transpose(Application.Transpose(a_sn)),"") - a 1-dimensional array a_sn - with Lbound(a_sn) =0 - with Ubound(a_sn) = 10 A 'column' array has 1 column and several rows. You can create it using a_sn=range("A1:A12") - a 2-dimensional 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) - a 1-dimensional array a_sn - dimension 1: Lbound 1, Ubound 12 If you need a 1-dimensional array ans Lbound 0 a_sn=range("A1:A12") a_sn = filter(Application.Transpose(a_sn),"") 6.14.2.2.2 Excel function Index See: Function Index: detailed explanationThe 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) - a 1-dimensional array a_sn - Lbound(a_sn) =1 - Ubound(a_sn) = 12 6.15 Convert an array to a string Any 1-dimensional 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) - string: "aa1 20-10-2014 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,"") msgbox join(a_sn,"|") msgbox "This is item " & join(a_sn,vbLf & "This is ") a_sn=Range("A1:K12") for each it in a_sn c00=c00 & "|" & it
nextmsgbox c00 The function Index can convert any row in a 2-dimensional array to a 1-dimensional array. The method Join can convert that 1-dimensional 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),"_")
nextCopy 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:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .GetFromClipboard
End Withc00 = .GetText ' 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 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'. 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). Application.worksheetfunction.formula / Worksheetfunction.formulaIf the .worksheetfunction.formula generates an error, e.g. because a certain value has not been found, the VBA-code 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.formulaThe 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.formulaThe 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.formulaThe 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) 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" a_sn=range("A1:K10") a_sr=application.find("abc",a_sn) The resulting array a_sr is a 2-dimensional 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'
## ConclusionThe 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. ## ArgumentsIn 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 syntaxSometimes 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 FormulaeIn '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_sn=sheet1.range("A1:A10") Application.names.add "snb_01", a_sn msgbox [sum(snb_01)] msgbox Evaluate("sum(snb_01)") You can convert a 1-dimensional array into a string using 'transpose' and 'join'. a_sn=sheet1.range("A1:A10") msgbox Evaluate("sum(" & join(application.transpose(a_sn),",") a_sn=sheet1.range("A1:A10") Application.names.add "snb_01",a_sn msgbox Evaluate("sum(" & mid(application.names("snb_01").value,2) & ")") You can retrieve the position of a value in a 1-dimensional 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 non-discernible 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) 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 2-dimensional 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 2-dimensional arrays (a_sn and a_sq). The VBA-code for 1-dimensional 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 VBA-function '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 string-instance should be replaced is being ignored. sn = Array("aa1", "aa2", "cc", "aa4") sr = Application.Substitute(sn, "aa", "bb") |