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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
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.
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 |
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:
|
1 2 |
Set albs = New Albums albs.FillFromTable Sheet1.ListObjects("Best_Selling_Albums") |
… and the code in my collection class is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |
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?