EXCEL VBA study notes: Collection collection

Collection means an object. Refers to the user storing a set of data information in a "one-dimensional array" so that the user can access, add, and delete at any time; such a collection.

The collection object has no restrictions on the type of data stored in it; it can be various types of values, or text, or cell ranges, or pictures. The one-dimensional array just now is quoted because it is similar to a one-dimensional array. Compared with arrays, collections do not need to declare their length first, nor do they need to use statements when changing the length; after deleting one of the data, the subsequent data will automatically move forward without leaving a null value.

If you customize a variable-length array, add element statement

  Sub test() Dim arr() For i = 1 To 10 ReDim Preserve arr(1 To i)'Every time you need to add a one-dimensional array When adding new elements, you need to expand the array arr(i) = i'and then add new elements. NextEnd Sub  

1. When using a collection, this is the case (.add method)

  Dim NCN As New Collection 'Create a collection For i = 1 To 10 NCN.Add i'For a collection, elements can be added using the Add method. Next  

Newly added elements will be automatically added to the end of the queue.

2. The method of reading the elements in the collection (the attribute of the .count collection)

  For i = 1 To NCN.Count'Count attribute returns the total number of elements in the collection t = NCN(i)'Read the collection The i-th variable is stored in the temporary variable t t = NCN.Item(i)'Regular code writing (the effect is the same) Debug.Print NCN(i)'Display the value of the set (i) in the immediate window Next  

The starting sequence number of the elements in the set defaults to "1",Not "0". Although the collection is also called item, there is no Items in the dictionary, so you can't read all of them directly.

3. Method of deleting elements in the collection

  For i = NCN.Count to 1 step -1'Traverse the collection elements NCN.Remove (i)'Each time delete the last one Debug.Print NCN. Count'Display the number of remaining elements in the collection in the immediate window. Next  

Remove, the remaining elements in the collection are automatically forwarded. For example, Remove(10), the original 11-bit data becomes the 10th bit.

4. Clear remove(NCN.count); or Set NCN = Nothing (there is no Clear clear method, 2016 version of Excel)

Use For Each to traverse the collection object, the variables received Defined as a Variant type. Example: For Each i in NCN loop, the variable i, either not declared or Dim i As Variant, cannot be declared as another type; even if there are numbers in the NCN collection, if i is declared as a numeric type, an error will be reported here . Compared with a one-dimensional array, the

collection is more convenient to store data (when the amount of data is unknown), and it is more flexible than arrays when adding and reducing, but the disadvantages are also obvious: you cannot modify the stored elements; already stored The can only be read and deleted. It is more suitable for storage matters where the amount of data is unknown.