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

VBA Pivot Table

What is a Pivot Table? One of the most powerful features of Excel VBA is the Pivot Table. A pivot table is a VBA tool that is used to create summary...

3 minutes read.

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 Replace Function

VBA Replace Function: The Replace function in VBA searches for a substring within the specified string and replaces its occurrences with a second substring. Syntax Replace (Expression, Find, Replace, [Start], [Count], [Compare]) Parameter Expression (required) – This parameter represents...

2 minutes read.

Excel VBA TimeValue Function

Excel VBA TimeValue Function: The TimeValue function in VBA returns a Time from the specified String interpretation of a time /date where the date information for the given string is...

1 minute read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

VBA UBound

VBA UBound  The UBound or Upper Bound function in VBA is used to specify the length of an array and returns the highest subscript for a dimension for the specified array. It is...

3 minutes read.

Excel VBA Timer Function

Excel VBA Timer Function: The Timer function in VBA returns a Single data type, evaluating the number of seconds that have elapsed since midnight of the current day. Syntax Timer () Parameter NA Return This...

1 minute read.

Excel VBA Now Function

Excel VBA Now Function: The Now function in VBA returns the current date and time.  Syntax Now () Parameter NA Return This function returns the current date and time.  Example 1 Sub NowFunction_Example1() 'It will return...

1 minute read.

Excel VBA MessageBox

Message Box The MsgBox in Excel VBA is a dialog box used to inform the users of your program by showing a custom message or get some necessary inputs such as Yes/No or...

4 minutes read.

Excel VBA Conditional Statement

Conditional Statement in VBA Excel Conditional Statements in Excel VBA are one of the most powerful and useful features in programming, this will give you to perform comparisons to decide or...

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 Sgn Function

VBA Sgn Function: The Sgn function in VBA returns an integer (+1, 0, or -1), stating the arithmetic sign for the specified number. Syntax Sgn (Number) Parameter Number (required) –This parameter represents the number that...

1 minute read.

ActiveX Controls

ActiveX Controls are one of the most used Excel Controls to automate applications with Excel VBA. It has the same controls, unlike Form Controls (Command Button, combo box, checkbox, etc.), but it...

3 minutes read.

Excel VBA FormatCurrency Function

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

2 minutes read.

Basics of Userform

A User Form is a built-in customized dialog box that fetches data from the user through a user-friendly dialog box or window that makes up part of an application's user interface. It...

9 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.

Excel VBA Atn Function

VBA Atn Function: The Atn function in VBA returns the arctangent between quadrant -?/2 and +?/2 for the specified number, in radians. Syntax Atn (Number) Parameter Number (required) – This parameter represents the number that...

1 minute read.

Finding Last Row or Column in Excel VBA

Finding Last Row or Column in VBA Finding the last used row, column, or cell is one very commonly used task when we write macros and VBA applications.  Like other codes...

5 minutes read.

VBA Dim

What is Dim? DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.)...

7 minutes read.

VBA Option Explicit

VBA Option Explicit The Option Explicit in VBA is used to declare the variables at the top of your macro code. It is the most secure and easy option to maintain your variables....

5 minutes read.