Redim Preserve again.....

[Al]

Hey folks,

The following code relates to a listbox which is populated from an array
that has 9 rows and as many columns as items in the listbox.

When a user selected and item in the listbox and hits delete it also needs
to be deleted from the corresponding array. The following codes moves the
selected array column to the last element so it can be deleted in a Re Dim
preserve. (It has to be a column because you can only delete the last
element.)

For some reason if their is only one row in the list box, and hence one
column in the array I get a
Subscript out of range error (runtime error 9)

The codes breaks on line and again on the second instance of the line.

ReDim Preserve OffenderArray(9, NameCount - 2) 'This will flush the last
record


I guess its something to do with trying to delete the only row in the array?

The full code is below if anyone can help. Thanks!!

-Al

Sub DeleteColumnOffenderArray()

NameCount = ListBox2.ListCount ' Counts names
NametoDelete = ListBox2.ListIndex ' Selected Name
'the array size is OffenderArray(NameCount - 1,9)
'redefine the array
If NametoDelete = NameCount - 1 Then 'This means the user wants
to delete the last record
ReDim Preserve OffenderArray(9, NameCount - 2) 'This will
flush the last record
ListBox2.Column() = OffenderArray ' This updates listbox2
from new array
Else
For i = 0 To NameCount - NametoDelete - 2
For j = 0 To 9
OffenderArray(j, NametoDelete + i) =
OffenderArray(j, NametoDelete + i + 1)
Next j
Next i
End If


ReDim Preserve OffenderArray(9, NameCount - 2) ' Deletes Last Column in
Array
ListBox2.Column() = OffenderArray ' This updates listbox2 from new array

End Sub

 

[Al]

Oh and I also get an error when I run;

ListBox2.Column() = OffenderArray ' This updates listbox2 from new array

I get error 380.

I guess I need to set the listbox to blank - no items.

-AL

 

[Peter Hewett]

Hi Al

Looks like the second dimension of your array has gone negative to me.
Just to clarify things we're dealing with a zero based array here, so the
declaration:
Dim aintItem(5,5) As Integer
is really:
Dim aintItem(0 to 5,0 to 5) As Integer

When using arrays I ALWAYS declare the lower boundary to make it absolutely
obvious exactly what we're dealing with!

Anyway the problem seems to be:
ReDim Preserve OffenderArray(9, NameCount - 2)

when you only have 1 item in your ListBox Namecount has a value of 1,
so 1 - 2 = -1. Which of course is a negative array subscript reference!

HTH + Cheers - Peter

 

[Tim Ferguson]

The following code relates to a listbox which is populated from an array
that has 9 rows and as many columns as items in the listbox.

Can you not use a collection?

Tim F

 

[Al]

Peter,

Your right. It does reference a negative number, but if the array was (9,0)
for example,
because the numbering of the arrays starts at 0 that means their is one
element left in the array.

So I guess the question is how to flush the array?

I was thinking clearing it with a "Dim OffenderArray() but now I get an
error on line Listbox2.Column = OffenderArray()

Arrrrghhh.....

-Al

 

[Al]

I am clearing the listbox now with;

listbox2.removeitem(listbox2.listindex)

but am getting an error on this line now, (which is equal to
offenderarray(0,1) = offenderarray(0,2));

OffenderArray(j, NametoDelete + i) = OffenderArray(j, NametoDelete + i + 1)

Subscript out of range!!!!

The weird thing is that if I take out the first if clause (where case of
last & only row) then the code works. How is the first if clause affecting
the rest of the code when its not being invoked?????

Thanks in advance,

-Al

Sub DeleteColumnOffenderArray()

NameCount = ListBox2.ListCount ' Counts names
NametoDelete = ListBox2.ListIndex ' Selected Name
'the array size is OffenderArray(NameCount - 1,9)
'redefine the array
If NameCount - 1 = 0 Then ' This means the user wants to delete
last & only row
Dim OffenderArray()
ListBox2.RemoveItem (ListBox2.ListIndex) ' This updates
listbox2 from new array
MsgBox "Last & Only"
ElseIf NametoDelete = NameCount - 1 Then 'This means the user
wants to delete the last record
ReDim Preserve OffenderArray(9, NameCount - 2) 'This will
flush the last record
ListBox2.Column() = OffenderArray ' This updates listbox2
from new array
MsgBox "Last Record"
Else
For i = 0 To NameCount - NametoDelete - 2
For j = 0 To 9
OffenderArray(j, NametoDelete + i) =
OffenderArray(j, NametoDelete + i + 1)
Next j
Next i
ReDim Preserve OffenderArray(9, NameCount - 2) ' Deletes
Last Column (Defendant) in Array
ListBox2.Column() = OffenderArray ' This updates listbox2
from new array
End If
End Sub

 

[Al]

Umm, this seems to work now......but will happily take any comments;

Sub DeleteColumnOffenderArray()

NameCount = ListBox2.ListCount ' Counts names
NametoDelete = ListBox2.ListIndex ' Selected Name
'the array size is OffenderArray(NameCount - 1,9)
'redefine the array
If NameCount = 1 Then
ReDim OffenderArray(0, 0)
ListBox2.RemoveItem (ListBox2.ListIndex)
MsgBox "Case last & only"
ElseIf NametoDelete = NameCount - 1 Then 'This means the user
wants to delete the last record
ReDim Preserve OffenderArray(9, NameCount - 2) 'This will
flush the last record
ListBox2.Column() = OffenderArray ' This updates listbox2
from new array
MsgBox "Last Record"
Else
For i = 0 To NameCount - NametoDelete - 2
For j = 0 To 9
OffenderArray(j, NametoDelete + i) =
OffenderArray(j, NametoDelete + i + 1)
Next j
Next i
ReDim Preserve OffenderArray(9, NameCount - 2) ' Deletes
Last Column (Defendant) in Array
ListBox2.Column() = OffenderArray ' This updates listbox2
from new array
End If
End Sub