Subscript out range error when redimensioning dynamic array
 

I am trying to move items between two listboxes and am using an array
to do it. Here is the code in question. I have no problem copying the
listbox contents to the dynamic arrays. The problem seems to be that
when one of the listboxes is empty and I copy it to my dynamic array
there is no problem -- however when I attempt to redim it I get a
subscript out of range (error '9') error. If the listbox has data when
I copy it to the dynamic array there is no problem -- what gives?


This is code in an Office 2003 macro for Excel.


Thanks much.


Dim source() As Variant
Dim destination() As Variant


Dim i As Integer
Dim j As Integer


ReDim Preserve source(0 To UBound(lstAvailableMSCs.Column, 1), 0)
ReDim Preserve destination(0 To UBound(lstAvailableMSCs.Column, 1),

0)


source = lstAvailableMSCs.Column
destination = lstSelectedMSCs.Column


For i = 0 To lstAvailableMSCs.ListCount - 1
If lstAvailableMSCs.Selected(i) Then
ReDim Preserve destination(0 To UBound(source, 1), 0 To
UBound(destination, 2) + 1)
' ReDim Preserve destination(0 To UBound(source, 1), 1)
For j = 0 To UBound(source, 1)
destination(j, UBound(destination, 2)) =
lstAvailableMSCs.Column(j, i)
Next j
Else
ReDim Preserve source(0 To UBound(source, 1), 0 To
UBound(source, 2) + 1)
For j = 0 To UBound(source, 1)
source(j, UBound(source, 2)) =
lstAvailableMSCs.Column(j, i)
Next j
End If
Next i


lstSelectedMSCs.Column = destination
lstAvailableMSCs.Column = source

Subscript out range error when redimensioning dynamic array
 

Replying to my own post -- I see where I screwed up -- can't do UBound
on an uninitialized array.


Crazy Cat wrote:
I am trying to move items between two listboxes and am using an array
to do it. Here is the code in question. I have no problem copying the
listbox contents to the dynamic arrays. The problem seems to be that
when one of the listboxes is empty and I copy it to my dynamic array
there is no problem -- however when I attempt to redim it I get a
subscript out of range (error '9') error. If the listbox has data when
I copy it to the dynamic array there is no problem -- what gives?


This is code in an Office 2003 macro for Excel.


Thanks much.


Dim source() As Variant
Dim destination() As Variant


Dim i As Integer
Dim j As Integer


ReDim Preserve source(0 To UBound(lstAvailableMSCs.Column, 1), 0)
ReDim Preserve destination(0 To UBound(lstAvailableMSCs.Column, 1),

0)


source = lstAvailableMSCs.Column
destination = lstSelectedMSCs.Column


For i = 0 To lstAvailableMSCs.ListCount - 1
If lstAvailableMSCs.Selected(i) Then
ReDim Preserve destination(0 To UBound(source, 1), 0 To
UBound(destination, 2) + 1)
' ReDim Preserve destination(0 To UBound(source, 1), 1)
For j = 0 To UBound(source, 1)
destination(j, UBound(destination, 2)) =
lstAvailableMSCs.Column(j, i)
Next j
Else
ReDim Preserve source(0 To UBound(source, 1), 0 To
UBound(source, 2) + 1)
For j = 0 To UBound(source, 1)
source(j, UBound(source, 2)) =
lstAvailableMSCs.Column(j, i)
Next j
End If
Next i


lstSelectedMSCs.Column = destination
lstAvailableMSCs.Column = source