Lesson 8: Mastering Formatting Functions in Excel VBA

Formatting functions in Excel VBA allow you to present data in a professional and readable manner. This lesson covers both predefined and custom formatting options to enhance your Excel applications.

8.1 Predefined Format Functions

The predefined Format function provides standard formatting options that are commonly used in business applications. The syntax is:

Format(expression, "style_argument")

Practical Example 8.1

Sub FormatSalesData()
    Dim sales As Double
    sales = 1254873.5689
    
    ' Apply different formats to the same number
    Range("A1") = "General Number: " & Format(sales, "General Number")
    Range("A2") = "Fixed: " & Format(sales, "Fixed")
    Range("A3") = "Standard: " & Format(sales, "Standard")
    Range("A4") = "Currency: " & Format(sales, "Currency")
    Range("A5") = "Percent (as decimal): " & Format(sales/10000000, "Percent")
End Sub

8.2 User-Defined Format Functions

When predefined formats don't meet your needs, you can create custom formats using special symbols:

Format(expression, "custom_format")

Practical Example 8.2

Private Sub FormatFinancialReport()
    ' Format financial data in various ways
    Cells(1, 1).Value = "Revenue: " & Format(1254873.5689, "$#,##0.00")
    Cells(2, 1).Value = "Growth Rate: " & Format(0.15783, "0.00%")
    Cells(3, 1).Value = "Units Sold: " & Format(12548, "#,##0")
    Cells(4, 1).Value = "Avg. Price: " & Format(99.956, "$#,##0.00")
    Cells(5, 1).Value = "Market Share: " & Format(0.2256, "0.0%")
    
    ' Conditional formatting based on value
    Dim profitMargin As Double
    profitMargin = 0.1845
    
    If profitMargin > 0.15 Then
        Cells(6, 1).Value = "Profit Margin (High): " & Format(profitMargin, "0.00%")
        Cells(6, 1).Font.Color = RGB(0, 128, 0) ' Green
    Else
        Cells(6, 1).Value = "Profit Margin (Low): " & Format(profitMargin, "0.00%")
        Cells(6, 1).Font.Color = RGB(255, 0, 0) ' Red
    End If
End Sub

8.3 Date and Time Formatting

The Format function is particularly powerful for displaying dates and times:

Practical Example 8.3

Sub FormatDateTimeExamples()
    Dim currentTime As Date
    currentTime = Now
    
    ' Format current date/time in different ways
    Range("A1") = "Short Date: " & Format(currentTime, "mm/dd/yyyy")
    Range("A2") = "Long Date: " & Format(currentTime, "dddd, mmmm dd, yyyy")
    Range("A3") = "Time: " & Format(currentTime, "hh:mm:ss AM/PM")
    Range("A4") = "Combined: " & Format(currentTime, "mmmm dd, yyyy hh:mm AM/PM")
    Range("A5") = "ISO Format: " & Format(currentTime, "yyyy-mm-dd hh:mm:ss")
    
    ' Calculate and format time differences
    Dim startTime As Date, endTime As Date
    startTime = #9:15:00 AM#
    endTime = #4:45:30 PM#
    
    Range("A7") = "Work Duration: " & _
                  Format(endTime - startTime, "hh"" hrs ""mm"" mins""")
End Sub

8.4 Advanced Formatting Techniques

Conditional Formatting with VBA

Sub ApplyConditionalNumberFormatting()
    Dim rng As Range
    Set rng = Range("B2:B10") ' Assuming these cells contain numbers
    
    For Each cell In rng
        Select Case cell.Value
            Case Is >= 1000000
                cell.NumberFormat = "$#,##0.0,,""M""" ' Display as millions
            Case Is >= 1000
                cell.NumberFormat = "$#,##0.0,""K""" ' Display as thousands
            Case Else
                cell.NumberFormat = "$#,##0.00" ' Regular currency format
        End Select
    Next cell
End Sub

Formatting Phone Numbers

Function FormatPhoneNumber(phone As String) As String
    ' Format 10-digit numbers as (XXX) XXX-XXXX
    If Len(phone) = 10 And IsNumeric(phone) Then
        FormatPhoneNumber = Format(phone, "(000) 000-0000")
    Else
        FormatPhoneNumber = phone ' Return original if not 10 digits
    End If
End Function

Summary: Key Points About VBA Formatting Functions

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial