VBA for smarties: ArrayList

ArrayList

4. Create an ArrayList
4.1 Early binding
4.1.1 Local scope
4.1.2 Private scope
4.1.3 Public scope
4.2 Late binding
4.2.1 Local scope
4.2.2 Private scope
4.2.3 Public scope

5. Populate an ArrayList
5.1 Separate elements
5.1.1 Method Add
5.1.2 Method Insert
5.2 A group of elements
5.2.1 Method AddRange
5.2.2 Method InsertRange
5.3 Kinds of Items
5.3.1 empty string
5.3.2 normal string
5.3.3 not-printable character
5.3.4 number
5.3.5 date
5.3.6 1-dimensional array
5.3.7 multi-dimensional array
5.3.8 object
5.3.9 controls in Userform
5.3.10 ActiveX controls
5.3.11 all worksheets

6. The size of an ArrayList

7. Check an item's presence

8. Check an item's position

9. Read elements
9.1 one element
9.2 adjacent elements
9.3 all element

10 Change an element

11 Sorting elements
11.1 Ascending sorting
11.2 Descending sorting
11.3 Sorting Arrays

12 Delete elements
12.1 one element by contents
12.2 one element by index
12.3 adjacent elements
12.4 all elements

13. Copy an ArrayList

14. Key words

- .Add
- .AddRange
- .Clear
- .Clone
- .Contains
- .Count
- .GetRange
- .IndexOf
- .Insert
- .InsertRange
- .Item(..)
- .LastIndexOf
- .Remove
- .RemoveAt
- .RemoveRange
- .Reverse
- .Sort
- .ToArray

1. What is an ArrayList ?

In VBA an ArrayList is a 'container' object: you can store all kinds of information in it: numbers, strings, dates, arrays, ranges, variables and objects.
VBA has several comparable options to store information:
- a dictionary
- a collection
- an array variable
- an ActiveX ComboBox
- an ActiveX ListBox
- a Userform control ComboBox
- a Userform control ListBox
- a sortedlist

Which one to use is dependent of the goal you want to achieve.
In this page I don't want to make an extensive comparison of all methods.
I confine the information to the possibilities of the ArrayList.

There's 1 outstanding difference to other storing methods like Collection, Array or Dictionary; the arraylist has a built in sorting method.
As far as I know it's the only sorting method in VBA (apart from the 'sortedlist').
This sorting method is also very fast.
It can be a valid consideration to use an ArrayList.

2. The purpose of an ArrayList

You can collect data from several sources into an ArrayList to employ them: analysing, comparing, calculating, filtering, etc.
Instead of manipulating data in an Excel-worksheet, a Word Document or a Powerpoint presentation, you can use the Arraylist to do this in memory.
No screenupdating, no redundant calculations nor event procedures are being performed so the data processing can be very fast.

3. The origin of the ArrayList

The ArrayList is not part of the standard VBA-library.
The ArrayList is part of the library 'System.Collections'.
You'll find it in the file ....\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb or in a comparable directory.

3.1 'Early Binding': link to the library manually

In the VBEditor you can establish a direct link to this library by checking mscorlib.tlb in the list of References (Tools/References..)
A file in which the direct link exists will store the link and activate after distribution.

3.2 'Early Binding': link to the library in VBA

In VBA you can also establish the reference using the .AddFromFile method or the .AddFromGuid method.

ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"

ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4

3.3 'Late Binding': link to the library in VBA

You don't need an active reference (('early binding') to make use of the ArrayList.
You can also decide to create the link at the moment you need it ('late binding').
This is the code to establish a new ArrayList:

CreateObject("System.Collections.ArrayList")

4. Create an ArrayList

VBA has several methods to create an ArrayList.
Which one to use is dependent of:
- whether you want to use 'early binding' or 'late binding'
- which 'scope' you want to deploy: the availability of the ArrayList in 1 procedure or several procedures: 'local scope', 'private scope', 'public scope'

4.1 Established reference to the library: 'early binding'

4.1.1 Local scope

If you only need the ArrayList in 1 macro or function you can restrict the definition of the ArrayList to that procedure.
without the use of a variable:

With new ArrayList

.Add "aa1"

end with

declaring the variable and assigning the new Arraylist simultaneously

dim a_00 as New ArrayList
a_00.Add "aa1"

assigning and implicitly declaring

set a_00 = New ArrayList
a_00.add "aa1"

declararing (as Object) and assigning separately

dim a_00 as Object
set a_00= new ArrayList
a_00.add "aa1"

declararing (as ArrayList) and assigning separately

dim a_00 as ArrayList
set a_00= new ArrayList
a_00.add "aa1"

4.1.2 Private scope

If you want to make the ArrayList available in several procedures (macros or functions) in 1 worksheet, workbook, Userform, document, Macromodule or Classmodule you'll have tu use Private Scope.
In that case the use of a variable is obligatory.
The variable has to be declared in the declaration area of the codemodule.

4.1.2.1 Variable declaration and assigning a new instance simultaneously

If you use this code in the declaration area

Dim a_00 As New ArrayList

Private a_00 as New ArrayList

Now the ArrayList is available in every procedure of the codemodule by the variabele a_00.

4.1.2.2 Variable declaration and assigning a new instance separately

The declaration of the variable must be part of the declaration area in the codemodule.
The datatype of the variabele can be Object, ArrayList or Variant.
Since Variant is the default datatype you can refrain from explicitly specifying the datatype.

In the declaration area of the codemodule

Dim a_00

Private a_00

Dim a_00 as Variant

Private a_00 as Variant

Dim a_00 as Object

Private a_00 as Object

Dim a_00 as ArrayList

Private a_00 as ArrayList

In a procedure (macro or function) you enter

Set a_00 = New ArrayList

4.1.3 Public scope

4.1.3.1 Variable declaration and assigning a new instance simultaneously

In the declaration area of the codemodule of a macromodule

Public a_00 As New ArrayList

Now the ArrayList is available in every procedure in the workbook (=VBProject) through the variabele a_00.

4.1.3.2 Variable declaration and assigning a new instance separately

The declaration of the variable must be part of the declaration area in the codemodule of a macromodule.

The datatype of the variabele can be Object, ArrayList or Variant.
Since Variant is the default datatype you can refrain from explicitly specifying the datatype.

In the declaration area of the codemodule of a macromodule:

Public a_00

Public a_00 as Variant

Public a_00 as Object

Public a_00 as ArrayList

In a procedure (macro or function)

Assign a new instance of the Arraylist to the declared variable.
Since the instance of an ArrayList is an Object you need to use 'Set' to assign the instance to the variable.

Set a_00 = new ArrayList

Now the ArrayList is available in every procedure in the workbook (=VBProject) through the variable a_00.

4.2 Without a reference to the library: 'late binding'

4.2.1 Local scope

If you only need the ArrayList in 1 macro or function you can restrict the definition of the ArrayList to that procedure.
without the use of a variable:

With CreateObject("System.Collections.ArrayList")

.Add "aa1"

end with

with the use of a variable:

set a_00 = CreateObject("System.Collections.ArrayList")
a_00.Add "aa1"

There is an alternative way to create 'late binding'.
You can use the Class Identification Number CLSID in combination with the GetObject method.
Every CLSID is stored in the Windows Registry.

without a variable:

With GetObject("New:{6896B49D-7AFB-34DC-934E-5ADD38EEEE39}") ' sortedlist

.Add "aa1"

End With

assigning to a variable:

set a_00 = GetObject("New:{6896B49D-7AFB-34DC-934E-5ADD38EEEE39}") ' sortedlist
a_00.Add "aa1"

4.2.2 Private scope

If you want to make the ArrayList available in several procedures (macros or functions) in 1 worksheet, workbook, Userform, document, Macromodule or Classmodule you'll have to use Private Scope.
In that case the use of a variable is obligatory.
The variable has to be declared in the declaration area of the codemodule.

The datatype of the variabele can be Object or Variant.
Since there's no reference yet to the library you can't use the datatype 'ArrayList'.
Since Variant is the default datatype you can refrain from explicitly specifying the datatype.

In a procedure (macro or function) you have to assign a new instance of the ArrayList to the previously declared variable.

In the declaration area of the codemodule

Dim a_00

Private a_00

Dim a_00 as Variant

Private a_00 as Variant

Dim a_00 as Object

Private a_00 as Object

In a procedure (macro or function)

Set a_00 = CreateObject("System.Collections.ArrayList")

Now the ArrayList is available in every procedure in the same codemodule through the variable a_00.

4.2.3 Public scope

If you want to make the ArrayList available in all procedures (macros or functions) in the workbook (=VBProject) you'll have to use Public Scope.
In that case the use of a variable is obligatory.
The variable has to be declared in the declaration area of a/any macromodule.

The datatype of the variabele can be Object or Variant.
Since there's no reference yet to the library you can't use the datatype 'ArrayList'.
Since Variant is the default datatype you can refrain from explicitly specifying the datatype.

In a procedure (macro or function) you have to assign a new instance of the ArrayList to the previously declared variable.

In the declaration area of the codemodule of a macromodule

Public a_00

Public a_00 as object

In a procedure (macro or function)

set a_00 = CreateObject("System.Collections.ArrayList")

Now the ArrayList is available in every procedure in the same codemodule through the variable a_00.

In this page I will only use the With ... End With method in a 'late binding' context.

5. Populate an ArrayList

The content of an item can be various: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects.

You can assign items to an ArrayList separately or as a group of items.
You also can indicate where the new items should be added into the ArrayList.

5.1 Add separate items

5.1.1 method .Add

The method .Add always adds a new element after the last one of the ArrayList.

With CreateObject("System.Collections.ArrayList")

.Add "content" .Add New Collection
.Add 123
.Add Date
.Add Array("red", "white", "green")

End With

5.1.2 method .Insert

The method .Insert provides the opportunity to indicate in an argument at which index number the new item has to be inserted into the ArrayList.
Take notice of the fact that the first element in the ArrayList has index number 0.

With CreateObject("System.Collections.ArrayList")

.Add "aa1"
.Add "aa2"
.Add "aa4"
.Insert 2,"aa3"

End With

5.2 add a group of items

An ArrayList accepts the the addition of a group of items simultaneously that will be treated as separate items in the ArrayList.
That group of items has to be prepared into a special 'Array'.
To that purpose you need the us of a '.Queue', that is also part of the same library that contains the ArrayList.
So first you will have to populate a '.Queue' with the items you want to add as a group.
The instruction to list them in a Queue is '.Enqueue' (cfr. .Add in the ArrayList).
In the next step this Queue can be added to the ArrayList with the method '.AddRange'.
You can add the Queue at the rear (.AddRange) or at an indicated index number (.InsertRange).

5.2.1 method .AddRange

The method .AddRange always adds the group of items at the rear of the ArrayList.

Set q_00 = CreateObject("System.Collections.Queue")
q_00.Enqueue "one"
q_00.Enqueue "two"

With CreateObject("System.Collections.ArrayList")

.Add "aa1"
.Add "aa2"
.Add "aa3"
.AddRange q_00

End With

5.2.2 method .InsertRange

In the method .InsertRange you can specify in the first argument in which place in the ArrayList the elements should be inserted.
Take notice that the first element in the ArrayList has index number 0.

Set q_00 = CreateObject("System.Collections.Queue")
q_00.Enqueue "four"
q_00.Enqueue "five"

With CreateObject("System.Collections.ArrayList")

.Add "aa1"
.Add "aa2"
.Add "aa3"
.InsertRange 1, q_00

End With

5.3 Items

You can either add items in serial order (using .Add) or at an indicated position (using .Insert).
Items can be of various types: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects.
Some illustration of several types of elements:

5.3.1 an empty string

With CreateObject("System.Collections.ArrayList")

.Add vbNullString
.Add ""

End With

5.3.2 a normal string

With CreateObject("System.Collections.ArrayList")

.Add "abcde"
.Insert 0, "fghi"

End With

5.3.3 a non printable character

With CreateObject("System.Collections.ArrayList")

.Add vbTab
.Insert 0, vbLf

End With

5.3.4 a number

With CreateObject("System.Collections.ArrayList")

.Add 12345
.Insert 1, RGB(23, 45, 678)

End With

' typename: Integer
' typename: Long

5.3.5 a date(typename: Date)

With CreateObject("System.Collections.ArrayList")

.Add Date
.Add CDate("23-04-2012")
.Insert 2, DateSerial(2013, 10, 12)

End With

5.3.6 a 1-dimensional Array (typename: Variant())

With CreateObject("System.Collections.ArrayList")

.Add Array("aa1", "aa2", "aa3")
.Add Split("bb1_cc1_dd1", "_")
.Insert 1,Array("aa1", "aa2", "aa3")

End With

5.3.7 a multi-dimensional Array (typename: Variant())

With CreateObject("System.Collections.ArrayList")

ReDim sn(6, 10)
.Add sn
.Insert 1, sn

End With

5.3.8 an object

With CreateObject("System.Collections.ArrayList")

.Add Range("A1:K10")
.Insert 1, Range("A1:K10")

End With

5.3.9 all userform controls

With CreateObject("System.Collections.ArrayList")

For Each it In Controls

.Add it

End With

5.3.10 all ActiveX-controls in a worksheet

With CreateObject("System.Collections.ArrayList")

For Each it In Sheets("sheet1").OLEObjects

.Add it

End With

5.3.11 all sheets in a workbook

With CreateObject("System.Collections.ArrayList")

For Each sh In Sheets

.Add sh
.Insert 1, sh

End With

6. the size of an ArrayList

The property .Count represents the number of items in an ArrayList.

With CreateObject("System.Collections.ArrayList")

For Each sh In Sheets

.Add sh
.Insert 1, sh

Next

MsgBox .Count

End With

7. Check the existence of an item

An ArrayList can contain the same value an unlimited time.
The property .Contains indicates whether the value is part of the ArrayList:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

If Not .contains(it) Then .Add it

End With

The property .Contains only checks for values (numbers, strings, dates); an Array, Range or an Object is being ignored.
The use of the property .Contains helps to create a list of unique values/items. See the example above.

8. Check the item's position in an ArrayList

The property .IndexOf indicates the position (=index number) of an item in an ArrayList.
The first argument is the value you are looking for, the second one the position from which you want to check the occurrence of the value:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

If Not .contains(it) Then .Add it

Next
msgbox .IndexOf("aa4", 0)

End With

' 3 rd position typename: Integer

If the item doesn't exist the result is -1
If the item exists the result is the index number.
NB. the first index number=0.

If you want to check the occurrence of an item before or from a certain index number onwards you can enter that index number as second argument.
The value you are looking for will be sought in the 'sub array' starting at the indexnumber that is the second argument.
If the value occurs in this 'sub array' the indexnumber of the value in the complete list will be returned.
If the 'sub array' doesn't contain the value you are checking for the result will be -1.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa4", "aa6")

If Not .contains(it) Then .Add it

Next
msgbox .IndexOf("aa2", 0)
msgbox .IndexOf("aa2", 1)
msgbox .IndexOf("aa2", 2)
msgbox .IndexOf("aa2", 3)
msgbox .IndexOf("aa2", 4)
msgbox .IndexOf("aa2", 5)

End With

' position 1
' position 1
' position 3
' position 3
' position -1
' position -1

Since identical values can occur several times you can check the last occurrence of that value in the ArrayList.
That's what the property .LastIndexOf does:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
msgbox .LastIndexOf("aa2")

End With

9. Reading/retrieving elements

9.1 Reading/retrieving 1 element

Use the index number to retrieve an item.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.add array("P_01","P_02","P_03","P_04")
.add "last item"

msgbox .Item(0)
msgbox .Item(.count-1)
msgbox .Item(6)(3)

End With

' the first item
' the last item
' the 4th element in the array in the 6th item in the ArrayList.

9.2 reading/retrieving successive elements

The method .GetRange starts at the index number (first argument) and read an amount (second argument) of consecutive elements from the ArrayList.
Since .GetRange stores the result in another Arraylist you need the 'Set' statement to assign it to an object variable.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

Set st = .getrange(2, 4)
for each it in st

msgbox it

End With

' read 4 elements starting at index number 2 (= third item)

If you want the ArrayLIst that is the result of .GetRange to be written to a 'normal' Array you can use the method that is the subject of the next paragraph: .ToArray.
In that case you don't need the 'Set' statement.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

st = .GetRange(2, 4).ToArray
msgbox Join(st,vbLf)

End With

' read 4 elements starting at index number 2 (= third item)

9.3 reading/retrieving all elements

The method .ToArray writes all elements of the ArrayList into a 1-dimensional Array.
Dependent of the resulting arrays contents you can apply the methods 'join', 'filter' and 'replace'.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

sn=.toarray
MsgBox Join(sn,vbLf)

End With

If you want to write the result into an Excel worksheet:

Horizontally:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

sheet1.cells(1).resize(,.count)=.toarray

End With

Vertically:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

sheet1.cells(1).resize(.count)=application.transpose(.toarray)

End With

10. change an element

You can change an existing element in an ArrayList by assigning a different value to that item or a different element.
You can write directly into the item if you use the method .Item in combination with its index number.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

.item(3)="aa20"
.item(4)=12
.item(5)=Date
.item(2)=array(1,2,3,4,5)

End With

11. sorting elements

11.1 ascending sorting elements

The method .Sort sorts all elements in the ArrayList in ascending order.
Prerequisite of course is that any sorting is possible: the values of the elements must contain strings or numbers (no arrays or objects e.g.).
The ArrayList evaluates whether the sorting should be text based or number based.
A mixture of datatypes generates an error.

sorting strings

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.Sort
sn = .toarray

MsgBox Join(sn, vbLf)

End With

sorting strings

With CreateObject("System.Collections.ArrayList")

For Each it In Array("12", "112", "2", "34", "305", "302")

.Add it

Next
.Sort
sn = .toarray

MsgBox Join(sn, vbLf)

End With

sorting numbers

With CreateObject("System.Collections.ArrayList")

For Each it In Array(12, 112, 2, 34, 305, 302)

.Add it

Next
.Sort
sn = .toarray
MsgBox Join(sn, vbLf)

End With

error sorting mixed datatypes

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", 12, 112, "aa3", "aa2", "aa6")

.Add it

Next
.Sort

End With

' error

11.2 descending sorting elements

The method .reverse sorts all elements in an ArrayList in descending order.
Prerequisite of course is that any sorting is possible: the values of the elements must contain strings or numbers (no arrays or objects e.g.).
The ArrayList evaluates whether the sorting should be text based or number based.
A mixture of datatypes generates an error.

sorting strings

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.Reverse

MsgBox Join(.toarray, vbLf)

End With

sorting strings

With CreateObject("System.Collections.ArrayList")

For Each it In Array("12", "112", "2", "34", "305", "302")

.Add it

Next
.Reverse

MsgBox Join(.toarray, vbLf)

End With

sorting numbers

With CreateObject("System.Collections.ArrayList")

For Each it In Array(12, 112, 2, 34, 305, 302)

.Add it

Next
.Reverse

MsgBox Join(.toarray, vbLf)

End With

error sorting mixed datatypes

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", 12, 112, "aa3", "aa2", "aa6")

.Add it

Next
.Reverse

End With

' error

11.3 sorting arrays

If an Arraylist contains Arrays you can't sort them with the .Sort. or .Reverse method.
There's a simple workaround:

1. add the items of the Array's sorting 'column' in the Arraylist
2. sort the items using .Sort or .Reverse
3. put the sorted items in an Array using .ToArray
4. clear the ArrayList
5. Add the 'rows' of the original Array to the Arraylist in the order of the sorted Array

Sub M_snb()

sn = Sheet1.Range("A1:G20")

With CreateObject("System.Collections.Arraylist")

For j = 1 To UBound(sn)

.Add sn(j, 3)

Next
.Sort
sp = .ToArray
.Clear

For j = 0 To UBound(sp)

For jj = 1 To UBound(sn)

If sn(jj, 3) = sp(j) Then

.Add Application.Index(sn, jj)
sn(jj, 3) = ""
Exit For

End If

Next

For j = 0 To .Count - 1

Sheet1.Cells(j + 1, 10).Resize(, UBound(sn, 2)) = .Item(j)

End With

End Sub

12. delete elements from an ArrayList

12.1 delete 1 element by value

The method .Remove deletes 1 element from the ArrayList.
In the argument you enter the element's value.
If no such element exists the method will be ignored; VBA doesn't generate an error nor a message.

Delete the first item that contains the value "aa3":

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.Remove "aa3"

MsgBox Join(.ToArray,vbLf)

End With

12.2 delete 1 element by its position (index number) in the ArrayList

The method .RemoveAt deletes the element with the index number as entered in the argument of the method.
If the indicated index number doesn't exist VBA will generate an error and an error message.

Delete the 5th element (= index number 4)

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

.Add it

Next
.RemoveAt 4

MsgBox Join(.ToArray,vbLf)

End With

12.3 delete successive elements

The method .RemoveRange deletes a group of adjacent elements.
In the first argument you enter the index number of the first item that has to be deleted, in the second argument the number of elements after the first one that have to be deleted.
If any of both arguments is incorrect VBA generates an error and an error message.

Delete 3 elements starting from index number 2:

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.RemoveRange 2,4

MsgBox Join(.ToArray,vbLf)

End With

12.4 delete all elements

The method .Clear deletes all elements from the ArrayList.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next
.Clear

MsgBox .Count

End With

13. make a copy of the ArrayList

The method .Clone creates an independent copy of the ArrayList.
Changes in the copy won't affect the original ArrayList.
Since the copy is also an ArrayList you will need the instruction 'Set' to assign the copy to a variable.

With CreateObject("System.Collections.ArrayList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add it

Next

set c_00=.Clone
msgbox join(c_00.ToArray,vbLf)

c_00.item(2)="~"
msgbox join(.ToArray,vbLf) & vbLf & vbLf & join(c_00.ToArray,vbLf)

End With