VBA FormatNumber Function

The VBA FormatNumber function is a built-in function in Microsoft Visual Basic for Applications (VBA) that is used to format a numeric expression as a string. This function is very useful for developers who work with large amounts of data and need to display it in a readable format.

VBA FormatNumber Function – Purpose, Syntax and Arguments

Purpose

The purpose of the FormatNumber function is to take a numeric expression and format it into a string, based on the specified format. This can be especially helpful when dealing with currencies, percentages, or large numbers where the standard display format may not be suitable.

Syntax

FormatNumber(Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits])

Arguments

1. Expression: This is the required argument that specifies the numeric expression to be formatted.
2. NumDigitsAfterDecimal: This is an optional argument that specifies the number of digits to display after the decimal point. The default value is -1, which indicates that the computer’s regional settings will be used to determine the number of decimal places to display.
3. IncludeLeadingDigit: This is an optional argument that specifies whether or not to include a leading digit for numbers less than one. The default value is False, which means that leading zeros will be excluded from the formatted string.
4. UseParensForNegativeNumbers: This is an optional argument that specifies whether to enclose negative values in parentheses. The default value is False, which means that negative values will have a minus sign (-) in front.
5. GroupDigits: This is an optional argument that specifies whether or not to use a separator character, such as a comma, to group the digits in the formatted string. The default value is False, which means that no separator will be used.

Example

Suppose we have a column of numbers in an Excel spreadsheet that we want to format for better readability. The numbers are currently displayed in the general format, which shows them with decimal places and no leading zeros. We can use the FormatNumber function to change the format to currency, with two decimal places and a comma separator.

FormatNumber(A2, 2, -1, 0, -1)

In this example, A2 is the cell reference for the first number in our list. The first argument is the expression we want to format. The second argument, 2, indicates that we want to show two decimal places. The third argument, -1, means that we want to exclude any leading zeros. The fourth argument, 0, tells the function to use a minus sign for negative numbers instead of parentheses. And the last argument, -1, indicates that we want to use the computer’s regional settings for grouping the digits.
If the number in A2 is 123456.789, the formatted string would be “$123,456.79”.

Remarks

• The FormatNumber function is very similar to the ‘Format’ function, which can also be used to format values in VBA. The main difference between the two is that FormatNumber specifically deals with numeric values, while ‘Format’ can be used for strings and other data types as well.
• The FormatNumber function is not case-sensitive, which means that whether you use uppercase or lowercase letters in the function name does not matter.
• The FormatNumber function can be used in combination with other formatting functions, such as ‘FormatCurrency’ or ‘FormatPercent’, to achieve more specific formatting needs.

Important Notes

• The FormatNumber function is only available in VBA and cannot be used in regular Excel formulas.
• This function may not be supported in older versions of VBA, so it is important to check the compatibility before using it.
• When working with large amounts of data, it is generally more efficient to use the ‘Format’ function instead of the FormatNumber function.

In conclusion, the VBA FormatNumber function is a powerful tool for formatting numeric values as strings in Visual Basic for Applications. Its various arguments give developers the flexibility to customize the format of their numeric expressions, making them more readable and user-friendly. This function is an essential tool for anyone working with data in VBA and is definitely worth exploring further for its incredible potential in data formatting.

Understanding VBA FormatNumber Function with Examples

Using the FormatNumber Function to Format Currency

In VBA, the FormatNumber function is used to format a number with specific attributes, such as currency, decimal places, and thousands separators. The syntax for this function is as follows:

FormatNumber(Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits])

1. Expression: This is the required argument that specifies the number or numeric expression that you want to format.
2. NumDigitsAfterDecimal: This is an optional argument that specifies the number of digits to the right of the decimal point.
3. IncludeLeadingDigit: This is also an optional argument that specifies whether the first digit to the left of the decimal point should be included in the return value.
4. UseParensForNegativeNumbers: This is an optional argument that specifies whether negative numbers should be enclosed in parentheses instead of using a minus sign.
5. GroupDigits: This is the final optional argument that specifies whether a thousands separator should be used.

Now let’s look at an example of how we can use the FormatNumber function to format a currency value. Consider the following code:

Dim total As Currency
total = 25000.50
MsgBox "The total amount is: " & FormatNumber(total, 2) & " USD"

In this example, we have declared a variable ‘total’ as a Currency type and assigned it a value of $25,000.50. Then, we have used the FormatNumber function to format the value with two decimal places. The output of this code will be:

The total amount is: $25,000.50 USD

As you can see, the FormatNumber function has added a dollar sign and a decimal point to the value, and also formatted it with two decimal places. This is a simple yet useful example of how we can use this function to format currency values in VBA.

Using the FormatNumber Function to Format Decimal Places

As mentioned earlier, one of the optional arguments of the FormatNumber function is ‘NumDigitsAfterDecimal’, which allows us to specify the number of decimal places we want to display. Let’s take a look at an example to understand this better:

Dim num1 As Double
num1 = 123.456
MsgBox "The number is: " & FormatNumber(num1, 3)

In this code, we have declared a variable ‘num1’ as a Double type and assigned it a value of 123.456. Then, we have used the FormatNumber function to format the value with three decimal places. The output of this code will be:

The number is: 123.456

If we change the value of ‘num1’ to 123.4 and run the code again, the output will be:

The number is: 123.400

As you can see, the FormatNumber function has added a zero at the end to meet the specified number of decimal places. Similarly, if we change the value of ‘num1’ to 123.4567, the output will be:

The number is: 123.457

The FormatNumber function has rounded off the value to three decimal places and displayed it accordingly. This is a simple example, but it demonstrates how we can use this function to format numbers with a specific number of decimal places.

Using the FormatNumber Function to Format Negative Numbers

In VBA, we can use the FormatNumber function to decide how negative numbers should be displayed. The optional argument ‘UseParensForNegativeNumbers’ allows us to specify whether negative numbers should be enclosed in parentheses or displayed with a minus sign. Let’s take a look at an example:

Dim num2 As Double
num2 = -123.45
MsgBox "The number is: " & FormatNumber(num2)

In this code, we have declared a variable ‘num2’ as a Double type and assigned it a negative value. Then, we have used the FormatNumber function without specifying any arguments. The output of this code will be:

The number is: -123.45

As expected, the negative number is displayed with a minus sign. Let’s modify the code and specify the ‘UseParensForNegativeNumbers’ argument as True:

Dim num2 As Double
num2 = -123.45
MsgBox "The number is: " & FormatNumber(num2, , , True)

The output of this code will be:

The number is: (123.45)

As you can see, the FormatNumber function has now enclosed the negative number in parentheses. This is just one example of how we can use this argument to customize the formatting of numbers in VBA.

Using the FormatNumber Function to Group Digits

The final optional argument of the FormatNumber function is ‘GroupDigits’, which allows us to specify whether a thousands separator should be used while formatting numbers. Let’s take a look at an example:

Dim num3 As Double
num3 = 1234567
MsgBox "The number is: " & FormatNumber(num3)

In this code, we have declared a variable ‘num3’ as a Double type and assigned it a value of 1,234,567. Then, we have used the FormatNumber function without specifying any arguments. The output of this code will be:

The number is: 1234567.00

The number is displayed without any thousands separators. Let’s modify the code and specify the ‘GroupDigits’ argument as True:

Dim num3 As Double
num3 = 1234567
MsgBox "The number is: " & FormatNumber(num3, , , , True)

The output of this code will be:

The number is: 1,234,567.00

As you can see, the number is now displayed with a comma as a thousands separator, making it easier to read. This is just one way to use the FormatNumber function to format numbers in VBA.

Conclusion:

The FormatNumber function in VBA is a powerful and versatile tool that allows us to format numbers in a variety of ways. It can be particularly useful when working with financial data or when presenting numbers to the user in a more readable format. In this blog post, we have looked at four examples of how we can use this function to format currency, decimal places, negative numbers, and group digits. With a good understanding of the FormatNumber function, you can easily customize and format numbers in your VBA code according to your specific requirements.