Need help to speed up my search <Resolved>

[wbc12]

Hi

can anyone guru out there help me speed up this search from a textbox to search at MSHFlexGrid. Cause this search is very slow when it comes to 10,000 records. And a search is done from every keypress.

Private Sub Textbox1_KeyUp(KeyCode As Integer, Shift As Integer)
Call search(1, Textbox1, MSHFlexGrid1)
End Sub
 
Public Sub search(ByVal pCol As Integer, ByRef pObject As TextBox, ByRef Grid As Object)
Dim temp As Integer
If Grid.Rows > 1 Then
 
    Grid.ColHeader(0) = flexColHeaderOff
    Grid.Col = pCol
    Grid.Sort = 5
    Grid.ColHeader(0) = flexColHeaderOn
 
        For temp = 1 To Grid.Rows - 1
            If LCase(pObject) = LCase(Left(Grid.TextMatrix(temp, Grid.Col), Len(pObject.Text))) Then
                Grid.highlight = flexHighlightAlways
                Grid.Row = temp
                Grid.SelectionMode = flexSelectionByRow
                Grid.Col = 0
                Grid.ColSel = Grid.Cols - 1
                Grid.TopRow = Grid.Row
                Exit For
            Else
                Grid.highlight = flexHighlightWithFocus
            End If
 
        Next
 
End If
End Sub

[dee-u]

Will you try the ff...

Public Sub GridSearch(ByVal pCol As Integer, ByVal pString As String, ByRef Grid As Object)
    Dim lngTemp         As Long
    Dim lngLimit        As Long
    Dim intLength       As Integer
    
    If Grid.rows > 1 Then
 
        Grid.ColHeader(0) = flexColHeaderOff
        Grid.Col = pCol
        Grid.Sort = 5
        Grid.ColHeader(0) = flexColHeaderOn
        
        lngLimit = Grid.rows - 1
        pString = LCase$(pString)
        intLength = Len(pString)
        
        For lngTemp = 1 To lngLimit
            If pString = LCase$(Left$(Grid.TextMatrix(lngTemp, pCol), intLength)) Then
                Grid.Highlight = flexHighlightAlways
                Grid.Row = lngTemp
                Grid.SelectionMode = flexSelectionByRow
                Grid.Col = 0
                Grid.ColSel = Grid.cols - 1
                Grid.TopRow = Grid.Row
                Exit For
            Else
                Grid.Highlight = flexHighlightWithFocus
            End If
        Next
    End If
End Sub

[wbc12]

hi dee-u

thanks for the code. Sorry but the searching is still quite slow when the number of character type increases. Maybe you could give me some idea. This is what i really need. I want to do a search function which will scan through a column of the MSHFlexGrid which consist of over 10,000+ record and the total lenght of a column record is around 100 characters for every character is it type on the textbox.

For example when i type "A" it will search through the record and search for any record that starts with A. Then when i type "Ap", it will again search through the MSHFlexgrid.

The solution i posted is the only way i know. But it is slow when i type very fast and also when the number of characters increase.

Hope you could give me some sample code ideas..

Thanks

[da_silvy]

If you created an array that stored the positions of every record starting with A, B etc would that speed up your search?

Or you could store the locations of all the cells that started with A, then when the next letter's typed in, you only search those cells. You'd need to check that the new search string is just the old one, with another character appended, or else it'll stuff up if they insert characters before the current one. i.e. Ap -> Asp or some such

if left(newstring, len(oldstring)) = oldstring
' only search the stored cells
end if

and you could possibly store the cells in a multidimensional array
search(0,1) = 1
search(0,2) = 5
-> first cell is at 1,5!

Just something for you to consider

[wbc12]

hi da_silvy,

Thank you for your input, if u ask me to store the information in multi dimension array, first thing how will i know from where to where the item is starting with "A" and even if i do wouldn't it be slower than because i have to scan through the array and then look at the grid??

Isn't it the same as u scan through the grid then in the array??. I'm not sure..

Anyway i'm not really good in doing multi dimensional array. if u know how to make it faster then by all means please show me how..

Thanks.

[da_silvy]

I meant something like this:

Dim iSearch(10, 2) As Integer
Dim strSearch(10) As String
Dim lastSearch As String
Public Sub Search()
    pString = "chie"
    Dim iTmp(10, 2) As Integer
    Dim strTmp(10) As String
    Dim iLimit As Integer
    Dim iCol As Integer
    Dim iLength As Integer
    Dim j As Integer
    
    iLimit = 10
    j = 0
    iCol = 0
     
    iLength = Len(pString)
    If lastSearch = "" Then
        lastSearch = pString
    Else
        If Left(pString, Len(lastSearch)) = lastSearch Then
            ' We can use our old results
            For i = 0 To UBound(strSearch)
                 If LCase(Left(strSearch(i), iLength)) = pString Then
                    Debug.Print strSearch(i)
                    iTmp(j, 0) = iCol
                    iTmp(j, 1) = i
                    strTmp(j) = strSearch(i)
                End If
            Next
            ' You'll need to copy these over
            'strSearch = strTmp
            'iSearch = iTmp
            lastSearch = pString
            Exit Sub
        End If
    End If
    
    lastSearch = pString
    For i = 1 To iLimit
        If LCase(Left(Sheet1.Range("A" & i).Value, iLength)) = pString Then
            Debug.Print Sheet1.Range("A" & i).Value
            iSearch(j, 0) = iCol
            iSearch(j, 1) = i
            strSearch(j) = Sheet1.Range("A" & i).Value
            j = j + 1
        End If
    Next
    
End Sub

I'm not sure how to just copy over the arrays, maybe CopyMemory ??

I don't have VB, so that was done in VBA, hopefully you get the gyst and can alter it to suit!

[dee-u]

How about the ff...

Option Compare Text
 
Private Sub Text1_Change()
    GridSearch 0, Text1.Text, mshfStatus
End Sub
 
Public Sub GridSearch(ByVal pCol As Integer, ByVal pString As String, ByRef Grid As Object)
    Dim lngTemp     As Long
    Dim lngLimit    As Long
    
    If Grid.rows > 1 Then
 
        Grid.ColHeader(0) = flexColHeaderOff
        Grid.Col = pCol
        Grid.Sort = 5
        Grid.ColHeader(0) = flexColHeaderOn
        Grid.SelectionMode = flexSelectionByRow
        Grid.Highlight = flexHighlightAlways
        
        lngLimit = Grid.rows - 1
        pString = LCase$(pString) & "*"
        
        For lngTemp = 1 To lngLimit
            'If pString = LCase$(Left$(Grid.TextMatrix(lngTemp, pCol), intLength)) Then
            'If LCase$(Grid.TextMatrix(lngTemp, pCol)) Like pString Then
            If Grid.TextMatrix(lngTemp, pCol) Like pString Then
                Grid.Row = lngTemp
                Grid.TopRow = lngTemp
                Exit For
            End If
        Next
    End If
End Sub

[eimroda]

or use ListView instead, i guess? ...

[wbc12]

Hi dee-u,

Thank you so much for your input. It works..

[dee-u]

Is the speed difference significant?