VBFlexGrid Control (Replacement of the MSFlexGrid control)

[Karl77]

A next question.

In Excel etc., when a cell is selected, the fixed rows/cols do highlight the cell coordinate.
Like this:

And when the control lost the focus, it should go away.

Would it be possible to have such a function inside the control?

[gibra]

Is there an edit function in the works?
I mean a textbox in a cell.

Already there. See:

http://www.vbforums.com/showthread.p...=1#post5290225

[Karl77]

Already there. See:

Aha, very cool.
A "bit" more than I wanted, but even better.
Thank you.

[Krool]

A next question.

In Excel etc., when a cell is selected, the fixed rows/cols do highlight the cell coordinate.
Like this:

And when the control lost the focus, it should go away.

Would it be possible to have such a function inside the control?

Yes, you can implement such feature by following code addition into your Form:

Private Sub VBFlexGrid1_GotFocus()
Call SetRowColColorIndicator
End Sub

Private Sub VBFlexGrid1_LostFocus()
Call SetRowColColorIndicator(True)
End Sub

Private Sub VBFlexGrid1_RowColChange()
Call SetRowColColorIndicator
End Sub

Private Sub SetRowColColorIndicator(Optional ByVal Clear As Boolean)
Static LastRow As Long, LastCol As Long, InitDone As Boolean
With VBFlexGrid1
If InitDone = True Then
    If LastRow > -1 Then .Cell(FlexCellBackColor, LastRow, 0) = -1
    If LastCol > -1 Then .Cell(FlexCellBackColor, 0, LastCol) = -1
End If
If Clear = False Then
    If .FixedCols > 0 Then
        .Cell(FlexCellBackColor, .Row, 0) = vbRed
        LastRow = .Row
    Else
        LastRow = -1
    End If
    If .FixedRows > 0 Then
        .Cell(FlexCellBackColor, 0, .Col) = vbRed
        LastCol = .Col
    Else
        LastCol = -1
    End If
    InitDone = True
Else
    LastRow = -1
    LastCol = -1
    InitDone = False
End If
End With
End Sub

[Krool]

double posted by error

[Karl77]

Private Sub SetRowColColorIndicator

That works great.
I thought of adding such a feature into the control itself.
Now as I see it is a few lines only, it is very ok to have it in the form code.
Thank you.

Regarding the edit field the same applies.
It is, as said, not so much to do in the form code.

[Krool]

Update released.

Internal GDI leak fixed. E.g. happen when the grid is loaded and unloaded dynamically many times.

[Krool]

Important update released.

The internal TEXTMETRIC structure was declared as ANSI and not as WIDE.
This bug has no functional effect on Unicode since the corrupted declaration is below the used section.
However, the WIDE API expects a larger structure than declared which is then a memory risk because the API will fill data beyond the declared structure. (not good)

[Jonney]

Important update released.

The internal TEXTMETRIC structure was declared as ANSI and not as WIDE.
This bug has no functional effect on Unicode since the corrupted declaration is below the used section.
However, the WIDE API expects a larger structure than declared which is then a memory risk because the API will fill data beyond the declared structure. (not good)

also affects CommonControls?

[Krool]

also affects CommonControls?

Yes, but only ListBoxW Style <> Normal and new FontCombo.

[Krool]

Update released.

Major performance boost in the internal GetHitTestInfo function.

This boost is only noticeable in very large grids, e.g. 500,000 rows.

Now the GetHitTestInfo function is same fast in a 500,000 rows grid then in a 100 rows grid.

Problem was if X or Y is below 0 then unnecessary iterations were performed. And the larger the grid the slower it gets. But this got now fixed.

[Krool]

Minor bugfix (1px miscalc) related to todays release for performance boost in the internal GitHitTestInfo function.

Worth mention is that the overall performance is boosted as the internal function is called often internally.
Therefore a 500,000 rows grid performs now quite good.

Next step is then virtual mode via custom data source to eliminate the long populate for such big row numbers.

[Krool]

I'm working currently with the FlexDataSource functionality where a custom data source can be set via IVBFlexDataSource interface.

As previously discussed in page 1 it's only for display of the viewport. (like virtual mode)
Therefore the Find and Sort functionality must be turned off as this is managed by the custom data source.

However, what about other functionalities like .Clip, .FormatString, .AddItem, .Clear, .TextMatrix ?

There would be a little overhead as it needs to be evaluated whether to call IVBFlexDataSource::GetData or read internal TCELL.
Or also disable .Clip, .FormatString etc. when custom data source is set ?

Anyhow, .TextMatrix needs to be kept as setting .TextMatrix could then invoke IVBFlexDataSource::SetData. If not there where else ?

Maybe Schmidt (Olaf) can advise how this works exactly in the vsFlexGrid.

[Schmidt]

I'm working currently with the FlexDataSource functionality where a custom data source can be set via IVBFlexDataSource interface.

As previously discussed in page 1 it's only for display of the viewport. (like virtual mode)
Therefore the Find and Sort functionality must be turned off as this is managed by the custom data source.

However, what about other functionalities like .Clip, .FormatString, .AddItem, .Clear, .TextMatrix ?

There would be a little overhead as it needs to be evaluated whether to call IVBFlexDataSource::GetData or read internal TCELL.
Or also disable .Clip, .FormatString etc. when custom data source is set ?

Anyhow, .TextMatrix needs to be kept as setting .TextMatrix could then invoke IVBFlexDataSource::SetData. If not there where else ?

Maybe Schmidt (Olaf) can advise how this works exactly in the vsFlexGrid.

Can't say much with regards to .TextMatrix-Delegations (in both, read- and -write-directions),
because the whole point of an external DataSource is, to "not bother with storing data inside the Grid via TextMatrix".

And yes, sorting is done on the external Container (usually an Rs) - the only thing from the Grid we used
to trigger that, was "a Header-Click-Event".
And the "write-direction" of the external DS (::SetData) was triggered by "InCell-Edits" (not .TextMatrix)
(which the "big FlexGrid" allows).
Formatting was done via FlexGrid-Methods though (but not much - alternating Row-Colors mostly, along with ColWidth-settings).

We had only one case, where we needed "extensive Formatting" - and for that case we enabled
*also* the OwnerDraw-Mode on the Flex (in addition with using the external DS).

Olaf

[miquel]

Great work Krool. I have replaced completely the MS flexgrid with yours and it's working great.

Update, included ClipMode property that determines whether to include (default) or to exclude hidden cells in a clip command.

I tested this funcionality in the EXE version but in my programs I am using the ActiveX version and it is not included yet. Would you be so kind to add it in future versions ?

Thanks again, Miquel.

[Krool]

Can't say much with regards to .TextMatrix-Delegations (in both, read- and -write-directions),
because the whole point of an external DataSource is, to "not bother with storing data inside the Grid via TextMatrix".

And yes, sorting is done on the external Container (usually an Rs) - the only thing from the Grid we used
to trigger that, was "a Header-Click-Event".
And the "write-direction" of the external DS (::SetData) was triggered by "InCell-Edits" (not .TextMatrix)
(which the "big FlexGrid" allows).
Formatting was done via FlexGrid-Methods though (but not much - alternating Row-Colors mostly, along with ColWidth-settings).

We had only one case, where we needed "extensive Formatting" - and for that case we enabled
*also* the OwnerDraw-Mode on the Flex (in addition with using the external DS).

Olaf

Currently an intrinsic in-cell editing is not yet developed.
But for sure I would include ::SetData to be prepared interface wise already.
But can you maybe test in vsFlexGrid if a .TextMatrix delegation does fire a ::SetData ?
Because most MSFlexGrid users (which might migrate to VBFlexGrid) handle there in-cell edit by placing a external TextBox in cell location and need .TextMatrix therefore and for this case a call to ::SetData would be needed.

Another question: is the 'Record' Long param in ::GetData 1-based? The 'Field' param is for sure 0-based.

Thanks

Great work Krool. I have replaced completely the MS flexgrid with yours and it's working great.



I tested this funcionality in the EXE version but in my programs I am using the ActiveX version and it is not included yet. Would you be so kind to add it in future versions ?

Thanks again, Miquel.

Yes, of course. In version 1.2.
But first a few other features need to be ready before.

[Schmidt]

- Interface-Callback and mapping-wise it is all Zero-Based...
- FG.TextMatrix, as well as FG.Clip (and probably all other stuff) is routed to GetData/SetData of the DS-implementation, as soon as this was set.

Here is Test-Code for a small Form (which only contains a VSFlex, named F).

Private SArr As New cStringArrayDS

Private Sub Form_Load()
  F.ColWidth(-1) = 1500
  SArr.BindTo F, 3, 10
  
  Dim x, y
  For y = 0 To SArr.Rows - 1: For x = 0 To SArr.Cols - 1
    SArr.Matrix(x, y) = "Row " & y & ", Col " & x
  Next x, y
  
  Debug.Print F.TextMatrix(2 + F.FixedRows, 2)
  F.TextMatrix(2 + F.FixedRows, 2) = "2,2"
  Debug.Print F.TextMatrix(2 + F.FixedRows, 2)
End Sub

Here a ScreenShot (with FixedCols an FixedRows both at 1):


As one can see above, the TextMatrix-call is only matching with the SArr.Matrix-call,
when the current F.FixedRows are added to the Index-Position (in case F.FixedRows is > 0).
Only in x-Direction (addressing Cols), the zerobased indexes for SArr.Matrix and F.TextMatrix are matching -
so the DataSource ignores any current FixedRows-Header-Settings, whilst .TextMatrix wants the Header included in the x, y Offsets.


Ok, below is Code for the Binding-Class (named cStringArrayDS), which encapsulates a zerobased StringArray as the DataSource.

Option Explicit

Implements IVSFlexDataSource
Private mArr() As String

Public Sub BindTo(F As VSFlexGrid, ByVal Cols As Long, ByVal Rows As Long)
  ReDim mArr(0 To Cols - 1, 0 To Rows - 1)
  F.FlexDataSource = Me
End Sub

Public Property Get Rows() As Long
  Rows = UBound(mArr, 2) + 1
End Property
Public Property Get Cols() As Long
  Cols = UBound(mArr, 1) + 1
End Property

Public Property Get Matrix(ByVal xIdx As Long, ByVal yIdx As Long) As String
  Matrix = mArr(xIdx, yIdx)
End Property
Public Property Let Matrix(ByVal xIdx As Long, ByVal yIdx As Long, RHS As String)
  mArr(xIdx, yIdx) = RHS
End Property

'simple IVSFlexDataSource-implementation, which works against the internal array
Private Function IVSFlexDataSource_GetFieldName(ByVal Field As Long) As String
  IVSFlexDataSource_GetFieldName = "ColHdr_" & Field
End Function

Private Function IVSFlexDataSource_GetFieldCount() As Long
  IVSFlexDataSource_GetFieldCount = UBound(mArr, 1) + 1
End Function
Private Function IVSFlexDataSource_GetRecordCount() As Long
  IVSFlexDataSource_GetRecordCount = UBound(mArr, 2) + 1
End Function

Private Function IVSFlexDataSource_GetData(ByVal Field As Long, ByVal Record As Long) As String
  IVSFlexDataSource_GetData = mArr(Field, Record)
End Function
Private Sub IVSFlexDataSource_SetData(ByVal Field As Long, ByVal Record As Long, ByVal newData As String)
  mArr(Field, Record) = newData
End Sub

HTH

Olaf

[Krool]

That's strange that the vsFlexGrid doesn't offset the FixedCols in the FlexDataSource.
When using the applying the DataSource (ADO recordset) then the fixed cols are also taken in consideration.
But for sake of compatibility I will replicate the behavior as like in the vsFlexGrid.

Thanks Schmidt for testing/pointing out the details.

[ChrisE]

Hi,

I like to see the selected Row/Col change in width.

here is what I added

'in the Form Header section:
Private DoColumnsResize As Boolean

'also 
Private Sub Form_Activate()
   DoColumnsResize = True
End Sub

'the Function:
Private Function mFlex_ColumnResize() As Boolean

   Dim i As Long
   Dim w As Single
   Dim Row As Long
   Dim Col As Long
   Static backColor As Long
   
   
      With VBFlexGrid1
         .Redraw = False
         Row = .Row
         Col = .Col
         'play with Value / 12 for width
         w = (.Width - .ColWidth(0) - 290) / 12
         .Row = 0
         For i = 1 To .Cols - 1
            .Col = i
            If i <> Col Then
               If .ColWidth(i) <> w Then
                  .ColWidth(i) = w
                  .CellForeColor = &H80000008
               End If
            Else
               If .ColWidth(i) <> w * 2 Then
                  .ColWidth(i) = w * 2
                  .CellForeColor = vbBlue
               End If
            End If
         Next
         .Col = 0
         For i = 1 To .Rows - 1
            .Row = i
            .CellBackColor = .BackColorFixed
            If i = Row Then
               .CellBackColor = &H80000018
            End If
         Next
         .Row = Row
         .Col = Col
         .Redraw = True
      End With
End Function

'and:
Private Sub VBFlexGrid1_RowColChange()
   'set the widht
      If DoColumnsResize Then
         DoColumnsResize = False
         mFlex_ColumnResize
         DoColumnsResize = True
      End If
End Sub

just my taste

regards
Chris

[Krool]

Important bugfix concerning info tips and in-place label tips.

The internal tooltip had two problems. I described the second problem earlier in this thread. Both were solved by adding the WS_EX_TRANSPARENT style bit. It allows all mouse events to pass through.
1. When control A has focus and user hovers over the flexgrid B which shows a info tip or in-place label tip and the user clicks on that tip then the focus was not changing from A to B.
2. In Win10 the mouse wheel scroll did not work when the mouse hovered over an in-place tip.

[Krool]

FG.TextMatrix, as well as FG.Clip (and probably all other stuff) is routed to GetData/SetData of the DS-implementation, as soon as this was set.

Can you also please check if, once an DS-implementation is set, it is still possible to modify the '.Rows' property or to call 'AddItem'/'RemoveItem' because that would interfere in the number of records indicated by the DS-implementation and could raise an outer bound error?
Thanks

[Krool]

Update released.

Beside other small new features 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]

Included optional argument Direction in the FindItem function. (Down or Up)
Now it's very easy to use and apply the "Find and replace" Common Dialog with the .FindItem function.


Also the FindItem cannot be used anymore now on a fixed row. This was not a problem at all without the Wrap or DirectionUp search.
But now you wouldn't want to search from bottom to up and get a hit by the column name by coincidence..
So that limitation is cosmetic to avoid this issue right away.
VBFLXGRD11.OCX will not be commited with that cosmetic limitation since it does not have Wrap or DirectionUp anyway.

[dreammanor]

In FarpointSpread and my own Spread, ColHeaderRows and RowHeaderCols are separate from normal grid cols and rows, that is, changing ColHeaderRows and RowHeaderCols does not affect normal grid cols/rows. This design makes the source code and data storage structure more complicated, but it's also more user-friendly and more flexible.

[Krool]

VBFLXGRD12.OCX is now released.

[ChenLin]

Hi Krool, if I want to use another free registration OCX, how do I generate tblid in RES? Thank you!

[Eduardo-]

Hi Krool, if I want to use another free registration OCX, how do I generate tblid in RES? Thank you!

You can use ResourceHacker to edit the .res file.

[Eduardo-]

I) Hello Krool, I want to report two differences that I found between VBFlexGrid and MS ones:
The first is how it draws the vertically centered texts when they don't fit in the row:



II) And the second is that in MS FlexGrids the CellFontSize value take some "stepped" values, but in the VBFlexGrid it doesn't.

For example, I select a cell, then set the size FlexGrid1.CellFontSize = 22
When I select the cell again, in the MS ones when I get back that value I get 22.2, but with the VBFlexGrid I get 22.
I could say that the VBFlexGrid is more "exact", but still that makes a difference that in some cases cause visible differences (that it would be long to explain now).

III) I have another question: is there any way to know what cells have particular formatting without having to navigate each one? I mean something like a property Get FormattedCellsMatrix(Row, Col) As Boolean

TIA

[Krool]

I) Hello Krool, I want to report two differences that I found between VBFlexGrid and MS ones:
The first is how it draws the vertically centered texts when they don't fit in the row:

Hello Eduardo,
thanks for your report. I will process issue 1 first. (step by step)

Yes. I was certainly aware and could reproduce the behavior now again. Let's explain little bit:

If you set on the MS control the WordWrap property to True it will behave exactly like VBFlexGrid. Only if WordWrap is False the behavior is "little bit" different.
That's because the MS control will use DT_SINGLELINE for DrawText when WordWrap is False.
I explicity avoided that because else having WordWrap or multiline text are two things and should not be dependent on. I wanted to allow multiline text even if WordWrap is False.

Below would be the code modification marked as red in internal sub 'DrawCell' (not yet implemented; to be discussed) to replicate exact behavior for both WordWrap False/True:

If PropWordWrap = True Then Format = Format Or DT_WORDBREAK Else Format = Format Or DT_SINGLELINE
[...skipped part for here...]
Dim CalcRect As RECT, Height As Long, Result As Long
Select Case Alignment
    Case FlexAlignmentLeftCenter, FlexAlignmentCenterCenter, FlexAlignmentRightCenter, FlexAlignmentGeneral
        LSet CalcRect = TextRect
        Height = DrawText(hDC, StrPtr(Text), -1, CalcRect, Format Or DT_CALCRECT)
        If PropWordWrap = True Then
            Result = (((TextRect.Bottom - TextRect.Top) - Height) / 2)
        Else
            Format = Format Or DT_VCENTER
        End If
    Case FlexAlignmentLeftBottom, FlexAlignmentCenterBottom, FlexAlignmentRightBottom
        LSet CalcRect = TextRect
        Height = DrawText(hDC, StrPtr(Text), -1, CalcRect, Format Or DT_CALCRECT)
        If PropWordWrap = True Then
            Result = ((TextRect.Bottom - TextRect.Top) - Height)
        Else
            Format = Format Or DT_BOTTOM
        End If
End Select
If Result > 0 Then TextRect.Top = TextRect.Top + Result

As you see here comes DT_SINGLELINE in place which I want to avoid. DT_BOTTOM and DT_VCENTER are only supported when DT_SINGLELINE is set.

Currently always DT_TOP is applied in both (no DT_SINGLELINE at all) and the vertical adjustment is done manually. That's no issue as DrawText will be clipped to not go beyond the cell rectangle. However, in order to have the manual vertical adjustment to work with DT_TOP I would need to have the text rectangle less beyond the cell rectangle, which would look like this:

Possible solution (fix) would be to create a region and clip the DC accordingly while doing that.
That would solve the issue?
Or going the exact replicate behavior and use DT_SINGLELINE ? I want to discuss this first before implementing.

Thanks

[Eduardo-]

OK, Krool. It is not an "issue", at least not now.
Showing texts in the cells exceeding the height of the cells as I did is not a normal thing to do.
I found this difference only by chance while testing other things, but this code is not in an actual real life program.
Now thinking in your explanation of wordbreak <> multiline it makes sense to perhaps leave it as it is now.
Or wait to see if it is an issue for someone else. Now I can't think of a situation where it could be actually a problem.

Or perhaps (I'm not saying you to do it, but just something to think about whether it is a good idea or not) you could have a property where can be set compatibility flags, and where such differences could be avoided.
Let's suppose there are a couple more of this improvements that change the old behavior.

It could be a property named for example OldFlexBehavior.
It would have a Long type value. The default value being 0.
Let's suppose there are three flags: 1, 2 and 4.
7 means full MS emulation.

Then if (mOldFlexBehavior and 1) = 1 it means when WordWrap is False to write in single lines as MS does.

But the question is: does it worth the effort? Will someone ever benefit from that?
If it were me, perhaps I would wait until someone actually needs it.

[Krool]

However, in order to have the manual vertical adjustment to work with DT_TOP I would need to have the text rectangle less beyond the cell rectangle, which would look like this:

Possible solution (fix) would be to create a region and clip the DC accordingly while doing that.

I think that way would be best to have same behavior when WordWrap is True or False. But allowing multiline always (no DT_SINGLELINE when WordWrap is False)
My last concern is that this clipping region for every cell would slow down the drawing..

EDIT:
Or keep as is.. and just include a new MultiLine property which controls DT_SINGLELINE to use or not.
Then if WordBreak is False and MultiLine is False also DT_VCENTER and DT_BOTTOM come into play to have same behavior as MS. Else have "manual" vertical offset adjustment with DT_TOP. (Like now as is, support multiline text)
This way it's separated and more specific under control.

[Eduardo-]

My last concern is that this clipping region for every cell would slow down the drawing..

Yes, I don't think that would be a good idea.

EDIT:
Or keep as is.. and just include a new MultiLine property which controls DT_SINGLELINE to use or not.
Then if WordBreak is False and MultiLine is False also DT_VCENTER and DT_BOTTOM come into play to have same behavior as MS. Else have "manual" vertical offset adjustment with DT_TOP. (Like now as is, support multiline text)
This way it's separated and more specific under control.

I vote for that.

[Krool]

I vote for that.

Great. I will call the property 'SingleLine'. So False being the default.
I just need to ensure to make a restriction that WordWrap and SingleLine cannot be both set to True.
That's it. Update will follow accordingly soon after careful testing. (Also that LabelTip will be in sync etc.)

[ChenLin]

You can use ResourceHacker to edit the .res file.

Thank you. I want to know how to get tblid.

[Eduardo-]

Thank you. I want to know how to get tblid.

What is tblid?

The XML information needed in the manifest for SxS installations?

[ChenLin]

Yes, should this information be exempt from registration? And CLSID。

Please see the picture, which is the red line information.

[Eduardo-]

There are several programs that people use for getting that information.
I use one that was made by dilettante (a forum member) but it is not available any more in the web site it used to be because it is not currently developed and not published any more from the original author (it was available with source code).
It is called MMM or "Make My Manifest". I found a version here in GitHub.
There is also another one called UMMM. It is from wqweto, another forum member.

There are also others that are commercial. So far I only used MMM. It is not perfect. I have a couple of versions and both produce (different) errors, mainly because of Service Packs that are installed. But I manage to make them work by editing the XML text by hand.
Perhaps UMMM is better.

Or perhaps someone else has another one to recomend.

[ChenLin]

Neither of them tested successfully, gave up testing, and now uses the shell "regsvr32 /s " & App.Path & "\MSHFLXGD.OCX", vbHide method in the program startup.

Still thank you for your reply.

[Eduardo-]

Neither of them tested successfully, gave up testing, and now uses the shell "regsvr32 /s " & App.Path & "\MSHFLXGD.OCX", vbHide method in the program startup.

Still thank you for your reply.

But that does a very different thing. That registers the ocx in Windows. It is the same that an installer would do.
And I don't know if that would work in 64 bits Windows.

The manifest thing is somethign else. It is not registering the ocx in Windows, but making it work without registering it (and just with that program).

[ChenLin]

Maybe I didn't use it well, it just generated a file like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0" xmlns:asmv3="urn:schemas-microsoft-com:asm.v3">
    <assemblyIdentity name="BVO Computing Services.MMM" processorArchitecture="X86" type="win32" version="0.12.0.311" />
    <description>XCopy Packager for VB 6.0</description>
    <file name="MMM.exe">
    </file>
</assembly>

it doesn't include files like TYPEID or clsid.