Beyond Excel: VBA and Database Manipulation

Jan 25 2010   6:06PM GMT

Adding Formatting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Now it’s time to add basic formatting.  Formatting is important.  Accountants must see currency values formatted their special way.  Dates must be presented according to regional standards.  Fortunately, adding formatting to your results is as easy as adding one more column to our Fields table and one more routine to our library.   See below:

Fields Table w/Format

Fields Table w/Format

You can add any one of the following formatting to your results:

Value Result
L Left align – the default for character results
R Right align – the default for numeric results
W Wrap text to fit
mm/dd/yyyy This is a typical date format. You can also use any data format string allowed by Excel
#,###/00 This is a typical number format. You can also use any number format string allowed by Excel
$#,##0_);[Red]($#,##0) This is a typical currency format in the US. You can also use any currency format string allowed by Excel
You get the idea – anything allowed by Excel as a format works

Below is the new routine to add to your library and the modifications to Macro1().

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
'   Format_Range:   Apply numeric formatting or alignment
'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
    Dim s As String
    Dim lCol As Long
    Dim lRow As Long
    Dim lRows As Long
    lRows = Range(sDataRange).Rows.Count
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            s = Range(sFieldRange).Cells(lRow, lCol)
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select
            End With
    End With
    Format_Results = Success    'Normal end, no errors
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

Macro1() new line in red:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Format_Results "Data", "Fields"
    End If
End If

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: