Excel VBA Masterclass

Posted on April 27, 2020 by jkpieterse

Hi there. Hope you and yours are well in these difficult times!

This is just a very short announcement that I’ll be doing an on-line version of my Excel VBA Masterclass.

The training is scheduled for May 18, 20, 26, 28, June 2, 4 and I’ll be using Microsoft Teams to deliver it to your homes!

Regards,

Jan Karel Pieterse

Storing Stuff in VBA Lists

Posted on October 5, 2016 by Dick Kusleika

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:

Collections: http://www.snb-vba.eu/VBA_Collection_en.html
Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html
ArrayLists: http://www.snb-vba.eu/VBA_Arraylist_en.html

The Encyclopedia of Dictionaries

Posted on October 22, 2013October 24, 2013 by Dick Kusleika

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:

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

Posted on February 22, 2013 by jkpieterse

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!).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

An MSForms Treeview

Posted on January 31, 2013 by jkpieterse

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:

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.
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!

Regards,

Jan Karel Pieterse

Populating Class Properties

Posted on December 26, 2011January 27, 2017 by Rob van Gelder

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

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

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
        Next
    End With
End Sub

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)

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
     Album
    Released
    Genre
    Sales
End Enum

Private Enum AlbumCols

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

Album

Released

Genre

Sales

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)

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")

1 2	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
    Next
End Sub

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?