How to empty an array in VBA?

Question

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

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.

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.

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

EDIT (clarification to a comment below):

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

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

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.

I don't think that you can. AFAIK, you can only Erase them and then catch the errors when you try to test their size. Se here: stackoverflow.com/questions/206324/… — RBarryYoung
– RBarryYoung, Commented Jan 22, 2014 at 17:48
@RBarryYoung: I can do the check on the VBA side, but the COM server is a 3rd party application, and they just expect an empty array. I just found a workaround that works only with string arrays: Split(""). I need a generic solution for IAnything. Does this help your fantasy? — stenci
– stenci, Commented Jan 22, 2014 at 17:57
"Does this help your fantasy?" Huh? Sorry, I don't follow..? — RBarryYoung
– RBarryYoung, Commented Jan 22, 2014 at 18:04
Sorry, I meant remind of a way to do the job or gives you an idea for a new trick. — stenci
– stenci, Commented Jan 22, 2014 at 18:43

Chel · Accepted Answer · 2014-01-22 20:00:18Z

4

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

edited Jan 22, 2014 at 20:00

answered Jan 22, 2014 at 18:21

Chel

2,6231 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stenci Over a year ago

I accepted this answer because it's very close to what I need, even if it doesn't give me the type I want. I noticed that if I have the array in the watch window and a breakpoint after the Erase, VBA will initialize the array to an empty array, and at that point I can continue the execution and it works well. Do you know what happens under the hood of that watch window?

Chel Over a year ago

Could you post the code you're running, and where the breakpoint is? I'm having trouble understanding what you mean when you're talking about an Erase statement.

Chel Over a year ago

@stenci Okay, I've updated my answer with a much more straightforward solution for early-bound objects.

stenci Over a year ago

Now I can create a factory for all my arrays. Thanks!

stenci Over a year ago

I am having problems with this solution. I posted another question, I would appreciate if you could look at it and find a solution. Thanks! stackoverflow.com/questions/48794596/…

Tim Williams · Accepted Answer · 2014-01-22 18:04:30Z

-1

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

answered Jan 22, 2014 at 18:04

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

stenci Over a year ago

This works like Erase: creates a corrupted/undefined array. It crashes reading UBound(FreshArray).

Collectives™ on Stack Overflow

How to empty an array in VBA?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related