VBA - 从属性获取返回数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5613564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Returning array from Property Get
提问by ThomasMcLeod
If arrays are returned by reference, why doesn't the following work:
如果数组是通过引用返回的，为什么以下不起作用：
'Class1 class module
Private v() As Double
Public Property Get Vec() As Double()
Vec = v()
End Property
Private Sub Class_Initialize()
ReDim v(0 To 3)
End Sub
' end class module
Sub Test1()
Dim c As Class1
Set c = New Class1
Debug.Print c.Vec()(1) ' prints 0 as expected
c.Vec()(1) = 5.6
Debug.Print c.Vec()(1) ' still prints 0
End Sub
采纳答案by Steve Jorgensen
In VBA, arrays are never returned by reference unless they are returned through a ByRefparameter. Furthermore, whenever you use =to assign an array to a variable, you've made a new copy of the array, even if you're assigning it to a ByRef argument inside of a procedure, so you're pretty much out of luck trying to make this work.
在 VBA 中，除非通过ByRef参数返回数组，否则永远不会通过引用返回数组。此外，每当您=将数组分配给变量时，您都会创建该数组的新副本，即使您将其分配给过程内的 ByRef 参数，因此您在尝试时几乎不走运使这项工作。
Some alternative are...
一些替代方案是...
- Use a VBA.Collection instead of an array.
- Make your own class that encapsulates an array and exposes procedures for indirectly accessing and manipulating the internal array.
- 使用 VBA.Collection 而不是数组。
- 创建您自己的类来封装数组并公开间接访问和操作内部数组的过程。
回答by Clayton S
You don't have a let property. Also, the get property is returning the entire array, rather than just the element in question. Change the return type of Property Get from Double() to just plain Double. Add Property Let. Note that it takes two inputs, but only one is passed to it. The last variable (MyValue, in this case) is assumed to get it's value from whatever is after the = sign. Put a break point somewhere early in Test1() and see how the values are affected in the Locals window. Compare the variables created by the original code versus my code:
你没有出租物业。此外， get 属性返回整个数组，而不仅仅是有问题的元素。将 Property Get 的返回类型从 Double() 更改为简单的 Double。添加属性让。请注意，它需要两个输入，但只有一个被传递给它。假定最后一个变量（在本例中为 MyValue）从 = 符号之后的任何内容中获取它的值。在 Test1() 早期的某个地方放置一个断点，然后在 Locals 窗口中查看这些值是如何受到影响的。比较原始代码和我的代码创建的变量：
'Class1 class module
Private v() As Double
Public Property Get Vec(index As Long) As Double
Vec = v(index)
End Property
Public Property Let Vec(index As Long, MyValue As Double)
v(index) = MyValue
End Property
Private Sub Class_Initialize()
ReDim v(0 To 3)
End Sub
' end class module
'Begin module
Sub Test1()
Dim c As Class1
Set c = New Class1
Debug.Print c.Vec(1) ' prints 0 as expected
c.Vec(1) = 5.6
Debug.Print c.Vec(1) ' prints 5.6
End Sub
'End module
回答by Andreas Dietrich
I want to suggest another nice way to do this using a Collectionand a static Propertywithout the need to use a class:
我想建议另一种使用 aCollection和 astatic Property而不需要使用类的好方法：
imagine you want to have the xlCVErrorenum as an array (or collection), e.g. to loop through it on errors and handle it based on the actual error.
想象一下，您希望将xlCVError枚举作为数组（或集合），例如在错误时循环遍历它并根据实际错误处理它。
The following is initialized once on access:
以下在访问时初始化一次：
'from https://stackoverflow.com/a/56646199/1915920
Static Property Get XlCVErrorColl() As Collection
Dim c As Collection 'will be already initalized after 1st access
'because of "Static Property" above!
Set XlCVErrorColl = c
If Not c Is Nothing Then Exit Property
'initialize once:
Set c = New Collection
c.Add XlCVError.xlErrDiv0
c.Add XlCVError.xlErrNA
c.Add XlCVError.xlErrName
c.Add XlCVError.xlErrNull
c.Add XlCVError.xlErrNum
c.Add XlCVError.xlErrRef
c.Add XlCVError.xlErrValue
Set XlCVErrorColl = c
End Property
Turning this into an array or implementing it as an array is straight forward, but collections seem to be more useful to me, with the disadvantage that their elements are not implicitely typed/(compile-time-)type checked.
So this would e.g. turn it into an (read-only) array(with the in-mem-copy-disadvantage mentioned in other answers/comments):
将其转换为数组或将其实现为数组很简单，但集合对我来说似乎更有用，缺点是它们的元素没有隐式类型/（编译时）类型检查。
因此，这将例如将其转换为（只读）数组（在其他答案/评论中提到了 in-mem-copy-disadvantage）：
'from https://stackoverflow.com/a/56646199/1915920
Static Property Get XlCVErrorArr() As XlCVError()
Dim a() As XlCVError
XlCVErrorArr = a
If UBound( a ) > 0 Then Exit Property
'initialize once:
Dim c As Collection: Set c = XlCVErrorColl
ReDim a(c.Count)
Dim i As Integer: For i = 1 To c.Count
a(i) = c(i)
Next i
XlCVErrorArr = a
End Function
So transforming the example from Clayton Ss answerinto a static, modifiable module property using some arrayit would be:
因此，使用某个数组将Clayton S答案中的示例转换为静态的、可修改的模块属性，它将是：
'module (no class required)
'from https://stackoverflow.com/a/56646199/1915920
Private v() As Double
Static Property Get Vec(index As Long) As Double
If UBound(v) < 3 Then 'initialize once:
ReDim v(0 To 3) 'one could initialize it with anyting after here too
end if
Vec = v(index)
End Property
Public Property Let Vec(index As Long, MyValue As Double)
v(index) = MyValue
End Property
