To visually specify that you want a cell to display its numeric value with the comma delimiter, click the cell to give it focus. Then, in the Home tab of the Ribbon, in the Number section, click the Comma Style button . The thousand numbers would display with a comma sign which makes it easier to read.
To visually control the number of decimal values on the right side of the comma, in the Number section of the Ribbon:
You can click the Decrease Decimal button to remove one decimal value. You can continuously click the Decrease Decimal button to decrease the number of digits.
You can click the Increase Decimal button to increase the number of digits
To programmatically specify that you want a cell to display the comma style of number, assign the "Comma" string to the Style property of the Range class. Here is an example:
Sub SpecifyComma()
ActiveCell.Style = "Comma" End Sub
Alternatively
, to programmatically control how the number should display, you can pass the second argument to the Format() function. To produce the number in a general format, you can pass the second argument as "g", "G", "f", or "F" .
To display the number with a decimal separator, pass the second argument as "n", "N", or
"Standard". Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "STANDARD") End Sub
An alternative to get this format is to call a function named FormatNumber. Its syntax is:
Function FormatNumber(
ByVal Expression As Variant,
Optional ByVal NumDigitsAfterDecimal As Integer = -1, Optional ByVal IncludeLeadingDigit As Integer,
Optional ByVal UseParensForNegativeNumbers As Integer,
Optional ByVal GroupDigits As Integer ) As String
Only the first argument is required and it represents the value to display. If you pass only this argument, you get the same format as the Format() function called with the Standard option. Here is an example:
Sub Exercise()
Dim Number As Double Number = 20502.48
ActiveCell = FormatNumber(Number) End Sub
This would produce the same result as above.
If you call the Format() function with the Standard option, it would consider only the number of digits on the right side of the decimal separator. If you want to display more digits than the number actually has, call the FormatNumber() function and pass a second argument with the desired number. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = FormatNumber(Number, 4) End Sub
In the same way, if you want the number to display with less numbers on the right side of the decimal separator, specify that number.
We saw that you could click the Decrease Decimal button on the Ribbon to visually control the number of decimal values on the right side of the comma and you could continuously click that button to decrease the number of digits. Of course, you can also exercise this control programmatically.
You can call the Format() function to format the number with many more options. To represent the integral part of a number, you use the # sign. To specify the number of digits to display on the right side of the decimal separator, type a period on the right side of # followed by the number of 0s representing each decimal place. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "#.00000") End Sub
The five 0s on the right side of the period indicate that you want to display 5 digits on the right side of the period. You can enter as many # signs as you want; it would not change anything. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "##########.00000") End Sub
This would produce the same result as above. To specify that you want to display the decimal separator, include its character between the # signs. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "###,#######.00000") End Sub
You can include any other character or symbol you want in the string to be part of the result, but you should include such a character only at the beginning or the end of the string, otherwise the interpreter might give you an unexpected result.
Practical Learning: Using the SUM Function
Locate the CreateWorkbook procedure and change its code as follows:
Sub CreateWorkbook() '
' CreateWorkbook Macro '
' Keyboard Shortcut: Ctrl+Shift+W '
. . . No Change
Rem Format the values in the unit prices Range("D15").Style = "Comma" Range("D16").Style = "Comma" Range("D17").Style = "Comma" Range("D18").Style = "Comma" Range("D19").Style = "Comma" Range("D20").Style = "Comma" 1.
157
Rem Format the values in the sub totals Range("F15").Style = "Comma" Range("F16").Style = "Comma" Range("F17").Style = "Comma" Range("F18").Style = "Comma" Range("F19").Style = "Comma" Range("F20").Style = "Comma"
Rem Format the values in the Order Summary section Range("I17").Style = "Comma"
Range("I19").Style = "Comma" Range("I20").Style = "Comma"
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False End Sub
Return to Microsoft Excel and press Ctrl + Shift + W 2.
Enter the following values in the worksheet:
Receipt #: 1001 Order Status: Processing
Customer Name: Raymond Fuqua Customer Phone: (140) 173-9024 Date Left: 12/19/2008 Time Left: 09:42 AM Date Expected: 12/22/2008 Time Expected: 08:00 AM
Unit Price Qty
Shirts 1.25 4 Pants 1.95 2 Jacket 4.50 1 3. Click cell A1 4. Press Ctrl + Shift + C 5.
158
Return to Microsoft Visual Basic 6.