Introduction

Logic Architect for Excel/VBA is a toolset for manipulating tabular data efficiently in Excel VBA which greatly simplifies project development.

The main features are:

• There are extensive facilities for manipulating data on worksheets
• Databases can be created, maintained (including adding new tables) and queried with SQL with simple programming.
• There is a wide range of data transformations using virtual tables, which store data within VBA.
• There is a full object model for use in VBA where parameters are easy to change.
• Methods can be called individually so can be placed where required in VBA code.
• In addition to high level procedures (such as left join, unpivot, etc.) access is also provided to individual records and fields.
• Calculation is done with Excel formulae. Regular Expressions are supported for data extraction.
• User defined functions in VBA are supported.
• It provides an easy way to use SQL on data in the same workbook, to maintain workbook data in an Access database or save it in other formats.
• It is straightforward to create reports and ensure that formulae are protected and formatting is applied.
• It runs on all versions of Excel from 2007 onwards.

Get and Transform in Excel is frequently used to transform data and provides a user interface. If Get and Transform needs to form part of a VBA application, queries can be run from VBA, but if they need to be altered this requires editing the M code created by the Get and Transform user interface. Logic Architect provides most of the transformations which are required in practice in a way which is easier to use in VBA, more flexible and frequently faster. It also uses Excel formulae for calculation and allows VBA access to individual records.

Logic Architect is written as a set of class modules:

• TableData manipulates data on a worksheet.
• RsetData manipulates data in a database using ADODB.
• ArrData manipulates data in virtual tables.

It is written so that:

• These implement a common interface (called Xdata), so many of the frequently used methods are identical between all three and it is possible to write code to manipulate a Xdata object without needing to know what type it is.
• The logic for data on worksheets is identical whether it is stored in tables or not.
• Fields can always be referenced either by name or number.
• Many operations match field names automatically between different objects, allowing code to be written very simply, though additional parameters can be provided to override the default behaviour.

When programming in VBA, intermediate data can normally be stored in ArrData objects, so arrays are seldom needed. Any complex logic can be put in user defined functions, eliminating the need for loops in most cases. So the program code frequently consists of a simple sequence of calls to the methods/properties of one of the Logic Architect objects.

The core facilities of TableData and ArrData are available for Windows and Mac, though:

• The database component (RsetData) is not available on a Mac, as ADODB is not supported.
• None of the facilities which require RsetData are available on a Mac, including Export and saving to XML files.
• Regular expressions are not supported on the Mac.
• The Windows version makes extensive use of the Dictionary object. This is not available on a Mac, and so a Dictionary.cls class module has been provided which can be used instead.
• Problems have occasionally been encountered as a result of incompatibilities between VBA on the Mac and on Windows. All known problems have been fixed.