The Format$ Function – Part 5
(Multiple Formats)
A user-defined format expression can have from one to four sections separated by semicolons. (If the format argument contains one of the named formats, only one section is allowed.)
|
If you use |
The result is |
|
One section |
The format expression applies to all values. |
|
Two sections |
The first section applies to positive values and zeros, the second to negative values. |
|
Three sections |
The first section applies to positive values, the second to negative values, and the third to zeros. |
|
Four sections |
The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values. |
The following example has two sections: the first defines the format for positive values and zeros; the second section defines the format for negative values.
$#,##0;($#,##0)
If you include semicolons with nothing between them, the missing section is printed using the format of the positive value. For example, the following format displays positive and negative values using the format in the first section and displays "Zero" if the value is zero.
$#,##0;;\Z\e\r\o
The following table shows would be output, given the indicated format string and inputs of 5, -5, .5, and Null, respectively. Bear in mind that the only standard VB data type that can contain Null is the Variant. Nulls would be most likely found in database fields.
|
Format String |
Positive 5 |
Negative 5 |
Decimal .5 |
Null |
|
Zero-length string |
5 |
-5 |
0.5 |
|
|
0 |
5 |
-5 |
1 |
|
|
0.00 |
5.00 |
-5.00 |
0.50 |
|
|
#,##0 |
5 |
-5 |
1 |
|
|
#,##0.00;;;Nil |
5.00 |
-5.00 |
0.50 |
Nil |
|
$#,##0;($#,##0) |
$5 |
($5) |
$1 |
|
|
$#,##0.00;($#,##0.00) |
$5.00 |
($5.00) |
$0.50 |
|
|
0% |
500% |
-500% |
50% |
|
|
0.00% |
500.00% |
-500.00% |
50.00% |
|
|
0.00E+00 |
5.00E+00 |
-5.00E+00 |
5.00E-01 |
|
|
0.00E-00 |
5.00E00 |
-5.00E00 |
5.00E-01 |
|
To demonstrate multiple formats, set up another "Try It" project, and place the following code in the cmdTryIt_Click event (note: this test only uses the first three sections of the multi-format string; Null values will not be tested for):
Private Sub cmdTryIt_Click()
Dim strUserInput As String
Dim dblTestNumber As Double
strUserInput = InputBox("Please enter a number to demonstrate multiple formats:")
dblTestNumber = Val(strUserInput)
Print "Demo using the multi-format string '###,##0.00;(###,##0.00);\ '"
Print "Your input was: "; dblTestNumber
Print "Using the multi-format, the output is: "; _
Format$(dblTestNumber, "###,##0.00;(###,##0.00);\ ")
End Sub
Run the project and click the "Try It" button. When the input box appears, enter a positive, negative, or zero test value and examine the results. You can click the "Try It" button multiple times to see how different values will be formatted. When the form fills up, you can use the "Clear" button. Shown below is a run where first a positive, then a negative, and then a zero value was input:
Download the VB project code for the example above here.