Can I test dynamic array for empty?

[LabElf]

I'm reading Excel files with a Visual Basic 6.0 program. In several cases,
I'm building dynamic arrays with the elements I find. I would like an
elegant way to determing if anything has been put into my dynamic array yet.

Before the array has been ReDim-ed the first type, attempts to use LBound or
UBound give subscript out of range errors.
The debugger shows me "arrayName = Nothing" before the array is ReDim-ed.
Attempting to test arrayName = Nothing gives "Invalid Use of Object" error.
Attempting to test arrayName Is Nothing gives "Type Mismatch" error.

I've been working around this by Redimensioning the array as (0 To 0) until
the first use, then Redimensioning as (1 to 1) for the first element, (1 to
2) next, etc., but this is clunky and using 1-based subscripts isn't portable
to VB.Net, which I may do in the future. I don't know an easy way of
determining if a (0 to 0) array is empty or contains one element, without
using some dummy value.

Does anyone know a better way?

 

[Alan Beban]

Consider Application.Counta(arrayName)

Alan Beban

 

[Myrna Larson]

What about using error trapping?

On Error Resume Next
X = ArrayName(0)
If Err.Number <> 0 Then
'it's not dim'd
End If
On Error Goto 0

PS: Nothing is used only with object variables, not "regular" variables and
arrays.

 

[LabElf]

Alan -
Thank you for your help. Unfortunately, the CountA function gave me
"Compile Error: Only user-defined types defined in public object modules can
be coerced to or from a variant or passed to late-bound functions". This
error probably occurs because my array contains elements of a type I defined;
the method might work for my other arrays. Can someone point me toward more
information on public object modules? I couldn't find much enlightenment in
the online VB help.

 

[LabElf]

Myrna -
Thank you, that worked for my situation. I'm only using the array once. I
don't know if there's a way to completely clear the array so that it would be
undefined again, if I wanted to use it over.

 

[Dave Peterson]

Take a look at Erase in VBA's help.

Myrna -
Thank you, that worked for my situation. I'm only using the array once. I
don't know if there's a way to completely clear the array so that it would be
undefined again, if I wanted to use it over.

 

[LabElf]

Ah, thank you. That fills in the blanks. (I *thought* I remembered seeing
something along those lines before...)