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.
To read about how to use cDataSet classes read How to use cDataSet and Data Manipulation Classes This page is a quick primer of the object model with some examples of how to access it.
Creating cDataSets collection and initializing each cDataSet
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
A cCell is the lowest addressable structure of a cDataSet. It can be addressed as any of the following
- 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. Sub showDatasetHeadings() 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") For Each dc In .Headings With dc MsgBox ("The value of the heading for " & _ " col " & .Column & _ " is " & .toString & _ " the original address is " & .Where.Address) End With Next dc End With End With Set dSets = NothingEnd Sub