The function returns a class containing the function that returns the class

Question

I am working on an object-oriented Excel add-in to extract information from our ERP system database. The following is an example of a function call:

itemDescription = Macola.Item("12345").Description

Macola is an instance of a class that takes care of accessing the database. Item () is a function of the Macola class that returns an instance of the ItemMaster class. Description () is a function of the ItemMaster class. It all works correctly.

Elements can be stored in several places, so the next step:

quantityOnHand = Macola.Item("12345").Location("A1").QuantityOnHand

Location () is a function of the ItemMaster class that returns an instance of the ItemLocation class (well, theoretically anyway). QuantityOnHand () is a function of the ItemLocation class. But for some reason, the ItemLocation class is not even initialized.

 Public Function Location(inventoryLocation As String) As ItemLocation Set Location = New ItemLocation Location.Item = item_no Location.Code = inventoryLocation End Function 

In the above example, the variable item_no is a member variable of the ItemMaster class.

Oddly enough, I can successfully create an instance of the ItemLocation class outside of the ItemMaster class in a non-class module.

 Dim test As New ItemLocation test.Item = "12345" test.Code = "A1" quantityOnHand = test.QuantityOnHand 

Is there a way to make this work the way I want? I am trying to maintain the API as simple as possible. So only one line of code is required to get the value.

Answer 1

Each time your function refers to a location, it creates a new ItemLocation (because it resembles a function, recursive), or so it seems. You might need to isolate the ItemMaster inside the function, e.g.

 Public Property Get Location(inventoryLocation As String) As ItemLocation Dim clsReturn As ItemLocation Set clsReturn = New ItemLocation clsReturn.Item = "item_no" clsReturn.Code = inventoryLocation Set Location = clsReturn End Property 

I'm not sure why you are using a function instead of a property, but if you have a good reason, I’m sure you can adapt it. I also could not understand where item_no came from, so I made it a string.

Answer 2

You can try to separate the declaration and the instance of the objects in the VBA code. I would also create an object variable local to the function and return it at the end. Try the following:

 Public Function Location(inventoryLocation As String) As ItemLocation Dim il As ItemLocation 'Declare the local object ' Set il = New ItemLocation 'Instantiate the object on a separate line ' il.Item = item_no il.Code = inventoryLocation Set Location = il 'Return the local object at the end ' End Function 

I'm not sure if this caused this problem, but I remember reading that VB6 / VBA has a problem with declaring and creating an object in one line of code. I always highlight Dim from my Set in VBA on two lines.

Answer 3

I cannot reproduce this, but let me tell you what I did, and maybe this will help you find your problem.

Here is the code for Class1:

 Public Function f() As Class2 Set f = New Class2 fp = 42 End Function 

and here is the code for Class2:

 Private p_ Public Property Let p(value) p_ = value End Property Public Property Get p() p = p_ End Property Private Sub Class_Initialize() Debug.Print "Class 2 init" End Sub Private Sub Class_Terminate() Debug.Print "Class 2 term" End Sub 

If I go to the nearest window and enter:

 set c1=new Class1 

and then

 ?c1.f().p 

I'm coming back:

 Class 2 init 42 Class 2 term 

So, an instance of class 2 is created, its property “p” is written and read, but then VBA kills it after this line is executed, because no variable has a reference to this instance.

As I said, this does not match your problem as described. I probably lack details in the details, but I hope this helps.

EDIT:

To clarify, I mean for my simpler example calling 'c1.f (). P' to match your

 quantityOnHand = Macola.Item("12345").Location("A1").QuantityOnHand 

but my simple example works just fine. So now you have three answers that “need more information,” but this is an interesting little puzzle.

If you don’t see an instance of “ItemLocation” at all, does that mean you also don’t see a call to your “Location” method of the “ItemMaster” class? Therefore, the problem may be related to the hosted Location code.