Creative Blog

Étiquette : FORMU

  • Number Formatting in VBA with Excel VBA

    To display a numeric value as a date, time, currency, or in a special format in VBA, use the Format() function, which returns a Variant (String) containing the expression formatted according to the format description.

    Format(Expression[, Format[, FirstDayOfWeek [, FirstWeekOfYear]]])
    • Expression — any valid expression.
    • Format — any valid named or user-defined format expression. For example, the named format Fixed displays a numeric value with two decimal places. Named format examples are shown in Tables 1 and 2.
    • FirstDayOfWeek — constant specifying the first day of the week.
    • FirstWeekOfYear — constant specifying the first week of the year.

    Table1. Named Numeric Formats

    Format Name Description
    General Number Number without a thousands separator
    Currency Uses system regional settings. Displays two decimal digits
    Fixed At least one digit to the left and two to the right of the decimal point
    Standard At least one digit to the left, two to the right, and shows thousands separator
    Percent Displays the number as a percentage with two decimal digits
    Scientific Uses floating-point scientific notation
    Yes/No Displays No if the number is 0, Yes otherwise
    True/False Displays False if the number is 0, True otherwise
    On/Off Displays Off if the number is 0, On otherwise

    Table 2. Named Date and Time Formats

    Format Name Description
    General Date Displays date or time. If no fractional part, displays date only
    Long Date Displays date according to Windows long date format
    Medium Date Displays date according to standard Windows date format
    Short Date Displays date according to short Windows date format
    Long Time Displays hours, minutes, and seconds
    Medium Time Displays hours and minutes in 12-hour format
    Short Time Displays hours and minutes in 24-hour format

    For example, the following code  outputs the formatted values to the Immediate Window.

    Examples of Named Formats

    Sub Frm()
    Dim x As Double
    x = 4654646.544564   
    Debug.Print "General Number", Format(x, "General Number")
    Debug.Print "Currency", Format(x, "Currency")
    Debug.Print "Fixed", Format(x, "Fixed")
    Debug.Print "Standard", Format(x, "Standard")
    Debug.Print "Percent", Format(x, "Percent")
    Debug.Print "Scientific", Format(x, "Scientific")
    Debug.Print "Yes/No", Format(x, "Yes/No")
    Debug.Print "True/False", Format(x, "True/False")
    Debug.Print "On/Off", Format(x, "On/Off")   
    Debug.Print "General Date", Format(Now, "General Date")
    Debug.Print "Long Date", Format(Now, "Long Date")
    Debug.Print "Medium Date", Format(Now, "Medium Date")
    Debug.Print "Short Date", Format(Now, "Short Date")
    Debug.Print "Long Time", Format(Now, "Long Time")
    Debug.Print "Medium Time", Format(Now, "Medium Time")
    Debug.Print "Short Time", Format(Now, "Short Time")
End Sub

    Formatted Values in the Immediate Window