Defining a Variable as New Collection

**maacmaac** · 12-07-2008, 10:03 PM

I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.

I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). If someone can explain how this works or point me to a website with more information, I would really appreciate it. I have attached a sample file and a text file with dummy data if needed to see how the code is working. Thanks.

Sub TradeAnalysis()

' imports text file
' shortcut key = control + j

Dim i               As Long
Dim j               As Long
Dim n               As Long
Dim LastRow         As Long
Dim Prompt          As String
Dim Path            As String
Dim AccountNumbers  As New Collection
Dim FieldOrder      As New Collection
Dim WkbData         As Workbook

    FieldOrder.Add "A"
    FieldOrder.Add "B"
    FieldOrder.Add "J"
    FieldOrder.Add "AQ"
    FieldOrder.Add "AM"
    FieldOrder.Add "AF"
    FieldOrder.Add "AR"
    FieldOrder.Add "AP"
    FieldOrder.Add "AD"
    FieldOrder.Add "T"
    FieldOrder.Add "X"
    FieldOrder.Add "L"
    FieldOrder.Add "K"
    FieldOrder.Add "AN"
    FieldOrder.Add "P"
    FieldOrder.Add "C"
    FieldOrder.Add "O"
    FieldOrder.Add "AE"
    FieldOrder.Add "G"
    FieldOrder.Add "AG"
    FieldOrder.Add "AH"
    FieldOrder.Add "AI"
    FieldOrder.Add "I"
    FieldOrder.Add "Q"
    FieldOrder.Add "AK"
    FieldOrder.Add "V"
    FieldOrder.Add "Z"
    FieldOrder.Add "AB"
    FieldOrder.Add "D"
    FieldOrder.Add "E"
    FieldOrder.Add "AO"
    FieldOrder.Add "F"
    FieldOrder.Add "AU"
    FieldOrder.Add "AV"
    FieldOrder.Add "AS"
    FieldOrder.Add "AT"
    FieldOrder.Add "AJ"
    FieldOrder.Add "AL"
    FieldOrder.Add "M"
    FieldOrder.Add "N"
    FieldOrder.Add "R"
    FieldOrder.Add "S"
    FieldOrder.Add "Y"
    FieldOrder.Add "AA"
    FieldOrder.Add "U"
    FieldOrder.Add "W"
    FieldOrder.Add "AC"
    n = FieldOrder.Count

    Prompt = "Select the text file to process."
    Path = Application.GetOpenFilename("Text Files (*.txt), *.txt", , Prompt)
    If Path = "False" Then
        GoTo ExitSub:
    End If

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Workbooks.OpenText Filename:=Path, Origin:=2, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|"  ', TrailingMinusNumbers:=True
    Set WkbData = ActiveWorkbook
        
    With wsTradeTable
        .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).ClearContents
        LastRow = Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
            Application.StatusBar = "Importing Data: " & i & " of " & LastRow
            For j = 1 To n
                .Cells(i, j).Value = Range(FieldOrder(j) & i).Value
            Next j
            If Application.WorksheetFunction.CountIf(.Range("B2:B" & i), "=" & .Range("B" & i).Value) = 1 Then
                AccountNumbers.Add (.Range("B" & i).Value)
            End If
        Next i
        WkbData.Close SaveChanges:=False
        
        n = AccountNumbers.Count
        wsAnalysis.Activate
        Range(Cells(2, 1), Cells(Rows.Count, Columns.Count)).ClearContents
        For i = 1 To n
            Application.StatusBar = "Processing Data: " & i & " of " & n
            Range("A" & i + 1).Value = AccountNumbers(i)
        Next i
    End With
    
ExitSub:
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    
    Set WkbData = Nothing
    
End Sub

**Leith Ross** · 12-08-2008, 01:26 AM

Hello maacmaac,

The Collection object is similar to an array. The referencing of the elements is done by a hash table. A hash table allows random access of the array elements using a mathematical algorithm. The elements of the Collection are always paired. Each item has a key. The key is the reference element that must be unique and must be a string. However, the item can be a string, number, date, or an object. If you do not specify a key then the default key becomes the element's creation order index number. I know this is confusing. A few examples will help. This will also allow all code in the project access to the collection.

  Dim MyColl As New Collection
   'Default key is 1,2,3 etc.
    MyColl.Add "Item1"
    MyColl.Add "Item2"
    MyColl.Add "Item3"
   
   'Returning an item using the default key
    Ret = MyColl(1)    'Ret will equal "Item1"
    Ret = MyColl(2)    'Ret will equal "Item2"

Example of assigning a unique Key to a collection item...

    MyColl.Add "Item1", "Key1"
    MyColl.Add "Item2", "Key2"
    
   Ret = MyColl("Key1")   'Ret will equal "Item1"
   Ret = MyColl("Key2")   'Ret will equal "Item2"

If you attempt to add a Key that already exists, you will generate Error 457 "This key is already associated with element of this collection." The collection remains in memory as long as Sub or Function is running. To make the collection last as long as the VBA project is loaded, declare it as Public in the declarations section of a VBA Module.

Sincerely,
Leith Ross

**maacmaac** · 12-08-2008, 10:39 AM

Leith,

Thanks for the detailed response. It is starting to make sense. I am still a little confused on how the table is created. In the code below, I have highlighted a part of the code. Correct me if I am wrong but I think in this part of the code... if the countif function in the previous line is true, then add the current account number to a "hash" table.

        For i = 2 To LastRow
            Application.StatusBar = "Importing Data: " & i & " of " & LastRow
            For j = 1 To n
                .Cells(i, j).Value = Range(FieldOrder(j) & i).Value
            Next j
            If Application.WorksheetFunction.CountIf(.Range("B2:B" & i), "=" & .Range("B" & i).Value) = 1 Then
                AccountNumbers.Add (.Range("B" & i).Value)
            End If
        Next i

I believe I now have a "collection" of account numbers (correct me if I am wrong). These account numbers are stored in a table. Each account number is assigned a unique "key"

Now when the next part of the code is executed (as below), the information needed is being pulled from the "hash" table (or collection).

        For i = 1 To n
            Application.StatusBar = "Processing Data: " & i & " of " & n
            Range("A" & i + 1).Value = AccountNumbers(i)
        Next i

I am hoping from my comments that I am somewhat understanding how this works. Please comment if my understanding is correct, of if I am still a little bit off. Thanks.

**Leith Ross** · 12-08-2008, 12:20 PM

Hello maacmaac,

Both examples use the the Default Key assignment to store and retrieve the items in the collection. Remember I said the collection elements are paired, one item with one unique key. If you don't specify a key name then the automatic index (1,2,3 etc.) is used by default. The automatic index starts at 1 and increases by 1 each time a new item is added to the collection.

The first example you have will add the cell value of Range("B" & i), provided the range "B2:B' & i is not empty, to the collection. The second example retrieves the collection item specified by the key, which is the index number (default key), and stores the collection item for that key in the cell "A" & i.

Worksheets are an easy way to understand a collection object. A worksheet can referenced by index number or name. For example, the first worksheet is named "Sheet1" then "Sheet2" etc. Those same worksheets can be accessed by using their index numbers as well.

  'These references are the same
   Worksheets("Sheet1")              'Using a unique Key 
   Worksheets(1)                     'Using the automatic index as the Key

Sincerely,
Leith Ross

Sincerely,
Leith Ross