Custom Formats with Excel VBA

In MS Excel, you can define your own custom number format. A custom format can be set as follows:

• Select the desired range of cells.
• Use Format | Format Cells | Number tab, located in the Cells group on the Home tab (or via the context menu of the selected area).
• In the Format Cells dialog, choose All Formats from the list of numeric formats, then enter the desired custom format in the Type field

Custom formats can consist of four sections, separated by semicolons (;):

1. Positive number format (for positive numbers)
2. Negative number format (for negative numbers)
3. Zero format (for zero)
4. Text format (for text)

When defining custom formats, you can use the symbols shown in Table 1.

Table 1. Custom Format Symbols

To hide numbers using a custom format, leave the section between semicolons blank. Hidden numbers are still present and usable in formulas; they appear in the formula bar when the cell is selected.

To hide zeros:

• Create a custom format with white font for zeros;
• Use the IF() function;
• Or hide zeros for the entire sheet: File > Options > Advanced > Display options for this worksheet > uncheck « Show a zero in cells that have zero value ». Example formula:

=IF(A1+B3=0,  » « , A1+B3)

Creating Custom Date and Time Formats (Table 2)

Examples:

• DDDD → Monday
• MMMM D, YYYY → August 16, 2004
• [Blue] D MMM, YY → 16 Aug, 04 (in blue)

For headers with current date, concatenate text with TEXT() function:

="Today " & TEXT(TODAY(),"D MMM YYYY")

TODAY() updates the current date whenever the worksheet is opened.

Summary of Custom Format Rules:

• # and 0 define digits; 0 shows non-significant zeros, # omits them.
• Spaces are used for thousands separators.
• Comma , defines digits before/after the decimal.
• – before a number indicates negative numbers.
• Colors: [Black], [Blue], [Cyan], [Purple], [Red], [White], [Yellow] can highlight numbers.
• Symbols like -, /, or space can be used as separators.
• Quotes «  » display text literally.
• Conditions use <, >, =, <=, >=, <> in square brackets.
• Semicolons ; separate format sections.
• Currency formats can also include currency symbols.

Table 3. Examples of Custom Formats

Custom Formats in VBA
You can also use the symbols from Table 1 in VBA with the Format() function.

Table 4. Examples in VBA

Number Formatting
In VBA, numbers can be formatted using the specialized function FormatNumber().

FormatNumber(Expression[, NumDigitsAfterDecimal [, IncludeLeadingDigit [, UseParensForNegativeNumbers [, GroupDigits]]]])

• Expression — required parameter specifying the numeric expression to format.
• NumDigitsAfterDecimal — optional parameter specifying the number of digits to display after the decimal separator. Valid values: vbTrue, vbFalse, vbUseDefault.
• IncludeLeadingDigit — optional parameter specifying whether to display a leading zero. Valid values: vbTrue, vbFalse, vbUseDefault.
• UseParensForNegativeNumbers — optional parameter specifying whether negative numbers should be displayed in parentheses. Valid values: vbTrue, vbFalse, vbUseDefault.
• GroupDigits — optional parameter specifying whether to group digits. Valid values: vbTrue, vbFalse, vbUseDefault.

Table 5. Examples of Using FormatNumber()

Percent Formatting
To format percentages in VBA, use the specialized function FormatPercent(), which has the same syntax as FormatNumber().

Example:

x = 0.2342
Debug.Print FormatPercent(x, 2)

Output in the Immediate window: 23.42% — i.e., the number formatted as a percentage with two digits after the decimal point.

Currency Formatting
To display numbers in currency format in VBA, use the specialized function FormatCurrency(), which has the same syntax as FormatNumber().

Example:

FormatCurrency(12312.3453, 2)

Output: 12,312.35

Date and Time Formatting
To format dates and times in VBA, use the specialized function FormatDateTime().

FormatDateTime(Date[, NamedFormat])

• Date — required parameter specifying the date to format.
• NamedFormat — optional parameter specifying the formatting style. Valid values: vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, vbShortTime.

Example: Suppose this line was written on April 15, 2011, at 18:15.

FormatDateTime(Now, vbShortTime) ' Returns 18:15
FormatDateTime(Now, vbShortDate) ' Returns 15.04.2011