VBFlexGrid Control (Replacement of the MSFlexGrid control)

[Krool]

Let it be a conditional compilation. Why not?

Well, that would be possible for the UserControl version only. For the OCX (pre-compiled) all features must be enabled.

[Schmidt]

Well, that would be possible for the UserControl version only. For the OCX (pre-compiled) all features must be enabled.

A Publically exposed "DefaultFormatsOnly"-Prop (defaulting to False), could help there...

Olaf

[Krool]

A Publically exposed "DefaultFormatsOnly"-Prop (defaulting to False), could help there...

Olaf

I understand. But that would be w/o "conditional compilation".
And having that would imply 2 different structure sets which means at the end to duplicate a lot of code.

[Schmidt]

I understand. But that would be w/o "conditional compilation".
And having that would imply 2 different structure sets which means at the end to duplicate a lot of code.

Yeah, just tried to suggest an alternative for a "compiler-switch-boolflag"...

As for "two different sets of code" - I wouldn't go there either -
perhaps instead I'd try to introduce a "sparse-matrix" instead (as suggested earlier by arnoutdv in #950), in two steps:
1) intruduce Read- and Write-functions like e.g. GetFmtInfoForCell(x, y) As myUDT
.. (where everything is routed over - and within these 2 helper-functions you are still using your UDT-Arrays)

Then test, that the Grid still shows the same behaviour as before
(whilst using only the 2 Helper-functions in the places where you read or write such extended fmt-info).

Once this was succesfull, change the "function-bodies" of these 2 Helpers-routines to a Collection, or Hashlist-based "sparse-matrix".
I don't expect for the performance to really suffer when you use a Collection inside these functions (instead of the prior UDT-arrays).

Olaf

[Nouyana]

Well, that would be possible for the UserControl version only. For the OCX (pre-compiled) all features must be enabled.

Yes, I understand. Let it be only the UserControl version. Just document it well to understand what functions will not be avaliable, and what features we should not use when developing using the OCX version and the MountainMan compilation utility. If we will try to use them, we will get a compilation error, so there will be no critical problems here.

In my opinion there should not be too many such functions. May be three or five of the most memory-consuming functions.

[Krool]

On the second thought I think using conditional compilation const for "DefaultFmtOnly" is a bad idea, even for UserControls only.
Because that UserControl is then having that setting for the whole project. And I can imagine that some projects want to use mixed grids. For the larger ones w/o custom cell formattings and for smaller ones with some colors, etc.
So, going forward there will be a "DefaultFmtOnly" (As Boolean) property which re-routes certain cell fmt info as described by Schmidt.
However, that's then nothing for the OCX 1.6. So someday there will be a 1.7 coming

[Nouyana]

So IVBFlexDataSource2 shall be extended as well with a GetComboCue/SetComboCue interface. (?)

For sure! And, please, think about the ItemData functionality.

[Krool]

About ItemData. in vsFlexGrid this can be integrated into the ComboItems property.
E.g.

#1234;Charlie|#5678;Arnold

would add two strings. "Charlie" and "Arnold" having item data 1234 and 5678.

However, what you do later on with it ? (extract via ComboItemData(ByVal Index As Long) property)

[Nouyana]

However, what you do later on with it ? (extract via ComboItemData(ByVal Index As Long) property)

This should be the Primary Key using for relationships between tables. If user changes Charlie to Arnold, it means that you should change 1234 to 5678 in the recordset.

[Nouyana]

Ok, Krool, Olaf, I need help.

I've almost made a universal CFlexDataSource class, that can be used with different types of recordsets in paged mode. I'll publish it in the Code bank when it's ready.

FlexDataSourceIssue.7z

Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
                                           ByVal Record As Long) As String
   AbsolutePosition = Record
   #If PAGED_MODE Then
      IVBFlexDataSource_GetData = PageArray(Field, Record)
      With objVBFlexGrid
'         Set .FlexDataSource = Nothing
'         .Row = .Row - PageInfo.OffsetStep
'         .Refresh
'         Set .FlexDataSource = Me
      End With
   #Else
      If RecordsetType = rstypVBSQLite Then
         IVBFlexDataSource_GetData = objRecordset.Columns(Field + 1)
      Else
         IVBFlexDataSource_GetData = objRecordset.Fields(Field)
      End If
   #End If
   PrevRecord = Record
End Function

I have an issue here. See the scheme below. When user is changing the grid's cell from row 8 to row 9 the PageArray gets new values using the AbsolutePosition property (first line in the GetData sub). But if I just pass these values to the IVBFlexDataSource_GetData using Field and Record arguments then user will move from row 8 to row 15, not 9. So, I need to use the offset. I keep the full ofset in the PageInfo.Offset and the offset between two adjacent pages in the PageInfo.OffsetStep. Keep in mind that no matter how many pages the user has moved, the cursor should be shifted by only one offset step. The question is, how to move the cursor without invoking the GetData function?

+---+---+---+   +-----------+
|   |   |   |1  |           |
|   |   |   |2  |           |
+---+---+---+   |           |
|   |   |   |3  |  OFFSET   |
|   |   |   |4  |   STEP    |
+---+---+---+   |           |
|  PAGE #1  |5  |           |
|   |   |   |6  +-----------+
+---+---+---+   +---+---+---+
|   |   |   |7  | PAGE CHAN-|7
|   |   |   |8  | GING AREA |8
+---+---+---+   +---+---+---+
| PAGE CHAN-|9  |   |   |   |9
| GING AREA |10 |   |   |   |10
+---+---+---+   +---+---+---+
                |  PAGE #2  |11
                |   |   |   |12
                +---+---+---+
                |   |   |   |13
                |   |   |   |14
                +---+---+---+
                |   |   |   |15
                |   |   |   |16
                +---+---+---+

[Krool]

New idea to overcome memory issue w/o a new property.

So, having a new TCELL as:

Private Type TCELL
Text As String
FmtgPtr As Long
End Type

The member FmtgPtr would be a dynamic allocated memory block.
If it's 0 (zero) then no memory is allocated = use default formatting values. If a cell get's custom formatted that memory block would be created and only freed when reducing rows/cols etc.

[Nouyana]

The member FmtPtr would be a dynamic allocated memory block.
If it's 0 (zero) then no memory is allocated = use default formatting values. If a cell get's custom formatted that memory block would be created and only freed when reducing rows/cols etc.
?

Sounds very good.

[Krool]

Yeah, just tried to suggest an alternative for a "compiler-switch-boolflag"...

As for "two different sets of code" - I wouldn't go there either -
perhaps instead I'd try to introduce a "sparse-matrix" instead (as suggested earlier by arnoutdv in #950), in two steps:
1) intruduce Read- and Write-functions like e.g. GetFmtInfoForCell(x, y) As myUDT
.. (where everything is routed over - and within these 2 helper-functions you are still using your UDT-Arrays)

Then test, that the Grid still shows the same behaviour as before
(whilst using only the 2 Helper-functions in the places where you read or write such extended fmt-info).

Once this was succesfull, change the "function-bodies" of these 2 Helpers-routines to a Collection, or Hashlist-based "sparse-matrix".
I don't expect for the performance to really suffer when you use a Collection inside these functions (instead of the prior UDT-arrays).

Olaf

Ok. Sounds also good.
However I think you suggest to access such a Collection by a "key", right?
But how to build the key? A string of #row/#col would not be good due to sorting. So I would need an unique id string to read/write the collection.

Such a Collection sounds also good as really only needed memory is created.
But yeah I have doubts about the performance as a lot of OERN needs to happen. (If coll item does not exist)

Also removing coll items when reducing rows/cols sounds not so optimal.

[Schmidt]

Ok. Sounds also good.
However I think you suggest to access such a Collection by a "key", right?
But how to build the key? A string of #row/#col would not be good due to sorting. So I would need an unique id string to read/write the collection.

Such a Collection sounds also good as really only needed memory is created.
But yeah I have doubts about the performance as a lot of OERN needs to happen. (If coll item does not exist)

Also removing coll items when reducing rows/cols sounds not so optimal.

The sorting-problem can be easily solved via an "indirect sort".
Which requires only the "maintenance" of a properly redim-preserved Long-Array...
as e.g. SortRowIndex() As Long

This array will eat-up only FlexGrid.Rows * 4 bytes of memory.

The maintenance requires (via helper-functions),
that after redim-preserving it (in larger chunks),
you will "fill up" the "added Row-Range" with the (increasing) index-values (of that Row-Range).

e.g. a SortRowIndex(0 to 4) should contain the Values 0 to 4 in its "value-slots" (when it is yet unsorted).

If you read "current cell-information", you will then always go the little indirection over the SortRowIndex -
(which barely costs any CPU-cycles)
instead of: Cols(CurColIndex).GetRowValue(CurRowIndex)
now you do: Cols(CurColIndex).GetRowValue(SortRowIndex(CurRowIndex))

Within your Sort-Routine, you should now access the current "Compare-Values" also via that indirect SortRowIndex-array of course -
and when you do so, all you need to do in case "a swap is needed", is to exchange the stored Long-Values within SortRowIndex-array.
(which should increase also the Sort-Performance, because no expensive copying of the real Cell-Data is needed)

Regarding performance of the "Sparse-Matrix-Collection" -
(which can always maintain its "original order" now, due to the indirect sorting-approach)...

In read-direction (when you re-render the grid) - you will only need to access the "currently visible Cells"
(which in the "worst-case of a full-screen-grid" amounts to something like 200Rows x 20Cols = 4000 exists-checks to perform).

This should be barely noticable either (given the larger task of "re-rendering all visible cells graphically via GDI").

I've never studied your current code - but if you have "separated Col-structures" already
(which I assume you have, to support user-column-reordering) -
then you don't even need a "RowIdx:ColIdx"-KeyPair for this sparse-matrix -
but now a single MS-Dictionary (with RowIdxes as Long-Typed-Keys) can act as a "sparse-vector" instead.
(one each, as a member of your TCol-struct, assuming you have those split-up already and hold them in a "Cols-Array").

The first, very performant "Exists-Check" (on a single TCol) would then be,
to check whether the TCol.RowExtFmtsDict member is "still Nothing" (does not have any extended Formats stored, for this Column).
If it is not Nothing, you can still check pretty fast for specific Cell-Formats (in this current Col) by using:

If Cols(CurColIndex).RowExtFmtsDict.Exists(SortRowIndex(CurRowIndex)) Then ...

I've used this approach already in an "early Grid I've developed 20 years ago" -
and it did not disappoint (performance-wise).

Although the explanation may seem "wordy and kinda complicated" - it is not really rocket-science -
and can be implemented in a straight-forward way
(and as said, will be even easier, when you already have the Col-Infos separated in their own structs).

Olaf

[Krool]

Update released.

Major performance boost for InplaceMergeSort (.Sort) / BubbleSortIter (FlexSortCustom; Compare event) and .RowPosition property.

The TCOL elements are now swapped by the pointers only and not by copying the content over.

[Nouyana]

The question is, how to move the cursor without invoking the GetData function?

There were no problem with invoking the GetData function. The problem was here:

Private Sub VBFlexGrid1_SelChange()
   oDataSource.AbsolutePosition = VBFlexGrid1.Row
End Sub

I've deleted this routine. Now I can move freely around the grid using arrows. This is my GetData Function:

Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
                                           ByVal Record As Long) As String
      Dim RealRecord As Long
      RealRecord = Record + IIf(PageInfo.PageNumber > 1&, PageInfo.Offset, 0&)
      AbsolutePosition = RealRecord
      PrevRecord = RealRecord

      IVBFlexDataSource_GetData = PageArray(Field, Record)
      
      If bNeedGridOffset Then
         bNeedGridOffset = False
         If PageInfo.PageNumber > 1& Then
            With objVBFlexGrid
               If bIsMovingUp Then
                  If PageInfo.PageNumber = 1 Then
                     .Row = .Row + PageInfo.OffsetLastStep
                  Else
                     .Row = .Row + PageInfo.OffsetStep
                  End If
               Else
                  If PageInfo.PageNumber = PageInfo.PagesCount Then
                     .Row = .Row - PageInfo.OffsetLastStep
                  Else
                     .Row = .Row - PageInfo.OffsetStep
                  End If
               End If              
               Call .CellEnsureVisible(FlexVisibilityCompleteOnly)
            End With
         End If
      End If
End Function

But now I have the next navigation issue - when I use the Ctrl+Up/Dn keys or a vertical scroll bar. I can't use objVBFlexGrid.Row when I'm scrolling. So I'm stuck. In my opinion it doesn't matter what to use - multiple recordsets or arrays. The scrolling problem should be the same.

Does it make sense to anybody? My previous post was unreplied. Does anybody have a working sample of paged mode FlexDataSource class?

@Olaf. I haven't started creating a separate thread yet. It's not about type of datasets or programming techniques. It's about how to move the cursor if the Row property is unavaliable.

FlexDataSourceTest.7z
CreatingDatabase.zip

[Krool]

For me a paged mode is simple: having a left and right button below the grid and a label of how many pages are in there. If it's only 1 page the left and right paging button are disabled.

[Schmidt]

For me a paged mode is simple: having a left and right button below the grid and a label of how many pages are in there. If it's only 1 page the left and right paging button are disabled.

I see it the same way - the cMyBinding-class does not know about any "paging" (internally) -
it only knows about the (record-reduced Sub-)Recordset internally
(the Grid reflecting only the RowCount which matches the reduced RecordCount of such a Sub-Recordset)

All the "Paged-Splitups" which result in a smaller "current Sub-Recordset" (along with the "Paging-Buttons" you mentioned) -
are happening outside (in the hosting Form).

And Ok, one might want to develop a UserControl for Paging (which then encapsulates the Left-Right-Buttons and a Label) -
to not clutter the Form too much...

Olaf

[Nouyana]

All the "Paged-Splitups" which result in a smaller "current Sub-Recordset" (along with the "Paging-Buttons" you mentioned) - are happening outside (in the hosting Form).

So, this is not a paged-mode Binding-class. Each programmer will have to independently handle all the events of deleting and changing records, moving through pages, searching, filtering and so on.

For me a paged mode is simple: having a left and right button below the grid and a label of how many pages are in there. If it's only 1 page the left and right paging button are disabled.

This is very inconvenient. What if user needs to select and copy 10 records to the clipboard, but five of them are on the first page and five - on the second? This is not the only example.

[Krool]

Nouyana,
soon the memory issue will be fixed, where formatting data will be allocated (HeapAlloc) at run-time when necessary only. (Via helper function GetCellFmtg/SetCellFmtg)

[Schmidt]

So, this is not a paged-mode Binding-class.

No, any normal "Rs-Binding-Class" (with support for Updates, Deletes, Inserts) will work *also* in paged mode.

Each programmer will have to independently handle all the events of deleting and changing records...

No - all of these actions will work on the "currently Bound Sub RecordSet" without problems.

...moving through pages, searching, filtering and so on.

And only that requires a few (trivial) extra-lines in the Form-Code
(where also the "Paging-Buttons" with their Click-Events reside anyways).

I guess it is your not (yet) existing experience with Disconnected-Rs (and the UpdateBatch-method they support)?

All the CRUD-actions will accumulate "within" such an Rs - and will be synchronized with the DB,
when you call Rs.UpdateBatch (in the Form).

And it doesn't matter at all in this mode, whether the (currently bound) Rs represents:
- a whole table
- or if it's just a "Where Clause filtered SubSet" (derived from that table)

As for Filtering (on top of the Page-based SQL Limit-Filter-conditions) ...
If the Where-Clause (on a larger table) is restricting the returned Rs.Recordcount so much,
that it sits below your given "Max-Records-Per-Page limit" - then the paging-buttons will simply be disabled -
but the Binding (on that heavily filtered Rs) will work "as always" (supporting Update, Deletes, Inserts) on that smaller Rs.

Olaf

[Nouyana]

It seems that the Light and non-Light versions are reversed.

Private Sub Command1_Click()
   With FlexGrid1
      .Font.Name = "Arial"
      .Font.Size = 14
      .Row = 1: .Col = 1
      .CellTextStyle = FlexTextStyleRaisedLight
      .Row = 2: .Col = 1
      .CellTextStyle = FlexTextStyleRaised
      .Row = 1: .Col = 2
      .CellTextStyle = FlexTextStyleInsetLight
      .Row = 2: .Col = 2
      .CellTextStyle = FlexTextStyleInset
   End With
End Sub

[Krool]

Update released.

Major memory reduction for default formatted cells. Once a cell gets custom formatted additional memory will be allocated.
It can only be freed by removing those cells or call .Clear method with FlexClearFormatting or FlexClearEverything.
The performance is tested to be nearly the same as before.

Major performance boost for AddItem/RemoveItem method. The TCOL elements are now moved by the pointers only and not by copying the content over.

[Nouyana]

Update released. Major memory reduction for default formatted cells.

The best results! Thank you, Krool!

[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.

With v1.6.15 and using FlexDataSource VBFlexGrid loads 1 750 000 records in 1,2 seconds or 40 times faster then MSHFlexGrid consuming the same memory.

[Krool]

It seems that the Light and non-Light versions are reversed.

Private Sub Command1_Click()
   With FlexGrid1
      .Font.Name = "Arial"
      .Font.Size = 14
      .Row = 1: .Col = 1
      .CellTextStyle = FlexTextStyleRaisedLight
      .Row = 2: .Col = 1
      .CellTextStyle = FlexTextStyleRaised
      .Row = 1: .Col = 2
      .CellTextStyle = FlexTextStyleInsetLight
      .Row = 2: .Col = 2
      .CellTextStyle = FlexTextStyleInset
   End With
End Sub

I don't get this one. You mean it's different then in MSFlexGrid?

[Nouyana]

You mean it's different then in MSFlexGrid?

Yes.

[Nouyana]

CheckBoxDrawMode property (run-time only) included with related CheckBoxOwnerDraw event...

Can you, please, give us an example for the CheckBoxOwnerDraw event handler or just list CheckBox-aware API functions to use/read about? May be some links.

[Nouyana]

It seems that the Light and non-Light versions are reversed.

I mean
FlexTextStyleRaisedLight should look like FlexTextStyleRaised, and
FlexTextStyleRaised should look like FlexTextStyleRaisedLight

The same with Insets.

[Krool]

I mean
FlexTextStyleRaisedLight should look like FlexTextStyleRaised, and
FlexTextStyleRaised should look like FlexTextStyleRaisedLight

The same with Insets.

Update released.

Fixed. Thanks for reporting.

FlexTextStyleRaised/FlexTextStyleInset needed indeed 3 layers instead of only 2 like for FlexTextStyleRaisedLight/FlexTextStyleInsetLight.

[Nouyana]

The Clip property doesn't work correctly. The left-bottom cell is not pasted. There is a Ctrl+C/Ctrl+V example on the GIF below. I tried to use my own ClipboardText routine with the Clip property and got the same result.

Private Sub Form_Load()
   Dim i As Integer
   With FlexGrid1
      .FocusRect = FlexFocusRectNone
      .FillStyle = FlexFillStyleRepeat
      .AllowUserEditing = True
      .AutoClipboard = True         ' AutoClipboard
      .Cols = 4
      .Rows = 1
      For i = 1 To 50
         .AddItem i & vbTab & "Name " & i & vbTab & i * 100 & vbTab & "text"
      Next i
      .TextArray(0) = "ID"
      .TextArray(1) = "NAME"
      .TextArray(2) = "SUM"
      .TextArray(3) = "DESCR"
   End With
End Sub
Private Sub Command1_Click()        ' This is my own routine, which
   FlexGrid1.Clip = ClipboardText   ' works with the same results
End Sub

[Krool]

The Clip property doesn't work correctly. The left-bottom cell is not pasted. There is a Ctrl+C/Ctrl+V example on the GIF below. I tried to use my own ClipboardText routine with the Clip property and got the same result.

Private Sub Form_Load()
   Dim i As Integer
   With FlexGrid1
      .FocusRect = FlexFocusRectNone
      .FillStyle = FlexFillStyleRepeat
      .AllowUserEditing = True
      .AutoClipboard = True         ' AutoClipboard
      .Cols = 4
      .Rows = 1
      For i = 1 To 50
         .AddItem i & vbTab & "Name " & i & vbTab & i * 100 & vbTab & "text"
      Next i
      .TextArray(0) = "ID"
      .TextArray(1) = "NAME"
      .TextArray(2) = "SUM"
      .TextArray(3) = "DESCR"
   End With
End Sub
Private Sub Command1_Click()        ' This is my own routine, which
   FlexGrid1.Clip = ClipboardText   ' works with the same results
End Sub

Excel uses vbCrLf and VBFlexGrid (or MSFlexGrid) uses only vbCr.

You can fix it by:

VBFlexGrid1.ClipSeparatorRow = vbCrLf

[Nouyana]

Excel uses vbCrLf and VBFlexGrid (or MSFlexGrid) uses only vbCr.

You can fix it by:

VBFlexGrid1.ClipSeparatorRow = vbCrLf

I have no such a problem with MSFlexGrid

VBFlexGridClip.zip

(use CommandButton for paste)

[Nouyana]

And it is something wrong with AutoSelection.


Code with vbCr (default):

      .AutoClipboard = True
      .ClipCopyMode = FlexClipCopyModeIncludeFixedAll
      .ClipPasteMode = FlexClipPasteModeAutoSelection

Code with vbCrLf:

      .AutoClipboard = True
      .ClipCopyMode = FlexClipCopyModeIncludeFixedAll
      .ClipSeparators = vbCrLf
      .ClipSeparatorCol = vbTab
      .ClipPasteMode = FlexClipPasteModeAutoSelection

[Krool]

And it is something wrong with AutoSelection.


Code with vbCr (default):

      .AutoClipboard = True
      .ClipCopyMode = FlexClipCopyModeIncludeFixedAll
      .ClipPasteMode = FlexClipPasteModeAutoSelection

Code with vbCrLf:

      .AutoClipboard = True
      .ClipCopyMode = FlexClipCopyModeIncludeFixedAll
      .ClipSeparators = vbCrLf
      .ClipSeparatorCol = vbTab
      .ClipPasteMode = FlexClipPasteModeAutoSelection

.ClipSeparators = vbCrLf is wrong.
Use ClipSeparatorRow = vbCrLf

.ClipSeparators is an old prop which defines row and col property at once. (1st and 2nd char)

It can be replaced by the newer props so a more lengthy row seperator can be used, such as vbCrLf for Excel.

[Nouyana]

.ClipSeparators = vbCrLf is wrong.
Use ClipSeparatorRow = vbCrLf

Yes, it was my misprint. But there is another problem now. Three rows were selected instead of two, and the (3,1)-cell was cleaned out.

[Krool]

Yes, it was my misprint. But there is another problem now. Three rows were selected instead of two, and the (3,1)-cell was cleaned out.

That's because Excel isn't clean and puts an vbCrLf at the end of the string, even there is nothing.
When you paste into notepad you can see it.

We would need an "BeforePaste" event which has a ByRef Text where we can fix this... Or another property which ignores last "empty row".

[Nouyana]

That's because Excel isn't clean and puts an vbCrLf at the end of the string, even there is nothing.
When you paste into notepad you can see it.

We would need an "BeforePaste" event which has a ByRef Text where we can fix this... Or another property which ignores last "empty row".

1. Can we somehow recognise the Excel buffer and process it right way?
2. Can we change the default row separator to vbCrLf? We have the incompatibility with MSFlexGrid here.

[Nouyana]

   If (BufferColsCount * (BufferRowsCount - 1) + 1 = BufferCellsCount) _
   And (BufferSeparator = vbCrLf) And (Len(LastBufferCell) = 0) Then
      ThisIsExcelBuffer = True
   End If

[jpbro]

We would need an "BeforePaste" event which has a ByRef Text where we can fix this...

I'm a big fan of Before* events with a Cancel parameter, e.g.:

Public Event BeforePaste(ByRef Text As String, ByRef Cancel As Boolean)

Setting Cancel = True will abort the paste operation.


Another advantage of a BeforePaste event is that you can "massage" the incoming data if necessary (for example, de-formatting currency strings, normalizing date string, etc...).