I've searched the web and I've searched the questions here on *, but I haven't been able to find a solution.
我在网上搜索了关于*的问题，但是我还没找到解决方案。
Here is what I'd like to do:
以下是我想做的:
Suppose I have the following code in a class module named "MyClass"
假设我在一个名为“MyClass”的类模块中有以下代码
Option Explicit
Dim var1 as integer
Sub Initialize(v as integer) var1 = v End Sub
子初始化(v为整数)var1 = vend Sub
Function GetVar1() GetVar1 = var1 End Function
函数GetVar1() GetVar1 = var1结束函数
Then I have a UDF in a separate module with the code
然后在一个单独的模块中有一个UDF和代码。
Function InitializeMyClass(v as integer) as MyClass
Dim myvar as MyClass
Set myvar = new MyClass
Call myvar.Initialize(v)
Set InitializeMyClass = myvar
End Function
Function GetMyVar(m as MyClass) GetMyVar = m.GetVar1() End Function
函数GetMyVar(m作为MyClass) GetMyVar = m.GetVar1()结束函数
Now in cell A1 I have "=InitializeMyClass(3)" and in Cell A2 I have "=GetMyVar(A1)". I get #VALUE errors in both cells. This of course is caused by the fact that I am trying to return a user defined data type into cell A1. I feel that this should be possible, but I am not sure how.
在A1细胞中有"=InitializeMyClass(3)"在A2细胞中有"=GetMyVar(A1)"两个单元格中都有#VALUE错误。这当然是由于我正在尝试将一个用户定义的数据类型返回到单元A1中。我觉得这是可能的，但我不知道该怎么做。
Edit: Oh yeah, the question is, "Is there a way for me to return a user-defined data type into a cell and then have it be callable from another UDF in the example I gave above? I don't know if this requires COM or not. If it does, anyone know how I can get started? Ideally, if someone had an example of how this worked, that would be fantastic!"
编辑:哦，是的，问题是，“对于我来说，是否有办法将用户定义的数据类型返回到单元格中，然后在上面的例子中，让它可以从另一个UDF调用?”我不知道这是否需要COM。如果是的话，谁知道我该怎么开始呢?理想的情况是，如果有人能举个例子说明这是怎么回事，那就太棒了!
Another Edit: Here we go, now i know it can be done: read this description, it's not quantitative, but will give you a sense of what they do, http://www.quanttools.com/index.php?option=com_content&task=view&id=19
另一个编辑:现在我们开始，现在我知道它可以完成了:阅读这个描述，它不是定量的，但是会给你一种他们做什么的感觉，http://www.quanttools.com/index.php?
3 个解决方案
#1
2
As the other answers suggest, the literal answer to your question is "no". You can't store anything other than a number, string, boolean, error, etc. in a cell. You can't return anything other than a simple value such as those, or an array, or a range reference, from a UDF.
正如其他答案所暗示的，你的问题的字面答案是“不”。在单元格中只能存储数字、字符串、布尔值、错误等。除了从UDF返回一个简单的值(比如这些值)、数组或范围引用之外，您不能返回其他任何东西。
However, you can do essentially what you want by passing around (and storing in cells) some kind of handle to your objects that is a legal cell value (i.e. "myclass:instance:42"). This is probably what the example you linked to in your edit does. Your code has to be able to interpret the meaning of the handle values and maintain the objects in memory itself, though. This can get tricky if you care about not leaking objects, since there are lots of ways to erase or overwrite handles that you can't detect if you're using VBA to do all this.
但是，您可以通过传递(并存储在单元格中)某种类型的句柄来实现您想要的东西，这是一个合法的单元格值(即。“myclass:实例:42”)。这可能就是您在编辑中链接的示例所做的。但是，您的代码必须能够解释句柄值的含义，并在内存中维护对象本身。如果你关心的不是泄漏对象，这可能会变得棘手，因为有很多方法可以删除或覆盖你无法检测到的句柄，如果你使用VBA来完成所有这些事情。
I don't have it in front of me right now, but you might want to look at the book Financial Applications using Excel Add-in Development in C/C++ by Steve Dalton:
我现在还没准备好，但是你可能想看看Steve Dalton写的关于使用Excel插件开发C/ c++的财务应用:
http://www.amazon.com/Financial-Applications-using-Development-Finance/dp/0470027975/ref=ntt_at_ep_dpt_1
He discusses ways to work with handles like this more robustly with XLL add-ins.
他讨论了使用XLL插件更健壮地处理此类处理的方法。
#2
1
This looks to be a tough cookie. It's a little hokey, but what you could do is have your Initialize function return a name (string), then add the name parameter to the Get function. Basically manipulating the name string instead of the object directly.
这看起来很难吃。它是一个小的hokey，但是您可以做的是让初始化函数返回一个名称(字符串)，然后将名称参数添加到Get函数中。基本上是直接操作名称字符串而不是对象。
#3
0
The nesting won't work because myvar goes out of scope as soon as the UDF is done executing. There actually may be other problems associated with trying to return an object in a worksheet function (most certainly there are), but even if there weren't the scope problem would still kill it.
嵌套不起作用，因为一旦UDF执行完毕，myvar就会超出范围。实际上，尝试在工作表函数中返回一个对象可能会有其他问题(当然肯定会有)，但是即使没有范围问题，仍然会导致对象死亡。
You could store a pointer to the object in a cell and get the object via that pointer, but again the scope will kill it. To get the object from the pointer, it would have to remain in scope, so why bother storing the pointer.
您可以在单元格中存储一个指向对象的指针，并通过该指针获取该对象，但是这个范围将会杀死它。要从指针中获取对象，它必须保持在作用域内，所以为什么要麻烦地存储指针呢?
Obviously your real life situation is more complex than your example. So the answer is no as to storing objects in cells, but if you explain what you're trying to accomplish there may be alternatives.
显然，你的现实生活比你的例子更复杂。所以答案是，不能把物体储存在细胞中，但是如果你解释你想要完成的事情，可能会有其他的选择。
#1
2
As the other answers suggest, the literal answer to your question is "no". You can't store anything other than a number, string, boolean, error, etc. in a cell. You can't return anything other than a simple value such as those, or an array, or a range reference, from a UDF.
正如其他答案所暗示的，你的问题的字面答案是“不”。在单元格中只能存储数字、字符串、布尔值、错误等。除了从UDF返回一个简单的值(比如这些值)、数组或范围引用之外，您不能返回其他任何东西。
However, you can do essentially what you want by passing around (and storing in cells) some kind of handle to your objects that is a legal cell value (i.e. "myclass:instance:42"). This is probably what the example you linked to in your edit does. Your code has to be able to interpret the meaning of the handle values and maintain the objects in memory itself, though. This can get tricky if you care about not leaking objects, since there are lots of ways to erase or overwrite handles that you can't detect if you're using VBA to do all this.
但是，您可以通过传递(并存储在单元格中)某种类型的句柄来实现您想要的东西，这是一个合法的单元格值(即。“myclass:实例:42”)。这可能就是您在编辑中链接的示例所做的。但是，您的代码必须能够解释句柄值的含义，并在内存中维护对象本身。如果你关心的不是泄漏对象，这可能会变得棘手，因为有很多方法可以删除或覆盖你无法检测到的句柄，如果你使用VBA来完成所有这些事情。
I don't have it in front of me right now, but you might want to look at the book Financial Applications using Excel Add-in Development in C/C++ by Steve Dalton:
我现在还没准备好，但是你可能想看看Steve Dalton写的关于使用Excel插件开发C/ c++的财务应用:
http://www.amazon.com/Financial-Applications-using-Development-Finance/dp/0470027975/ref=ntt_at_ep_dpt_1
He discusses ways to work with handles like this more robustly with XLL add-ins.
他讨论了使用XLL插件更健壮地处理此类处理的方法。
#2
1
This looks to be a tough cookie. It's a little hokey, but what you could do is have your Initialize function return a name (string), then add the name parameter to the Get function. Basically manipulating the name string instead of the object directly.
这看起来很难吃。它是一个小的hokey，但是您可以做的是让初始化函数返回一个名称(字符串)，然后将名称参数添加到Get函数中。基本上是直接操作名称字符串而不是对象。
#3
0
The nesting won't work because myvar goes out of scope as soon as the UDF is done executing. There actually may be other problems associated with trying to return an object in a worksheet function (most certainly there are), but even if there weren't the scope problem would still kill it.
嵌套不起作用，因为一旦UDF执行完毕，myvar就会超出范围。实际上，尝试在工作表函数中返回一个对象可能会有其他问题(当然肯定会有)，但是即使没有范围问题，仍然会导致对象死亡。
You could store a pointer to the object in a cell and get the object via that pointer, but again the scope will kill it. To get the object from the pointer, it would have to remain in scope, so why bother storing the pointer.
您可以在单元格中存储一个指向对象的指针，并通过该指针获取该对象，但是这个范围将会杀死它。要从指针中获取对象，它必须保持在作用域内，所以为什么要麻烦地存储指针呢?
Obviously your real life situation is more complex than your example. So the answer is no as to storing objects in cells, but if you explain what you're trying to accomplish there may be alternatives.
显然，你的现实生活比你的例子更复杂。所以答案是，不能把物体储存在细胞中，但是如果你解释你想要完成的事情，可能会有其他的选择。