[2005] Array Help

[thefarewellnote]

Good Morning all and Happy New Year!!

I am working with a developer on a large VB project. The programmer has backed out on me. The code is complete but when running it I recieve the following error "The index was outside the bounds of the array." I have researched this area and figured out it has something to do with the array now being declared as large as the data it is testing. Right now I am testing around 27,000 records.

I have attached the code that is causing the error as well as the error (screenshot) that I am getting. I would really appreciate any help in this mater for I am completely lost.

Module ExcelModule

    Public Mark_for_delete(10) As Integer
    Public ErrorEntries(10) As String
    Public Error_Header As String
    Public No_of_Errors As Integer = 0
    Public Path As String
    Public excel As excel.Application
    Public workbook As excel.Workbook
    Public worksheet As excel.Worksheet
    Public Sheets As Excel.Worksheets





Private Sub ProcessColoumn(ByVal name As String, ByVal coloumn As String, ByVal IDSfilename As String)
        Dim ID(10) As String
        Dim Val(10) As String
        Dim count As Integer
        'Open file
        Dim SR As System.IO.StreamReader = File.OpenText(IDSfilename)
        Dim SW As StreamWriter = File.AppendText("Error.txt")
        Dim Row As Integer = 1
        Dim ArraySize As Integer = 10
        'get values into array
        Dim s As String = SR.ReadLine()
        While s <> Nothing
            s = s.Trim()
            ID(Row) = s.Split(",")(0)
            ID(Row) = ID(Row).Replace(",", "")
            Val(Row) = s.Split(",")(1)
            Val(Row) = Val(Row).Replace(",", "")
            If ((Row + 1) = ArraySize) Then
                ArraySize = ArraySize + 10
                ReDim Preserve ID(ArraySize + 10)
                ReDim Preserve Val(ArraySize + 10)
            End If
            Row = Row + 1
            s = SR.ReadLine()
        End While
        count = Row - 1
        'close file
        SR.Close()

        Row = 2

        While getValue("A", Row.ToString()) <> "" And getValue("A", Row.ToString()) <> Nothing
            FormMain.LabelStatus.Text = "Status: " + "Processing row " + Row.ToString() + " in coloumn " + name
            Dim i As Integer = 1
            Dim flag As Boolean = False
            Dim temp As String = getValue(coloumn, Row.ToString())
            If (temp <> Nothing) Then
                temp = temp.Trim()
                While (i <= count And flag = False And temp <> "")
                    If (i >= 678) Then
                        Dim x = 0
                        Dim d As String = Val(i).ToUpper()
                    End If
                    If (Val(i).ToUpper() = getValue(coloumn, Row.ToString()).ToUpper().Trim()) Then
                        SetValue(coloumn, Row.ToString(), ID(i))
                        flag = True ' to break
                    End If
                    i = i + 1
                End While
                If (flag = False And temp <> "") Then ' write error into error.txt and mark this row for deletion
                    SW.WriteLine("Coloumn(" + name + "):" + getValue(coloumn, Row.ToString()) + " not found in :" + IDSfilename)
                    CopyThisRow_And_MarkforDelete(Row)
                End If
            End If
            Row = Row + 1
        End While
        SW.Close()
    End Sub
    Public Sub Clear_ErrorEntry_Arrays()
        System.Array.Clear(Mark_for_delete, 0, Mark_for_delete.Length)
        ReDim Mark_for_delete(10)
        No_of_Errors = 0
        System.Array.Clear(Mark_for_delete, 0, Mark_for_delete.Length)
        ReDim ErrorEntries(10)
        Error_Header = ""
    End Sub





Private Sub ProcessColoumn(ByVal name As String, ByVal coloumn As String, ByVal IDSfilename As String)
        Dim ID(10) As String
        Dim Val(10) As String
        Dim count As Integer
        'Open file
        Dim SR As System.IO.StreamReader = File.OpenText(IDSfilename)
        Dim SW As StreamWriter = File.AppendText("Error.txt")
        Dim Row As Integer = 1
        Dim ArraySize As Integer = 10
        'get values into array
        Dim s As String = SR.ReadLine()
        While s <> Nothing
            s = s.Trim()
            ID(Row) = s.Split(",")(0)
            ID(Row) = ID(Row).Replace(",", "")
            Val(Row) = s.Split(",")(1)
            Val(Row) = Val(Row).Replace(",", "")
            If ((Row + 1) = ArraySize) Then
                ArraySize = ArraySize + 10
                ReDim Preserve ID(ArraySize + 10)
                ReDim Preserve Val(ArraySize + 10)
            End If
            Row = Row + 1
            s = SR.ReadLine()
        End While
        count = Row - 1
        'close file
        SR.Close()

        Row = 2

        While getValue("A", Row.ToString()) <> "" And getValue("A", Row.ToString()) <> Nothing
            FormMain.LabelStatus.Text = "Status: " + "Processing row " + Row.ToString() + " in coloumn " + name
            Dim i As Integer = 1
            Dim flag As Boolean = False
            Dim temp As String = getValue(coloumn, Row.ToString())
            If (temp <> Nothing) Then
                temp = temp.Trim()
                While (i <= count And flag = False And temp <> "")
                    If (i >= 678) Then
                        Dim x = 0
                        Dim d As String = Val(i).ToUpper()
                    End If
                    If (Val(i).ToUpper() = getValue(coloumn, Row.ToString()).ToUpper().Trim()) Then
                        SetValue(coloumn, Row.ToString(), ID(i))
                        flag = True ' to break
                    End If
                    i = i + 1
                End While
                If (flag = False And temp <> "") Then ' write error into error.txt and mark this row for deletion
                    SW.WriteLine("Coloumn(" + name + "):" + getValue(coloumn, Row.ToString()) + " not found in :" + IDSfilename)
                    CopyThisRow_And_MarkforDelete(Row)
                End If
            End If
            Row = Row + 1
        End While
        SW.Close()
    End Sub

Public Sub CopyThisRow_And_MarkforDelete(ByVal Row As Integer)
        FormMain.LabelStatus.Text = "Status: " + "Row " + Row.ToString() + ":Error found - Marking It"
        FormMain.LabelStatus.Update()
        Dim r As Excel.Range = worksheet.Range("A" + Row.ToString())
        If (Row + 1 = Mark_for_delete.Length) Then
            ReDim Preserve Mark_for_delete(Mark_for_delete.Length + 10)
        End If
        If (Error_Header = "") Then
            Dim head As Excel.Range = worksheet.Range("A1")
            head.EntireRow.Copy()
            Error_Header = ""
            Error_Header = Clipboard.GetDataObject().GetData(DataFormats.Text, True)
            Clipboard.SetDataObject("")
        End If
        If (Mark_for_delete(Row) <> 1) Then
            r.EntireRow.Copy()
            No_of_Errors = No_of_Errors + 1
            If (No_of_Errors = ErrorEntries.Length) Then
                ReDim Preserve ErrorEntries(ErrorEntries.Length + 10)
            End If
            ErrorEntries(No_of_Errors) = Clipboard.GetDataObject.GetData(DataFormats.Text)
            Clipboard.SetDataObject("")
        End If
        Mark_for_delete(Row) = 1
    End Sub

It looks like the "Mark_for_delete" array has a maximum size of 10 values. "Row" is greater than 10, and that is where the error is appearing. I am not to familiar with arrays but I know I need to increase it to around 27,000 to get it to work. Any ideas what i need to change?

[Tom Sawyer]

It looks like the guy put a check for this problem earlier in the code, but didn't test it properly.

Change this code:

If (Row + 1 = Mark_for_delete.Length) Then
    ReDim Preserve Mark_for_delete(Mark_for_delete.Length + 10)
End If

to

If (Row  >= Mark_for_delete.Length  - 1) Then
    ReDim Preserve Mark_for_delete(Mark_for_delete.Length + 10)
End If

That way, everytime you come to the upper bound of your array, another ten spots will be created in it. Of course, if you know you're going to be processing 27,000 rows on a regular basis, you may want to change it to +27000 or something like that, because redim preserving an array a few thousand times is even more inefficient than having an array with 27,000 things in it in the first place.

[Shaggy Hiker]

Redimming 27k times wouldn't be all that efficient, either, unless you know up front that that is the number you need, and none other. A good compromise, which is used in lots of memory management schemes, is to double the size of the array each time you re-size it. This would mean

ReDim Preserve Mark_for_delete(Mark_for_delete.Length + 10)


would become

ReDim Preserve Mark_for_delete(2*Mark_for_delete.Length )

or slightly faster:

ReDim Preserve Mark_for_delete(Mark_for_delete.Length << 1)

but that last one you might need to test because:

1) I keep getting left and right shift backwards.
2) There may be some promotion going on there that would not be ideal.

[thefarewellnote]

Thanks for the help guys, I am going to test this today and will post back with the results. Would I need to do what Tom said as well as what Shaggy said or just what Shaggy suggested?

[obi1kenobi]

You need to follow Tom's advice if you want it to work at all, and Shaggy's advice is for performance only, so it's up to you to decide if you want to try it or not. But you must follow Tom's advice in order for the app to work properly.

[Tom Sawyer]

If you know how big the array is going to be, it's quicker to set that manually at the beginning, rather than redimming it everytime you hit the limit. If you do not know, then Shaggy's suggestion is the way to go, because that will limit the number of redims that you have to do.

So if you pull 27,000 records out of the database and then loop through and add them to the array, just dim the array with the number of records you pulled and you don't need to worry about resizing it at all. Since you're reading it from a file, though, it would probably be more trouble than it's worth to find the number of records before hand, so you should use Shaggy's way to resize it, as that's the most efficient.

[thefarewellnote]

Going from that I should have something along the lines of the following correct?

   If (Row >= Mark_for_delete.Length - 1) Then
            ReDim Preserve Mark_for_delete(2 * Mark_for_delete.Length)
        End If

[Shaggy Hiker]

Yeah, that'll do.

[thefarewellnote]

Yeah, that'll do.

Thanks Shaggy and everyone else. I am off to test it.

[thefarewellnote]

Just ran it with the changes mentioned above and the code still errored out on the same exact spot.

Under this procedure

  Public Sub Clear_ErrorEntry_Arrays()
        System.Array.Clear(Mark_for_delete, 0, Mark_for_delete.Length)
        ReDim Mark_for_delete(10)
        No_of_Errors = 0
        System.Array.Clear(Mark_for_delete, 0, Mark_for_delete.Length)
        ReDim ErrorEntries(10)
        Error_Header = ""
    End Sub

Should I increase the ReDim Mark_For_Delete to around 27k?

I also noticed this at the top of the code

    Public Mark_for_delete(10) As Integer
    Public ErrorEntries(10) As String

I did that earlier and it ran fine, very slow but fine. Getting a little nervous here - completely lost

[Shaggy Hiker]

Frankly, I don't see where that Clear_ErrorEntry_Arrays is getting called, but it shouldn't have any impact on the situation. Just to make sure that we are all talking about the same thing, how about posting the current version of that sub that is causing the error.

[Tom Sawyer]

Also, tell us what the values of Row and mark_for_delete.Length are when the error occurs.