Database integration using object classes

This month I am working on a quantitative analysis tool for a stock broker.  He wants to track stock data over time and rank stocks in a variety of ways.  Rather than try to figure out all the different reports and charts we will need to build, we agreed that the tool needs to be completely open-ended.  This means the customer can configure every aspect of the application, including the data fields being captured, the format of the reports, and the formulas used for ranking and classifying the data.

Capturing daily numbers for 5000+ companies requires a robust data storage and query component, so an Access database has been created.  This de-cupling of the data from the program logic is essential for an application of this scale.  Since an Excel project is never finished, the steady stream of updates and fixes can be deployed simply by emailing an updated Excel workbook.  The macro then connects to the Access file on the client's system, loads all the settings and information the client has been inputting and collecting, and configures itself.

Logic was created to handle custom field definitions and custom formulas.  A report designer was also built allowing the operator to layout new reports, control grouping and sorting of data, sub-totals, and formatting.  All this setup data is stored in the Access database.

When the user wants to view a report, the macro loads the setup details from the database, queries the company data tables for the required data, and draws up the report on a worksheet.  To handle all these inter-related data entities, we need to define a set of object classes in VBA.

I created classes for Field, Report, Company, Sector, Industry, Price, and all the other entities that our database is tracking.  At the project level I define Collections which are loaded with the individual instances of each class when the workbook is opened.  So when the user asks for a report, the code iterates through the Reports collection and builds the list of available reports to choose from.

The classes contain the logic they need to interact with the database and with each other.  For example, each class has Load, Insert, Update and Delete methods.  When the user defines a new custom Field, the code creates an instance of the Field object, updates it's properties with the settings the user has input, and calls it's Insert method to write the actual record to the database and insert itself into the Fields collection.

When the reports get generated, the Report object "knows" how to load a sub-collection of the fields that are on the report.  Each Field knows how to fetch the appropriate piece of data and format it for presentation.  If a Field is calculated based on other pre-defined fields, the logic will parse this out and use new Field objects to fetch the individual elements that make up the calculation.

By carefully modelling your objects this way, your code becomes incredibly powerful, because the "work" is distributed across the object model.  Your core routines can be quite lean and efficient, and much easier to read and maintain.  Instead of 20 different Reporting macros, you have one generic macro that produces all possible reports.  And since you're manipulating massive data structures completely in RAM, your code can run very quickly compared to the basic approach of stacking tables of data on different spreadsheets and iterating through cells row by row.

Learning how to model your data using object classes is not an easy skill to master.  I have many years of programming experience on a variety of platforms, including VB.Net, to call upon.  I have also made it a priority to master the design and programming relational databases using SQL.  

Yet gaining this knowledge was achieved by doing.  When you start experimenting with advanced techniques such as object classes and external databases, you build confidence, overcome challenges, and ultimately learn how to produce enterprise-level applications that are extendible, powerful, portable and maintainable.

For more information on these topics, please see some of my prior postings:

• Using SQL and Access
• Excel as front-end to an Access Database
• Using Object Classes

Programming with Class... Modules

A recent discussion on LinkedIn asked: who is using Class Modules, and for what purposes? Answers were either something like "Class what? Never heard of it." or "Couldn't live without 'em."  Rather than post my obscure personal opinions there, I post them here...

To put it very simply: a class is just another type of variable.  Variables, as you know, act as containers, designed to hold a piece of data of a certain type.  So in vba if I type Dim intX as Integer, I'm defining the variable intX and telling the computer to set aside some space for a number I'll be tracking.  Then, when I type intX = 32,765, I'm putting a number in that container.  I can pull it out later and do something with it, as in: Cell(1,1) = intX/8.

A class takes this storage of data to the next level, allowing you to build a complex data structure to hold many different types of data.  A class can (and should) also contain logic pertaining to itself - code that resides in sub-routines, functions, and class properties.

To help visualize the distinction between a simple variable and a class, imagine we're trying to track a list of team members for a payroll application we're building.  Each team member has a variety of information that distinguishes him or her from the other people: Name, DOB, Job Title, etc.  By creating a "Person" class in vba, with methods to handle the storage and retrieval of each of these personal attributes, we can keep each person's details together in memory, and move those details around in fun and also in beguilingly complicated ways.

Object Variables

The class module in Excel's code editor is where you define the attributes of a class - its name, methods, properties, and so on.  Elsewhere in your code you create Instances of the class using an object variable, as in: Dim objTM as clsTeamMember and Set objTM = New clsTeamMember. You can create many, many instances of a class, just like there can be many, many integer variables, all instances of the Integer data type.   

Collecting Objects

Once packed up into an object variable, the class can join others like it in a Collection.  A collection is like a list or stack that can then be searched, summarized, or iterated using the For Each... construct, as in: For Each objTM in colMembers.  Collections are, for me, the best reason I can think of to use class modules in vba.

Collections can be used to generate a unique list (as I demonstrated in an earlier posting), or to fill pick-lists in user dialogs, among other things.  A class can even contain a collection as one of it's properties, allowing you to create nested collections of collections, without limit. [Tip: Always set your object variables to Nothing when done with them to avoids those pesky memory leaks!]

But why would you need this?  That really is the question.  Most of what you can do in Excel's memory can be done in a worksheet's cells.  But pulling your data into memory to manipulate it there is much faster and, I find, more interesting, than copying and pasting onto worksheets.  Knowing when to apply object-oriented programming techniques is largely a matter of gut feeling, which comes with experience.

Personally, when building something like a reporting engine, I often define in-memory collections containing the data I'm trying to summarize.  It's then quite easy to make new reports by writing code against the classes and collections I have set up.  Effectively, I think of classes and collections as a giant universe of infinite worksheets in the sky, to conjure or disperse as needed...  

Try to read up what you can on this topic, and chip away at understanding until you begin see the light.  I recommend diving in and adding a class module to your next vba project.  This is, after all, an essential step in the road from Macro Recorder to Power Programmer.