[RESOLVED] Need Advice

[RS_Arm]

Hi.
I've got a function to search data in a MSHFlexGrid.
Here goes the code:

Option Explicit
Sub Grid(SearchText As String, objMSHFGrid As MSHFlexGrid)
Dim r As Integer, c As Integer, x As Integer
Dim str1 As String, str2 As String
Dim exist As Long
With objMSHFGrid
  
  x = Len(SearchText )
  For r = 1 To .Rows - 1
    For c = 0 To .Cols - 1
        str1 = LCase(.TextMatrix(r, c))
        str2 = LCase(SearchText )
        exist = InStr(1, str1, str2, vbTextCompare)
        If exist <> 0 Then 
            .Row = r     
            .Col = c
            .TopRow = r
            Exit Sub  
        End If
    Next c
  Next r


End With
End Sub

Now, what I want to do is a "Find Next"
How should I do it?

[Fazi]

Hai RS_Arm

Start you loop like this

Create a Private integer variable name Raw_Num and Assign 0

For r = Raw_Num To .Rows - 1

When you find the first result, just assign the current raw number +1 (+1 mean next raw) to the Raw_Num variable and put exit sub.

When the user clicks the 'Find Next' button just point him to the same function. so the loop starts with the next raw.

[Ellis Dee]

Quick optimization tips to make the search faster:

• Change the two LCase() calls to LCase$().
• Change vbTextCompare to vbBinaryCompare. (Don't need a text comparison because of the LCase$() calls.)
• I've heard that Longs process faster than Ints, but I've not tested that.

Finally, this won't help with speed, but you could get rid of "exist" altogether and just put the InStr() comparison in the If...Then line directly:

If InStr(str1, str2) <> 0 Then

One weird quirk of VB is that specifying optional parameters in native functions sometimes makes the call slower. Because of that, I try to avoid ever specifying an optional parameter unless I'm changing the default. Thus only using two parameters in the InStr() call example above.

[RS_Arm]

Fazi, that was almost working, but, because I'm using this function to multiple forms and MSHFlexGrid's, it is not working very well, because the "Raw_Num" persist from "older" forms.
Thank you, any way.

Ellis Dee, thank you for your tips, but what i'm looking for is to do a "Find Next" in my function.

Let's make the ultimate search function to MSHFlexgrid's!
More tips needed.

[Al42]

Pass the starting point to the function. Keep a local static variable in every routine that calls the function. Reset it to .FixedRows, .FixedCols on a Find First.

Some more optimizations (including Ellis's)

Sub Grid(SearchText As String, objMSHFGrid As MSHFlexGrid, lStartRow As Long, iStartCol As Integer)
Dim r As Long, c As Integer
Dim str2 As String

  str2 = LCase$(SearchText ) 'doing this every loop slows things
  With objMSHFGrid
    For r = lStartRow To .Rows - 1
      For c = iStartCol To .Cols - 1
        If InStr(LCase$(.TextMatrix(r, c)), str2, vbBinaryCompare) Then
          .Row = r     
          .Col = c
          .TopRow = r
          Exit For  
        End If
      Next c
    Next r
  End With

End Sub

[Ellis Dee]

No, he's got multiple grids that he wants this to work for.

Use the tag property. Every new search operation should add the first match position to the grid's tag. A Find Next operation would then simply query the tag to see where it should start from.

[Ellis Dee]

I really like your function prototype, Al42, but specifying the start position might not be necessary if you use the tag property.

Also, I think I detect a bug. Your "Exit For" on match returns control to the outer loop instead of ending the search.

[Ellis Dee]

On second thought, remembering the last position isn't necessary; the way a FindNext normally works is to simply start searching at whatever the current position is. The tag could still be used to remember the search term, but even that isn't necessary.

A couple quick questions about the grid, since I've never used it:

- Rows start at 1 but columns start at 0?!
- Is there a way to return the entire row's worth of text in one shot, (maybe to an array?) instead of iterating the columns? Any methods or properties like that?

Anyway, using Al's enhancements, I envision something like this:

Public Sub GridFindFirst(pgrd As MSHFlexGrid, pstrFind As String, Optional pblnMatchCase As Boolean = False)
    Dim strFind As String
    
    Select Case pblnMatchCase
        Case True: strFind = pstrFind
        Case False: strFind = LCase$(pstrFind)
    End Select
    If Not GridSearch(pgrd, strFind, pblnMatchCase) Then
        MsgBox "'" & pstrFind & "' not found", vbInformation, "Notice"
    End If
End Sub

Public Sub GridFindNext(pgrd As MSHFlexGrid, pstrFind As String, Optional pblnMatchCase As Boolean = False)
    Dim strFind As String
    Dim lngRow As Long
    Dim lngCol As Long
    
    Select Case pblnMatchCase
        Case True: strFind = pstrFind
        Case False: strFind = LCase$(pstrFind)
    End Select
    If pgrd.Col < pgrd.Cols - 1 Then
        lngRow = pgrd.Row
        lngCol = pgrd.Col + 1
    ElseIf pgrd.Row < pgrd.Rows - 1
        lngRow = pgrd.Row + 1
    End If
    If Not GridSearch(pgrd, strFind, pblnMatchCase, lngRow, lngCol) Then
        MsgBox "'" & pstrText & "' not found", vbInformation, "Notice"
    End If
End Sub

Private Function GridSearch(pgrd As MSHFlexGrid, pstrFind As String, pblnMatchCase As Boolean, Optional plngRow As Long = 0, Optional plngCol As Long = 0) As Boolean
    Dim strGrid As String
    Dim x As Long
    Dim y As Long
    Dim lngColMax As Long

    lngColMax = pgrd.Cols - 1
    For x = plngRow To pgrd.Rows - 1
        For y = plngCol To lngColMax
            Select Case pblnMatchCase
                Case True: strGrid = pgrd.TextMatrix(x, y)
                Case False: strGrid = LCase$(pgrd.TextMatrix(x, y)
            End Select
            If InStr(strGrid, pstrFind) Then
                pgrd.Row = x
                pgrd.Col = y
                pgrd.TopRow = x
                GridSearch = True
                Exit Sub  
            End If
        Next
    Next
End Function

This was coded freehand, so be on the lookout for bugs. Note that the GridFindNext() function takes advantage of the fact that numeric variables default to 0 if they are never assigned a value. If you do a FindNext when the current cell is the last one in the grid, it starts searching from the beginning.

Since the original code has the row loop going to Rows - 1, I concluded that the rows start at 0 and not 1. The code needs to be altered if this isn't the case.

I took the liberty of adding in logic to support both case-sensitive and non-case-sensitive searches.

Note that Al's code will be faster in general because copying strings isn't very fast. If you want to keep the MatchCase logic, a possible optimization would be to create two different GridSearch() versions: one that ignores case (set up like Al's) and one that matches case. Then call the appropriate one from GridFindFirst and GridFindNext based on the value of pblnMatchCase.

[RS_Arm]

Hi
Sorry for the delay

Ellis Dee
1st Question: The rows start at 0 position (I just don't want to search the fixed row).
2nd Question: I don't think the MSHFlexGrid has that capabilities.

I'm steel testing your code, but looks pretty good (some little bugs)
I'll be in touch soon.

Thank's

[RS_Arm]

Ok. Now it's working

Here goes the code:

Public Sub GridFindFirst(pgrd As MSHFlexGrid, pstrFind As String, Optional pblnMatchCase As Boolean = False)
    Dim strFind As String
    
    Select Case pblnMatchCase
        Case True: strFind = pstrFind
        Case False: strFind = LCase$(pstrFind)
    End Select
    If Not GridSearch(pgrd, strFind, pblnMatchCase) Then
        MsgBox "'" & pstrFind & "' not found", vbInformation, "Notice"
    End If
End Sub

Public Sub GridFindNext(pgrd As MSHFlexGrid, pstrFind As String, Optional pblnMatchCase As Boolean = False)
    Dim strFind As String
    Dim lngRow As Long
    Dim lngCol As Long
    
    Select Case pblnMatchCase
        Case True: strFind = pstrFind
        Case False: strFind = LCase$(pstrFind)
    End Select
    If pgrd.Col < pgrd.Cols - 1 Then
        lngRow = pgrd.Row
        lngCol = pgrd.Col + 1
    Else
        If pgrd.Row < pgrd.Rows - 1 Then
            lngRow = pgrd.Row + 1
        End If
        
    End If
If Not GridSearch(pgrd, strFind, pblnMatchCase, lngRow, lngCol) Then MsgBox "'" & pstrFind & "' not found", vbInformation, "Notice"
End Sub

Private Function GridSearch(pgrd As MSHFlexGrid, pstrFind As String, pblnMatchCase As Boolean, Optional plngRow As Long = 0, Optional plngCol As Long = 0) As Boolean
    Dim strGrid As String
    Dim x As Long
    Dim y As Long
    Dim lngColMax As Long

    lngColMax = pgrd.Cols - 1
    For x = plngRow To pgrd.Rows - 1
        For y = plngCol To lngColMax
            Select Case pblnMatchCase
                Case True:
                    strGrid = pgrd.TextMatrix(x, y)
                Case False:
                    strGrid = LCase$(pgrd.TextMatrix(x, y))
            End Select
            If InStr(strGrid, pstrFind) Then
                pgrd.Row = x
                pgrd.Col = y
                pgrd.TopRow = x
                GridSearch = True
                Exit Function
            End If
        Next
    Next
End Function

Thank you all that contributed for my questions.
Thank you Ellis Dee, Thank you Al42, Thank you Fazi!
Stay cool

[Al42]

Rows start at .FixedRow, columns start at .FixedCol. That way the code is correct regardless of how many fixed rows or columns you have. (Absolute rows and columns start at 0.)

The entire selected area can be returned with .Row and .RowSel. The inner loop will return just the current row. I don't think there's a property to return a row.

For i = .Row To .RowSel
  For j = .FixedCols To .Cols - 1
    strRow = strRow & .TextMatrix(i, j) [COLOR="Green"]
    If j < .Cols - 1 Then strRow = strRow & vbTab
  Next j
  strRow = strRow & vbNewLine
Next i

And good catch on my premature For exit.

[Ellis Dee]

Rows start at .FixedRow, columns start at .FixedCol. That way the code is correct regardless of how many fixed rows or columns you have. (Absolute rows and columns start at 0.)

I'm slow on the uptake today. Would you mind elaborating on this?

I'm not sure I understand what a fixed row/column even is.

[si_the_geek]

They are headers (either at the top of the grid, or on the left) that don't contain data, and don't scroll (hence 'fixed').

As the first row/column is 0, the number of fixed rows/columns (returned by .FixedRows/.FixedCols ) is always equal to the first 'data' row/column.

eg: if the number of fixed rows is 1, then row(0) will be the header, and row(1) is the first row of data.