Excel VBA Grid Control

by Igor Katenov, the lead developer at 10Tec

This brief article is dedicated to the usage of our ActiveX grid control, iGrid, as a Microsoft Excel VBA grid control. The term "VBA grid" is equivalent to VBA DataGrid or VBA DataGridView in this context, so we can use these terms interchangeably.

iGrid is used in the VBA automation environment as a UserForm control. Thus, all the words below are also applicable to Microsoft Word sharing the same VBA environment and UserForms package with Excel.

Using iGrid as an Excel VBA grid control

Well, let's start from the Microsoft Visual Basic for Applications environment for Excel/Word. As you can see from the picture below, you can add iGrid as an Excel VBA grid control to the VBA IDE Toolbox and drop an instance of this grid control onto a UserForm:

Adding iGrid as Excel VBA grid control to UserForms Toolbox

The VBA property grid can be used to change the properties of our VBA DataGrid like you can do that for any intrinsic VBA UserForms control. As for doing this in code, you will not face any problems with using iGrid's properties, methods and events in the VBA code editor - iGrid was built and compiled in the classic VB environment (VB6) and is totally based on the VB/VBA runtime library.

When we launch that VBA form with our VBA grid control from the previous screenshot, we see the following picture that will help us to highlight some important features of iGrid when it is used as an Excel VBA grid:

10Tec Excel VBA grid in action

The main benefits of the iGrid control used as VBA DataGridView in Microsoft Word/Excel are the followings:

  • iGrid's cells can store values of any type as they are Variant. This means you can have values of different data types even within the same column.
  • iGrid fully supports displaying and editing of Unicode texts in all modern versions of Microsoft Windows.
  • You can use numerous cell formatting options (like background and foreground color, custom fonts, word wrapping and any vertical/horizontal alignment of cell contents).
  • If a cell's text is truncated, you can see the full cell text in the built-in tooltip like on the picture above.
  • iGrid allows you to edit its cells using not only simple text editor, but also drop-down combo lists and check box controls.
  • The grid contents can be sorted (multi-column sort is available) and grouped using its visual interface.
  • One of the grid columns can display a TreeView so you may implement a multi-column VBA tree grid as well.

As for the iGrid ActiveX control itself, it is implemented in one OCX file and does not depend on any external libraries. If you need to redistribute this control with your end-user Excel VBA solution (template, add-in, etc.), you just need to copy the OCX file to the client's computer and register it in the Windows registry like any other ActiveX control.

Alternative free solutions and the final words

If you google a query like excel vba grid control, the first search engine result page will contain links to articles in which authors write how to use the Microsoft FlexGrid control supplied with Visual Basic 5/6 as a VBA grid control. One of the mentioned resources on the MSDN is the following:

Inserting the DataGrid control? (Excel VBA)

You can try similar search queries like excel userform grid control or activex vba grid control, but in the vast majority of cases the first 10-20 search results will point to the use of MSFlexGrid or ListView in report view as VBA grid controls.

One of the most significant problems of both controls is that they do not allow you to edit its cells. To implement editing functionality, you need to use an extra text box control as the cell editor and write a lot of additional code to maintain this construction. 10Tec iGrid does not require all this as its cell editors are built-in features.

Another drawback of these free UserForms VBA grids is that they were developed before 2000, and they are no longer supported. As consequence, these controls have the outdated unchangeable 3D look. iGrid is being still actively supported, and it can use all the visual benefits of modern versions of Windows like visual styles. By default, its column headers and scroll bars have the same look-and-feel like you can see in other native Windows applications (Windows Explorer, etc.) - which is nice for the end-users of your Excel VBA solutions.

You can read about other distinctive features of iGrid in the corresponding articles in this section (one of them is iGrid - Editable VB6 MSFlexGrid Replacement). If you became intrigued by the usage of iGrid ActiveX as an Excel VBA grid control, you can continue your acquaintance with the control in the Control Tour using the link below.

Find out more about this VBA grid control in the Control Tour »