The below is technical help for those familiar with VBA on how to use the grid with your data.
Coding Help
Simple steps to instantiate a Grid and display it:
1. Add a reference to XLInCellGrid.xla to your vba Project:
From VBA editor, click Tolls -> References -> Browes...
and select the XLInCellGrid.xla
2. Add a VBA Module to your vba Project.
3. Create a reference to a XLGrid object as:
Public myGrid As XLInCellGrid.xlGrid
4. Create a function / method that will be used to populate the Grid, for example:
Public Sub LoadGrid_Click(myGridData as Variant)
Set myGrid = XLInCellGrid.NewXLGrid()
myGrid.LoadData myDestinationRangeName, _
myGridData, _
numOfKeys, _
myGridTitle
End Sub
5. Add the following function to your worksheet code, for example Sheet1:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
XLInCellGrid.HandleDoubleClick
End Sub
The above code will populate the Grid with the data pased
via the myGridData variant parameter. It will be displayed
on the cell that coresponds to the myDestinationRangeName range name.
It is going to be formated with all Default column properties.
For each column on each drill-down level you can specify a set of attributes
using a collection of sets of XLGridColumn objects.
See the XLInCellGrid_Demo.xls for a compleate example or/and contact the support for advance usage.
API Help
XLInCellGrid.NewXlGrid()
- Returns an instanse of a Grid. Do not use VBA New function.
XLInCellGrid.NewXLGridColumn()
- Returns an instanse of a XLGridColumn object. Used to define the column properties.
XLGrid.LoadData()
- The main function to populate the Grid.
- Returns XL_GRID_NO_ERROR (0), if Successful.
- Parameters:
destRangeName As String - Destination range name, where the grid is going to be populated.
srcDataVariantOrDirectUseRangeOrFile As Variant - Input Grid data. Can be passed as:
1. A Variant with the data.
2.Range that contains the data.
3. Previously save via the SaveGridDataToFile() file name.
Optional ByVal numKeyFlds As Integer = 1 - Defines how many columns from the left are considred as Keys. This is the drill-down deep, or drill-down level.
Optional reportTitle As String = "" - Report Title.
Optional xlGridColumnList As Collection = Nothing - A Collection of XLGridColumn objects. To
Optional totalRowType As XL_GRID_TOTAL_ROW_TYPE = XL_GRID_TOTAL_ROW_NONE - To automatically add a Total row set it to XL_GRID_TOTAL_ROW_BOTTOM.
Optional updateScreen As Boolean = True - True means it will manage the screen update, by disabling first then enabling back. Pass False if you want to manage when to update the screen.
Optional processErrorInData As Boolean = False - If some or all values have 'Error: ' in front of it, then those cells and deriving parent
values will be market with a different color. This option allows to highlight some values
to indicate, for example, some minor errors when there were calculated.
Optional extraRightShiftValue As Integer = 0 - Will indicate how many more columns from the right of the Grid will be used when
expanding / collapcing the grid. Should be used if you have another Grid or just data bellow
this grid and they have more columns that this grid, in order to not shift down / up only part of the data bellow.
Optional errorColorCode As XL_GRID_COLOR_CODES = XL_GRID_DEF_ERROR_COLOR - Defines the color code for the Cells that have 'Error: ' in front of the values.
Will take effect only if the processErrorInData is set to True.
Optional headerColorCode As XL_GRID_COLOR_CODES = XL_GRID_DEF_HEADER_COLOR - Can be used to change the default header color.
Optional replEmptyKeysWith As String = 'NA' - To replace the empty Key values with this.
Optional defExpandLevel As XL_GRID_EXPAND_LEVEL = XL_GRID_EXPAND_NONE - When the Grid is initially populated, it can automatically Expand
it up the deep level specified here.
XLInCellGrid.HandleDoubleClick()
- Add the above line into the 'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range)' function of the Sheet that has this Grid.
This is how double-click on the sheet will get into the Grid to be processed.
XLGrid.HideColumns()
- To Hide some of the columns of this Grid. Must be already populated using the XLGrid.LoadData().
- Parameters:
columnsNums As Collection - A collection of column numbers to hide.
Optional unHideOthers As Boolean = True - A collection of column numbers to unhide.
Optional numKeys As Integer = 0 - Total number of Keys used to populate the Grid; that is the numKeyFlds passed to the XLGrid.LoadData().
Optional updateScreen As Boolean = True - True means it will manage the screen update, by disabling first then enabling back.
Pass False if you want to manage when to update the screen.
XLGrid.UnHideColumns()
- To Unhide some of the columns of this Grid. Must be already populated using the XLGrid.LoadData().
- Parameters:
columnsNums As Collection - A collection of column numbers to unhide.
Optional updateScreen As Boolean = True - True means it will manage the screen update, by disabling first then enabling back.
Pass False if you want to manage when to update the screen.
XLInCellGrid.SaveGridDataToFile()
- To dump the full Grid data to a file. Data data file can be loaded back using the LoadData().