Function returning a class containing a function returning a class

Question

I'm working on an object-oriented Excel add-in to retrieve information from our ERP system's database. Here is an example of a function call:

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

Macola is an instance of a class which takes care of database access. Item() is a function of the Macola class which returns an instance of an ItemMaster class. Description() is a function of the ItemMaster class. This is all working correctly.

Items can be be stored in more than one location, so my next step is to do this:

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

Location() is a function of the ItemMaster class which returns an instance of the ItemLocation class (well, in theory anyway). QuantityOnHand() is a function of the ItemLocation class. But for some reason, the ItemLocation class is not even being intialized.

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

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

Oddly enough, I can successfully instantiate 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 some way to make this work the way I want? I'm trying to keep the API as simple as possible. So that it only takes one line of code to retrieve a value.

Answer 1

You might try separating out the declaration and instantiation of objects in your VBA code. I would also create an object variable local to the function and return it at the end. Try this:

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 is what caused the problem, but I remember reading that VB6/VBA has a problem with declaring and instantiating an object on the same line of code. I always separate out my Dim from my Set in VBA into two lines.

Answer 2

I can't seem to reproduce this, but let me report what I did and maybe that will help you find your problem.

Here is the code for Class1:

Public Function f() As Class2
    Set f = New Class2
    f.p = 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 immediate window and enter:

set c1=new Class1

and then

?c1.f().p

I get back:

Class 2 init
 42 
Class 2 term

So an instance of Class2 gets created, it's property 'p' gets written and read, but then VBA kills it after that line executes because no variable has a reference to that instance.

Like I said, this doesn't match up with your problem as described. I am probably missing some point in the details, but I hope this helps.

EDIT:

To clarify, I mean for my simpler example of calling 'c1.f().p' to correspond to your

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

but my simpler example works just fine. So you now have three answers that amount to "need more info", but it's an interesting little puzzle.

If you're not seeing an instance of 'ItemLocation' get created at all, does that mean you're also not seeing a call to your 'Location' method of class 'ItemMaster'? So possibly the problem is upstream from the 'Location' code posted.

Answer 3

Every time your function refers to Location, it creates a New ItemLocation (because it recalls the function, recursive like), or so it seems. Maybe you need to isolate the ItemMaster inside the function, like this

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 use a function instead of a property, but if you have a good reason, I'm sure you can adapt this. I also couldn't figure out where item_no came from, so I made it a string.