## Beyond Excel: VBA and Database Manipulation

Feb 6 2010   5:37PM GMT

Profile: Craig Hatmaker

It’s great that we can download and display data in Excel where users can sort it, filter it, print it… whatever they want to it.  It’s great that we can summarize that data in interactive pivot tables.  It’s great that we can display those pivot tables in interactive charts.  Those of you that have been following along now have a spreadsheet you can use as a template to do all those things.  But there’s one thing that really makes these techniques a true replacement for traditional paper based, batch run reports – calculations.

Traditional programmers are accustomed to adding complex calculations to the reports they provide end users.  End users often want reports that calculate the age of an invoice, or the percent of sales overall or within a geographic region.  Without complex calculations, these techniques couldn’t replace traditional methods.  Well – get ready to leave traditional methods behind.  Stop killing trees and start replacing all of those paper based reports with Excel because we are adding all of Excel’s rich library of functions directly into our results simply by adding the formula to our Fields Table.  Below is our new Fields Table, complete with “XL Func.” column.

Fields Table with Excel Functions (click to enlarge)

Examine the examples here.  There are three major categories of Excel functions: cell to cell; entire column based; and array formulas.

Entering Cell to Cell formulas:
Cell to cell is by far the most common formula type.  You’ve probably used them frequently already.  In this example we are calculating the age of the orders by subtracting the order date from today’s date.

`'=Int(Now())-int({`Order Date`})`
```- or its R1C1 equivalent
'=Int(Now()-int(RC[-1])```

Instructions:

• Reserve space in your result set by:
• Entering *None in the Table column or leave it empty
• Leaving the Alias column empty
• Entering “” (two double quotes) in the Field column
• Enter formulas in this table with a leading single quote ‘ or surround them with double quotes “.  This prevents Excel from trying to calculate a result in this table.  We want Excel to calculate results only in our result set.
• To use result fields in your formulas, type the Field name or its Heading and surround it with curly brackets {}.  Field names don’t change (normally).  Column Headings might.  For that reason I recommend using Field names when possible so if the end user wants a column heading changed from ‘Ship State/Province’ to ‘State’, you don’t have to change the formula as well.  Field names don’t exist for calculated fields so you have to use Heading references for them.
• If you don’t use result field references (which I strongly urge), formulas MUST be entered in the R1C1 notation and should use relative  positions.  Relative positions are prefered so formulas don’t have to be changed if fields are added to the result.
• This is a relative position in R1C1 notation designating one column to the left of the cell receiving the formula: RC[-1].
• This is a fixed position in R1C1 notation designating the third column: RC3.

Entering Column based formulas:
Column based formula types aren’t so common but still have their uses.  In this example we are summing the total quantity and placing the result in each row.  This is a bit inefficient for the computer, but very efficient for you.  For reports that generate small result sets, this method will probably make more sense than coding an alternative.  To designate an entire column, place a “C” in front of the field reference.

`'=Sum(C{Quantity})`
```- or its R1C1 equivalent
'=Sum(C[-1])```

Entering Array formulas:
Since the introduction of the SumIF command, I haven’t had much use for Array Formulas.  But for older versions of Excel, Array Formulas are the only equivalent.  In this example, we are getting the total for the row’s State.

```'{=SUM(
IF(C{State}={State},
C{Quantity},0)
)}```
```- or its R1C1 equivalent
'{=SUM(
IF(C[-5]=RC[-5],
C[-3],0)
)}```

Special Instructions:

• Surround Array Formulas with curly brackets {}.  These MUST be in the first and last positions of the formula (inside any quotes).
• You do not need to format the formulas with line breaks like I do (Alt-Enter).  It’s just my personal preference.

Next Post
I spent a good deal of blog space to explaining how to enter formulas.  Next post will be the coding.  See you then.