top of page

Better Arrays for VBA

About
Download
Update history
How to install
Documentation

About

This is a VBA Class Module that introduces a better Array system, object and properties.
See the Download section to download the cls file (Class Module).
When downloaded, see the How to install section to learn how to implement Better Arrays in your VBA Project.
Finally we recommend you to see the Documentation section to learn how to use Better Arrays.

About
Download
Update history

Changelog

v2.2.1 (Released on 2022-02-03)

  • Fixed an issue with the Splice function.

​

v2.2.0 (Released on 2022-01-22)

  • Added out of range errors when getting or setting an item from an invalid index;

  • Fixed NewList function.

​

v2.1.0 (Released on 2022-01-22)

  • Added new functions:

    • Fill​;

    • Find;

    • FindIndex;

    • Flat;

    • Reduce;

    • ReduceRight;

  • Added Items parameter to the Splice function;​

  • The ToString function now also converts arrays and Lists to string;

  • Added Utils module with the functions:

    • NewList;

    • IsList.

​

v2.0.0 (Released on 2022-01-17)

  • Added "Includes" and "Map" functions​;

  • Added Index parameter for "Join" callback function.

  • Changed function names:

    • "AddItem" to "Add";

    • "RemoveItem" to "Remove";

    • "GetItems" to "Clone";

  • Changed "Length" function to be a read-only property.​

​

v1.4.4 (Released on 2021-05-12)
- Changed version format from x.xx to x.x.x;
- 'Sort' function now supports an optional custom sorting function like the 'Filter' function;
- 'Join' function now supports an optional custom joining function like the 'Filter' function;
- Small improvements were made.

v1.43 (Released on 2020-11-08)
- Added 'Intersect' function;
- Fixed an issue when passing an index equal to the list's length in the 'AddItem' method.

v1.42 (Released on 2020-10-22)
- Added 'Shuffle' function;
- Fixed an issue when passing an index equal to the list's length in the 'AddItem' method.

v1.41 (Released on 2020-05-20)
- Fixed an issue with the 'Reverse' function;
- Changed all Integer to Long.

v1.40 (Released on 2020-05-04)
- Added 'Splice' function;
- Added 'ToString' function;
- Removed 'SuppressErrors' property;
- Removed error handling;
- Made 'Slice' function EndIndex optional;
- A number of issues have been fixed and various improvements were made.
NOTE: This version may be incompatible with code written with previous versions of Better Arrays.

v1.30 (Released on 2020-04-19)
- Added 'GetItems' function;
- Added 'Last' function;
- Added 'Filter' function;
- Added 'SuppressErrors' property;
- 'Concat', 'Reverse', 'Sort' and 'Slice' functions now return a List object instead of an array.
NOTE: This version may be incompatible with code written with previous versions of Better Arrays.

v1.20 (Released on 2020-03-15)
- Changed Items property;
- Added Item property;
- Made initial Items an array.
NOTE: This version may be incompatible with code written with previous versions of Better Arrays.

v1.17 (Released on 2020-02-13)
- Fixed an issue when adding an object to a list using the AddItem method.

v1.16 (Released on 2019-09-28)
- Improved error handling;
- Some improvements were made;
- Fixed an issue with the Items property.

v1.15 (Released on 2019-09-26)
- Added Clear method;
- Added some error handling;
- Some improvements were made.

v1.14 (Released on 2019-09-20)
- Some improvements were made;
- Fixed some issues.

v1.13 (Released on 2019-09-16)
- Fixed an issue that returned an error when using the RemoveItem Mehtod in a List of Objects.

v1.12 (Released on 2019-09-06)
- Added SetItems method;
- Improved some methods;
- Fixed some issues.

v1.11 (Released on 2019-08-31)
- Improved some methods;
- Fixed some issues.
 
v1.10 (Released on 2019-08-30)
- Added new methods and functions;
- Improved some methods;
- Fixed some issues.

v2.2.1 (Released on 2022-02-03)

  • Fixed an issue with the Splice function.

​

v2.2.0 (Released on 2022-01-22)

  • Added out of range errors when getting or setting an item from an invalid index;

  • Fixed NewList function.

​

v2.1.0 (Released on 2022-01-22)

  • Added new functions:

    • Fill​;

    • Find;

    • FindIndex;

    • Flat;

    • Reduce;

    • ReduceRight;

  • Added Items parameter to the Splice function;​

  • The ToString function now also converts arrays and Lists to string;

  • Added Utils module with the functions:

    • NewList;

    • IsList.

​

v2.0.0 (Released on 2022-01-17)

  • Added "Includes" and "Map" functions​;

  • Added Index parameter for "Join" callback function.

  • Changed function names:

    • "AddItem" to "Add";

    • "RemoveItem" to "Remove";

    • "GetItems" to "Clone";

  • Changed "Length" function to be a read-only property.​

Show more

Documentation

Documentation (versions 1.40, 1.4.4 and 2.0.0+)

Better Arrays - versions 1.40, 1.4.4 and 2.0.0+

NOTE: This documentation only applies to versions 1.40,1.43 and 2.0.0+ of Better Arrays. To see the documentation of other versions click here.

To distinguish VBA Arrays from Better Array's arrays, we use List for the term Array.

Index

List members

  • Items

  • Item

  • SetItems / Clone (v2.x.x)

  • GetItems (prior to v2.0.0)

  • Join

  • ToString

  • AddItem (prior to v2.0.0) / Add (v2.x.x)

  • RemoveItem (prior to v2.0.0) / Remove (v2.x.x)

  • Clear

  • Length

  • IndexOf

  • Includes (v2.0.0)

  • Reverse

  • Sort

  • Shuffle (v1.42)

  • Slice

  • Splice

  • Concat

  • Intersect (v1.43)

  • Filter

  • Map (v2.x.x)

  • Fill (v2.1.x)

  • Find (v2.1.x)

  • FindIndex (v2.1.x)

  • Flat (v2.1.x)

  • Reduce (v2.1.x)

  • ReduceRight (v2.1.x)

  • Last


Utils members (v2.1.x)
Note: The ListUtils module is optional and is not required for the List class to work.

  • NewList

  • IsList

Get Started

​Create a new Better Arrays List. Better Arrays' arrays are called Lists.

Example [VBA]

Dim MyList As New List

Properties and Methods

Items

Sets or gets all items of a List in array format.

Syntax

<ListName>.Items

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2") 'Sets the items to an array

​

' MyList.Items --> returns an array, so you can use MyList.Items(<index>) to return an item from the List/array.

Debug.Print MyList.Items(0) 'Prints the first item of the returned array, in this case "item1"

​

' Note: You can use the Item property to set or return an item from the List.

​Item
Sets or gets an item of a List. The item can be of any type. When trying to get an item of an invalid index, an error is returned.

​

Note: This is the default property of the List class, so it's optional (e.g. you can use MyList(0), instead of MyList.Item(0)).

Syntax

<ListName>.Item[(<ItemIndex>)]

- or -

<ListName>[(<ItemIndex>)]

​

ItemIndex - index of the item to select.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

Debug.Print MyList.Item(0) 'Prints the first item of the List, in this case "item1"

Debug.Print MyList(0) 'Prints the first item of the list, in this case "item1"

Debug.Print MyList.Item(1) 'Prints the second item of the List, in this case "item2"

Debug.Print MyList(1) 'Prints the second item of the list, in this case "item2"

SetItems

Sets the items of a List.

Syntax

<ListName>.SetItems(<Item>)

​

Items - ParamArray of items to set.

Example [VBA]

Dim MyList As New List

MyList.SetItems "item1", "item2" ' Sets the items of the list to "item1", "item2".

GetItems

Returns a new List containing the items of the selected List.

Syntax

<ListName>.GetItems

Example [VBA]

Dim MyList1 As New List, MyList2 As List

Set MyList2 = MyList1.GetItems ' Sets List2 to be equal to List1

Join

Joins all items of the list and returns them as a string.

Syntax

<ListName>.Join[(<Separator>)]

​

Separator - String that separates each item. Default: "" (empty string).

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Join(", ") ' Prints the following string: "item1, item2, item3".

ToString

Returns a string containing all items joined by commas and delimited by square brackets. [ item1, item2, ... ]

Syntax

<ListName>.ToString[(<WhiteSpaces>, [StringQuotes])]

​

WhiteSpaces - Boolean that indicates whether or not to include spaces. Default: False.

StringQuotes - String with the symbol that should be used to delimit a string. Default: "'" (single quote).

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", 3, 4)

​

Debug.Print MyList.ToString ' Prints the following string: "[ 'item1', 'item2', 3, 4 ]".

Debug.Print MyList.ToString(False) ' Prints the following string: "['item1','item2',3,4]".

Debug.Print MyList.ToString(True, """") ' Prints the following string: "[ "item1", "item2", 3, 4 ]".

AddItem

Adds a new item to the List.

Syntax

<ListName>.AddItem <Item>[, Index]

​

Item - Item to add

Index - Index at which the item should be added. If it's -1 then the item is added at the end of the List. Default: -1.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1")

​

MyList.AddItem "item2" ' Adds the item "item2" to the List. The list is now "item1", "item2".

MyList.AddItem "item3", 1 ' Adds the item "item3" at index 1 to the List. The list is now "item1", "item3", "item2".

RemoveItem

Removes an item from the List and returns that item.

Syntax

<ListName>.RemoveItem(<ItemIndex>)

​

ItemIndex - index of the item to remove.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

MyList.RemoveItem 0 ' Removes the first item. The list is now "item2", "item3".

Debug.Print MyList.RemoveItem(1) ' Removes the second item and prints "item2" (which is the item that got removed). Now the list only contains the item "item2".

Clear

Removes all items from the List.

Syntax

<ListName>.Clear

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

MyList.Clear ' Removes all items. Now the list contains no items and the length is 0.

Length

Returns the number of items of the list.

Syntax

<ListName>.Length

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Length ' Prints 3.

IndexOf

Returns the first index at which a given element can be found in the array, or -1 if it is not present.

Syntax

<ListName>.IndexOf(<Item>[, StartIndex])

​

Item - item to locate in the list.

StartIndex - index to start looking for the item. Default: 0.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item1")

​

Debug.Print MyList.IndexOf("item1") ' Prints 0.

Debug.Print MyList.IndexOf("item1", 1) ' Prints 2.

Debug.Print MyList.IndexOf("item2") ' Prints 1.

Debug.Print MyList.IndexOf("item3") ' Prints -1, because the list does not contain "item3".

Reverse

Returns a new List but reversed.

Syntax

<ListName>.Reverse

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

MyList.Items = MyList.Reverse.Items ' Sets the items of the List to the same list but reversed. Basically it reverses the list. The list is now "item3", "item2", "item1".

​

' The above line of code can also be written like so:

Set MyList = MyList.Reverse

Sort

Returns the list but sorted.

Syntax

<ListName>.Sort

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item3", "item1", "item2")

​

MyList.Items = MyList.Sort.Items ' Sets the items of the list to the same list but sorted. Basically it sorts the list. The list is now "item1", "item2", "item3".

​

' The above line of code can also be written like so:

Set MyList = MyList.Sort

Shuffle

Returns a new list with items in a random order.

Syntax

<ListName>.Shuffle

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3", "item4", "item5")

​

MyList.Items = MyList.Shuffle.Items ' Sets the items of the list to the same list but shuffled. Basically it shuffles the list. The list is now "item4", "item2", "item3", "item1", "item5".

​

' The above line of code can also be written like so:

Set MyList = MyList.Shuffle

Slice

Returns a new List with only the specified items from the selected List.

Syntax

<ListName>.Slice(StartIndex, EndIndex)

​

StartIndex - index of the first item

EndIndex - index of the last item

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3", "item4", "item5")

​

MyList.Items = MyList.Slice(1, 3).Items ' Sets the items of the list to the same list but sliced. Basically it slices the list. The list is now "item2", "item3", "item4".

​

' The above line of code can also be written like so:

Set MyList = MyList.Slice(1, 3)

Splice

Returns a new List without the specified items from the selected List.

Syntax

<ListName>.Splice(Index, Count)

​

Index - index of the first item

Count - number of items to select

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3", "item4", "item5")

​

MyList.Items = MyList.Slice(1, 2).Items ' Sets the items of the list to the same list but spliced. Basically it removes the specified items from the list. The list is now "item1", "item4", "item5".

​

' The above line of code can also be written like so:

Set MyList = MyList.Splice(1, 2)

Concat

Returns a List containing the union of the items of the current List with an array.

Syntax

<ListName>.Concat(OtherArray)

​

OtherArray - array which will be concated.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

MyList.Items = MyList.Concat(Array("item3", "item4").Items 'Sets the items of the list to the returned array, which is the union of the list and the array. The list is now "item1", "item2", "item3", "item4".

​

' The above line of code can also be written like so:

Set MyList = MyList.Concat(Array("item3", "item4")

Intersect

Returns a List containing only the items that both arrays contain.

Syntax

<ListName>.Intersect(OtherArray)

​

OtherArray - array which will be intersected with.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("Apple", "Banana", "Strawberry", "Pear")

​

MyList.Items = MyList.Intersect(Array("Banana", "Pineapple", "Apple", "Banana", "Peach").Items 'Sets the items of the list to the returned array, which is the intersection of the list and the array. The list is now "Apple", "Banana".

​

' The above line of code can also be written like so:

Set MyList = MyList.Concat(Array("Banana", "Pineapple", "Apple", "Banana", "Peach")

Filter

Returns a new List containing only items that passed a filter defined by a given function. If no items passed the filter or if the list has no items, then a new empty list is returned.

Syntax

<ListName>.Filter(FilterFunctionName)

​

FilterFunctionName - a string with the name of a VBA function which has a parameter, which represents an item, and a returning Boolean, which indicates whether or not the item passed the filter.

Example [VBA]

Sub MySub()

​

    Dim MyList As New List

    MyList.Items = Array("Apple", "Banana", "Strawberry", "Pear")

​

    MyList.Items = MyList.Filter("TheItem").Items ' Sets the items of the List to the returned items, which are the ones that passsed the filter. The list is now "Apple", "Pear".

​

    ' The above line of code can also be written like so:

    Set MyList = MyList.Filter("TheItem")

​

End Sub

​

​

Function MyFilter(TheItem) ' The function that will be used to filter MyList.

​

    MyFilter = (Len(TheItem) <= 5) ' Returns a boolean indicating whether the current item's length is less or equal 5.

​

End Sub

Last

Returns a the last item of the List.

Syntax

<ListName>.Last

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Last ' Prints the last item of MyList: "item3".

SuppressErrors

Sets or gets a boolean indicating whether or not erros should be hidden. The default value is False.

Syntax

<ListName>.SuppressErrors

Example [VBA]

Dim MyList As New List

MyList.Items = Array()

​

MyList.RemoveItem 0 ' Returns an error, because there are no items to remove, and ends all code execution.

​

MyList.SupressErrors = True ' Enables the SupressErrors property.

​

MyList.RemoveItem 0 ' Does not return an error, because SupressErrors is set to True, and ends all code execution.

Errors

1: "List is empty."
The method or property does not allow the List to be empty.
​
2: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is -1."
You tried to run a method, get or set a property and provided an index which is less than -1.
​
4: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is 0."
You tried to run a method, get or set a property and provided an index which is than 0. Lists can only indices greater than or equal to 0.
​
8: "'%Item_Index%' is not a valid ItemIndex. Maximum allowed is %MAX_INDEX%."
You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1
​
16: "'%Index%' is not a valid Index. Minimum allowed is -1."
You tried to run a method, get or set a property and provided an index which is less than -1.
​
32: "'%Index%' is not a valid Index. Maximum allowed is %MAX_INDEX%."
You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1
​
64: "'%StartIndex%' is not a valid StartIndex. Minimum allowed is -1."
You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

Anchor 1

Documentation (version 1.30)

Better Arrays - version 1.30

​

NOTE: This documentation only applies to version 1.30 of Better Arrays. To see the documentation of other versions click here.

​

To distinguish VBA Arrays from Better Array's arrays, we use List for the term Array.

Get Started

Create a new Better Arrays List. Better Arrays' arrays are called Lists.

Example [VBA]

Dim MyList As New List

Properties and Methods

Items

Sets or gets all items of a List in array format.

Syntax

<ListName>.Items

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2") 'Sets the items to an array

​

' MyList.Items --> returns an array, so you can use MyList.Items(<index>) to return an item from the List/array.

Debug.Print MyList.Items(0) 'Prints the first item of the returned array, in this case "item1"

​

' Note: You can use the Item property to set or return an item from the List.

Item

Sets or gets an item of a List. The item can be of any type. When trying to get an item of an invalid index, an error is returned.

 

Note: This is the default property of the List class, so it's optional (e.g. you can use MyList(0), instead of MyList.Item(0)).

Syntax

<ListName>.Item[(<ItemIndex>)]

- or -

<ListName>[(<ItemIndex>)]

​

ItemIndex - index of the item to select.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

Debug.Print MyList.Item(0) 'Prints the first item of the List, in this case "item1"

Debug.Print MyList(0) 'Prints the first item of the list, in this case "item1"

Debug.Print MyList.Item(1) 'Prints the second item of the List, in this case "item2"

Debug.Print MyList(1) 'Prints the second item of the list, in this case "item2"

SetItems

Sets the items of a List.

Syntax

<ListName>.SetItems(<Item>)

​

Items - ParamArray of items to set.

Example [VBA]

Dim MyList As New List

MyList.SetItems "item1", "item2" ' Sets the items of the list to "item1", "item2".

GetItems

Returns a new List containing the items of the selected List.

Syntax

<ListName>.GetItems

Example [VBA]

Dim MyList1 As New List, MyList2 As List

Set MyList2 = MyList1.GetItems ' Sets List2 to be equal to List1

Join

Joins all items of the list and returns them as a string.

Syntax

<ListName>.Join[(<Separator>)]

​

Separator - String that separates each item. Default: "" (empty string).

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Join(", ") ' Prints the following string: "item1, item2, item3".

AddItem

Adds a new item to the List.

Syntax

<ListName>.AddItem <Item>[, Index]

​

Item - Item to add

Index - Index at which the item should be added. If it's -1 then the item is added at the end of the List. Default: -1.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1")

​

MyList.AddItem "item2" ' Adds the item "item2" to the List. The list is now "item1", "item2".

MyList.AddItem "item3", 1 ' Adds the item "item3" at index 1 to the List. The list is now "item1", "item3", "item2".

RemoveItem

Removes an item from the List and returns that item.

Syntax

<ListName>.RemoveItem(<ItemIndex>)

​

ItemIndex - index of the item to remove.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

MyList.RemoveItem 0 ' Removes the first item. The list is now "item2", "item3".

Debug.Print MyList.RemoveItem(1) ' Removes the second item and prints "item2" (which is the item that got removed). Now the list only contains the item "item2".

Clear

Removes all items from the List.

Syntax

<ListName>.Clear

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

MyList.Clear ' Removes all items. Now the list contains no items and the length is 0.

Length

Returns the number of items of the list.

Syntax

<ListName>.Length

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Length ' Prints 3.

IndexOf

Returns the first index at which a given element can be found in the array, or -1 if it is not present.

Syntax

<ListName>.IndexOf(<Item>[, StartIndex])

​

Item - item to locate in the list.

StartIndex - index to start looking for the item. Default: 0.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item1")

​

Debug.Print MyList.IndexOf("item1") ' Prints 0.

Debug.Print MyList.IndexOf("item1", 1) ' Prints 2.

Debug.Print MyList.IndexOf("item2") ' Prints 1.

Debug.Print MyList.IndexOf("item3") ' Prints -1, because the list does not contain "item3".

Reverse

Returns a new List but reversed.

Syntax

<ListName>.Reverse

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

MyList.Items = MyList.Reverse.Items ' Sets the items of the List to the same list but reversed. Basically it reverses the list. The list is now "item3", "item2", "item1".

​

' The above line of code can also be written like so:

Set MyList = MyList.Reverse

Sort

Returns the list but sorted.

Syntax

<ListName>.Sort

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item3", "item1", "item2")

​

MyList.Items = MyList.Sort.Items ' Sets the items of the list to the same list but sorted. Basically it sorts the list. The list is now "item1", "item2", "item3".

​

' The above line of code can also be written like so:

Set MyList = MyList.Sort

Slice

Returns a new List with only specific items from the selected List.

Syntax

<ListName>.Slice(StartIndex, EndIndex)

​

StartIndex - index of the first item

EndIndex - index of the last item

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3", "item4", "item5")

​

MyList.Items = MyList.Slice(1, 3).Items 'Sets the items of the list to the same list but sliced. Basically it slices the list. The list is now "item2", "item3", "item4".

​

' The above line of code can also be written like so:

Set MyList = MyList.Slice(1, 3)

Concat

Returns a List containing the union of the items of the current List with an array.

Syntax

<ListName>.Concat(OtherArray)

​

OtherArray - array which will be concated.

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2")

​

MyList.Items = MyList.Concat(Array("item3", "item4").Items 'Sets the items of the list to the returned array, which is the union of the list and the array. The list is now "item1", "item2", "item3", "item4".

​

' The above line of code can also be written like so:

Set MyList = MyList.Concat(Array("item3", "item4")

Filter

Returns a new List containing only items that passed a filter defined by a given function. If no items passed the filter or if the list has no items, then a new empty list is returned.

Syntax

<ListName>.Filter(FilterFunctionName)

​

FilterFunctionName - a string with the name of a VBA function which has a parameter, which represents an item, and a returning Boolean, which indicates whether or not the item passed the filter.

Example [VBA]

Sub MySub()

​

    Dim MyList As New List

    MyList.Items = Array("Apple", "Banana", "Strawberry", "Pear")

​

    MyList.Items = MyList.Filter("TheItem").Items ' Sets the items of the List to the returned items, which are the ones that passsed the filter. The list is now "Apple", "Pear".

​

    ' The above line of code can also be written like so:

    Set MyList = MyList.Filter("TheItem")

​

End Sub

​

​

Function MyFilter(TheItem) ' The function that will be used to filter MyList.

​

    MyFilter = (Len(TheItem) <= 5) ' Returns a boolean indicating whether the current item's length is less or equal 5.

​

End Sub

Last

Returns a the last item of the List.

Syntax

<ListName>.Last

Example [VBA]

Dim MyList As New List

MyList.Items = Array("item1", "item2", "item3")

​

Debug.Print MyList.Last ' Prints the last item of MyList: "item3".

SuppressErrors

Sets or gets a boolean indicating whether or not erros should be hidden. The default value is False.

Syntax

<ListName>.SuppressErrors

Example [VBA]

Dim MyList As New List

MyList.Items = Array()

​

MyList.RemoveItem 0 ' Returns an error, because there are no items to remove, and ends all code execution.

​

MyList.SupressErrors = True ' Enables the SupressErrors property.

​

MyList.RemoveItem 0 ' Does not return an error, because SupressErrors is set to True, and ends all code execution.

Errors

1: "List is empty."

The method or property does not allow the List to be empty.

​

2: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is less than -1.

​

4: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is 0."

You tried to run a method, get or set a property and provided an index which is than 0. Lists can only indices greater than or equal to 0.

​

8: "'%Item_Index%' is not a valid ItemIndex. Maximum allowed is %MAX_INDEX%."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

​

16: "'%Index%' is not a valid Index. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is less than -1.

​

32: "'%Index%' is not a valid Index. Maximum allowed is %MAX_INDEX%."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

​

64: "'%StartIndex%' is not a valid StartIndex. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

DocOld

Documentation (versions 1.12 to 1.20)

Better Arrays - version 1.12 - 1.20

​

NOTE: This documentation only applies to version 1.12, 1.13, 1.14, 1.15, 1.16, 1,17 and 1.20 of Better Arrays. To see the documentation of version 1.10, download the .zip file of the 1.10 version and open the Documentation.txt file.

​

To distinguish VBA Arrays from Better Array's arrays, we use List for the term Array.

Get Started

Create a new Better Arrays List. Better Arrays' arrays are called Lists.

Example [VBA]

Dim myList As New List

Properties and Methods

Items

Sets or gets one or more items of a List. The items can be of any type. When trying to get an item of an invalid index, an error is returned.

​

NOTE: In version 1.20, the Items property has changed.

Syntax

# version 1.12 - 1.17

​

<ListName>.Items[(<ItemIndex>)]

- or -

<ListName>[(<ItemIndex>)]

​

ItemIndex - index of the item to select. Default: -1. When is set to -1: selects all items from the List.

​

​

# version 1.20

​

<ListName>.Items

Example [VBA]

' version 1.12 - 1.17

​

Dim myList As New List

myList.Items = Array("item1", "item2") 'Sets the items to be equal to an array

​

Debug.Print myList.Items(0) 'Prints the first item of the List, in this case "item1"

Debug.Print myList(1) 'Prints the second item of the list, in this case "item2"

​

​

' version 1.20

​

Dim myList As New List

myList.Items = Array("item1", "item2") 'Sets the items to be equal to an array

​

' myList.Items --> returns an array, so you can use ..Items(<index>) to return an item from the list/array.

Debug.Print myList.Items(0) 'Prints the first item of the returned array, in this case "item1"

Item (v1.20 only)

Sets or gets an item of a List. The item can be of any type. When trying to get an item of an invalid index, an error is returned.

Syntax

<ListName>.Item[(<ItemIndex>)]

- or -

<ListName>[(<ItemIndex>)]

​

ItemIndex - index of the item to select.

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2") 'Sets the items to be equal to an array

​

Debug.Print myList.Item(0) 'Prints the first item of the List, in this case "item1"

Debug.Print myList(1) 'Prints the second item of the list, in this case "item2"

SetItems

Sets the items of a List.

Syntax

<ListName>.SetItems(<Item>)

​

Items - ParamArray of items to set.

Example [VBA]

Dim myList As New List

myList.SetItems "item1", "item2" ' Sets the items of the list to "item1", "item2".

Join

Joins all items of the list and returns them as a string.

Syntax

<ListName>.Join[(<Separator>)]

​

Separator - Expression that separates each item. Default: "" (empty string).

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2", "item3")

​

Debug.Print myList.Join(", ") 'Prints the following string: "item1, item2, item3".

AddItem

Adds a new item to the List.

Syntax

<ListName>.AddItem <Item>[, Index]

​

Item - Item to add

Index - Index at which the item should be added. If it's -1 then the item is added at the end of the List. Default: -1.

Example [VBA]

Dim myList As New List

myList.Items = Array("item1")

​

myList.AddItem "item2" 'Adds the item "item2" to the List. The list is now "item1", "item2".

myList.AddItem "item3", 1 'Adds the item "item3" at index 1 to the List. The list is now "item1", "item3", "item2".

RemoveItem

Removes an item from the List.

Syntax

<ListName>.RemoveItem <ItemIndex>

​

ItemIndex - index of the item to remove.

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2")

​

myList.RemoveItem 0 'Removes the first item. Now the list only contains the item "item2".

Clear (v1.15 and above only)

Removes all items from the List. Only applies to versions 1.15 and 1.16.

Syntax

<ListName>.Clear

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2")

​

myList.Clear 'Removes all items. Now the list contains no items and the length is 0.

Length

Returns the number of items of the list.

Syntax

<ListName>.Length

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2")

​

Debug.Print myList.Length 'Prints 2.

IndexOf

Returns the first index at which a given element can be found in the array, or -1 if it is not present.

Syntax

<ListName>.IndexOf(<Item>[, StartIndex])

​

Item - item to locate in the list.

StartIndex - index to start looking for the item. Default: 0.

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2", "item1")

​

Debug.Print myList.IndexOf("item1") 'Prints 0.

Debug.Print myList.IndexOf("item1", 1) 'Prints 2.

Debug.Print myList.IndexOf("item2") 'Prints 1.

Debug.Print myList.IndexOf("item3") 'Prints -1, because the list does not contain "item3".

Reverse

Returns the list but reversed.

Syntax

<ListName>.Reverse

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2", "item3")

​

Debug.Print myList.Reverse 'Print the list but reversed.

myList.Items = myList.Reverse 'Sets the items of the list to the same list but reversed. Basically it reverses the list. The list is now "item3", "item2", "item1".

Sort

Returns the list but sorted.

Syntax

<ListName>.Sort

Example [VBA]

Dim myList As New List

myList.Items = Array("item3", "item1", "item2")

​

Debug.Print myList.Sort 'Print the list but sorted.

myList.Items = myList.Reverse 'Sets the items of the list to the same list but sorted. Basically it sorts the list. The list is now "item1", "item2", "item3".

Slice

Returns specific items from a list.

Syntax

<ListName>.Slice(StartIndex, EndIndex)

​

StartIndex - index of the first item

EndIndex - index of the last item

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2", "item3", "item4", "item5")

​

myList.Items = myList.Slice(1, 3) 'Sets the items of the list to the same list but sliced. Basically it slices the list. The list is now "item2", "item3", "item4".

Concat

Returns the union of the current list with an array.

Syntax

<ListName>.Concat(OtherArray)

​

OtherArray - array which will be concated.

Example [VBA]

Dim myList As New List

myList.Items = Array("item1", "item2")

​

myList.Items = myList.Concat(Array("item3", "item4") 'Sets the items of the list to the returned array, which is the union of the list and the array. The list is now "item1", "item2", "item3", "item4".

Errors (v1.16 only)

1: "List is empty."

The method or property does not allow the List to be empty.

​

2: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is less than -1.

​

4: "'%Item_Index%' is not a valid ItemIndex. Minimum allowed is 0."

You tried to run a method, get or set a property and provided an index which is than 0. Lists can only indices greater than or equal to 0.

​

8: "'%Item_Index%' is not a valid ItemIndex. Maximum allowed is %MAX_INDEX%."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

​

16: "'%Index%' is not a valid Index. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is less than -1.

​

32: "'%Index%' is not a valid Index. Maximum allowed is %MAX_INDEX%."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

​

64: "'%StartIndex%' is not a valid StartIndex. Minimum allowed is -1."

You tried to run a method, get or set a property and provided an index which is greater than MAX_INDEX. Lists can only have indices less than or equal to MAX_INDEX. MAX_INDEX = ListLength - 1

bottom of page