I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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

  • 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