函数返回一个包含返回类的函数的类

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:

我正在开发一个面向对象的Excel插件，用于从ERP系统的数据库中检索信息。以下是函数调用的示例：

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

itemDescription = Macola.Item（“12345”）。描述

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.

Macola是一个负责数据库访问的类的实例。 Item（）是Macola类的函数，它返回ItemMaster类的实例。 Description（）是ItemMaster类的函数。这一切都正常。

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

quantityOnHand = Macola.Item（“12345”）。位置（“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.

Location（）是ItemMaster类的一个函数，它返回ItemLocation类的一个实例（理论上，无论如何）。 QuantityOnHand（）是ItemLocation类的函数。但由于某种原因，ItemLocation类甚至没有初始化。

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.

在上面的示例中，变量item_no是ItemMaster类的成员变量。

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

奇怪的是，我可以在非类模块中成功实例化ItemMocation类以外的ItemLocation类。

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.

有没有办法让这项工作按我想要的方式进行？我正在努力保持API尽可能简单。因此，它只需要一行代码来检索值。

3 个解决方案

#1

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

每次你的函数引用Location时，它会创建一个New ItemLocation（因为它会重新调用函数，像递归一样），或者看起来如此。也许你需要在函数中隔离ItemMaster，就像这样

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.

我不确定你为什么使用函数而不是属性，但如果你有充分的理由，我相信你可以适应这个。我也无法弄清楚item_no来自哪里，所以我把它变成了一个字符串。

#2

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:

您可以尝试在VBA代码中分离出对象的声明和实例化。我还会在函数本地创建一个对象变量，并在最后返回它。尝试这个：

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.

我不确定这是否是导致问题的原因，但我记得读过VB6 / VBA在同一行代码上声明和实例化对象时遇到问题。我总是将我的Dim从VBA中的Set中分成两行。

#3

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:

这是Class1的代码：

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

and here is the code for Class2:

这是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.

因此，创建了一个Class2的实例，它的属性'p'被写入和读取，但是在该行执行之后VBA将其杀死，因为没有变量具有对该实例的引用。

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

为了澄清，我的意思是我调用'c1.f（）。p'来对应你的简单示例

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.

如果您没有看到“ItemLocation”的实例完全被创建，这是否意味着您还没有看到对“ItemMaster”类的“位置”方法的调用？所以问题可能是发布的“位置”代码的上游。

#1

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