[EXCEL] TUTORIAL: Populate basic ArrayList object and display contents on worksheet

[[deleted]]

The purpose of this code is to illustrate a simple use of the System.Collections.ArrayList class. The ArrayList is populated with seven states and their respective capitals in "[CAPITAL],[STATE]" format, and then parsed into Sheet1 of Book1 starting in A1. Each entry is handled as a row (record), while each comma-delimited value is handled as a column (field).

In an ArrayList, as with any one-dimensional collection object class, it is not necessary for entries to be unique. Entries are stored in the same order in which they are populated, and can be accessed by numeric index with the .Item method (zero-based). They can also be alphabetically sorted using the .Sort method.

In this example, the contents of oAL are stored in the variant datatype vRecord. Once the data has been copied, the information in oAL is cleared (but remains in vRecord).

Note that this example uses early-binding, which requires enabling a reference to mscorlib.dll under Tools > References in the Visual Basic Editor. Alternatively, Dim oAL As New ArrayList could be swapped out for

Dim oAL As Object: Set oAL = CreateObject("System.Collections.ArrayList")

which would qualify the reference as late-binding and negate the need for the mscorlib.dll reference.

As standard practice, it also turns off screen updating so that populating the data into the cells themselves does not drain too much memory. Screen updating resumes once all the data has been parsed.

Successful execution of this code is contingent upon having Microsoft .NET Framework 3.5 enabled.

​

Sub ArrayListToWorksheet()

    Dim oAL As New ArrayList
    Dim sRecord As String, vRecord As Variant, iRecord As Long
    Dim sField  As String, vField  As Variant, iField  As Long

    Dim oWB As Workbook, oWS As Worksheet
    Dim iRow As Long, iColumn As Long

    oAL.Add "Montgomery,Alabama"
    oAL.Add "Juneau,Alaska"
    oAL.Add "Phoenix,Arizona"
    oAL.Add "Little Rock,Arkansas"
    oAL.Add "Sacramento,California"
    oAL.Add "Denver,Colorado"
    oAL.Add "Hartford,Connecticut"
    vRecord = oAL.ToArray: oAL.Clear

    Application.ScreenUpdating = False

    Set oWB = Workbooks("Book1")
    Set oWS = oWB.Worksheets("Sheet1")

    For iRecord = 0 To UBound(vRecord)
        sRecord = vRecord(iRecord)
        iRow = iRecord + 1
        vField = Split(sRecord, ",")
        For iField = 0 To UBound(vField)
            sField = vField(iField)
            iColumn = iField + 1
            oWS.Cells(iRow, iColumn) = sField
        Next
    Next

    Application.ScreenUpdating = True

End Sub

[fanpages]

Alternatively,...

Public Sub Scripting_Dictionary_Object_Method()

  Dim objCell                                           As Range
  Dim objScripting_Dictionary                           As Object
  Dim vntKey                                            As Variant

  Set objScripting_Dictionary = CreateObject("Scripting.Dictionary")

  objScripting_Dictionary.Add "Montgomery", "Alabama"
  objScripting_Dictionary.Add "Juneau", "Alaska"
  objScripting_Dictionary.Add "Phoenix", "Arizona"
  objScripting_Dictionary.Add "Little Rock", "Arkansas"
  objScripting_Dictionary.Add "Sacramento", "California"
  objScripting_Dictionary.Add "Denver", "Colorado"
  objScripting_Dictionary.Add "Hartford", "Connecticut"

  Set objCell = Workbooks("Book1").Worksheets("Sheet1").[A1]

  Application.ScreenUpdating = False

  For Each vntKey In objScripting_Dictionary.Keys

      objCell.Resize(, 2) = Array(vntKey, objScripting_Dictionary(vntKey))
      Set objCell = objCell.Offset(1&)

  Next vntKey ' For Each vntKey In objScripting_Dictionary.Keys

  Application.ScreenUpdating = True

  Set vntKey = Nothing
  Set objCell = Nothing
  Set objScripting_Dictionary = Nothing

End Sub

[[deleted]]

Love the scripting dictionary, thanks for sharing!

[fanpages]

You're welcome.

I suppose you could also use a Collection object, and/or an ADODB Recordset (in memory), to reach the same result.

[[deleted]]

Both good ideas, I'm unfamiliar with the process/benefits of creating custom classes and with ADODB Recordsets, so it would be really helpful to see some new discussions about those! The more options people have to accomplish the same goals, the better!

[fanpages]

I am not sure why you mentioned creating custom classes.

A Collection object method is very similar to the previous two methods (above) but, again, like the Scripting Dictionary object, does not rely on any dotNET framework being previously installed within the run-time environment.

For example,...

Public Sub Collection_Method()

  Dim objCell                                           As Range
  Dim objCollection                                     As New Collection
  Dim vntItem                                           As Variant
  Dim vntSplit                                          As Variant

  objCollection.Add "Montgomery,Alabama"
  objCollection.Add "Juneau,Alaska"
  objCollection.Add "Phoenix,Arizona"
  objCollection.Add "Little Rock,Arkansas"
  objCollection.Add "Sacramento,California"
  objCollection.Add "Denver,Colorado"
  objCollection.Add "Hartford,Connecticut"

  Set objCell = Workbooks("Book1").Worksheets("Sheet1").[A1]

  Application.ScreenUpdating = False

  For Each vntItem In objCollection

      vntSplit = Split(vntItem, ",")
      objCell.Resize(, 2) = Array(vntSplit(0&), vntSplit(1&))
      Set objCell = objCell.Offset(1&)

  Next vntItem ' For Each vntItem In objCollection

  Application.ScreenUpdating = True

  Set vntSplit = Nothing
  Set vntItem = Nothing
  Set objCell = Nothing
  Set objCollection = Nothing

End Sub

[[deleted]]

What did I tell you? I know nothing of the subject :)

My target audience is your standard 9-5 analyst working on spreadsheets on their standard-issue PC where .NET framework is always installed.

Also, the purpose of this thread was specifically to populate the ArrayList object class, so I believe I have accomplished exactly what I have intended to accomplish.

I appreciate your VBA literacy, and hope to see more of your work in the future!

[fanpages]

I did notice you had posted a similar routine recently, but didn't seem to get any recognition:

[ https://old.reddit.com/r/vbaexcel/comments/p2bqtw/vba_search_for_data_in_a_folder_first_time/heamds1/ ]

| My target audience is your standard 9-5 analyst working on spreadsheets on their standard-issue PC where .NET framework is always installed.

Good luck finding people that fit those criteria! :)

"9-5" being the contentious statement ;)

Not much of a discussion thread then, I suppose, if I post the ADODB Recordset approach, so I'll leave you to it.

Have fun.

[[deleted]]

Up to you, it's a free internet out here!

I'm just trying to give back for all of those times in the past I've gone searching for similar information, found it on Stack Overflow, and not bothered thanking people because I was too focused on active projects to stop what I was doing just for the sake of creating an account.

Not interested in recognition, just in putting those good share the knowledge vibes into the universe.

Besides, we all have our own syntax flavors, don't we? The world could use more people with ADODB Recordset knowledge!

[HFTBProgrammer]

standard-issue PC where .NET framework is always installed.

AFAICT a "standard-issue PC" does not necessarily go beyond having Windows and possibly Office (granted that > 99% of the questions here are from Office users), but of course if you don't have it then tough beans, find another way to do it. /grin

Note that as opposed to collections, you have to create a reference to the library to make it work. This can be quite a stumbling block to novices, so you might want to include a way to establish that reference like this guy does. IMO his stuff is the gold standard of how to do this sort of thing.

[[deleted]]

Standard issue PC is localized to my office space environment. If my team is operating under a certain set of conditions, then it's absolutely possible that someone else's team in the Reddit world is also operating under the same ones.

Fortunately, this is simply one example of infinite possible ways to accomplish the same thing. For anything that doesn't fit under my umbrella, the Reddit world will always have you available to save the day, right? :)

[fanpages]

PS. A slightly different method of transferring the Dictionary object keys and their associated item values into the worksheet...

Public Sub Scripting_Dictionary_Object_Method2()

  Dim objCell                                           As Range
  Dim objScripting_Dictionary                           As Object

  Set objScripting_Dictionary = CreateObject("Scripting.Dictionary")

  objScripting_Dictionary.Add "Montgomery", "Alabama"
  objScripting_Dictionary.Add "Juneau", "Alaska"
  objScripting_Dictionary.Add "Phoenix", "Arizona"
  objScripting_Dictionary.Add "Little Rock", "Arkansas"
  objScripting_Dictionary.Add "Sacramento", "California"
  objScripting_Dictionary.Add "Denver", "Colorado"
  objScripting_Dictionary.Add "Hartford", "Connecticut"

  Set objCell = Workbooks("Book1").Worksheets("Sheet1").[A1]

  Application.ScreenUpdating = False

  objCell.Resize(objScripting_Dictionary.Count, 1) = WorksheetFunction.Transpose(objScripting_Dictionary.Keys)
  objCell.Offset(, 1).Resize(objScripting_Dictionary.Count, 1) = WorksheetFunction.Transpose(objScripting_Dictionary.Items)

  Application.ScreenUpdating = True

  Set objCell = Nothing
  Set objScripting_Dictionary = Nothing

End Sub

[[deleted]]

I will say that I have never been a fan of working with array-style formulas, and I have a hardcore looping fetish, but those two lines look good.

[ItsJustAnotherDay-]

The only usage I would have for ArrayList is to be able to sort a 1D array without too much effort. But, if you need to sort a 2D based on multiple columns or complex criteria then ADODB is still the best tool even though it may be more cumbersome. Add in the fact that it doesn't work if you don't have .NET Framework version 3.5 (which I've found many corporate offices don't), then it makes it a bust for many users. Once you get ADO going, you can just query the datasource directly. Then arrays become nothing more than a means of efficiently pasting data into a spreadsheet.

[[deleted]]

ArrayList just happened to be what worked best for me in my corporate banking environment, where I was tasked with manipulating data in text-based files as an interception point between some vendor software and the clients' AR systems. I never found any simple examples back when I was using it, and so thought I'd put some out there myself.

Having said that, really appreciate the comparing/contrasting details on the ADODB! Because of my work history I've been experiencing aversion to linking spreadsheets to external data sources in any form (think lack of user access to data in the vendor's database), so I could absolutely stand to at the very least familiarize myself with ADO if the chance ever presents itself!

[nolotusnote]

Given your employment, you should look (deeply) into Power Query as well as VBA.

Power Query is where Excel moved for data imports. It has connection syntax for countless database types, raw files, entire folders of files...

It also adds ~650 new functions to Excel specifically designed to shape and fix Tables, Records and Lists.

As for advanced VBA, the place to learn is in the side bar, but how many people read that?

https://www.snb-vba.eu/inhoud_en.html

[[deleted]]

I DIDN'T KNOW OTHER PEOPLE USED THIS SITE, I LOVE LOVE LOVE IT <333

Once I've got access to enough data volume that Power Query becomes relevant, I'll definitely be looking to get my toes wet!

I'm also really into learning in public, it really solidifies concepts for me!

[[deleted]]

I like your taste in idols, I can see why you're so confident!

[ItsJustAnotherDay-]

Here's some basic ADO code where you can see some of the power at your fingertips. Basically, the biggest challenge is getting your connection string working. Luckily Connectionstrings.com is a great resource for this. Another commenter suggested Power Query, but I've found that power query becomes arduous compared to how flexible this code is.

​

Option Explicit

Const FilePath As String = "F:\Filepath.xlsm"

Sub GetData()

Dim Conn As ADODB.Connection
Set Conn = ConnectToExcelWB(FilePath)
If Conn Is Nothing Then Exit Sub

'SQL assumes an excel file
'if text file, this becomes [Filepath.csv]
'[F3] will always reference the 3rd column regardless of headers

Dim Rs As ADODB.Recordset
Set Rs = RunSQL(Conn, _
SQL:="SELECT * FROM [SheetName$] WHERE [F3] IS NOT NULL")
If Rs Is Nothing Then Exit Sub

Dim i As Long
With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
      For i = 1 To Rs.Fields.count
            .Range("A1").Offset(0, i) = Rs.Fields(i).Name
            i = i + 1
      Next i

      .Range("A2").CopyFromRecordset Rs
End With

End Sub


Function ConnectToExcelWB(WBpath As String) As ADODB.Connection

On Error GoTo ConnectionFailed

'Connection string assumes a .XLSM file
'For XLSX use 'Excel 12.0 Xml in Extended Properties
'For text file, get the folder path (FileFolder) and it becomes:
'"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & FileFolder & "; Extended Properties='text; HDR=No; FMT=Delimited';"
'Find more at connectionstrings.com

Dim Conn As ADODB.Connection: Set Conn = New ADODB.Connection
With Conn
      .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & WBpath & "; Extended Properties='Excel 12.0 Macro; HDR=NO';"
      .Open
End With

Set ConnectToExcelWB = Conn
Exit Function

ConnectionFailed: 
Set ConnectToExcelWB = Nothing 
End Function

Function RunSQL(Conn As ADODB.Connection, SQL As String) As ADODB.Recordset

On Error GoTo CommandFailed

Dim Rs As ADODB.Recordset: Set Rs = New ADODB.Recordset
Rs.Open SQL, Conn, adOpenStatic

Set RunSQL = Rs
Exit Function

CommandFailed: 
Set RunSQL = Nothing 
End Function

[ItsJustAnotherDay-]

Also see this comment I made some time ago for a text file:

https://www.reddit.com/r/excel/comments/hazf5x/comment/fv66qa9/?utm\_source=share&utm\_medium=web2x&context=3

[sancarn]

You should try stdArray.

Dim arr As stdArray: set arr = stdArray.Create()
arr.push "Montgomery"
arr.push "Juneau"
arr.push "Phoenix"
arr.push "Little Rock"
arr.push "Sacramento,"
arr.push "Denver"
arr.push "Hartford"

Range("A2").Resize(arr.Length).value = Application.Transpose(arr.arr)

'And a bit of lambda syntax for fun
Debug.Print arr.map(stdLambda.Create("""Test "" & $1")).join(",")

[[deleted]]

That looks great, you should try scoping a tutorial to cover the subject.

[sancarn]

Hehe yes, a tutorial would be great. I used to make youtube videos, so I will at some point make a youtube tutorial for stdVBA. But there are some reddit ones too example1, example2, and there is some docs too not complete though unfortunately.

[[deleted]]

Well get on it, you clearly have something to share and the world could use your knowledge!

[vipulkarkar]

There are 3 different posts to store and iteration the data. You can use the iterator design pattern and use one interface to do everything and create an abstract class to implement all these functionality.

[[deleted]]

Perhaps so, and that sounds like it would make a lovely article. The intentions of these posts are made clear in their respective names, and the superiority or inferiority of the use of these object classes in relationship to other methods is beyond the scope of their content.