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.