在VBA中作为类成员的类对象数组 [英] Array of class objects as class member in VBA

问题描述

我正在VBA中编写一个Excel宏，以向图书馆顾客发送电子邮件，提醒他们过期的资料.数据来自包含以下数据的电子表格

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.

我想创建一个包含成员的Book类

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

和带有成员的EmailData类

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

其中，book_list是Book对象的数组，以及方法sendEmail().

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

但是，这不起作用，因为显然我无法将Book对象的数组定义为EmailData对象的成员.

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?

推荐答案

根据我的评论，我将使用一个集合.这是定义和初始化的方式

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

并使用它

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

这篇关于在VBA中作为类成员的类对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！