Use VBA to Bold Detects by Looping through an Excel Cross-tab

The EQuIS cross-tab report is super handy and reliably spits out nicely organized cross-tab reports from EQuIS queries (reports).  Earthsoft even worked out some cool conditional formatting such as underlines and italics to show where reported concentrations exceed screening levels. This in-app formatting is handy for screening, but looks ugly in a final report table.

For most of AMEC's projects we're asked by the project teams to format the data in Excel using a combination of BOLD for detects and some cell-shading color scheme to call out results equal to or above action levels such a risk screening levels or regulatory levels like MCLs.  Colored tables cost more for report production and of course, you lose the color format's value when the table is photocopied in black and white.  If there's only one action level, you're golden- just use BOLD for detects and a light shade of yellow for an exceedance which shows up as light gray in a photocopy, but still catches your eye on the monitor.

AMEC's data management team has used Visual Basic for Applications (VBA) to work up all sorts of Excel-based formatting apps for post-processing EQuIS reports. The most complex apps set up page headers and footers with table names, project numbers, and draft stamps, as well as insert table-specific notes in the appropriate location depending on a landscape or portrait table format and layout borders and page breaks.

At the heart of all our VBA-controlled formats is logic to correctly BOLD detects and manage shading of cells containing data with action level exceedances. Explaining all the logic for the complex formatting described above is beyond the scope of a blog post, but I will show you how to get started using VBA to loop through a cross-tabbed data set and locate and format detects. Next post we'll get into shading results that exceed screening criteria.

To get a handle on looping through data you'll need to set up way to use VBA to move from cell to cell in a predicable manner. Picture an automated way to move through your table with the up-down and side-to-side arrow keys. I always loop through the data going downward from row to row beginning in the first data column. I'm always consistent in my approach so it's become easy for me to recreate a loop without much thought- there's was a LOT of head scratching at first!!  The idea is to begin at the first column and move downward row by row until there's no more data, then move back to the first row, and shift left to the next column, and so on.

To do this you'll need to write two nested loops in VBA, one to move through the rows and one to move across the columns.  The code below depends on the lack of "U" qualifier to determine if the cell value is a detect. In first example below (Example 1) the code handles the case where qualifiers are in the same cell as the result value like this.

In Example 2 I'll show you how to handle cases like this where the qualifiers are in their own column next to the result value.

To try this out just create a new Excel VBA module in the workbook with your data and drop the code into it. Hopefully my notes in the code will explain how it works...

The code could be modified slightly to handle a "<" symbol should that be your way to identify ND's.

Feel free to drop me an email or call if you have problems or need more clarification.

I'm thinking of putting on a one day Excel VBA for Environmental Data course in AMEC's training center here in Portland. Please let me know if you're interested.

Dan


'EXAMPLE 1
 'Sub to Bold detects where result and qualifier are in the same cell
Sub Loop_thru_cells1()
Dim j As Integer, i As Integer  'declare variable j for columns and i for rows
Dim sVal As String    'declare sVal as a variable to hold the result and qualifier text string

Cells.Font.Bold = False 'clears all bolds in the table
For j = 5 To 100 ' Starting column = 5; last column with data is 100; change to fit your data
   For i = 4 To 88  'Starting row is 4; last row is 88; change to fit your data
        sVal = Cells(i, j)  'set variable sVal = to the cell contents
        If InStr(sVal, "U") = 0 Then 'search the string for presence of a "U"; this covers "UJ";
             Cells(i, j).Font.Bold = True ' if a "U" is present then instr(sVal,"U") will be greater than 1 and the cell will be bold.
         End If
   Next i  'next row; when i = 88 this loop ends and the column (j) loop increases by 1
Next j  'next column

End Sub



'EXAMPLE 2
'Sub to bold detects where result and qualifier are in adjacent cells
Sub Loop_thru_cells2()
Dim j As Integer, i As Integer  'declare variable j for columns and i for rows
Dim sVal As String    'declare sVal as a variable to hold the result and qualifier text string

Cells.Font.Bold = False   'clears all bolds in table
For j = 5 To 100 Step 2 ' skips every other column
   For i = 4 To 88  'Starting row is 4; last row is 88; change to fit your data
        sVal = Cells(i, j + 1) 'sets the string variable = to the cell adjacent to the result
        If InStr(sVal, "U") = 0 Then 'search the string for presence of a "U"; this covers "UJ";
            Range(Cells(i, j), Cells(i, j + 1)).Font.Bold = True 'sets both the result and qualifier cells as bold
        End If
        Cells(i, j).Select
   Next i  'next row; when i = 88 this loop ends and the column (j) loop increases by 1
Next j  'next column

End Sub