VBA has become a pleasant helper within my office routine to automate many things in the Microsoft Office world. Sometimes it happens that you want to format a cell in Excel as currency using VBA.
In another article I had already shown how to format an Excel cell as a date using VBA. Now we will deal with currencies, although this procedure is also generally applicable for other formatting.
In the example shown, we simply take an Excel cell in which a number is entered. As you can see, this cell has no formatting or is formatted as “General“.
Now there are two approaches how to do the currency formatting in VBA. This is somewhat dependent on what the rest of the VBA program looks like and what you plan to do with it.
Use Currency data type
Let’s assume that the value to be entered into the cell exists in the VBA macro. In this case we can easily define a Currency data type and assign the value to it. Then we pass the currency value to the cell in question.
Sub FormatAsCurrency_1()
Dim myCur As Currency
myCur = 1.5678
Cells(2, 2).Value = myCur
End SubExcel now automatically recognizes the data type and applies the correct formatting. Excel automatically takes into account the country-specific settings. So if your Office/Windows is set e.g. to “Germany” or another european country, Excel will automatically format this to e.g. 1.57 €.
Use NumberFormat property
Another case can be when, for example, the values are already present in the Excel spreadsheet. So it makes no sense to introduce the data type Currency. Then you can assign the data format directly to the respective cell. To do this, use the NumberFormat property.
Format cell as Euro (€)
For our example, it looks like this. We assign a formatting string to the NumberFormat property.
Sub FormatAsCurrency_2()
Cells(2, 2).NumberFormat = "#,##0.00 €"
End SubIn this example, the cell in question is then formatted as EUR in the format €1.57. In the formatting string, do not get confused by the localizations. In the VBA code you have to use the original formatting. That means as thousands separator the comma and as decimal separator the point. If you call “Format Cells” directly in Excel, it may be displayed the other way round, because Excel already takes the country-specific settings into account.
Format cell as dollar ($)
If you want to format the cell in a completely different currency, for example as a dollar in the format $1.57 then the macro code would look like this:
Sub FormatAsCurrency_2()
Cells(2, 2).NumberFormat = "$#,##0.00"
End SubUse FormatCurrency function
In VBA there is also an own function called FormatCurrency, which supports the formatting of a value into a currency format. The disadvantage of this function is that it returns a string and thus the value in the Excel spreadsheet is formatted as text or string. Thus it is not suitable for further calculations in Excel.
In this example, a value of data type Double is formatted as currency and assigned to the cell.
Sub FormatAsCurrency_3()
Dim myVal As Double
myVal = 1.5678
Cells(2, 2) = FormatCurrency(myVal, 2)
End SubThe FormatCurrency function provides several parameters for formatting here:
FormatCurrency(Expression, [ NumDigitsAfterDecimal, [ IncludeLeadingDigit, [ UseParensForNegativeNumbers, [ GroupDigits ]]]])
Expression: This is the numerical value to be converted into a currency.
NumDigitsAfterDecimal: This is an optional parameter. Here you can specify how many decimal places after the decimal point should be displayed.
