Format A Numeric Expression

You can custom format a numeric expression so that it displays with specific formatting by using either the FormatNumber, FormatCurrency, or FormatPercentage functions. These functions all take a numeric value and return a formatted number based upon the values you specify for each argument. The FormatNumber function returns a formatted number, whereas the FormatCurrency function returns a number that begins with a currency symbol, and the FormatPercentage function returns a number followed by a percentage sign.

Each of these functions have the same five arguments with the first being required: Expression, NumDigitsAfterDecimal, IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits.

Expression, the first argument, specifies the numeric value to format. The NumDigitsAfterDecimal argument indicates the number of decimal places to display on the right side of the decimal. The IncludeLeadingDigit argument determines whether a zero displays before the decimal for numbers between -1 and 1.The

UseParensForNegativeNumbers argument specifies whether to place parentheses around negative numbers. Finally, the GroupDigits argument determines how Excel groups numbers to make them more readable. For example, with this argument, you express fifty thousand as 50,000.

The last three arguments, IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits, all use the same three constant values. Use vbTrue as the value of the argument to perform the corresponding formatting. Use vbFalse if you do not want that type of formatting. If you do not specify a value, or specify vbUseDefault, the function utilizes the corresponding value from your computer's regional settings.

Windows stores all default settings for you system on the Regional Options dialog box. You can modify these settings at anytime, but modifications affect all Windows applications. You can view and modify your regional settings by selecting them via the Regional Options dialog box. To access this dialog box, see the section "Format a Date Expression."

FORMAT A NUMERIC EXPRESSION

FORMAT A NUMERIC EXPRESSION

'-H Create a new subroutine.

B Type Dim NumberVar As Variant, replacing NumberVar with a variable for use with a number format function.

'-H Create a new subroutine.

B Type Dim NumberVar As Variant, replacing NumberVar with a variable for use with a number format function.

Type NumberVar = FormatNumber(123, 2, vbUseDefault, vbUseDefault, vbTrue), replacing the first two arguments with the number to convert and number of decimal places.

0 0

Post a comment