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.
Code:
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?
[2005] Array Help
Reply With Quote