How to assign array to a MSflexgrid

[thiru_rajamani]

I have a array as delared following, but I do not know how to assign them to a flexgrid.
I have a MSflesxgrid with 5 rows and 5 columns.


Private Sub UserForm_Initialize()
Dim i, j
Dim xx(5, 5)

For i = 0 To 4
For j = 0 To 4
xx(i, j) = i & j
Debug.Print xx(i, j)
Next
Next
End Sub

[si_the_geek]

You can set the text of the cells using TextMatrix() in a loop, like this:

For i = 0 To 4
  For j = 0 To 4
    MsFlexgrid1.TextMatrix(i,j) = xx(i, j)
  Next
Next

Depending on how your grid is set up (fixed rows/columns etc) you may want to change (i,j) to (i+1,j) or (i,j+1) etc.

You could do it in the same loops you already have (just replace the "Debug.Print xx(i, j)" with the TextMatrix line).


By the way, you should declare your variables with data types, as it eliminates chances for hidden errors/mistakes. In this case it should be:

Dim i as Integer, j as Integer
Dim xx(5, 5) as String

[thiru_rajamani]

I understand. and I am using the same what you suggested. But I feel it consumes more time. Is there anyother way to input the data quickly ?

[si_the_geek]

The easy way of speeding up any work with a Flexgrid is to stop the re-painting of the control every time something changes.

Just put this before the loop:
MsFlexgrid1.Redraw = False

And this after the loop:
MsFlexgrid1.Redraw = True

It should be 10-50% quicker, depending on the formatting and contents of the grid.


A faster way of actually putting the data in is to use the .Clip property of the grid, but to do that you need to format the text first (a tab between each column, and a CR between each row), which could possibly take more time than just putting it straight into the grid.

In your case you could do this:

Dim sClip as String
sClip = ""
For i = 0 To 4
  For j = 0 To 3
    sClip = sClip & xx(i, j) & vbTab
  Next j
  sClip = sClip & xx(i, 4) & vbCr
Next i
With MsFlexGrid1
  .Row = 0    '("select all")
  .Col = 0
  .RowSel = 4
  .ColSel = 4
  .Clip = sClip  '("paste")
  .RowSel = 0  'reset selection
  .ColSel = 0
End With

Obviously this method lends itself more to situations where you can get the data in the right format to start with.