vba 如何在VBA中清空数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21289970/
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
How to empty an array in VBA?
提问by stenci
I'm working on an Excel VBA addin that exchanges objects with a COM server, something like this:
我正在开发一个 Excel VBA 插件，它与 COM 服务器交换对象，如下所示：
'get an array of objects
Dim Ents() As ISomething
ComObject.GetEntities Ents
'send an array with 10 objects
ReDim Ents(9)
Set Ents(0) = ...
...
ComObject.SetEntities Ents
Getting the arrays works well: if the array contains objects it works as expected, if the array is empty then UBound(Ents) = -1and everything works as expected.
获取数组效果很好：如果数组包含对象，它会按预期工作，如果数组为空，则UBound(Ents) = -1一切都按预期工作。
Sending the arrays works only with not empty arrays, because I can't Redim Ents(-1), and Eraseing the array both VBA and the COM server crash: Debug.Print UBound(Ents)crashes in VBA and who knows what crashes the server.
发送数组仅适用于非空数组，因为我不能Redim Ents(-1)，并且EraseVBA 和 COM 服务器都崩溃了数组：Debug.Print UBound(Ents)VBA 崩溃了，谁知道是什么导致了服务器崩溃。
It looks like the Erasestatement leaves the array undefined/corrupted rather than empty.
看起来该Erase语句使数组未定义/损坏而不是空。
EDIT (clarification to a comment below):
编辑（对以下评论的澄清）：
Executing this code it crashes because it can't calculate the UBound:
执行此代码会崩溃，因为它无法计算UBound：
Sub Test()
Dim Ents() As ISmartId
Debug.Print UBound(Ents)
End Sub
But if you add Entsto the watch window, then set a break point to the Debug.Printline and execute, the debugger shows the ISmartId(0 to -1)in the Type column. After this the execution continues without crash, and the Debug window shows the expected -1.
但是，如果您添加Ents到监视窗口，然后在该Debug.Print行设置断点并执行，则调试器会ISmartId(0 to -1)在“类型”列中显示 。在此之后，执行继续而不会崩溃，并且调试窗口显示预期的-1.
It looks like the debugger was able to correctly initialize the empty array the way I need it just to show its value.
看起来调试器能够以我需要的方式正确初始化空数组，只是为了显示它的值。
采纳答案by Chel
For objects, you can do this just by copying an undefined array into a variant and back:
对于对象，您只需将未定义的数组复制到变体中并返回即可：
Dim o() As Worksheet
Dim v As Variant
v = o
o = v
For non-objects, make an empty array in a variant and then change its type code:
对于非对象，在变体中创建一个空数组，然后更改其类型代码：
Private Declare Sub GetMem2 Lib "msvbvm60" (src As Any, dest As Any)
Dim i() as Long
Dim v as Variant
v = Array()
Dim NewTypeCode As Integer
NewTypeCode = vbArray Or vbLong
GetMem2 NewTypeCode, v
i = v
回答by Tim Williams
If you need a fresh array you could create a "factory" function to return one
如果你需要一个新的数组，你可以创建一个“工厂”函数来返回一个
Function FreshArray() As ISomething()
Dim rv() As ISomething
FreshArray = rv
End Function
Ents = FreshArray()
ComObject.GetEntities Ents
