Logo
×

Excel VBA FormatNumber Function

VBA FormatNumber Function: The FormatNumber function in VBA is used to apply a number format to a numeric expression, and it returns the result as a string.

Syntax

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

Parameter

Expression (required) – This parameter represents the numeric expression that you want to format.

NumDigitsAfterDecimal (optional) – This parameter represents the number of digits that should be shown after the decimal. The default value is -1.

IncludeLeadingDigit (optional) – This parameter represents the vbTriState enumeration value, stating whether a leading zero should be shown for fractional values. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not display a leading zero.
  • vbTrue – It displays a leading zero.
  • vbUseDefault (default)– It uses the default computer settings.

UseParensForNegativeNumbers (optional) – This parameter represents the vbTriState enumeration value, stating whether negative numbers should be encased within parentheses. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not encase negative numbers in parentheses.
  • vbTrue – It encases the negative numbers in parentheses.
  • vbUseDefault (default)– It uses the default computer settings.

GroupDigits (optional) – This parameter specifies whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not group digits.
  • vbTrue – It groups the digits.
  • vbUseDefault (default)– It uses the default computer settings.

Return

This function returns a string value after applying the number format to the supplied numeric expression.

Example 1

Sub ValFunction_Example1()
 ' Converting the strings into numeric values.
 Dim num_val As Double, num_val2 As Double
 num_val = FormatNumber(1000.987, 3)
 ' num1 is now equal to the String "1000.99".
 Cells(1, 1).Value = num_val
 num_val2 = FormatNumber(1000.987, 2)
 ' num1_val2 will return the String "1000.99".
 Cells(2, 1).Value = num_val2
 End Sub

Output

1000.987
1,000.99

Example 2

Sub ValFunction_Example2()
 ' Converting the strings into numeric values.
 Dim num_val As Double, num_val2 As Double
 num_val = FormatNumber(-500, 2, , vbTrue)
 ' num_val is now equal to the String "-500".
 Cells(1, 1).Value = num_val
 End Sub

Output

-500

Related Topics

Debugging in Excel VBA

Debugging in VBA: Debugging is a technique used to fix errors in programming languages. In Excel VBA, we have different ways by which you can identify the error in the...

2 minutes read.

Excel VBA Right Function

VBA Right Function: The Right function in VBA returns a substring from the end of the given string. Syntax Right (Str, Length) Parameter Str (required) – This parameter represents the string from which you want to...

1 minute read.

Excel VBA CBool Function

VBA CBool Function: The CBool function in VBA calculates an expression and returns the result as a Boolean data type. Syntax CBool (Expression) Parameter Expression (required) – This parameter represents the expression that that you want...

1 minute read.

Excel VBA: Do Until….Loop

DO UNTIL….Loop The “Do Until” Loop is same unlike DO WHILE statement just that it will keep on looping till the condition is not met. This loop is used to repeat a set...

4 minutes read.

Steps to Create a Chart in VBA

Steps to Create a Chart Charts are created either by directly working with the chart variable object that defines the chart data or by ChartObject method. In order to get to...

3 minutes read.

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria. Syntax Filter (SourceArray, Match, [Include], [Compare]) Parameter SourceArray (required) – This parameter the array of Strings that you...

2 minutes read.

Four VBA Clear methods

Four VBA Clear methods In Microsoft Excel, many times, a situation arises where the user wants to clear the data or any specific range of data. What if the user automates this task with...

6 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

Excel VBA FormatPercent Function

VBA FormatPercent Function: The FormatPercent function in VBA is used to apply a percent format to a numeric expression, and it returns the result as a string. Syntax FormatPercent (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Excel VBA DateDiff Function

The DateDiff function in VBA returns a Long data value representing the number of intervals between two specified dates/times where the type of interval is supplied by the user. Syntax DateDiff (Interval, Date1, Date2, [FirstDayOfWeek], [FirstWeekOfYear]) Parameter Interval (required)...

2 minutes read.

Excel VBA Rnd Function

VBA Rnd Function: The Rnd function in VBA returns a random number that is greater than or equal to (>=) 0 and is less than (<) 1. Syntax Rnd ([Number]) Parameter Number (optional) –This...

1 minute read.

Excel VBA CInt Function

VBA CInt Function: The VBA Cint function converts the specified expression into an Integer. Syntax Cint (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to an Integer wherein...

1 minute read.

Excel VBA: Select … Case Statement

Select … Case Statement When a group of statements is executed, depending upon the value of an Expression, then Switch Case is used.  If you have several conditions to check, then the If condition...

4 minutes read.

Excel VBA Round Function

VBA Round Function: The Round function in VBA rounds a number to a specified number of decimal places and returns the number. Syntax Round (Number, [NumDigitsAfterDecimal]) Parameter Number (required) –This parameter represents a numeric value one wants...

1 minute read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

Excel VBA String Function

The String function in VBA creates a String, consisting of several repeated characters. Syntax String (Number, Character) Parameter Number (required) – This parameter represents the number of characters in the returned String. Character (required) – This parameter...

1 minute read.

Excel VBA StrConv Function

The StrConv function in VBA converts a string into a specified format. Syntax StrConv (String, Conversion, [LocaleID]) Parameter String (required) – This parameter represents the string to be converted. Conversion (required) – This parameter specifies the type of conversion. It can...

1 minute read.

Excel VBA Split Function

Excel VBA Split Function: The Split function in VBA is used to split a string into several substrings and return a one-dimensional array of substrings. Syntax Split (Expression, [Delimiter], [Limit], [Compare]) Parameter Expression (required) – This parameter...

2 minutes read.

VBA Regex

What is a Regex? Regex stands for Regular Expression is basically a pattern matching strings within another string. They are supported in many languages, including .net, C++, Python, etc. They are...

5 minutes read.

Excel VBA Hex Function

VBA Hex Function: The Hex function in VBA converts the given number into hexadecimal notation and returns the result as a string. Syntax Hex (Number) Parameter Number (required) – This parameter represents the numeric value...

1 minute read.