VBA for smarties: Arrays

Arrays

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, 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.

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 1-dimensional and multi-dimensional arrays

3.1 1-dimensional array

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)

3.2 multi-dimensional array

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.

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 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)

In a multi-dimensional 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 1-dimensional array

x=Ubound(a_sn)

Since the default Lbound is 0, an array contains Ubound(a_sn)+1 elements.

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

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 Vartype-numbers of the distinct content types; for a normal variabele and after () for an array variable.

2 () 8194
3 () 8195
4 () 8196
5 () 8197

Integer
Long
Single
Double

6 () 8198
7 () 8199
8 () 8200
9 () 8201

Currency
Date
String
Object

11 () 8203
12 () 8204
17 () 8209
36 () 8228

Boolean
Variant
Byte
UserDefinedType

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

After 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)

a_sp(j, jj) = j * jj

Next

MsgBox 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)

a_sp(j, jj) = j * jj

Next

MsgBox 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)

a_sq(j, jj) = j * jj

Next

MsgBox 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 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

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 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 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 multi-dimensional 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 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)

In these examples the variable's lower limit is 0
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

If 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)

In this case the number of elements equals the Ubound of the array.
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

5.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 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.count-1, thisworkbook.sheets.count-1)

for j=0 to Ubound(a_sn)

for jj=0 to Ubound(a_sn,2)

a_sn(j,jj)=choose(jj+1,thisworkbook.sheets(j+1).name,thisworkbook.sheets(j+1).hyperlinks.count)

If 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)

a_sn(j,jj)=choose(jj,thisworkbook.sheets(j).name,thisworkbook.sheets(j).hyperlinks.count)

5.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 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

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)

a_sn(UBound(a_sn)) = 12
MsgBox a_sn(UBound(a_sn))

end sub

ReDim in the macro

Dim a_sn

Sub M_snb()

ReDim a_sn(9)

a_sn(UBound(a_sn)) = 312
MsgBox a_sn(UBound(a_sn))

end sub

Array in the macro

Dim a_sn

Sub M_snb()

a_sn= Array (12,45,36)
MsgBox a_sn(UBound(a_sn))

end sub

Split in the macro

Dim a_sn

Sub M_snb()

a_sn= split ("aaa,450," & date,",")
MsgBox a_sn(UBound(a_sn))

end sub

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.

1-dimensional array: fixed size

Dim a_sn(5)
Dim a_sn(1 to 22)
Private a_sn(40)
Public a_sn(6)

1-dimensional array: dynamic size

Dim a_sn()
Private a_sn()
Public a_sn()

multi-dimensional 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 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

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 1-dimensional 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 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)

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 ActiveX-control

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

Write the ComboBox/ListBox-data 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 1-dimensional 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,""))]

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),"dd-mm-yyyy"))]

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 2-dimensional 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 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

- 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 1-dimensional array

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")

x0=.item("one")
x0=.item("two")
x0=.item("three")
a_sn=.keys

end with

end sub

- 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 1-dimensional array that contains all items.

Sub M_snb()

with createobject("scripting.dictionary")

.item("it_" & .count)="one"
.item("it_" & .count)="two"
.item("it_" & .count)="three"
a_sn=.items

end with

end sub

- an array a_sn holding 3 elements: "one", "two" en "three"
- Lbound: 0

5.4.4.2 2-dimensional array

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")

.item("it_" & .count)=array("one","two","three","four")
.item("it_" & .count)=array("four","five","six","seven")
.item("it_" & .count)=array("seven","eight","nine","ten")
a_sn=application.index(.items,0,0)

end with

end sub

result:
- 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.

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 sub

Sub M_snb_000(c00)

msgbox c00

end sub

If you want to pass several arguments you can do so separating them by commas.

Sub M_snb()

M_snb_000 "Illustration",4,date

End sub

Sub M_snb_000(c00,y,d01)

msgbox c00
msgbox y
msgbox d01

end sub

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 sub

Sub M_snb_000(c00 As String, y As Integer, d01 As Date)

msgbox c00
msgbox y
msgbox d01

end sub

Instead of passing arguments to several receiving variables you can use a paramarray.
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 Sub

Sub M_snb_param(ParamArray a_sn())

MsgBox LBound(a_sn) & vbLf & UBound(a_sn)
MsgBox 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

End Sub

6. Working with arrays

An array isn't a VBA-object 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)

But that result isn't better than

c00=a_sn(3,5)

6.0.2 Retrieve 1 row from a 2-dimensional array

Retrieve row 3 from array a_sn

a_sp=application.index(a_sn,3,0)

Remarkably the function 'Index' converts the selected 'row' in a 1-dimensional array, Lbound 1 and Ubound the Ubound of the second dimension of array a_sn.

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)

Result
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 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 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)]))

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 1-dimensional 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 multi-dimensional 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.count-1, thisworkbook.sheets.count-1)

for j=0 to Ubound(a_sn)

for jj=0 to Ubound(a_sn,2)

a_sn(j,jj)=choose(jj+1,thisworkbook.sheets(j).name,thisworkbook.hyperlinks.count)

next

Redim 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)

a_sn(j, jj) = j & "_" & jj

Next

ReDim Preserve a_sn(3, 7)

MsgBox a_sn(2, 3) & vbLf & UBound(a_sn) & vbTab & UBound(a_sn, 2)

A 3-dimensional array.

ReDim a_sn(3, 3, 3)

For j = 0 To UBound(a_sn)

For jj = 0 To UBound(a_sn, 2)

For jjj = 0 To UBound(a_sn, 3)

a_sn(j, jj, jjj) = j & "_" & jj & "_" & jjj

Next

ReDim 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-dimensional 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 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

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 1-dimensional 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 1-dimensional 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

next

msgbox 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

next

msgbox 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=""

next

msgbox "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 = ""

next

msgbox "Item aa3 has " c00 & "been found"

6.2.2 2-dimensional array

6.2.2.1 A loop

To check the occurrence of an item in a 2-dimensional array

For Each it In a_sn

If it = "aa33" Then

c00 = " "
Exit For

End If

Next
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

next
msgbox 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

Next
MsgBox 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-dimensional 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 2-dimensional array

6.3.2.1 A loop

for j=0 to Ubound(a_sn)

for jj=0 to Ubound(a_sn)

if a_sn(j,jj)="abcde" then exit for

next
if jj< (Ubound(a_sn)+1) then exit for

next

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 1-dimensional 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 2-dimensional 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 1-dimensional 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 1-dimensional array containing 3 string elements
- array a_sp contains the elements "1250", "aa12" and "aa14"
- Lbound: 0; Ubound: 2

6.5.2 2-dimensional array

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.

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 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.

6.6.1 Excel function Index

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)]))

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 2-dimensional 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

next

a_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.

6.7.1 Excel function Index

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))

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 1-dimensional 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

next

a_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 1-dimensional 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 ActiveX-control: 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 2-dimensional array.
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)

6.8.2 2-dimensional array

VBA has no method to delete an item by index number in a 2-dimensional array.
With the use of an ActiveX-control it can be accomplished.

6.8.2.1 ActiveX-control

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

6.8.3.1 1-dimensional 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 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)

Case sensitive

a_sn=array("aa1","aa2","aa3","aa4","aA4a","aa5")
a_sp=filter(a_sn,"A4",false,0)

Result
- 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)

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 1-dimensional array

6.8.4.1.1 VBA method filter

The VBA method filter deletes all items in a 1-dimensional 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 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 is a 2-dimensional 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 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 is a 2-dimensional 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 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

6.11.1 1-dimensional 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)

.Add a_sn(j)

Next
.Sort
a_sp = .toarray()

End With

- 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)

.Item(a_sn(j)) = ""

Next

For j = 0 To .Count - 1

a_sn(j) = .getkey(j)

End With

6.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
.Open

For j = 0 To UBound(a_sn)

.AddNew
.Fields("item")= a_sn(j)
.Update

Next
.Sort = "item"
a_sp = split(.GetString,vbCr)

End With

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 2-dimensional array

VBA has no sorting method for a 2-dimensional 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 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) & "))"))

- an arraylist can only contain values, no 'keys' (like in an dictionary, collection or sortedlist)
- 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

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 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)

.Item(a_sn(j, 1)) = j

Next

For j = 0 To .Count - 1

a_sr(j,0) = .Item(.getkey(j))

End With

a_sp = Application.Index(a_sn, a_sr, Array(1, 2, 3, 4, 5))

6.11.2.3 ADODB

a_sn = Range("A1:K14")

With CreateObject("ADODB.recordset")

For j = 1 To UBound(a_sn, 2)

.Fields.Append "item" & j, 200, 30

Next
.Open

For j = 1 To UBound(a_sn)

.AddNew
For jj = 1 To UBound(a_sn, 2)

.Fields("item" & jj) = a_sn(j, jj)

Next
.Update

Next
.Sort = "item1"
a_sp = Application.Transpose(.GetRows)

End With

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 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

This 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 With

a_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 1-dimensional array

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,"|"),"|")

6.13.1.2 VBA method Redim

If two 1-dimensional 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

a_sq(j) = a_sp(j - UBound(a_sn) - 1)

Else

a_sq(j) = a_sn(j)

End If

6.13.1.3 Excel worksheet

Write both 1-dimensional arrays to 1 row in a worksheet.
Assign 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)))

6.13.2 2-dimensional 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 1-dimensional arrays to one 2-dimensional array.
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)

.Item("nr" & .Count) = Application.Index(a_sn, j, 0)

Next

For j = 1 To UBound(a_sp)

.Item("nr" & .Count) = Application.Index(a_sp, j, 0)

Next

a_sq = Application.Index(.items, 0, 0)

End With

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)

Alternative syntax

a_sp=application.transpose(Array("aa1","aa2","aa3","aa4","aa5"))

- a_sn is a 1-dimensional array; Lbound 0 and Ubound 4.
- 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.

6.14.1.2 ActiveX-control

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

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 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

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 2-dimensional array to a 1-dimensional array

The conversion of two special 2-dimensional 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 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),"")

Result
- 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))

Result
- 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)),"")

Result
- a 1-dimensional 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 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)

Result
- 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),"")

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 1-dimensional array a_sn
- Lbound(a_sn) =1
- Ubound(a_sn) = 12

6.15 Convert an array to a string

6.15.1 1-dimensional array

6.15.1.1 VBA method Join

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)

Result
- 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,"")

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 2-dimensional array

6.15.2.1 A loop

a_sn=Range("A1:K12")

for each it in a_sn

c00=c00 & "|" & it

next

msgbox c00

6.15.2.2 Excel function Index

See: Function Index: detailed explanation

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),"_")

6.15.2.3 The clipboard

Write a 2-dimensional 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:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

.GetFromClipboard
c00 = .GetText

End With

'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
c00 = .GetText

End With

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 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)

The value 6 is being found and the resulting variable a_sr contains the value 3.

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)

The value 5 has not been found; VBA will show an error pop-up and stops the code.

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)

The value 5 has not been found, an error ID will be stored in variabele a_sr.
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 1-dimensional 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)

Resulting values a_sr(1)=1, a_sr(2)=3 and a_sr(3)=error 2042
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 2-dimensional arrays:

a_sn=range("A1:K10")
a_sr=application.find("abc",a_sn)

String "abc" will be looked for in the (10*11) array 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'

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)]

The same is the case with the alternative writing style for Evaluate:

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 1-dimensional array into a string using 'transpose' and 'join'.

a_sn=sheet1.range("A1:A10")
msgbox Evaluate("sum(" & join(application.transpose(a_sn),",")

For a 2-dimensional 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) & ")")

But this workaround has no advantage compared to the use of the named range itself.

6.16.2 Speed

The use of Excel formulae in VBA is not faster than the use of 'original' VBA-methods.
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)

compared to

for j=1 to ubound(a_sn)

if a_sn(j,1)="abc" then exit for

next
Msgbox 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")

information
y = Application.Count(a_sn )	count numbers in array
y = Application.CountA(a_sn )	count values in array
arithmetic
y = Application.Max(a_sn )	Maximum value in de array
y = Application.Min(a_sn )	Minimum value in de array
y = Application.Large(a_sn ,4)	third highest value in array
y = Application.Small(a_sn ,2)	second smallest value in array
y = Application.Gcd(a_sn ,2)	greatest common denominator
y = Application.Sum(a_sn )	sum of all values
y = Application.Product(a_sn )	product of all values
y = Application.SumProduct(a_sn ,a_sn)	sum of product of values in 2 or more arrays
y = Application.SumSq(a_sn )	sum squared values in 1 array
y = Application.SumX2PY2(a_sn ,a_sq)	sum squared values in 2 or more arrays
y = Application.SumX2MY2(a_sn ,a_sq)	sum of differences of squared values in 2 or more arrays
y = Application.SumXMY2(a_sn ,a_sq)	sum of squared differences of values in 2 or more arrays
a_sp = Array(1, 2, 3, 4, 5) a_st = Array(2, 10, 20) a_sr = Application.MMult(Application.Transpose(a_st), a_spr)	product of two 1-dimensional arrays (acting as rows and columns respectively) into a 2-dimensional array
a_st = Application.Index(a_sn , 2) a_sp = Application.Index(a_sn , evaluate("row(1:" & ubound(a_sn ) &")"), 3) a_sr = Application.MMult(a_st, a_sp)	product of the second row (a_st) and the third column (a_sp) of a 2-dimensional array a_sn
y = Application.Average(a_sn )	Average
y = Application.Median(a_sn )	Median
y = Application.AveDev(a_sn )	Average Deviation of Mean
y = Application.GeoMean(a_sn )	Geometric mean of positive numbers
y = Application.HarMean(a_sn )	Harmonic mean of positive numbers
y = Application.TrimMean(a_sn ,.05)	Mean of a percentage of all values; in this case 95 %
descriptive statistics
y = Application.Mode(a_sn )	most frequently occurring value (2007)
y = Application.Mode_Sngl(a_sn )	most frequently occurring value
y = Application.Mode_Mult(a_sn )	most frequently occurring values
y = Application.Percentile(a_sn ,.6)	60% percentile of the array (2007)
y = Application.Percentile_Exc(a_sn ,.6)	60% percentile of the array exclusive
y = Application.Perc_inc(a_sn ,.6)	60% percentile of the array inclusive
y = Application.PercentRank(a_sn ,3)	the rank of value 3 as percentage (2007)
y = Application.PercentRank_Exc(a_sn ,3)	the rank of value 3 as percentage exclusive
y = Application.PercenRank_Inc(a_sn ,34)	the rank of value 34 as percentage inclusive
y = Application.Quartile(a_sn ,3)	then third Quartile (2007)
y = Application.Quartile_Exc(a_sn ,3)	the third Quartile exclusive
y = Application.Quartile_Inc(a_sn ,4)	the fourth Quartile inclusive
y = Application.Var(a_sn )	estimates variance based on sample (2007)
y = Application.Var_S(a_sn ,3)	estimates variance based on sample
y = Application.VarA(a_sn ,3)	estimates variance based on sample; strings and booleans included
y = Application.VarP(a_sn )	variance in population (2007)
y = Application.Var_P(a_sn ,3)	variance in population
y = Application.VarPA(a_sn ,3)	variance in population; strings and booleans included
y = Application.StDev(a_sn )	estimate of standard deviation based on sample (2007)
y = Application.StDev_S(a_sn ,1)	estimate of standard deviation based on sample
y = Application.StDevA(a_sn ,1)	estimate of standard deviation based on sample; strings and booleans included
y = Application.StDevP(a_sn )	standard deviation in population (2007)
y = Application.StDev_P(a_sn ,1)	standard deviation in population
y = Application.StDevPA(a_sn )	standard deviation in population; strings and booleans included
y = Application.Covar(a_sn ,a_sq)	covariance of couples in 2 arrays (2007)
y = Application.Covariance_S(a_sn ,a_sq)	covariance in sample of couples in 2 arrays
y = Application.Covariance_P(a_sn ,a_sq)	covariance in population of couples in 2 arrays
y = Application.DevSq(a_sn )	sum of square deviations from mean
y = Application.SKew(a_sn )	skewness of a distribution
y = Application.Slope(a_sn ,a_sq)	slope of linear regression line
y = Application.StEyx(a_sn ,a_sq)	standard error of predicted y-value for each x-value
y = Application.ImProduct(a_sn )	product of complex numbers in the array
y = Application.ImSum(a_sn )	sum of complex numbers in the array
y = Application.Intercept(a_sn ,a_sq)	intersection of regression line with y-axis
a_sr = Application.Frequency(a_sn ,a_sn)	frequency of every number in the array
a_sr = Application.Growth(a_sn )	numbers in an exponential grow trend
a_sr = Application.Trend(a_sn )	numbers in a linear trend matching data points (least square method)
a_sr = Application.LinEst(a_sn )	straight line matching data points (least square method)
a_sr = Application.LogEst(a_sn )	exponential curve matching data points
testing statistics
y = Application.FTest(a_sn ,a_sq)	F-test: 2-sided test of significant variance
y = Application.TTest(a_sn ,a_sq,2,2)	Student T-test (2007)
y = Application.ZTest(a_sn )	one-sides P-value in an Z-test (2007)
y = Application.Pearson(a_sn ,a_sq)	Pearson correlation-coefficient: r
y = Application.RSq(a_sn ,a_sq)	the square of Pearson's correlation-coefficient
database
y = Application.VLookup(21,a_sn, 4,0)	looks for value 21 in the first column' of array sn, returns the corresponding value from the fourth 'column'
y = Application.HLookup("3",a_sn, 4,0)	looks for value "3" in the first 'row' of array sn, returns the corresponding value in 'row' 4
y = Application.Lookup(22,a_sn)	looks for value 22 and returns the corresponding value in the last column (2007)
y = Application.Match(21,sr,0)	position of a value in a 1-dimensional array
y = Application.Index(a_sn ,3,7)	value corresponding with row 3 and column 7
a_sr = Application.Transpose(a_sn )	inverts rows into columns and vice versa

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")

Result : sr(1) ="bb1", sr(2)="bb2", sr(3)="cc", sr(4)="bb4"