Hello fellow iGrid users!

I am new to using iGrid and the question may be quite silly, so beg your pardon.

Is there any way to retrieve iGrid values into an array in one statement without enumerating through, in a way similar to Range.Value in Excel or DAO's GetRows?
Likewise, is it possible to populate a grid from an array, like with MSForms.ListBox.List property ?

environment: Excel 2007+ add-in

We try to keep iGrid as small (and simple, cheap) as possible, and it does not have this functionality as a built-in method or property because of this. However, this does not mean it can't be done at all ;)

I implemented a new sample 'iGrid To/From Array' to demonstrate it. I have just published it in our online extra sample lib for iGrid here:

http://10tec.com/activex-grid/extra-samples.aspx 

The sample provides you with two methods, iGrid_To_Array and iGrid_From_Array, you can use in your solution.

iGrid_To_Array is pretty straightforward:


As for iGrid_From_Array, it looks a little bit more complicated:


If we populate iGrid from an array, many questions arise. For instance, should we use the existing column set, or we need to create a new one using say the first row of the array as column headers? This is one more reason why we do not incorporate this functionality into iGrid. There are plenty of cases when you need to adjust it for your particular case, and a source code solution like I placed in this post is a good start point to implement what you exactly need.

Igor,

Thanks a lot!

I thought as much, it would've been quite a job indexing and re-indexing an internal array, should you have decided to keep it with the control...
Guess that would have slowed down things quite a bit!

So, we'll have to do all the enumeration anyway ))

Can you tell us, why do you need to have a copy of the iGrid cells in a separate array?

One of the key ideas of iGrid is to provide you with an ARRAY of cells that is tightly integrated with its visualization part (what you see on the screen). I mean that you can easily access any cell like an item in a 2-dimensional array using the CellValue(<row>, <col>) syntax.

The special internal iGrid memory manager makes it work very fast if you work with large amounts of data - for instance, when you need to add a new record, or delete some rows. And moreover, with iGrid you get some unique and powerful features you do not have in traditional arrays like accessing its rows/columns using STRING row keys - which is useful when you need to access a dedicated row, and the row set is constantly changing.

Igor,

sorry for over a monthly delay with the reply, I obviousy messed up my account's settings and wasnt notified about your question.

That is actually a very good question! I personally like the control's ReDim'ming, row deleting and string key indexing abilities very much, and do use it extensively
There are two reasons why I use seperate arrays:

1) I develop Excel add-ins, and I have to dump a grid's contents into a worksheet cells very often. The fastest way is assigning an array to a Range.Value property.
(example: on my computer, an array of (50,000 to 50) is displayed in 5.1 seconds if I use range.value assignment, and in 51.86 seconds if a For-Next cycle is used, even with ScreenUpdating, Calculation and Events turned off - see the code)

2) there are some classes in my addin that do additional calculation with array values - usually adding several columnds and filling it with values.
It would be very much appealing to do such calculations directly in an iGrid control, with all its obvious advantages,
BUT (a) I am not sure whether Calculate-Assign action will work faster in iGrid compared to vb array
and (b) see point 1 😞 . I still have to dump them onto an Excel worksheet.
So, basically that's how it works currently (see below), and if you have an idea of improving the process, I'd be much obliged!


Collect data from various sources ==>

build an array ==>

[Display an array in iGrid] ==>

Modify the array data based on rules or user input ==>

Display data to excel sheet or update sources


Besides, I have problems subclassing iGrid in Excel VBA (that's how I like it done), but that is yet another thread.

I think array calculations if the fastest way to process any data, so continue to use what you have now. Add to this the super-fast displaying of data in an Excel worksheet if you assign an array to the Range.Value property 😉

I think array calculations if the fastest way to process any data, so continue to use what you have now. Add to this the super-fast displaying of data in an Excel worksheet if you assign an array to the Range.Value property ;)

[Weeping Bitterly] if there were a way to display iGrid contents onto a sheet at speed comparable to arrays... I'd walk out on arrays that very day! ))

OHWHATT STILL, there is some room for invention.

The thing is, the array displayed on a sheet is used mostly (uh-oh, always, actually) as a source for pivot tables.
aaaand iGrid has extensive features for dealing with databases!
So, since the addin works in a DataWarehouse manner (collects various sources in predefined tables),
then maybe the answer is to cut out the part with displaying array to a sheet, but use a temporary database file instead, which is to be used then for refreshing pivot tables.
Sounds a little too complicated, but I guess no more complicated than the way it gets done right now.

So, it should work like this then:
1) an iGrid is initialized in an invisible UserForm or thru subclassing (I generally have no need to display the whole of it)
2) the userform, or a wrapper class, initilizes a temporary database file of any kind and binds it to the iGrid, so that modifying iGrid contents updates the DB file
3) all target pivot tables are updated so that they refer to that temporary database

Igor, with all your experience in using iGrid 😉 do you think that might work? how difficult would it be to service the process?
Do you think I'd get enough speed in populating iGrid from Excel sheet sources compared to filling in arrays?

No, filling iGrid from a database source (BTW, it can be only an ADO or DAO recordset) is a way that takes more time. It's much better (=faster) to set its cell properties directly, i.e. to populate it from an array like we discussed above. This is the main mode iGrid was created for.

And note that iGrid can be populated from a database source, but it's just a one-way street: you only copy the filed values into iGrid if we talk about the FillFromRS method. iGrid is not a real data bound control - though you can implement this strategy using the rich set of the iGrid members.

The iGrid ActiveX 6.0 has two new methods to do what we discuss in this thread. These are LoadFromArray and LoadIntoArray methods.

Using LoadFromArray to populate iGrid can give 10x-40x performance gain if we compare it to the traditional loop based on setting the CellValue property.