Array of class objects as class member in VBA

Question

I'm writing an Excel macro in VBA to send emails to library patrons alerting them of overdue materials. The data comes from a spreadsheet with data like

UserID   Name        Email      Title    Author     Barcode Call Number Borrowed Date   Due Date
user2   Jones, Bob   bob@jones  Title1  A. Baker    a0001   H00027C     11/23/2014      1/1/2015
user2   Jones, Bob   bob@jones  Title2  C. Dalton   b0002   S00406R     11/23/2014      1/1/2015
user3   Smith, Mary  bob@jones  Title3  E. Franklin c0003   M00174R     11/23/2014      1/1/2015
user3   Smith, Mary  mary@smith Title4  F. Gelt     d0004   M00157G     11/23/2014      1/1/2015
user3   Smith, Mary  mary@smith Title5  H. Ivers    e0005   M00081G     11/23/2014      1/1/2015

I started out with a user defined type for book and patron, and gave each patron an array of books. I tried passing the patron to a function to generate the email text, but apparently I can't pass a user defined type as a parameter. So, now I'm trying classes.

I want to have a Book class with members

Public book_title As String
Public date_borrowed As Date
Public date_due As Date
Public barcode As String
Public call_number As String

and a EmailData class with members

Public email_text As String
Public patron_name As String
Public patron_email As String
Public sender_email As String
Public book_list() As Book

where book_list is an array of Book objects, and a method sendEmail().

But, this isn't working because apparently I can't define an array of Book objects as a member of the an EmailData object.

So, what is my approach here? I've heard of collections. Would that be a solution?

Answer 1

Per my comment I would use a collection. Here is how you define it and initialize it

Public book_list As Collection

'intitalize the collection in the constructor of the class
Private Sub Class_Initialize()
    Set book_list = New Collection
End Sub

and to use it

book_list.Add <book>
dim bk as Book
set bk  = book_list.Item(indexNumber)

Answer 2

There's a couple of options.

The easiest is just to make book_list a Variant type. You can treat it as an array using ReDim. You can use variables of Variant type to store object references just like you would with an object variable.

The other option is to make book_list a private variable and create accessor methods. This is the preferred way to use classes anyway, and if you're using classes in the first place, you might as well observe good object oriented design. Your class would look something like this.

Private email_text As String
...
Private book_list() As Book

Private Sub Class_Initialize()
    email_text = "default email text"
    ...
    ReDim book_list(10)
End Sub

Public Function GetBook(index As Long) As Book
    Set GetBook = book_list(index)
End Function

Public Sub SetBook(index As Long, b As Book)
    Set book_list(index) = b
End Sub