5

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.

stenci
  • 8,290
  • 14
  • 64
  • 104
  • 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: http://stackoverflow.com/questions/206324/how-to-check-for-empty-array-in-vba-macro – RBarryYoung Jan 22 '14 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 Jan 22 '14 at 17:57
  • 1
    "*Does this help your fantasy?*" Huh? Sorry, I don't follow..? – RBarryYoung Jan 22 '14 at 18:04
  • Sorry, I meant remind of a way to do the job or gives you an idea for a new trick. – stenci Jan 22 '14 at 18:43

2 Answers2

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
Chel
  • 2,593
  • 1
  • 18
  • 24
  • 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? – stenci Jan 22 '14 at 18:32
  • 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 Jan 22 '14 at 18:41
  • 1
    @stenci Okay, I've updated my answer with a much more straightforward solution for early-bound objects. – Chel Jan 22 '14 at 19:13
  • Now I can create a factory for all my arrays. Thanks! – stenci Jan 22 '14 at 19:17
  • 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! https://stackoverflow.com/questions/48794596/creating-an-empty-array-in-vba – stenci Feb 14 '18 at 19:15
-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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    This works like `Erase`: creates a corrupted/undefined array. It crashes reading `UBound(FreshArray)`. – stenci Jan 22 '14 at 18:18