MSHFlexGrid issues

02-13-2005, 07:16 AM
Hi ppl :)

I love, and try making use of FlyGuy's flexgrid functions ( like many others. :D

But I have some problems which are not addressed within those cool functions. I beleive that many other ppl also fighting with the same problems, so it would be great help if FlyGuy, or some other experts could give it a push. :rolleyes:

(First, I use MSHFlexGrid for presenting my hierarchical data, already in ADODB.Recordset.) Let me list the troubles down:

1. Hierarchical data:
FlyGuy's multiple-sort (and filtering?) routines don't work on hierarchical data..! So, my solution for this is, reloading the data from the database using a new "Order By" clause, everytime user askes for a new multi-sorting. This is obviously is very time consuming.

2. Sorting dates:
Sorting "date" fields doesn't work. HFlexGrid treat them as if they were "strings". Hence, "11 January 2005" for instance is placed above "20 May 2004" (ascending).

3. Column formatting:
May be it's my incompotency, but formatting number, and date columns is a kinda pain .. for me. I want them justified right, and for the numbers, 0.xx digits, for example. Still could not figure out the correct use of FormatString! (I may need to see some good examples.)
But, some automatic way for formatting columns, like checking the field's data type before the data load, and pre-constructing the format string would be much efficient. I tried it my self, but unfortunately, not that experienced to come out with a good solution that I could post here for the other clumsies. :(

Thats all for now... I would appreciate if any of you experts care for posting some usable solutions for the headaches above... :)

02-13-2005, 11:06 AM
Is there a special reason for not using the normal MSFlexGrid?

02-13-2005, 11:59 AM
Well, I thought it is the way for displaying my data in a grouped manner.

Can I do it with Flexgrid also? :confused: (If so, I'm struggling for months for nothing!)

02-13-2005, 04:54 PM
Sorry missed the line in which you were saying that you use hierarchical data. :o
I never used the MSHFlexGrid myself nor do I ever do DB programming.
So I really don't have clue about the hierarchical mode of the grid and how to handle this.
For years now I use the vsFlexGrid control from ComponentOne (, it's a big improvement over the original MS(H)FlexGrid with much more functionallity.

02-14-2005, 02:43 AM
I never used the MSHFlexGrid myself nor do I ever do DB programming.
So I really don't have clue about the hierarchical mode of the grid and how to handle this.


Anyways, lets forget about hierarchical data; can you help with

- Sorting dates, and
- Formatting number and date columns

for MSFlexGrid. I guess it would work with MSHFlex also.

:) (This request is sure open for the other experts around... :p )

02-15-2005, 02:16 AM
1. See the updated CompareValues function in the .. thread
2. It's not that hard :-)

' Procedure : FG_FormatColumn
' DateTime : 15-2-2005
' Author : Flyguy
' Purpose : Formatting a complete column
' FG_FormatColumn MSFlexGrid, 2, "dd MMM yyyy"
' FG_FormatColumn MSFlexGrid, 1, "##,##0.0"
Public Sub FG_FormatColumn(myFG As MSFlexGrid, lColumn As Long, sFormatString)
Dim lRow As Long
Dim sText As String

With myFG
.Redraw = False
For lRow = .FixedRows To .Rows - 1
.TextMatrix(lRow, lColumn) = Format(.TextMatrix(lRow, lColumn), sFormatString)
Next lRow
.Redraw = True
End With
End Sub

02-15-2005, 05:35 AM
1. See the updated CompareValues function in the .. thread
2. It's not that hard :-)

U r great..!

Yes, it looks not that hard. Actually, I was after some solution using FormatString property. Because I thought in that case, all formatting would be done by the control itself freeing us from reprocessing the whole text array. But if this is the solution, I sure will go for it. :D

I saw the updated version of your CompareValues, and will give it a try it right away.

Thanks a lot for your dedication and support, for the name of all who r making use of your functions. :)


02-15-2005, 06:27 AM
I saw the updated version of your CompareValues, and will give it a try it right away.

I tested it with date, double and integer data (generated data using the code below), and it works perfect... ;)


Private Sub Form_Load()
End Sub

Private Sub generate()
Dim lRow As Long, lCol As Long
' Just fill the grid with some random data
With MSHFlexGrid1 'see its HFlex..!
.Cols = 6
.Rows = 30
'generate date data. (it goes to 2004 since given 60.)
For lRow = .FixedRows To .Rows - 1
.TextMatrix(lRow, 1) = Format(DateAdd("d", -Int(rnd * 60), Now), "dd/mm/yy")
'generate double data
For lRow = .FixedRows To .Rows - 1
.TextMatrix(lRow, 2) = Format(Int(rnd * 10) / (Int(rnd * 100) + 1), "0.000")
'generate integer data
For lRow = .FixedRows To .Rows - 1
.TextMatrix(lRow, 3) = Int(rnd * 10)
For lRow = .FixedRows To .Rows - 1
.TextMatrix(lRow, 4) = Int(rnd * 15)
For lRow = .FixedRows To .Rows - 1
For lCol = .FixedCols + 4 To .Cols - 1
.TextMatrix(lRow, lCol) = Int(rnd * 20)
Next lCol
Next lRow
End With
End Sub

02-25-2005, 01:52 PM
Flyguy. (And all others interested.)

May look a bit late, but I just realized 2 different bugs in your last codes. :eek:

1. First is in the ShellSortMultiColumn module. It doesnt work if the data is only 2 rows. Sorting 2 rows-only data may sound unnecessary, but this is not the case... Nobody can know if the data will come up with 2 rows or more, especially if you are applying filters.

So, you can solve this problem by adding the following lines in ShellSortMultiColumn

'add below this line:
lLBound2 = LBound(sArray, 2)

'code to add:
If (lUBound - lLBound) = 1 Then
If compareValues(sArray, lLBound, lUBound, lNofColumns, aColumns, aOrder) Then
swapLines sArray, lLBound, lUBound, lLBound2, lUBound2
End If
End If

'add above this line:
lHValue = (lUBound - lLBound) \ 2

2. Second problem is in compareValues, and has more complications, actually. In the last addition, you used IsDate and IsNumber functions to handle date and number data.
But Microsoft got some surprises for us.!

IsDate function fails with numeric data passed in strings..! That is, IsDate says that "0,09" or "1,40" is date! It assumes it as date type, so typecasts it to long. This causes 0,09 be greater than 1,40 (because 36770 > 14611) !!! :whoops: As the comparison fails, so does the sorting.

That's not all. Also IsNumeric assumes, say, "14.02.05" is numeric! This causes it to interpret the date passed as 14022005, or 140205, depending on the format used.!

What happens then? "14.02.05" is greater than "12.11.04" :whoops: , because 140205 > 121104... So the sort fails again...
This is another "Microsoft Joke"..!

I replaced IsDate and IsNumeric checkings with some ugly! code to walk around. But they are so ugly, that I'm ashamed to write them here, :o though its totally MS guys fault (not mine). Hoping you can find some better way to check the types out.

3. Also, please check my last post of sorting functions for HFlexGrid . (
They really took some time and effort, and wanted to share it with enthuriasm. But got no responses at all (even from Flyguy). :( (Am I the only one having sorting trouble with hierarchical data?)

Have fun.

02-27-2005, 07:11 AM
1. Sorry I never tested the routine in that detailed mode.

2. I would never use such a generic routine myself. Normally you know what kind of date you have in each column so you can use the apropiate compare method.

3. I read your post, but I really don't know what you expect from me

Just to make some things clear

I only used the MSFlexGrid in the time I used VB5 (a long time ago)
Because of lacking functionallity of the MSFlexGrid, I have a subscription to the commercial vsFlexGrid control for since 1999.
Only because of a lot of questions about the MSFlexGrid on this forum I decided to try to mimic some functionallity of the vsFlexGrid which is lacking in the MSFlexGrid.

The MSHFlexGrid I really never ever used. It could be have been of some use if the hierarchical mode was available in unbound mode too.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum