Problem with Dynamic Array and listbox

[danbredy]

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

 

[Doug Robbins - Word MVP]

This group is for Word, not Excel, but check the .ListCount property of the
ListBox

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Crazy Cat]

This group is for Word, not Excel, but check the .ListCount property of the
ListBox

Sorry! I was using Google and clicked on the group too quickly without
noticing the 'word' in the title. Apologies.

Thanks,

<--- deleted to save bandwidth -->