|
Be the first user to complete this post
|
Add to List |
Excel-VBA : Math Functions – FORMAT()
Description: The FORMAT() function in MS excel takes a number and returns it as formatted string based on specified parameter.
Format:
VBA Function : FORMAT (number,[format])
Arguments:
- Number
- Mandatory
- Type: number
- number for which the formatted string will be returned
- Format
- Optional
- Type: string expression
- format in which the number will be returned.
Formats
| Format | Details |
| General Number | This format displays a number without thousand separators. |
| Currency | This format displays a number with thousand separators, along with two decimal places. |
| Fixed | This format displays at least one digit to the left of the decimal place and two digits to the right of the decimal place. |
| Standard | This format displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place. |
| Percent | This format displays a percent value - with 2 decimal places |
| Scientific | This format uses Scientific notation. |
| Yes/No | This format displays No if the number is 0 and displays Yes if the number is not 0. |
| True/False | This format displays True if the number is 0 and displays False if the number is not 0. |
| On/Off | This format displays Off if the number is 0 and displays On is the number is not 0. |
Examples:
| Number | Format | Output |
| 110.2 | Standard | 110.2 |
| 120.5 | Currency | $120.5 |
| 0.44.2 | Precent | 44.20% |
| 78 | Yes/No | Yes |
| 121212 | True/False | TRUE |
| 0 | On/Off | Off |
Example:
Function getFormat()
val1 = 110.2
val2 = 133.9
val3 = 0.882
val4 = 12345
val5 = 12
val6 = 0
strResult = "The FORMAT of " & val1 & " in Standrad is : " & Format(val1, "Standard") & vbCrLf
strResult = strResult & "The FORMAT of " & val2 & " in Currency is : " & Format(val2, "Currency") & vbCrLf
strResult = strResult & "The FORMAT of " & val3 & " in Percent is : " & Format(val3, "Percent") & vbCrLf
strResult = strResult & "The FORMAT of " & val4 & " in Yes/No is : " & Format(val4, "Yes/No") & vbCrLf
strResult = strResult & "The FORMAT of " & val5 & " in True/False is : " & Format(val5, "true/False") & vbCrLf
strResult = strResult & "The FORMAT of " & val6 & " in On/Off is : " & Format(val6, "On/Off") & vbCrLf
MsgBox strResult
End Function
