Passing objects to procedures in VBA?

Up vote 0 down vote favorite share g+ share fb share tw.

I am working on a simple tool that will allow me to parse multiple CSV files and spit them out onto a fresh worksheet "merged" together. Here is my implementation (I've simplified it) and my issue: Class A private variables as types property methods for accessing variables Class B private variables as types property methods for accessing variables Class C Private cA as ClassA Private cB as Collection 'Collection of ClassB Class D - Part of my problem Private cC as Collection 'Collection of ClassC 'Other member variables and their property get/lets Public Sub AddA(A as ClassA) If cC. Item(A.

Foo) is Nothing then dim tempC as ClassC set tempC = new ClassC tempC. A = A End if End Sub Main Module - Other half of my problem Dim cC as New ClassC 'Initialize Class C, this all works fine Dim tempA as ClassA Set tempA = new ClassA 'Set tempA properties cC. AddA tempA 'This is where my error is I've tried passing it as ByVal and ByRef each gives me different errors ("byref argument type mismatch", "invalid procedure or argument", and "Object doesn't support this property or method" I have no idea what to try next, I even tried the parenthesis "thing" that supposedly forces the parameter into either ByVal or ByRef, I can't remember, that was yesterday.

Thanks. Excel-vba link|improve this question edited May 11 '11 at 15:20 asked May 11 '11 at 14:08Timbermar858 89% accept rate.

The error is inside AddA, so some code would help. I'd also bet you want Public Sub AddA(ByVal A as ClassA). – GSerg May 11 '11 at 14:25 @GSerg I receive the error in the main module calling the sub, and I've tried using ByVal A as ClassA, I get the "Invalid procedure or argument" error.

Please note I added the code for AddA, and realised at the same time I completely left out a class. I've completely updated all the code. Sorry about that.

– Timbermar May 11 '11 at 15:21 Play with Tools -> Options -> General -> Error Trapping to change where you see the error. In this case you might want "Break in class module. " – GSerg May 11 '11 at 15:28 @GSerg Ok, now I see where the error is actually occurring, and it seems to be where I preform my check If cC.

Item(A. Foo) is Nothing then, according to the docs if the item doesn't exist, it will simply error instead of returning a nothing value. Let me fix this, then we'll see what happens – Timbermar May 11 '11 at 15:38.

This line: tempC. A = A means "assing to A property of tempC object the value of the default property of the A object. " Your A object apparently doesn't have a default property.

What you actually meant was probably: Set tempC. A = A But even then, you can't access a private field A of C class from D class. Make the field public or create a public SetA() method on C class and call it from D.

This didn't actually solve my problem, it was actually somewhere else in my code, but you set me looking on the right path, and I didn't know about the Error Trapping settings (which helped a ton), so I'm going to mark this as the answer. – Timbermar May 12 '11 at 13:06.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.