Can anyone please comment this VBA code what each line/part of the code does?

someone helped me with the code below.


it uses three different classes  CreateObject("System.Collections.ArrayList")  & With CreateObject("System.Collections.SortedList") and = CreateObject("Scripting.Dictionary")

i have seen previously Scripting.Dictionary"  but i have never seen the other two system.collections.arraylist and sortedlist.

i googled, only in microsoft support page there is a very little detail and no example. where and how it is used.

i need help with commeting the code below, that what line of the code does what.

also i need help if someone could lead me to somewhere where i can learn more about the two classes system.collections.arraylist and system.collections.sortedlist.  with example

Option Explicit

Sub Macro()
    Dim a, i As Long, ii As Long, AL As Object
    Set AL = CreateObject("System.Collections.ArrayList")
    a = Sheets("data").Cells(1).CurrentRegion.Value
    With CreateObject("System.Collections.SortedList")
        For i = 2 To UBound(a, 1)
            If (a(i, 2) <> 111) * (Not AL.Contains(a(i, 2))) Then AL.Add a(i, 2)
            If a(i, 3) Like "[5-7]*" Then
                If Not .Contains(a(i, 1)) Then
                    Set .Item(a(i, 1)) = CreateObject("Scripting.Dictionary")
                End If
                .Item(a(i, 1))(a(i, 2)) = .Item(a(i, 1))(a(i, 2)) + a(i, 4)
            End If
        Next
        ReDim a(1 To AL.Count + 1, 1 To .Count + 1): AL.Sort
        a(1, 1) = "Months/Years"
        For i = 0 To AL.Count - 1
            a(i + 2, 1) = AL(i)
        Next
        For ii = 0 To .Count - 1
            a(1, ii + 2) = .GetKey(ii)
            For i = 2 To UBound(a, 1)
                a(i, ii + 2) = .GetByIndex(ii)(a(i, 1))
            Next
        Next
    End With
    With Sheets("main").Cells(1).Resize(UBound(a, 1), UBound(a, 2))
        .ClearContents
        .Value = a: .Columns.AutoFit
    End With
End Sub

Open in new window

LVL 6
FloraAsked:
Who is Participating?
 
Bill PrewCommented:
0
 
Dirk IelegemsCommented:
@ Flora

Take a look at Robs Website.  You will get your answers here.

http://www.robvanderwoude.com/vbstech_data_arraylist.php
0
 
FloraAuthor Commented:
thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.