none
Need to improve the performance of a loop- vb.net RRS feed

  • Question

  • Hello All,

     

    PFB the code for a search button where when a user enters a text in the text box ,it takes the keyword compares it with an xls which has 1600 rows and 16 columns with keywords.

    If the keywords match, it has to full the 3rd column where the name is specified.

    Its running but its taking more than 20-25 mins for processing.

    I knw searching 1600 rows and 16 columns will take time but pls advice if i can make this process a lil faster.

    Pls Advice

    Abi

     

    Private Sub cmdScriptsSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdScriptsSearch.Click

     

    'Declare variables locally to search for scripts in the Excel

     

     

    Dim vSimpleCount, vComplexCount, vMediumCount As Integer

     

    Dim vtemp As String

    vtemp =

    ""

     

    Dim iRowIndex, iColumnIndex, iSearchText As Integer

     

    Dim xlBook As Object

    frmAssesment.Label2.Visible =

    False

    frmAssesment.txtBScriptsCount.Visible =

    False

     

    'Store the Script Excel path in the Objext

    xlBook = GetObject(

    "C:\Documents and Settings\WAW165\Desktop\Impact assessment sheet1.xls")

     

    'xlBook.Application.Visible = False

     

    'xlBook.Windows(1).Visible = False

    xlBook.Application.Windowstate = Excel.XlWindowState.xlMinimized

     

    ' Dim a, b As String

    vSimpleCount = 0

    vMediumCount = 0

    vComplexCount = 0

     

    'Label3.Visible = True

    ProgressBar1.Visible =

    True

    Button2.Enabled =

    False

    cmdScriptsSearch.Enabled =

    False

     

     

    'Search for Scripts in the Excel

     

    For iRowIndex = 3 To 1717

    ProgressBar1.Value = (iRowIndex / 1717) * 100

     

    For iColumnIndex = 6 To 16

     

    For iSearchText = 1 To 30

     

    'a = xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value

     

    'b = avFoundKeywordds(iSearchText)

     

    If ((CStr(xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value)) <> "") Then

     

    'Searching for the Keyword in the Excel

     

    If ((xlBook.Worksheets(2).Cells(iRowIndex, iColumnIndex).Value)) = avFoundKeywordds(iSearchText) Then

     

    'If (a = b) Then

     

    If (vtemp <> (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)) Then

     

    If ((xlBook.Worksheets(2).Cells(iRowIndex, 16).Value) = "Simple") Then

    vtemp = (xlBook.Worksheets(2).Cells(iRowIndex, 3).Value)

     

    'MsgBox(vtemp)

    TextBox1.Text = vtemp

     

    'Storing the Simple Scripts

     

    ' txtDisplay.Text = txtDisplay.Text & xlBook.Worksheets(2).Cells(iRowIndex, 3) & "Simple"

    TextBox2.Text = TextBox2.Text + TextBox1.Text +

    "--Priority-Simple" + vbNewLine

     

    'Increment the Simple Scripts Count

    vSimpleCount = vSimpleCount + 1

     

    End If

     

    If ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) = "Medium") Then

    vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)

    TextBox1.Text = vtemp

     

    'Storing the Medium Scripts

     

    ' txtDisplay.Text = txtDisplay.Text & xlBook.Worksheets(2).Cells(iRowIndex, 3) + "Medium"

    TextBox2.Text = TextBox2.Text + TextBox1.Text +

    "--Priority-Medium" + vbNewLine

     

    'Increment the Medium Scripts Count

    vMediumCount = vMediumCount + 1

     

    End If

     

    If ((xlBook.Worksheets(1).Cells(iRowIndex, 16).Value) = "Complex") Then

    vtemp = (xlBook.Worksheets(1).Cells(iRowIndex, 3).Value)

    TextBox1.Text = vtemp

     

    'Storing the Complex Scripts

     

    ' txtDisplay.Text = txtDisplay.Text & xlBook.Worksheets(2).Cells(iRowIndex, 3) + "High"

    TextBox2.Text = TextBox2.Text + TextBox1.Text +

    "--Priority-Complex" + vbNewLine

     

    'Increment the Complex Scripts Count

    vComplexCount = vComplexCount + 1

     

    End If

     

    End If

     

    End If

     

    End If

     

    Next iSearchText

     

    Next iColumnIndex

     

    Next iRowIndex

     

    'Store the Total no of Scripts

    txtScriptsCount.Text = vComplexCount + vMediumCount + vSimpleCount

    frmAssesment.txtScriptsCount.Text = txtScriptsCount.Text

    frmAssesment.txtBScriptsCount.Text = txtScriptsCount.Text

    txtSimple.Text = vSimpleCount

    txtMedium.Text = vMediumCount

    txtComplex.Text = vComplexCount

    frmAssesment.Show()

    frmAssesment.Label2.Visible =

    False

    frmAssesment.txtBScriptsCount.Visible =

    False

     

    Me.Hide()

     

    End Sub

    End

    Class

    Monday, August 15, 2011 3:24 PM

All replies

  • Hi Abi

    As you do not appear to be using the VSTO technology - this looks like a WinForms app? - it might be a good idea to move your question to the Excel for Developers forum where you'll find a lot more application specialists who might have some ideas how to improve your code.

    It will help everyone to know which version of Excel you're targeting. Especially since you appear to not be interacting with the user in the Excel interface, but in your WinForm(?). It doesn't appear that these are Office Open XML files, which would mean you could access them much more efficiently, but...

    Please let me know if you want me to move your message to the specialized Excel forum.


    Cindy Meister, VSTO/Word MVP
    Tuesday, August 16, 2011 9:59 AM
    Moderator