Random blog posts

Nowadays structured references are the preferred way to access table data in Excel and VBA. Although most of the examples you may have looked at so far have talked about ranges, you can also specify table references to cDataSet – this allows you to mix cDataSet integration capabilities with other code you may have written which uses structured table references.


Populating a dataset


You already have the ability to refer to data by it’s column name in cDataSet ( ds.row(“x”).column(“y”) ), so the only real integration with tables that is needed is to be able to use a reference to load them to a cdataset initially.
Normally, you would populate a dataset with
ds.populateData ("sheetName",...)
where sheetName is the name of the worksheet with the table data, and populateData would figure out the rest.
However, it’s not widely known that you can also use a range so that your table data can still be used regardless of where it is on the sheet
ds.populateData (someRange,...)
To load data into cDataSet from a structure table, you can first get its range with the function getTableRange (tableName), and simply pass that over
ds.populateData (getTableRange(“someTable”))
here’s an example of a few operations where a data table is used to specify the input to cDataSet
Private Sub testTables()
    Dim ds As cDataSet
    ' test getting a cDataSet from a table name
    Set ds = New cDataSet
    With ds.populateData(getTableRange("myTable"))
        ' print the range of the cdataset
        Debug.Print SAd(.where)
        ' clean up
    End With
    Set ds = New cDataSet
    ' one liner convert table to json
    Debug.Print ds.populateData(getTableRange("myTable")).jObject.stringify
    ' cleanup
End Sub

You’ll need a version of cDataSet.xlsm that has version 23 or higher of the usefulStuff module for this to work. You can either download  that from here, replace the specific module from github (code below, or use automatic updating as described here.

For more on this topic see How to use cDataSet