VBFlexGrid Control (Replacement of the MSFlexGrid control)

[Krool]

I don't know what does it mean, but when I start the DEMO project (the last version) I give this error. The project is loaded and works fine.

WinXp SP3

That's the VB6 IDE (dataview.dll) and has nothing todo with this project.
You may get rid of it when you rename said dll to let's say "_dataview.dll".

[Nouyana]

Krool,

Is it a "by design" behaviour?

Private Sub Form_Load()
   Dim i As Integer
   With FlexGrid1
      .FocusRect = FlexFocusRectNone
      .TabBehavior = FlexTabNext        ' Next what?
      .Cols = 4                         ' Creating columns
      .FixedRows = 0
      .Rows = 0
      For i = 1 To 50                   ' Creating 50 records.
         .AddItem "Record " & vbTab & i & vbTab & i * 100 & vbTab & "text"
      Next i
   End With
End Sub
Private Sub txtTextBox_GotFocus(Index As Integer)
   txtTextBox(Index).BackColor = vbRed
End Sub
Private Sub txtTextBox_LostFocus(Index As Integer)
   txtTextBox(Index).BackColor = vbWhite
End Sub

[Krool]

Krool,

Is it a "by design" behaviour?

Private Sub Form_Load()
   Dim i As Integer
   With FlexGrid1
      .FocusRect = FlexFocusRectNone
      .TabBehavior = FlexTabNext        ' Next what?
      .Cols = 4                         ' Creating columns
      .FixedRows = 0
      .Rows = 0
      For i = 1 To 50                   ' Creating 50 records.
         .AddItem "Record " & vbTab & i & vbTab & i * 100 & vbTab & "text"
      Next i
   End With
End Sub
Private Sub txtTextBox_GotFocus(Index As Integer)
   txtTextBox(Index).BackColor = vbRed
End Sub
Private Sub txtTextBox_LostFocus(Index As Integer)
   txtTextBox(Index).BackColor = vbWhite
End Sub

Yes it is. TabBehavior and WrapCellBehavior work together.
So just play each combination and look what is good for you.

[Nouyana]

TabBehavior and WrapCellBehavior work together.
So just play each combination and look what is good for you.

Thank you, Krool!

[Nouyana]

Included AutoClipboard property.

It seems that AutoClipboard property is not working for me. WinXP SP3.

[Nouyana]

List of enhancements and improvements in relation to the MSFlexGrid and/or MSHFlexGrid control:
- BackColorAlt property included to set alternate row back colors.

In fact, I will never use this property. For me it looks dazzled. But if somebody use the BackColorAlt, don't he need a ForeColorAlt property?

[Nouyana]

Another question is about the BeforeEdit event. What does the FlexEditReasonBackSpace constant mean? I can't get into edit mode by BackSpace. The BeforeEdit event fires, but I can't edit anything. And my arrow keys become frozen. WinXP SP3.

[Nouyana]

I don't know if it is a feature or a bug, but when I use the MsgBox function inside the BeforeUserResize event, the VBFlexGrid captures my mouse until I change active cell. WinXP SP3

Private Sub FlexGrid1_BeforeUserResize( _
      ByVal Row As Long, ByVal Col As Long, Cancel As Boolean)
   MsgBox "d"
End Sub

Private Sub Form_Load()
   Dim i As Integer
   With FlexGrid1
      .SelectionMode = FlexSelectionModeByRow
      .AllowMultiSelection = True
      .AllowBigSelection = False
      .FocusRect = FlexFocusRectNone
      .Align = vbAlignTop
      .AllowUserResizing = FlexAllowUserResizingBoth
      .AllowUserEditing = True
      .Cols = 4
      .FixedRows = 1
      .Rows = 1
      For i = 1 To 50
         .AddItem "Record " & vbTab & i & vbTab & i * 100 & vbTab & "text"
      Next i
   End With
End Sub

[Nouyana]

Included BestFitMode property which defaults to 0 - TextOnly (used for AutoSize/FormatString) to behave like intended.
Else you can choose between 1 - Full, 2 - SortArrowText or 3 - OtherText.
Having 2 - SortArrowText is the same as previous behavior, means leaving the other contents (checkbox, combocue) un-measured.

Krool, can you please explain in more detail, how to use Full and OtherText modes.

EDITED. For what purposes do we have the "2 - SortArrowText" mode if it has "the same behavior"?

[Nouyana]

The BorderStyle's default value is 3, not 1. It looks the same style, but incompatible with MS.

EDITED. Maybe FlexBorderStyleSunken should be 1 (default) and FlexBorderStyleSingle should be 3?

[Nouyana]

Memory consumption test.

Using the same memory (~1.3gb):

• MSHFlexGrid can load 4 250 000 records (4 fields, 256mb mdb-database)
• VBFlexGrid can load 1 750 000 records (4 fields, 104mb mdb-database)

Speed test.

Using the same database (1 750 000 records, 4 fields, 104mb mdb):

• MSHFlexGrid loads data in 49 seconds
• VBFlexGrid loads data in 19 seconds

So, the VBFlexGrid is 2.5 times faster, but consumes 2.5 times more memory.

[Krool]

The BorderStyle's default value is 3, not 1. It looks the same style, but incompatible with MS.

EDITED. Maybe FlexBorderStyleSunken should be 1 (default) and FlexBorderStyleSingle should be 3?

The BorderStyle can be considered "not compatible" because it has more options then the original property. Won't change it..

Memory consumption test.

Using the same memory (~1.3gb):

• MSHFlexGrid can load 4 250 000 records (4 fields, 256mb mdb-database)
• VBFlexGrid can load 1 750 000 records (4 fields, 104mb mdb-database)

Speed test.

Using the same database (1 750 000 records, 4 fields, 104mb mdb):

• MSHFlexGrid loads data in 49 seconds
• VBFlexGrid loads data in 19 seconds

So, the VBFlexGrid is 2.5 times faster, but consumes 2.5 times more memory.

That sounds logical. Because the VBFlexGrid has more internal data (more features/formats etc.) it is natural that the overall memory consumption is more.

Krool, can you please explain in more detail, how to use Full and OtherText modes.

EDITED. For what purposes do we have the "2 - SortArrowText" mode if it has "the same behavior"?

TextOnly measures only text. SortArrowText measure in addition the sort arrow indicator. Full measures everything. OtherText measures everything except sort arrow indicator.

I don't know if it is a feature or a bug, but when I use the MsgBox function inside the BeforeUserResize event, the VBFlexGrid captures my mouse until I change active cell. WinXP SP3




[CODE]Private Sub FlexGrid1_BeforeUserResize( _
ByVal Row As Long, ByVal Col As Long, Cancel As Boolean)
MsgBox "d"
End Sub

It makes no sense to display a msgbox if you want active user resizing. It makes sense if you want to cancel the user resizing.
In this case ensure to make a

Cancel = True

before the MsgBox.

In fact, I will never use this property. For me it looks dazzled. But if somebody use the BackColorAlt, don't he need a ForeColorAlt property?

The idea if the BackColorAlt is to make alternative back colors from row to row, which can increase readibility for the eyes to "follow" better from left to right in a row.

[Krool]

Another question is about the BeforeEdit event. What does the FlexEditReasonBackSpace constant mean? I can't get into edit mode by BackSpace. The BeforeEdit event fires, but I can't edit anything. And my arrow keys become frozen. WinXP SP3.

For me it works. When you enter into edit by backspace the text is erased by default.

It seems that AutoClipboard property is not working for me. WinXP SP3.

The AutoClipboard property is for copying pasting cells when you are "not" in edit mode. When you are in edit mode you just copy paste the text contents like in any normal TextBox.

[Nouyana]

For me it works. When you enter into edit by backspace the text is erased by default.

Pressing the backspace under WinXP just crashes the IDE or EXE. May be you shoul fix it or change the minimal system requrements.



EDITED. For WinXP users:

Private Sub VBFlexGrid1_BeforeEdit(Row As Long, Col As Long, ByVal Reason As VBFLXGRD16.FlexEditReasonConstants, Cancel As Boolean)
   If Reason = FlexEditReasonBackSpace Then Cancel = True
End Sub

[Krool]

Pressing the backspace under WinXP just crashes the IDE or EXE. May be you shoul fix it or change the minimal system requrements.



EDITED. For WinXP users:

Private Sub VBFlexGrid1_BeforeEdit(Row As Long, Col As Long, ByVal Reason As VBFLXGRD16.FlexEditReasonConstants, Cancel As Boolean)
   If Reason = FlexEditReasonBackSpace Then Cancel = True
End Sub

That's indeed a bug. The error is on the following line (for BackSpace)

SendMessage VBFlexGridEditHandle, EM_REPLACESEL, 1, ByVal 0&

The NULLPTR is a problem on Windows XP for EM_REPLACESEL. I fixed that already in the VBCCR TextBox but missed that one for the VBFlexGrid.
Solution (fix) is as following:

SendMessage VBFlexGridEditHandle, EM_REPLACESEL, 1, ByVal StrPtr("")

I will release fix. Thanks

[Nouyana]

VBFlexGrid has more internal data (more features/formats etc.) it is natural that the overall memory consumption is more.

Can you add a conditional compilation constant or maybe just a property to disable the most memory consuming features?

[Nouyana]

Solution (fix) is as following:

SendMessage VBFlexGridEditHandle, EM_REPLACESEL, 1, ByVal StrPtr("")

Yes, this works. Thank you.

[Nouyana]

Hi, Krool!

I continue to test your grid, and, if you find it useful, I'll continue to post the results here.

I don't know, is it a bug or feature, but Cell(FlexCellHeight) property ignores the RowSel and ColSel parameters. It seems logical to me if the Cell property (FlexCellHeight and FlexCellWidth) will return the width and height of the range, and not just the first cell.

For testing:

Private Sub Command1_Click()
   With FlexGrid1
      Debug.Print "Left:  "; .Cell(FlexCellLeft, .Row, .Col, .RowSel, .ColSel)
      Debug.Print "Top:   "; .Cell(FlexCellTop, .Row, .Col, .RowSel, .ColSel)
      Debug.Print "Height:"; .Cell(FlexCellHeight, .Row, .Col, .RowSel, .ColSel)
      Debug.Print "Width: "; .Cell(FlexCellWidth, .Row, .Col, .RowSel, .ColSel)
      Debug.Print "Row, Col:      "; .Row; .Col
      Debug.Print "RowSel,ColSel: "; .RowSel; .ColSel
      Debug.Print "================"
   End With
End Sub

[Arnoutdv]

What should .Cell(FlexCellBackColor, .Row, .Col, .RowSel, .ColSel) return when all cells have a different color?
For the .Cell property not all parameters are always used.
You can often "set" it for a range of cells, but "get" will return the value for a single cell, just the row and col specified.
Same goes for the vsFlexGrid by ComponentOne

[Nouyana]

What should .Cell(FlexCellBackColor, .Row, .Col, .RowSel, .ColSel) return when all cells have a different color?

This is an incorrect example because the Cell(FlexCellBackColor) property ignores RowSel and ColSel parameters only in the Property Get part, but not in the Property Let.

Anyway, the programmer should understand what to expect from the property behavour. And in my oppinion this sub should return the whole width of selected range:

Private Sub Command1_Click()
   With FlexGrid1
      Debug.Print "Width: "; .Cell(FlexCellWidth, .Row, .Col, .RowSel, .ColSel)
   End With
End Sub

If you want to work only with the first cell, you can omit RowSel and ColSel parameters.
It is not very difficult to implement/fix.

[Nouyana]

By the way, in my opinion, the FlexCellLeft and FlexCellTop properties shouldn't ignore the RowSel and ColSel too. It make sence when the (Row,Col) cell is in the right bottom corner of a range.

[Krool]

By the way, in my opinion, the FlexCellLeft and FlexCellTop properties shouldn't ignore the RowSel and ColSel too. It make sence when the (Row,Col) cell is in the right bottom corner of a range.

I would propose for new enums. E.g. FlexCellRangeTop, FlexCellRangeHeight.

[Nouyana]

I would propose for new enums. E.g. FlexCellRangeTop, FlexCellRangeHeight.

This will only make sence if someone has used this syntax:

Cell(FlexCellWidth, .Row, .Col, .RowSel, .ColSel)

meaning that he works with only one cell. I would not add new enums. You already have 25 of them there.

[Nouyana]

Your CellAlignment property works with a whole selected range and not just with one cell like the original one. No one even noticed this.

[Krool]

Your CellAlignment property works with a whole selected range and not just with one cell like the original one. No one even noticed this.

Yes. (Let Only)
Of course FillStyle must be FlexFillStyleRepeat.

[Nouyana]

Yes. (Let Only)
Of course FillStyle must be FlexFillStyleRepeat.

My mistake here. The MSDN OCT2001 says:

Returns or sets a value that determines the horizontal and vertical alignment of data within the current cell.

But in fact the original behavour depends on the FillStyle property too.

[Nouyana]

About BeforeEdit event again

How can I get (invoke?) the event with Reason = FlexEditReasonComboCueDblClick or Reason = FlexEditReasonComboCueAltUpDown?

EDITED: I figured out how to get the FlexEditReasonComboCueAltUpDown Reason. The only question is how to DblClick on a ComboCue? The FlexEditReasonComboCueClick comes faster then FlexEditReasonComboCueDblClick

WinXP SP3.

[Nouyana]

The alignment of the checkbox can be either left-center, center-center or right-center and is linked to the CellPictureAlignment property. (!)

The FlexPictureAlignmentRightCenter alignment doesn't work for CheckBoxes

[Nouyana]

I consider it as a feature, not a bug.

When the CellChecked = FlexDisabledTextAsCheckBox, the CheckBox is not really disabled. It only looks like disabled. Its behavour is the same as FlexTextAsCheckBox. If AllowUserEditing = True, then user can change the text, and a CheckBox changes its state.

[Nouyana]

...this update includes the FlexDataSource (run-time only) property which allows to define a custom (private) data source via the IVBFlexDataSource interface.
The definitions are equally to vsFlexGrid (IVSFlexDataSource) and hopefully also the behavior. The behavior in regard to row/col offset should match.
Concerning the behavior of the limitation (function disabling) I am not sure. However, they are now as following:
- Sort not possible
- Clear method is restricted. (not possible to clear text, only to clear formatting is possible)
- FindItem not possible

Everything else is redirected to/from the custom data source. (GetData/SetData)
Even changing the Rows/Cols is possible. Also to call AddItem/RemoveItem.
It is up to the application to sync that change to the custom data source. If that's not done an out of bounds error can happen or the change will be reversed after a .DataRefresh.

Krool, can you, please, give an example of using the FlexDataSource property? Where can I read about creating a custom (private) data source?

[Schmidt]

...example of using the FlexDataSource property?

FWIW, here's a codebank-entry which shows how to use it
(in conjunction with SQLite-recordsets).
https://www.vbforums.com/showthread....te-Recordsets)

In short, you need a Class, which implements the IVBFlexDataSource interface.
(e.g. cMy2DVariantArray.cls ... which then e.g. interacts in the interface-method-callbacks with an internal Variant-array).

Olaf

[Krool]

About BeforeEdit event again

How can I get (invoke?) the event with Reason = FlexEditReasonComboCueDblClick or Reason = FlexEditReasonComboCueAltUpDown?

EDITED: I figured out how to get the FlexEditReasonComboCueAltUpDown Reason. The only question is how to DblClick on a ComboCue? The FlexEditReasonComboCueClick comes faster then FlexEditReasonComboCueDblClick

WinXP SP3.

DblClick is for completeness and for rare cases.

Example:
Click on a combo cue and press quickly escape key and click again.
You then have Click, cancel edit, DblClick.

[Nouyana]

FWIW, here's a codebank-entry which shows how to use it
(in conjunction with SQLite-recordsets).
https://www.vbforums.com/showthread....te-Recordsets)
In short, you need a Class, which implements the IVBFlexDataSource interface.

Thank you, Olaf!
I have a couple of questions more:
1. Do we have any problem with MSBIND.DLL dependency on modern OS?
2. Should I implement only IVBFlexDataSource interface or BindTo, DeleteRow and SaveChanges are necessary too?

[Nouyana]

Example:
Click on a combo cue and press quickly escape key and click again.
You then have Click, cancel edit, DblClick.

Wow! I did it!

[Krool]

The FlexPictureAlignmentRightCenter alignment doesn't work for CheckBoxes

Thanks. That was indeed a bug which got fixed.

Btw, consider using ColCheckBoxAlignment instead of CellPictureAlignment.
The default value for ColCheckBoxAlignment is 'FlexCheckBoxAlignmentUsePictureAlignment'.

[Krool]

I consider it as a feature, not a bug.

When the CellChecked = FlexDisabledTextAsCheckBox, the CheckBox is not really disabled. It only looks like disabled. Its behavour is the same as FlexTextAsCheckBox. If AllowUserEditing = True, then user can change the text, and a CheckBox changes its state.

Well, what is disabled is actually just the checkbox "trigger". But the text editing is not restricted on FlexTextAsCheckBox or FlexDisabledTextAsCheckBox.
It's a good question if a cell shall be "not editable" (except by code) in such a case. I wait here for some feedback..

[Nouyana]

The best is to use the VBFlexGrid in a "virtual" way.
Means using the .FlexDataSource property. Schmidt provided a sample project somewhere showing how to use it for a SQLite Recordset.
It would be kind of easy to swap it with a DAO recordset.
Also performance and memory wise it is better - the data is loaded only once and not shovel over again from the recordset to the grid.

I do not know where is a problem here, but it's not look like a "virtual" way. I tried to load 5 million records from a MS Access database (4 fields, 301 megabytes mdb file). I did it, but the program uses more than 1,7 GB of memory. I got comparable results using the DataSource property.

If I load more than 1,5 million records using FlexDataSource, the grid starts to slow down (read only) and it is almost impossible to work with it. If I work using DataSource, I have no such problems. EDITED: The problem is appear only after pressing Ctrl+Down keys, that is, after last records are reached.

Here is my program (need DAO 3.6 reference and VBFlexGrid1 control on the form):

"MMain" module:

Private DBName     As String
Private oDB        As DAO.Database
Private t_RECORDS  As DAO.Recordset
Public oDataSource As cFlexDataSource

Sub Main()
   DBName = App.Path & "\database.mdb"
   Set oDB = DAO.OpenDatabase(DBName)
   Set t_RECORDS = oDB.OpenRecordset("t_RECORDS", dbOpenSnapshot)
   t_RECORDS.MoveLast
   t_RECORDS.MoveFirst
   Set oDataSource = New cFlexDataSource
   Set oDataSource.RS = t_RECORDS
   
   FMain.Show vbModal
   
HELL:
   Set oDataSource = Nothing
   t_RECORDS.Close
   Set t_RECORDS = Nothing
   oDB.Close
   Set oDB = Nothing
End Sub

"FMain" form:

Private Sub Form_Load()
  Set VBFlexGrid1.FlexDataSource = oDataSource
End Sub

"cFlexDataSource" class:

Implements IVBFlexDataSource

Public RS As DAO.Recordset

Private Function IVBFlexDataSource_GetFieldCount() As Long
  IVBFlexDataSource_GetFieldCount = RS.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
  IVBFlexDataSource_GetFieldName = RS(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
  IVBFlexDataSource_GetRecordCount = RS.RecordCount
End Function
Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
                                           ByVal Record As Long) As String
   If Record < RS.RecordCount Then
      With RS
         .AbsolutePosition = Record
         IVBFlexDataSource_GetData = .Fields(Field)
      End With
   Else
      Exit Function
   End If
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, _
                                      ByVal Record As Long, _
                                      ByVal NewData As String)
   If Record < RS.RecordCount Then
      With RS
         .AbsolutePosition = Record
         .Edit
         .Fields(Field) = NewData
         .Update
      End With
   End If
End Sub

[Nouyana]

For creating the database.mdb

CreatingDatabase.zip

[Nouyana]

FlexDataSourceToolTipText allows the tool tip text property to be treated in the flex data source. Otherwise GetToolTipTex/SetToolTipText will not be fired and treated as normal properties.

How to use it? I've been added this routines to my cFlexDataSource class:

Implements IVBFlexDataSource2


Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
   IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function

Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
                                                   ByVal Record As Long) As String   
   Debug.Print "IVBFlexDataSource2_GetToolTipText"
End Function

Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
                                              ByVal Record As Long, _
                                              ByVal NewValue As String)
   Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub

But nothing was printed in my Immediate window.

[Krool]

How to use it? I've been added this routines to my cFlexDataSource class:

Implements IVBFlexDataSource2


Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
   IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function

Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
                                                   ByVal Record As Long) As String   
   Debug.Print "IVBFlexDataSource2_GetToolTipText"
End Function

Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
                                              ByVal Record As Long, _
                                              ByVal NewValue As String)
   Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub

But nothing was printed in my Immediate window.

You have the ShowInfoTips property set to True ?