vsFlexGrid FindRow property seems buggy

[LittleTyke]

I have used vsFlexGrid, both the original VideoSoft product and the ComponentOne product, for years. Recently I wanted to find a row based on a partial match of the contents of a cell in a particular column.

My example below uses vsFlexGrid 8.0 (OLEDB) version 8.0.20033.190.

According to the documentation for FindRow, there are two boolean flags, CaseSensitive and FullMatch. Both flags are True by default.

The syntax of the FindRow property is:

val& = [form!]VSFlexGrid.FindRow(Item As Variant, [ Row As Long ], [ Col As Long ], [ CaseSensitive As Boolean ], [ FullMatch As Boolean])


Create a test VB6 program.

Add ComponentOne vsFlexGrid 8.0 (OLEDB) from the Components list. Name it fg.

Add a Command button (Command1).

Add the code below to the Click event. This code is based on the example given in the help file for FindRow. I want to find "Cell" or "cell", which is embedded in the string "MyCell". I cannot get FindRow to find it.

Private Sub Command1_Click()

fg.TextMatrix(2, 5) = "MyCell"

' All tests below return -1
' It is impossible to find "Cell" with any combination of CaseSensitive and FullMatch

' Test find "Cell" (with C in upper case)
Debug.Print fg.FindRow("Cell", , 5, False, False)
Debug.Print fg.FindRow("Cell", , 5, False, True)
Debug.Print fg.FindRow("Cell", , 5, True, False)
Debug.Print fg.FindRow("Cell", , 5, True, True)

' Test find "cell" (with c in lower case)
Debug.Print fg.FindRow("cell", , 5, False, False)
Debug.Print fg.FindRow("cell", , 5, False, True)
Debug.Print fg.FindRow("cell", , 5, True, False)
Debug.Print fg.FindRow("cell", , 5, True, True)

End Sub

[SamOscarBrown]

where did you get vsFlexGrid 8.0? Free download somewhere?

[LittleTyke]

There was a freebie CD some years ago. I can't remember where I got it from, perhaps a PC magazine, as back then PC mags had a CD, or later a DVD, on the cover. The CD contains all ComponentOne ActiveX controls.

Edit: Not a CD. See my most recent reply in this thread.

[LittleTyke]

Actually, I've done a search of my archive PC and you can find more info here: https://www.experts-exchange.com/que...cense-Key.html
Basically, you downloaded the VB.NET Resource Kit and that provided links to the free ComponentOne ActiveX controls, all above board.

Edit: Also see here:
https://web.archive.org/web/20040603...vbasic/vbrkit/

[Arnoutdv]

The original sample from CompontOne seems to work, but it's checking only the first characters of the cell content.
I assume it needs to work like AutoComplete in some combo/listboxes. This only works for all matching starting characters. It's not a InStr() search

Check the help for AutoSearch and ComboSearch, this explains how it works

FindRow function which does use the InStr() method:

Public Function FindRow(oFG As VSFlexGrid, ByVal sValue As String, Optional lStartRow As Long = -1, Optional lSearchCol As Long = -1, Optional CaseSensitive As Boolean = True, Optional FullMatch As Boolean = True) As Long
  Dim lRow As Long, lR1 As Long, lR2 As Long
  Dim bRowData As Boolean, iCompare As VbCompareMethod
  Dim sContent As String
  
  If lSearchCol = -1 Then bRowData = True
    
  bRowData = (lSearchCol = -1)
  If FullMatch Then iCompare = vbBinaryCompare Else iCompare = vbTextCompare
  
  If lStartRow = -1 Then lR1 = fg.FixedRows Else lR1 = lStartRow
  lR2 = fg.Rows - 1
  
  FindRow = -1
  For lRow = lR1 To lR2
    If bRowData Then sContent = fg.RowData(lRow) Else sContent = fg.TextMatrix(lRow, lSearchCol)
    If FullMatch Then
      If StrComp(sContent, sValue, iCompare) = 0 Then FindRow = lRow
    Else
      If InStr(1, sContent, sValue, iCompare) > 0 Then FindRow = lRow
    End If
    If FindRow <> -1 Then Exit For
  Next lRow
End Function

Private Sub Command1_Click()
' All tests below return -1
' It is impossible to find "Cell" with any combination of CaseSensitive and FullMatch

' Test find "Cell" (with C in upper case)
Debug.Print fg.FindRow("Cell", , 5, False, False)
Debug.Print fg.FindRow("Cell", , 5, False, True)
Debug.Print fg.FindRow("Cell", , 5, True, False)
Debug.Print fg.FindRow("Cell", , 5, True, True)

' Test find "cell" (with c in lower case)
Debug.Print fg.FindRow("cell", , 5, False, False)
Debug.Print fg.FindRow("cell", , 5, False, True)
Debug.Print fg.FindRow("cell", , 5, True, False)
Debug.Print fg.FindRow("cell", , 5, True, True)

Debug.Print "-----"

' Test find "Cell" (with C in upper case)
Debug.Print FindRow(fg, "Cell", , 5, False, False)
Debug.Print FindRow(fg, "Cell", , 5, False, True)
Debug.Print FindRow(fg, "Cell", , 5, True, False)
Debug.Print FindRow(fg, "Cell", , 5, True, True)

' Test find "cell" (with c in lower case)
Debug.Print FindRow(fg, "cell", , 5, False, False)
Debug.Print FindRow(fg, "cell", , 5, False, True)
Debug.Print FindRow(fg, "cell", , 5, True, False)
Debug.Print FindRow(fg, "cell", , 5, True, True)

End Sub

[LittleTyke]

The original sample from CompontOne seems to work, but there the is for the first characters of the cell content.
[/code]

Please can you clarify the "...but there the is for the first characters..."

I think you mean "...but that is for the first characters...".
Because that is how FindRow actually works. That is, it will find "My" or "MyC" or "MyCe", but not "yCe" or "Ce" or "Cell" or "cell".

I even tested an original VideoSoft vsFlexGrid from about 1999 and that appears to follow the same flawed logic. I say "flawed", because it was a ludicrous design decision in the original component not to search for partial matches anywhere in a word or phrase with the correct boolean flag used.

I did an exhaustive search of all my previous VB programs (over 500) going back some 20 years, looking for forms or modules with FindRow in them. There are quite a few. On inspecting the code, it just so happened I never needed to match anywhere in the string! Just luck of the draw, I suppose. However, back when I was working as a programmer in 2000, using VideoSoft products, I remember communicating directly with the programmers at VideoSoft in the States to report bugs and they were always very helpful and responsive. Often I got a fix sent the same day. I've been retired now since 2001. Now I just write programs for my own use.

To fix this particular issue I wrote my own function similar to what you've added to the thread above, using Instr. Thanks for your feedback!

[Arnoutdv]

Yeah, typing mistakes when correcting an already written sentence

I think the behavior is by design, it does a quick lookup like autocomplete in other components, and how Excel works when typing in a cell.
It's not a real search for a partial string in the grid.

I use the vsFlexGrid for about 20 years now and always use my own search functions.

[LittleTyke]

Searching the C1Enterprise CD content from 2003 acquired through the VB.Net Resource Kit I found this in one of the update zips and in an updated help file:

VSFlex8 Build Number 8.0.20041.200 Build Date: November 11, 2003

A new property!

FindRowRegex Property

Returns the index of the row that contains a match or -1 if no match was found.

Syntax

Property FindRowRegex(Pattern As String, Row As Long, Col As Long) As Long

Remarks

The parameters for the FindRowRegex property are described below:

Pattern As String

Pattern containing the regular expression to look for (see the Pattern property in the VBScript Regex object for regular expression syntax).

Row As Long

The row where the search should start (use -1 to start at the first scrollable row).

Col As Long

The column to search.

Data Type

Long


The version installed on my PC is 20033.190, which does not include FindRowRegex.

[Arnoutdv]

The version I bought (and updated) long time ago is: 8.0.20092.256
This version is has a .FindRowRegex property, but I've never used this property

The function I posted in post #5 does the partial match based on the InStr() method.

[SamOscarBrown]

Is this vsFlexGrid more versatile than Krool's vbFlexGrid? I use the latter ALMOST exclusively (except oft times use the MSFlexGrid to aid folks on this forum who don't have it.)

[LittleTyke]

I've not heard of Krool's vbFlexGrid. There are lots of grids out there. I started off 2 decades ago with VB's own MSFlexGrid, then found the VideoSoft version and my manager at the time was willing to buy several copies after I had tested it out.

[SamOscarBrown]

Well, that's cool. Check out Krool's stuff if you get the opportunity. MAYBE their vbFlexGrid is better??? Not sure as I don't have the vsGrid....


https://www.vbforums.com/showthread....ool+vbflexgrid

Sammy

[Arnoutdv]

Is this vsFlexGrid more versatile than Krool's vbFlexGrid? I use the latter ALMOST exclusively (except oft times use the MSFlexGrid to aid folks on this forum who don't have it.)

I think that Krool used the existing methods and properties of the vsFlexGrid as inspiration for his vbFlexGrid
And the vsFlexGrid hasn’t been updated for decades