在Excel单元格中返回用户定义的数据类型 [英] Return a User Defined Data Type in an Excel Cell

问题描述

我已经搜索过网络，我在stackoverflow上搜索了这些问题，但是我无法找到解决方案。

这是什么我想做：

假设我在一个名为MyClass的类模块中有以下代码

  
 Option Explicit 
 Dim var1 as integer 
 
 
 
 Sub Initialize（v as integer）
 var1 = v 
 End Sub 
 
 
 
函数GetVar1（）
 GetVar1 = var1 
结束函数
 
  
 
 
 
然后我在一个单独的模块中有一个UDF，代码为
 
 code> 
函数InitializeMyClass（v作为整数）作为MyClass 
 Dim myvar作为MyClass 
设置myvar = new MyClass 
调用myvar.Initialize（v）
设置InitializeMyClass = myvar 
 End Function 
 
 
 
函数GetMyVar（m作为MyClass）
 GetMyVar = m.GetVar1（）
 End Function 
 
  
 
 
 
现在在单元格A1中有= InitializeMyClass（3），在单元格A2中，我有= GetMyVar（A1）。我在两个单元格中都得到#VALUE错误。这当然是由于我试图将用户定义的数据类型返回到单元格A1。我觉得这应该是可能的，但我不知道如何。 
 
 
 
编辑：哦，是的，问题是，有没有办法让我将用户定义的数据类型返回到单元格，然后可以从另一个单元中调用在上面我给出的例子中UDF我不知道这是否需要COM，如果有的话，任何人都知道我如何开始？理想情况下，如果有人说明了这个工作的例子，那将是太棒了！另一个编辑：我们现在去，现在我知道可以做到这一点：阅读这个描述，它不是量化的，但会给你一个感觉，他们做什么，
 
 
 < a href =http://www.quanttools.com/index.php?option=com_content&task=view&id=19 =nofollow noreferrer> http://www.quanttools.com/index.php ？option = com_content& task = view& id = 19  
解决方案
正如其他答案所示，你的问题是不。您不能在单元格中存储数字，字符串，布尔，错误等数字以外的任何内容。您不能从UDF返回除简单值（例如数组或数组）或范围引用之外的任何内容。
 
 
 
但是，您可以通过传递（并存储在单元格中）来实现您想要的某种句柄，这些句柄是合法单元格值的对象（即myclass：instance：42）。这可能是您在编辑中链接到的示例。您的代码必须能够解释句柄值的含义，并将对象维护在内存中。如果您关心不泄漏的对象，这可能会变得棘手，因为有许多方法可以擦除或覆盖您无法使用VBA执行所有操作的句柄。
 
 
 
我现在没有在我面前，但是您可能想看看Steve Dalton在C / C ++中使用Excel加载项开发的图书财务应用程序：
 
 
 
  http://www.amazon.com/Financial-Applications- using-Development-Finance / dp / 0470027975 / ref = ntt_at_ep_dpt_1  
 
 
 
他讨论如何使用XLL加载项更强大地处理这样的句柄。 / p> 
I've searched the web and I've searched the questions here on stackoverflow, 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"


Option Explicit
Dim var1 as integer


Sub Initialize(v as integer)
   var1 = v
End Sub

Function GetVar1()
   GetVar1 = var1
End Function


Then I have a UDF in a separate module with the code
 
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


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. 

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!"

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
解决方案
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.

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.

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:

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.

                        
这篇关于在Excel单元格中返回用户定义的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！