Daily Dose of Excel

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

In Part I, I listed the controlling procedure that does the Quickbooks related stuff and calls all the other sub procedures. In Part II, I listed the sub procedures that created each of the four queries. Now that the queries are set up, the controlling procedure sends the XML requests to Quickbooks and waits for responses. I takes about four minutes to run the whole thing and three-and-a-half minutes are waiting for Quickbooks to send back the data.

Once the responses are received, I loop through them and use a Select Case statement to handle each request separately. By handle, I mean store the data in such a way that I can use it. For each of the four responses, I created a class module. For instance, I have a CPurchaseOrder class module that will be used to hold the purchase order information.

Nothing too fancy there. I set up a Public variable for every "field" that I'm bringing over, then use MZTools to convert all those Public variables to Property Get/Let procedures. With the class module set up, I can parse the request and put all the data in the class module.

The appropriate IResponse object is passed into this function, which then returns a collection. The collection is a bunch of CPurchaseOrder objects and can be passed to procedures that do calculations or write to worksheets or both. For every field that I want to store in the class, I have to determine if Quickbooks returned anything by comparing it to Nothing, then call the GetValue method to set the class module's property.

In addition to CPurchaseOrder and GetPurchaseOrder(), I have the following class module/function pairs: CAssembly, GetAssembly; CRawMaterial, GetRawMaterial; CSalesOrder, GetSalesOrder.

I don't need to use class modules for this. That's probably true for most uses of class modules. But there are a few reasons why I think it makes sense here. I could loop through all of the responses and immediately write the data to a worksheet, since that's all I'm doing in the end anyway. With class modules I can access the Quickbooks data once, create multiple sub procedures without having a bunch of hard-to-maintain module-level variables, and easily add new calculations/output.

Accessing Quickbooks data is key because of the requirement to check the objects for Nothing and use the GetValue method to actually retrieve the data. If I, for instance, wanted to write a subset of my sales orders to a worksheet, I would have to limit them with code like this:

I have to make sure that IsFullyInvoiced, IsManuallyClosed, and CustomerRef were returned by Quickbooks before I can access their values. Once that test is passed, I have to make sure that FullName was returned. I can't check for FullName Is Nothing until I know that CustomerRef is there. Once I know that all the objects are there, I can GetValue to actually test my conditions.

With a class module, I test each of these conditions only one time, then put the data into the classes' property. Now I can access the classes' property without all the rigmarole. Compare:

So much cleaner and easier to read.

The classes also come in handy for splitting up my procedures. I could use module-level variables to pass the responses back and forth between the procedures, but it's easier and cleaner to pass everything in a collection. In order to put all that data into a collection, I create class modules which are held in the collection. I use sales orders to track product that I've loaned out to potential customers (kludgey workaround, thanks Intuit). I obviously also use sales orders to track sales that haven't shipped - their intended use. With all my sales orders in one class, I can call a WriteSalesOrders procedure and a WriteLoaners procedure and pass each the same collection of CSalesOrder objects.

Once I had this set up, I liked it so much that I decided to use sales orders to track product that needed repair. All the sales orders were already in the class, so I only had to write a WriteRepairs procedure to pull the appropriate sales orders out and stick them on a worksheet. That compartmentalization makes it easy to add and delete features without screwing up stuff you know already works.

<< Part II Part IV >>