VBA Function to Return Collection

Question

I'm trying to return a type Collection from a function and I am getting the error:

Compile error:

Invalid use of property

Here is the below code that's I created to test the function. I know the collection is filling correctly as I've testing a run through of the collection and output all values; which all came out as intended.

QUESTION:

Why is this error occurring? And what's the best approach to fix it?

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

And here is the code initializing my Error Codes

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

Below is the function in conjunction with above to add to the collection

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

InitializeErrorCodes is a function, not a property, and you are trying to use it as a property. Correct call should be Set ErrorCodes = InitializeErrorCodes. And since InitializeErrorCodes does the collection initialization, you should drop the Set ErrorCodes = New Collection in testingFunc — Vincent G
– Vincent G, Commented Mar 14, 2018 at 16:56
@VincentG My oh my... not sure how I missed such a newbie mistake... Got a little rusty after a couple week break apparently. Thanks for spotting that out for me! Can you post as an answer so I can mark it? — Maldred
– Maldred, Commented Mar 14, 2018 at 16:59

Vincent G · Accepted Answer · 2018-03-14 17:01:58Z

5

InitializeErrorCodes is a function, not a property, but you are trying to use it as a property. Correct call should be Set ErrorCodes = InitializeErrorCodes.

And since InitializeErrorCodes does the collection initialization, you should drop the Set ErrorCodes = New Collection in testingFunc.

answered Mar 14, 2018 at 17:01

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

VBA Function to Return Collection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related