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?

change Book_List to a collection

Sorceri
– Sorceri

2015-02-03 21:40:27 +00:00
Commented Feb 3, 2015 at 21:40 — Sorceri
– Sorceri, Commented Feb 3, 2015 at 21:40

Tmdean · Accepted Answer · 2015-02-04 02:40:42Z

10

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)

edited Feb 4, 2015 at 2:40

Tmdean

9,31945 silver badges51 bronze badges

answered Feb 3, 2015 at 21:43

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

abalter Over a year ago

That looks simple enough. Would you mind fleshing out your answer just a bit? Do I first create a book object, and then add it to the list? Is bk = book_list.Item(indexNumber) how I retrieve the book object at indexNumber? When did I set the values for that item in the first place? Thanks --AB

abalter Over a year ago

Oh, and where do I put Public book_list As Collection? In the EmailData class?

Sorceri Over a year ago

yes you declare the member in your EmailData class, bk = book_list.Item(indexNumber) is how you would obtain the object. Yes you want to create the book object and then add it to the list. You could also create methods to search the books collection for a specific book but to provide an example I would need to know your book class and emaildata class.

abalter Over a year ago

Thanks. I think I understand enough now.

abalter Over a year ago

On the line book_list = New Collection I'm getting compile error argument not optional

|

Tmdean · Accepted Answer · 2015-02-03 21:44:59Z

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

Collectives™ on Stack Overflow

Array of class objects as class member in VBA

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related