Getting Started

The system is entirely object based, and is used by creating one or more objects:

• a TableData object for each array of tabular data on a worksheet which you want to manipulate using the system
• an ArrData object for each virtual table you wish to create and manipulate
• a RsetData object for each ADODB Recordset you wish to create (normally linked to a database)
• a XData object where you do not need to know which type of object you are manipulating. References in the text to Xdata objects are where any of the three types of object can be used.

Your object needs to be declared, for example

Dim Td1 as New TableData

Objects then need to be initialised. There are various ways to do this, depending on the type of object and the context.

Where procedures take a list of parameters (for example, a list of fields) this can either be written as a ParamArray list at the end of the procedure call or as an array of values. A single parameter is just specified individually. Where there are no parameters, they can simply be omitted, or the values Empty, Null or an empty array Array() can be used. It is also possible to specify a single element array containing an array.

TableData

Most commonly a TableData object will represent an existing data array or table on a worksheet. This must be set up with a single header row. Use the Initialise method as the first method on the TableData object you have defined, specifying the range where the data is placed:

• There is an option whether you want to allow an object to have no data (header row only).
• It is possible to allow the system to locate the data - horizontally and/or vertically. To extend down, just set the header as the data range; to extend across and down, just set the first cell as the data range; then select the appropriate options.
• Duplicate headers will normally be flagged as errors though there is an option to allow these (the second or later occurrence of the header can only then be referenced by number, not by name).
• In tables, totals rows are recognised and protected and do not form part of the data.

Alternatively it is possible to create a new data array on a worksheet by starting with the Create or the CreateFrom method. Create simply creates a new (empty) TableData object at a specific location on a worksheet from specified field names which form the headers. CreateFrom creates a TableData object from data on another XData object:

• Specify the XData object which will be used to supply the data.
• If the TableData object already exists, it will be replaced. If not, specify the first cell where the object will be placed.
• CreateFrom is frequently used in creating reports, to create the data for the first time. Then the worksheet can be formatted, the data possibly converted to a table, and the code changed to use Initialise.
• This approach is also used to create a variable width output (for example where the data has been pivoted) in which case the CreateFrom method will be used each time with the VariableWidth parameter set. Any unused columns will be cleared.
• If VariableWidth is not used and the output already exists, the fields on the XData object must match the corresponding fields on the existing array and be in the same order (any additional fields on the existing array are protected).

ArrData

An ArrData object is most commonly created using the Create method which creates an empty object. Fields in an ArrData object have one of the VBA data types so it is necessary to specify the names and data types of each field. For example:

Ad1.Create Array("Field1", "Field2", "Field3"), Array(vbString, vbLong, vbDouble)

It is also possible to use CreateFrom to create an ArrData object from any other Xdata object:

• Since data on Excel worksheets is not typed, if CreateFrom is used to create an ArrData object from a TableData object, Excel needs to determine the data type of each field and this can be unreliable. If this is used, RsetData must be present. It is usually better to use Create, where you can specify exactly what data types are required, and then copy in the data. You can use TableData GetHeaders and GetTypes to assist setting this up, though as GetTypes is only based on the first data row some of the types returned may need to be altered. Or use Create with the first field, AddFields to bring in all the other fields from the TableData object, and ChangeType where needed.
• CreateFrom another ArrData object is an easy way to clone an ArrData object (note that indexes and current record position are not cloned).
• CreateFrom a RsetData object is straightforward (data types are converted from the corresponding ADODB data types to the closest VBA equivalent).

RsetData

RsetData objects are most commonly used with a database, in which the first step will be to use the Connect method to connect to the database and then RsetOpen to open a recordset by specifying a SQL string.

Where multiple RsetData objects share a connection, it is possible to reuse an existing connection using the Connx property, for example:

Set Rd2.Connx = Rd1.Connx

The ConnectFrom method connects a RsetData object from an existing workbook OLEDB connection. The CreateConnection method creates a workbook connection from the connection previously set up in the RsetData object.

It is also possible to use the Create or CreateFrom methods (similar to the ArrData methods) to create a recordset which is not associated with a database. An example of the use of this would be to use as a data source for a pivot table.

Xdata

XData defines an interface which the other object types implement, and any of the methods in this interface can be used on an Xdata object. Normally an XData object would not be created directly, but defined as the data type of a parameter passed to a function. This would then be able to operate on the object without knowing what type the underlying object is. However if it is required to also use methods specific to the type of object, use the ObjType property to return either TableData, ArrData or RsetData as a string. The you can use GetTd, GetAd or GetRd in XShared to return the underlying object, for example:

Function DoSomething(Xd as Xdata)
Dim Xs as New XShared
Dim Td as New TableData
Dim Ad As New ArrData
Dim Rd as New RsetData
Select Case Xd.ObjType
Case "TableData"
  Set Td = Xs.GetTd(Xd)
Case "ArrData"
  Set Ad = Xs.GetAd(Xd)
Case "RsetData"
  Set Rd = Xs.GetRd(Xd)
End Select