Storing Stuff in VBA Lists

You no doubt recall when snb wrote about Scripting.Dictionaires. Well, there’s more.

I use Collection objects in my custom class modules almost exclusively. It’s the only object, that I know of, that I can enumerate using For Each.

Outside of custom class modules, I use Dictionary objects. I used to avoid them because they weren’t built in to the language. I was always afraid of some dependency problem. But I’ve never seen one in all my years, so I’m over that now. The advantage of the Exists property and the ability to produce an array of keys or items is awesome. it’s probably more awesome than For Each, but I just haven’t made that leap yet.

And I never use ArrayLists because I never remember them. That’s not totally true. When I’m writing a procedure with a Dictionary and I need to sort, I kick myself for not using an ArrayList.

Here’s some features of Collections, Dictionaries, and ArrayLists.

Feature Collection Dictionary ArrayList
New Enum in class Yes No No
Exists No .Exists .Contains
Key Value paradigm Yes Yes No
Unique keys Yes Yes NA
Key data types String Any NA
Get keys No Yes NA
Auto create items No Yes No
Insert anywhere .Add(,,before,after) No .Insert
Output to array No .Keys or .Items .ToArray

There are other differences. Those are just the ones that are important to me. If there’s a difference that’s important to you, leave a comment. You can read everything you ever wanted to know about these objects at one of the pages below:


The Encyclopedia of Dictionaries

Frequent commenter snb has written a thorough page on the Scripting.Dictionary object. I resisted Dictionaries for the longest time, but lately I’ve been using them in almost every situation where I would have used a Collection object. Here’s the table of contents:

– What is a dictionary ?
– What is the purpose of a dictionary ?
– Where does the Dictionary derive from ?
– How to create a Dictionary ?
– How to populate a Dictionary ?
– Add or replace ?
– Keys
– When is a key unique ?
– Create unique keys automatically
– A list of unique elements
– Items
– The number of elements in a Dictionary
– Check the existence of a key / item in the Dictionary
– How to retrieve 1 element from the Dictionary ?
– How to use the array .Keys ?
– How to use the array .Items collection ?
– Change the key of an item
– Copy an item inside the Dictionary
– Remove an item from the Dictionary
– Adapt the contents of an item in the Dictionary
– Remove all items from the Dictionary
– Early binding and late binding
– Examples

If you use Dictionaries, you should bookmark this page. If not, you should start.

An MSForms Treeview 2: Ready for beta testing

Hi everyone,

Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart.

This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still waiting for test results.

Peter Thornton (thank you Peter!) jumped in on the project enthusiastically and really made a difference adding all sorts of usefull stuff and optimising the code for performance.

Now we’re ready for beta testing.

Please visit this page of my website for a description of the control and a download file which includes a demo userform implementing the treeview classes we built:

An MSForms (all VBA) treeview

Tell us what you think of it (oh, and please report bugs too!).


Jan Karel Pieterse

An MSForms Treeview

If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.

There are a couple of problems with this Treeview control:

  1. Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
  2. The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.

Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)

The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:

Not bad, right?

Both Treeviews allow for checkboxes:

And both allow icons (windows Treeview not shown here):

You can also edit a node:

And expand and collapse nodes and navigate the tree using your arrow keys.

We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.

We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.

Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!


Jan Karel Pieterse

Populating Class Properties

Depending on the requirements, I’ll choose a method for populating my custom collection classes using from data from a worksheet.

In this example, I’m using a list of the best selling albums of all time.

My Album class has properties as follows:
Public Artist As String
Public Album As String
Public Released As Date
Public Genre As String
Public Sales As Long

The code in my main routine is:
Sub test()
Dim albs As Albums, alb As Album

Set albs = New Albums
albs.FillFromSheet Sheet1
End Sub

Filling the collection is just a matter of reading each row and popping the contained values into the right property.
The difficulty is knowing which columns relate to what properties. It’s a mapping problem – mapping columns to properties.

I could make an assumption about the positions of the columns and assume each is a known index.

Public Sub FillFromSheet(wks As Worksheet)
Const cFirstRow = 2
Dim i As Long, obj As Album

With wks
For i = cFirstRow To .Cells(Rows.Count, 1).End(xlUp).Row
Set obj = New Album
obj.Artist = .Cells(i, 1)
obj.Album = .Cells(i, 2)
obj.Released = .Cells(i, 3)
obj.Genre = .Cells(i, 4)
obj.Sales = .Cells(i, 5)

Me.Add obj
End With
End Sub

I don’t really like this because I’ve been taught and have tried to follow the approach that offsets/indices like these should be defined as constants.
I’ll modify my FillFromSheet code.

Const cArtistCol = 1, cAlbumCol = 2, cReleasedCol = 3, cGenreCol = 4, cSalesCol = 5
obj.Artist = .Cells(i, cArtistCol)
obj.Album = .Cells(i, cAlbumCol)
obj.Released = .Cells(i, cReleasedCol)
obj.Genre = .Cells(i, cGenreCol)
obj.Sales = .Cells(i, cSalesCol)

Seems roughly better, but this too has problems. It can be a pain when you want to change the column order or insert a new column.
You’d have to go through the code and update all of the numbers.
While this is ok for 5, as I have here, changing out 50 columns is a chore.

So, I use an Enum block to determine column positions.
At the top of the class module:

Private Enum AlbumCols
Artist = 1 ' =1 is necessary, otherwise Enum starts at zero
End Enum

And another modification to my FillFromSheet code:

obj.Artist = .Cells(i, AlbumCols.Artist)
obj.Album = .Cells(i, AlbumCols.Album)
obj.Released = .Cells(i, AlbumCols.Released)
obj.Genre = .Cells(i, AlbumCols.Genre)
obj.Sales = .Cells(i, AlbumCols.Sales)

That works well. If I rearrange my columns on the worksheet, the only code change needed is a swap of items in the Enum block – a painless and quick update!

But that’s only if the changes to column order are in my control.
What if an end user changes the column order? Do I really want them poking around in my Enum code too?
I’d usually stop now and decide that if the end user starts screwing with column positions, it’s fair enough that the code should break.
However, if I wanted to go the extra mile, I’d have to find the column index by searching for the text in the column headers.

Excel 2007 (and 2003 to an extent) has a feature called Tables, otherwise known as ListObjects.

My code in the main routine changes to:
Set albs = New Albums
albs.FillFromTable Sheet1.ListObjects("Best_Selling_Albums")

… and the code in my collection class is:

Public Sub FillFromTable(tbl As ListObject)
Const cArtistCol = "Artist", cAlbumCol = "Album", cReleasedCol = "Released"
Const cGenreCol = "Genre", cSalesCol = "Sales (millions)"

Dim i As Long, obj As Album, row As ListRow, col As New Collection

With tbl.HeaderRowRange: For i = 1 To .Count: col.Add i, .Columns(i): Next: End With

For Each row In tbl.ListRows
Set obj = New Album
obj.Artist = row.Range(, col(cArtistCol))
obj.Album = row.Range(, col(cAlbumCol))
obj.Released = row.Range(, col(cReleasedCol))
obj.Genre = row.Range(, col(cGenreCol))
obj.Sales = row.Range(, col(cSalesCol))

Me.Add obj
End Sub

In the preceding code I created a collection of key-value pairs. The key is the column label, the value is column index.
I use that as a lookup when populating my properties.

That’s just a few approaches to reading a table of values into a collection.
I’m interested in feedback. How do you map column positions to properties?