如何在VBA中清空数组？ [英] How to empty an array in VBA?

问题描述

我正在使用与COM服务器交换对象的Excel VBA插件，如下所示：

I'm working on an Excel VBA addin that exchanges objects with a COM server, something like this:

'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

获取数组的效果很好：如果数组包含的对象可以按预期工作，如果数组为空，则 UBound（Ents）= -1 一切正常

Getting the arrays works well: if the array contains objects it works as expected, if the array is empty then UBound(Ents) = -1 and everything works as expected.

发送数组仅适用于非空数组，因为我不能 Redim Ents（-1） ，并且 Erase 导致阵列VBA和COM服务器崩溃： Debug.Print UBound（Ents）在VBA中崩溃谁知道服务器崩溃的原因。

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.

看起来 Erase 语句使数组处于未定义/损坏状态，而不是

It looks like the Erase statement leaves the array undefined/corrupted rather than empty.

编辑（注释b的澄清） elow）：

EDIT (clarification to a comment below):

执行此代码会崩溃，因为它无法计算 UBound ：

Executing this code it crashes because it can't calculate the UBound:

Sub Test()
  Dim Ents() As ISmartId
  Debug.Print UBound(Ents)
End Sub

但是如果您在手表中添加 Ents 窗口，然后在 Debug.Print 行中设置一个断点并执行，调试器将显示 ISmartId（0到-1）在类型列中。之后，执行将继续而不会崩溃，并且调试窗口将显示预期的 -1 。

But if you add Ents to the watch window, then set a break point to the Debug.Print line 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.

调试器能够按照我需要的方式正确地初始化空数组，以显示其值。

It looks like the debugger was able to correctly initialize the empty array the way I need it just to show its value.

推荐答案

对于对象，您可以为此，只需将未定义的数组复制到变量中并返回：

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

这篇关于如何在VBA中清空数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！