返回集合的 VBA 函数

Question

我试图从函数返回类型Collection，但收到错误:

Compile error:

Invalid use of property

这是我为测试该功能而创建的以下代码。我知道集合填充正确，因为我已经测试了集合的运行并输出所有值；一切都按预期进行。

问题:

为什么会出现这个错误？解决这个问题的最佳方法是什么？

Sub testingFunc()

   Dim ErrorCodes As Collection
   Set ErrorCodes = New Collection

   Set InitializeErrorCodes = ErrorCodes '<--- Where my error is occurring when trying to retrieve the collection

End Sub

这是初始化我的错误代码的代码

Function InitializeErrorCodes() As Collection '<--- Where I'm trying to return the collection

   Dim ErrorCodes As Collection
   Set ErrorCodes = New Collection

   AddToErrorCollection ErrorCodes, "CSD_ERR_120", "Multiple products in a case is invalid"
   AddToErrorCollection ErrorCodes, "CSD_ERR_128", "Invalid Store with Zero Allocation"
   AddToErrorCollection ErrorCodes, "DET_ERR_101", "Purchase Order Status Notification"
   AddToErrorCollection ErrorCodes, "DET_ERR_104", "Tolerance Violation - Overshipment"
   AddToErrorCollection ErrorCodes, "DET_ERR_110", "Multiple Open Lines found for Product on PO"
   AddToErrorCollection ErrorCodes, "DET_ERR_111", "Tolerance Violation - short shipment"
   AddToErrorCollection ErrorCodes, "DET_ERR_112", "Tolerance Violation - Number of Store/SKU Discrepancies Exceeded"
   AddToErrorCollection ErrorCodes, "DET_ERR_114", "Product (casepack) Mismatch on one or more product identifiers"
   AddToErrorCollection ErrorCodes, "DET_ERR_115", "Product (Bulk) Mismatch on one or more product indentifiers"
   AddToErrorCollection ErrorCodes, "DET_ERR_116", "No Open Line was found. PO contains a line in Cancel Status for Product"
   AddToErrorCollection ErrorCodes, "DET_ERR_117", "Early Shipping Date Violation"
   AddToErrorCollection ErrorCodes, "DET_ERR_118", "PO Cancel Date Violation || Passed Cancel Date"
   AddToErrorCollection ErrorCodes, "DET_ERR_125", "Duplicate vendor case number"
   AddToErrorCollection ErrorCodes, "HDR_ERR_101", "Duplicate Vendor ASN Number"
   AddToErrorCollection ErrorCodes, "PRE_ERR_105", "Invalid Prepack configuration. Failed - Missing Component SKU(s)"
   AddToErrorCollection ErrorCodes, "PRE_ERR_106", "Product Quantity does not equal the sum of the case quantities"

   Set InitializeErrorCodes = ErrorCodes

End Function

下面是与上面结合添加到集合中的函数

Private Sub AddToErrorCollection(Col As Collection, eName As String, eDescription As String)

   Dim NewErrorCode As cErrorCodes
   Set NewErrorCode = New cErrorCodes
   NewErrorCode.name = eName
   NewErrorCode.description = eDescription

   Col.Add NewErrorCode

End Sub

Answer 1

InitializeErrorCodes 是一个函数，而不是一个属性，但您试图将它用作属性。正确的调用应该是Set ErrorCodes = InitializeErrorCodes。

由于 InitializeErrorCodes 会进行集合初始化，因此您应该将 Set ErrorCodes = New Collection 删除到 testingFunc 中。