1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
<?xml version="1.0" encoding="UTF-8"?>
<helpdocument version="1.0">
<!--
* This file is part of the LibreOffice project.
*
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
-->
<meta>
<topic id="textsbasicshared03170010xml" indexer="include" status="PUBLISH">
<title id="tit" xml-lang="en-US">FormatNumber Function [VBA]</title>
<filename>/text/sbasic/shared/03170010.xhp</filename>
</topic>
</meta>
<body>
<section id="formatnumber">
<bookmark id="bm_id541542193473856" xml-lang="en-US" branch="hid/zzzz" localize="false"/>
<bookmark xml-lang="en-US" branch="index" id="bm_id661542193659245">
<bookmark_value>FormatNumber function</bookmark_value>
</bookmark>
<paragraph id="hd_id871542193473857" role="heading" level="1" xml-lang="en-US"><link href="text/sbasic/shared/03170010.xhp" name="FormatNumber Function [VBA]">FormatNumber [VBA]</link></paragraph>
<paragraph id="par_id351542193473858" role="paragraph" xml-lang="en-US">Returns a string with a number formatting applied to a numeric expression.</paragraph>
</section>
<embed href="text/sbasic/shared/00000003.xhp#vbasupport"/>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="bascode" id="par_id291542194090512" xml-lang="en-US" localize="false">FormatNumber( expression, numDigitsAfterDecimal as Integer, includeLeadingDigit as Integer, <br/> useParensForNegativeNumbers as Integer, groupDigits as Integer )</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functvalue"/>
<paragraph role="paragraph" id="par_id761542194361212" xml-lang="en-US">String</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph id="par_id240720170117391741" role="paragraph" xml-lang="en-US"><emph>expression</emph>: Required. The numeric expression to be formatted.</paragraph>
<paragraph role="paragraph" id="par_id631542195798758" xml-lang="en-US"><emph>numDigitsAfterDecimal</emph>: Optional. A numeric value specifying the number of digits that should be displayed after the decimal. If omitted, it defaults to the value -1, meaning that the default settings for user interface locale should be used.</paragraph>
<paragraph role="paragraph" id="par_id961542200034362" xml-lang="en-US"><emph>includeLeadingDigit</emph>: Optional. A <link href="text/sbasic/shared/03040000.xhp#addvbaconstants" name="vbtristate">vbTriState</link> enumeration value, specifying whether a leading zero should be displayed for fractional values.</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id561542198440051" role="listitem" xml-lang="en-US"><emph>vbTrue or -1</emph>: Display a leading zero.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id21542198550868" role="listitem" xml-lang="en-US"><emph>vbFalse or 0</emph>: Do not display leading zeros.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id241542199046808" role="listitem" xml-lang="en-US"><emph>vbUseDefaults or -2</emph>: Use the user interface locale settings. This is the default when omitted.</paragraph>
</listitem>
</list>
<paragraph role="paragraph" id="par_id311542201637647" xml-lang="en-US"><emph>useParensForNegativeNumbers</emph>: Optional. A <link href="text/sbasic/shared/03040000.xhp#addvbaconstants" name="vbtristate">vbTriState</link> enumeration value specifying whether negative numbers should be encased in parenthesis.</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id561543198440051" role="listitem" xml-lang="en-US"><emph>vbTrue or -1</emph>: Use parenthesis for negative numbers.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id21542398550868" role="listitem" xml-lang="en-US"><emph>vbFalse or 0</emph>: Do not display parenthesis.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id241542191246808" role="listitem" xml-lang="en-US"><emph>vbUseDefaults or -2</emph>: Same as vbFalse. This is the default when omitted.</paragraph>
</listitem>
</list>
<paragraph role="paragraph" id="par_id531542201968815" xml-lang="en-US"><emph>groupDigits</emph>: Optional. A <link href="text/sbasic/shared/03040000.xhp#addvbaconstants" name="vbtristate">vbTriState</link> enumeration value specifying the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the system's regional settings.</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id561543198560051" role="listitem" xml-lang="en-US"><emph>vbTrue or -1</emph>: Group digits.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id215423985506768" role="listitem" xml-lang="en-US"><emph>vbFalse or 0</emph>: Do not group digits.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id241542191267808" role="listitem" xml-lang="en-US"><emph>vbUseDefaults or -2</emph>: Same as vbFalse. This is the default when omitted.</paragraph>
</listitem>
</list>
<embed href="text/sbasic/shared/00000003.xhp#errorcode"/>
<embed href="text/sbasic/shared/00000003.xhp#err13"/>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<bascode>
<paragraph role="bascode" id="par_id381542204697430" xml-lang="en-US" localize="false">Sub TestFormatNumber</paragraph>
<paragraph role="bascode" id="par_id551542204266762" xml-lang="en-US" localize="false">testName = "Test 1: positive, 2 decimals"</paragraph>
<paragraph role="bascode" id="par_id561542204276644" xml-lang="en-US" localize="false">str2 = "12.20"</paragraph>
<paragraph role="bascode" id="par_id341542204281478" xml-lang="en-US" localize="false">str1 = FormatNumber("12.2", 2, vbFalse, vbFalse, vbFalse)</paragraph>
<paragraph role="bascode" id="par_id771542204286220" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id411542204290664" xml-lang="en-US" localize="false">testName = "Test 2: negative, 20 decimals, use leading zero"</paragraph>
<paragraph role="bascode" id="par_id411542204295496" xml-lang="en-US" localize="false">str2 = "-0.20000000000000000000"</paragraph>
<paragraph role="bascode" id="par_id391542204301416" xml-lang="en-US" localize="false">str1 = FormatNumber("-.2", 20, vbTrue, vbFalse, vbFalse)</paragraph>
<paragraph role="bascode" id="par_id671542204307459" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id741542204311861" xml-lang="en-US" localize="false">testName = "Test 3: negative, 20 decimals, no leading zero"</paragraph>
<paragraph role="bascode" id="par_id871542204316094" xml-lang="en-US" localize="false">str2 = "-.20000000000000000000"</paragraph>
<paragraph role="bascode" id="par_id911542204322988" xml-lang="en-US" localize="false">str1 = FormatNumber("-0.2", 20, vbFalse, vbFalse, vbFalse)</paragraph>
<paragraph role="bascode" id="par_id61542204328859" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id781542204333400" xml-lang="en-US" localize="false">testName = "Test 4: negative, no leading zero, use parens"</paragraph>
<paragraph role="bascode" id="par_id161542204338478" xml-lang="en-US" localize="false">str2 = "(.20)"</paragraph>
<paragraph role="bascode" id="par_id251542204342658" xml-lang="en-US" localize="false">str1 = FormatNumber("-0.2", -1, vbFalse, vbTrue, vbFalse)</paragraph>
<paragraph role="bascode" id="par_id421542204347269" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id181542204351589" xml-lang="en-US" localize="false">testName = "Test 5: negative, default leading zero, use parens"</paragraph>
<paragraph role="bascode" id="par_id561542204357496" xml-lang="en-US" localize="false">str2 = "(0.20)"</paragraph>
<paragraph role="bascode" id="par_id341542204361912" xml-lang="en-US" localize="false">str1 = FormatNumber("-0.2", -1, vbUseDefault, vbTrue, vbFalse)</paragraph>
<paragraph role="bascode" id="par_id151542204366706" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id841542204371456" xml-lang="en-US" localize="false">testName = "Test 6: group digits"</paragraph>
<paragraph role="bascode" id="par_id711542204376020" xml-lang="en-US" localize="false">str2 = "-12,345,678.00"</paragraph>
<paragraph role="bascode" id="par_id481542204380404" xml-lang="en-US" localize="false">str1 = FormatNumber("-12345678", -1, vbUseDefault, vbUseDefault, vbTrue)</paragraph>
<paragraph role="bascode" id="par_id971542204385212" xml-lang="en-US" localize="false">msgbox( "FormatNumber returned: " + str1 + ", Expected: " + str2)</paragraph>
<paragraph role="bascode" id="par_id11542204687786" xml-lang="en-US" localize="false">End Sub</paragraph>
</bascode>
<section id="relatedtopics">
<embed href="text/sbasic/shared/00000003.xhp#VBAMath"/>
</section>
</body>
</helpdocument>
|
