What to download:
All the examples contain all the classes needed for them to work and all projects can be found here.
If you just want the main utility classes used throughout this site, in the downloads section cDataset.xlsm includes everything you need including some examples.
cDataSets are a collection of cDataSet. A cDataSet is an abstraction of an Excel Table, where a table is defined as a set of data with a heading row followed by data content, usually terminated by a blank row and a blank column. A parameter block is just a cDataSet, where the Excel table has been found by searching for a given Parameter block name, and where the rows (as well as the columns) are considered to be keys.
- There can be multiple cDataSet on a worksheet and of course their original spreadsheet locations can physically overlap.
- You can have multiple cDataSets collections if you want.
- The cDataSet deals only in values. No formula manipulation is possible, but each cCell has a record of it’s matching Excel range so you can access that directly.
- In a parameter block the first column is considered the row key, unless you specify a different column my name.
- Duplication of keys (either column or row .. if present) is not allowed. Normally there is only a row key for parameter blocks, but regular datasets can have a unique key as well if you want. Column keys are mandatory since the concept is one of abstracted data.
Traversing a cDataSet
- dsets.dataSet(“name”).cell(“row name or row number”, “column name” or “column number”)
- dRow.cell(“column name” or “column number”)
- dColumn.cell(“row name” or “row number”)
and common properties used are .Value , .toString , .Row, .Column or .Where
You typically traverse a cDataSet as follows. This example shows many of the properties available Sub showDataset() Dim dr As cDataRow, dSets As cDataSets, dc As cCell Set dSets = New cDataSets With dSets .create .init wholeSheet(“orderstransactions”), , “data”, , , True With .DataSet(“data”) MsgBox .Name & ” has ” & .RowCount & _ ” rows ” & .ColumnCount & ” columns” & _ ” and the original data is at ” & .Where.Address For Each dr In .Rows For Each dc In dr.Columns With dc MsgBox (“The value of the cCell at ” & _ ” row ” & .Row & ” col ” & .Column & _ ” is ” & .toString & _ ” the original address is ” & .Where.Address) End With Next dc Next dr End With End With Set dSets = NothingEnd Sub A cHeadingRow is just a special kind of cDataRow and it can be traversed as follows.
Dim dr As cDataRow, dSets As cDataSets, dc As cCell
Set dSets = New cDataSets
.init wholeSheet("orderstransactions"), , "data", , , True
For Each dc In .Headings
MsgBox ("The value of the heading for " & _
" col " & .Column & _
" is " & .toString & _
" the original address is " & .Where.Address)
Set dSets = Nothing