VBA FormatCurrency Function: Easy Formatting for Currency Values

I often come across situations where I need to present numerical values in a format that clearly indicates currency, whether it’s for budget tracking or financial reporting. That’s where the VBA FormatCurrency function becomes a handy tool in my VBA toolkit. With just a simple piece of code, numbers transform into neatly formatted currency values.

What makes the FormatCurrency function a favorite of mine is its ability to intuitively handle a variety of formatting nuances. It allows me to specify the number of decimal places, choose whether to use parentheses for negative numbers, and even group digits—ultimately ensuring that the figures in my reports are both accurate and aesthetically pleasing.

Syntax

I use **FormatCurrency** to apply a currency format. Here's how it looks:

- **Expression**: The number I'm formatting.
- **NumDigitsAfterDecimal** (optional): Digits after the decimal.
- **IncludeLeadingDigit** (optional): Include a leading zero.
- **UseParensForNegativeNumbers** (optional): Negative numbers in parentheses.
- **GroupDigits** (optional): Use grouping separator, like commas.

And the syntax in action:

```vb
FormatCurrency(value, 2, True, True, True)

It’s pretty handy when I need my numbers looking sharp as money!

Arguments

When I use the FormatCurrency function in VBA, I can tailor how I want my numbers to look with these parameters:

• 
  

  Expression: I need this to tell the function what number to format into currency.

  
  
• 
  

  NumDigitsAfterDecimal: I can decide how many decimal places to show. If I don’t specify, it defaults to what my computer’s regional settings decide.

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Value	Description
  -1	Default based on regional settings
  0	No decimal places
  2	Two decimal places (common for currency)

  
  

• IncludeLeadingDigit: Sometimes I want a zero in front of the decimal for amounts less than one. If I skip this, the system just picks the standard way.

  • False: No extra zero for me.
  • True: Yep, give me that leading zero.
  • Use Default: I’ll just stick with default settings.

• UseParensForNegativeNumbers: When I’m dealing with losses or debt, wrapping the numbers in parentheses makes it clear they’re negative.

  • False: No parentheses, thanks.
  • True: Wrap ’em up; makes it clear they’re in the red.
  • Use Default: I often just go with the flow of the default here.

• GroupDigits: Big numbers can be easier to read with commas or periods breaking them up.

  • False: I like my numbers straight with no breaks.
  • True: Break it down into thousands for me.
  • Use Default: The system knows best; bring on the default grouping.

Example

I’ve got a quick demo to show you how VBA’s FormatCurrency works:

Sub example_FORMATCURRENCY()
    Range("B1").Value = FormatCurrency(Range("A1"))
End Sub

In this bit of VBA code, I take a number from cell A1, wrap it up nicely in a currency format, and pop it into cell B1. Just like that, you’ve got yourself a formatted currency value, complete with a dollar sign. Check out the illustration below to see it in action:

Notes

• Negative Numbers: I use parentheses to denote negatives if I set UseParensForNegativeNumbers to vbTrue.
• Regional Settings: The currency symbol reflects my system’s control panel settings.
• Error Handling: If I encounter a non-numeric input, I get a run-time 13 error.
• Defaults: vbFalse and vbTrue influence formatting, whereas vbUseDefault applies system defaults.