Determining whether dynamic array has been used
 

Say I've got an array of user-defined variables. I don't know how many
"slots" I'm going to need, so I dimension it as a dynamic array.

Dim udtSheets() As MyDataType

When I want to add a new slot, I get the UBound.

ReDim Preserve udtSheets(UBound(udtSheets)+1)

However, the first time I try this, I get a "subscript out of range" error
because the zero-eth slot hasn't been done yet. I'm trying to find a VBA
function that can tell me whether that has happened yet. Something like

If HasBeenRedimmed(udtSheets) Then

ReDim Preserve udtSheets(UBound(udtSheets)+1)

Else

ReDim Preserve udtSheets(0)

End If


I tried "IsArray" and it returns True even if there have been no slots used.
For some reason I can't create my own function because Excel won't let me
pass a UDT array as a function parameter.

TIA.

--
Hmm...they have the Internet on COMPUTERS now!

Determining whether dynamic array has been used
 

Best way is to declare on very first Procedue Model using ReDim
Preserve udtSheets(0) and on subsequent use you should be able to
redefine

or

Using error handling:

on error resume NoArray

ReDim Preserve udtSheets(UBound(udtSheets)+1)


Noarray:
if err < 0 then ReDim Preserve udtSheets(0)

Determining whether dynamic array has been used
 

This seemed to work okay for me. It returns a user defined error code (2001)
if the argument is not an array, which you can remove if you like or do
something else.

Function IsDimmed(arrArg As Variant) As Variant
Dim lngUbound As Long

If IsArray(arrArg) Then
On Error Resume Next
lngUbound = UBound(arrArg)
IsDimmed = IIf(Err.Number, False, True)
Else: IsDimmed = CVErr(2001)
End If
End Function




"MDW" wrote:

Say I've got an array of user-defined variables. I don't know how many
"slots" I'm going to need, so I dimension it as a dynamic array.

Dim udtSheets() As MyDataType

When I want to add a new slot, I get the UBound.

ReDim Preserve udtSheets(UBound(udtSheets)+1)

However, the first time I try this, I get a "subscript out of range" error
because the zero-eth slot hasn't been done yet. I'm trying to find a VBA
function that can tell me whether that has happened yet. Something like

If HasBeenRedimmed(udtSheets) Then

ReDim Preserve udtSheets(UBound(udtSheets)+1)

Else

ReDim Preserve udtSheets(0)

End If


I tried "IsArray" and it returns True even if there have been no slots used.
For some reason I can't create my own function because Excel won't let me
pass a UDT array as a function parameter.

TIA.

--
Hmm...they have the Internet on COMPUTERS now!