cDataSet Model

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. 

cDataSet related classes 

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
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 = Nothing
End 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 = Nothing
End Sub

Comments