VBA Format Function

This tutorial will demonstrate how to use the Format function.

Format Function

Format Numbers with Predefined Formats

Format function can convert a number to string formatted with predefined named numeric formats. Those predefined formats are Currency, Fixed, Standard, Percent, Scientific, Yes/No, True/False, and On/Off.

Sub FormatExample_1()

MsgBox Format(1234567.8) 'Result is: 1234567.8
'Format just leaves the number as it is

MsgBox Format(1234567.8, "Currency") 'Result is: $1,234,567.80
'Currency uses the systems currency settings

MsgBox Format(1234567.8, "Fixed") 'Result is: 1234567.80
'Fixed: At least one digit before decimal point and 
'uses system settings for the decimal part

MsgBox Format(1234567.8, "Standard") 'Result is: 1,234,567.80
'Standard: Thousands separators and standard system

MsgBox Format(1234567.8, "Percent") 'Result is: 123456780.00%
'Percent, multiplies by 100 with % and standard system.

MsgBox Format(1234567.8, "Scientific") 'Result is: 1.23E+06
'Scientific notation

MsgBox Format(1234567.8, "Yes/No") 'Result is: Yes
'No if the number is zero

MsgBox Format(1234567.8, "True/False") 'Result is: True
'False if the number is equal to zero

MsgBox Format(1234567.8, "On/Off") 'Result is: On
'Off if the number is zero

End Sub

Format Numbers with User-Defined Formats

Format function can convert a number to a string, formatted user-defined numeric formats. 0 is a digit placeholder that displays a digit or zero. # is a digit placeholder that displays a digit or nothing. A dot (.) is the decimal placeholder, % is the percentage placeholder and comma (,) is the thousands separator. Text can be added in the format using double quotes (“”) and a single character can be added if it is used after a backslash (\).

Sub FormatExample_2()
MsgBox Format(7.8, "000.00")        'Result is: 007.80
MsgBox Format(12347.8356, "000.00") 'Result is: 12347.84
MsgBox Format(7.8, "###.##")        'Result is: 7.8
MsgBox Format(12347.8356, "###.##") 'Result is: 12347.84
MsgBox Format(7.8, "\$.00")         'Result is: $7.80
MsgBox Format(1237.835, "ABA0.00")  'Result is: ABA1237.84

MsgBox Format(12347.8356, "000.00%") 'Result is: 1234783.56%
MsgBox Format(12347.8356, "%000.00") 'Result is: %12347.84
End Sub

Format Numbers Depending on the Values

Format function can have different sections using different format rules for positive numbers, negative numbers, zero, and Null. These sections are separated by a semicolon.

Sub FormatExample_3()
MsgBox Format(7.8, "000.00;(000.00);\z\e\r\o;nothing")       'Result is: 007.80
MsgBox Format(-7.8, "000.00;(000.00);\z\e\r\o;nothing")      'Result is: (007.80)
MsgBox Format(0, "000.00;(000.00);\z\e\r\o;nothing")         'Result is: zero
MsgBox Format(Null, "000.00;(000.00);\z\e\r\o;nothing")      'Result is: nothing

End Sub

Format Dates with Predefined Formats

Format function can format dates with different predefined formats. Those formats are long, medium, and short date and also long, medium, and short time.

Sub FormatExample_4()
Dim DateEx As Date
DateEx = #4/18/2020 7:35:56 PM#

MsgBox Format(DateEx, "General Date")   'Result is: 4/18/2020 7:35:56 PM

MsgBox Format(DateEx, "Long Date")      'Result is: Saturday, April 18, 2020
MsgBox Format(DateEx, "Medium Date")    'Result is: 18-Apr-20
MsgBox Format(DateEx, "Short Date")     'Result is: 4/18/2020

MsgBox Format(DateEx, "Long Time")      'Result is: 7:35:56 PM
MsgBox Format(DateEx, "Medium Time")    'Result is: 07:35 PM
MsgBox Format(DateEx, "Short Time")     'Result is: 19:35
End Sub

Format Dates with User-Defined Formats

Format function can format dates with user-defined formats. Characters like d, m, y, w, q can be used to create custom date formats.

Sub FormatExample_5()
Dim DateEx As Date
DateEx = #4/18/2020 7:35:56 PM#

MsgBox Format(DateEx, "m/d/yy")      'Result is: 4/18/2020
MsgBox Format(DateEx, "mm-dd-yy")    'Result is: 04-18-2020
MsgBox Format(DateEx, "mmm-dd-yy")   'Result is: Apr-18-2020
MsgBox Format(DateEx, "mmmm-dd-yy")  'Result is: April-18-2020
MsgBox Format(DateEx, "mm-ddd-yy")   'Result is: 04-Sat-2020
MsgBox Format(DateEx, "mm-dddd-yy")  'Result is: 04-Saturday-2020

MsgBox Format(DateEx, "y") 
'Result is: 109
'number of day in year 1-366

MsgBox Format(DateEx, "ww") 
'Result is: 16
'number of week in year 1-52

MsgBox Format(DateEx, "q") 
'Result is: 2
'quarter in year 1-4

End Sub

Characters like h, n, s and am, pm combinations can be used to create custom time formats.

Sub FormatExample_6()
Dim DateEx As Date
DateEx = #4/18/2020 7:06:05 PM#

MsgBox Format(DateEx, "h:n:s")      'Result is: 19:6:5
MsgBox Format(DateEx, "hh:nn:ss")   'Result is: 19:06:05

MsgBox Format(DateEx, "hh:nn:ss am/pm")    'Result is: 07:06:05 pm
MsgBox Format(DateEx, "hh:nn:ss AM/PM")    'Result is: 07:06:05 PM
MsgBox Format(DateEx, "hh:nn:ss a/p")      'Result is: 07:06:05 p
MsgBox Format(DateEx, "hh:nn:ss A/P")      'Result is: 07:06:05 P
End Sub

Format for Better Readability

Format function can format strings or numbers for better readability. @ is a character placeholder used to display a character or space. & is a character placeholder used to display a character or nothing. ! can be used to use character placeholders from left to right and < or > can be used to enforce lower or upper case. Can be useful in formatting telephone numbers or other big numbers without changing the original value.

Sub FormatExample_7()
Dim StrEx As String
StrEx = "ABCdef"

MsgBox Format(StrEx, "-@@@-@@-@@")      'Result is: - AB-Cd-ef
MsgBox Format(StrEx, "-&&&-&&-&&")      'Result is: -AB-Cd-ef
'Starts from right to left.

MsgBox Format(StrEx, "-@@@-@@-@@-@@")   'Result is: -  -AB-Cd-ef
MsgBox Format(StrEx, "-&&&-&&-&&-&&")   'Result is: --AB-Cd-ef
'Starts from right to left. When out of characters @ adds spaces and & adds nothing

MsgBox Format(StrEx, "!-@@@-@@-@@-@@")   'Result is: -ABC-de-f -
MsgBox Format(StrEx, "!-&&&-&&-&&-&&")   'Result is: -ABC-de-f
'Starts from left to right because of the !

MsgBox Format(StrEx, ">")      'Result is: ABCDEF
MsgBox Format(StrEx, "<")      'Result is: abcdef

MsgBox Format(1234567890, "@@@-@@@-@@@@")   'Result is: 123-456-7890
MsgBox Format(1234567890, "@@@@-@@@-@@@")   'Result is: 1234-567-890
End Sub

Format Patterns in Worksheet Cells

Format function can be used in VBA code and also in worksheets cells. Select the cell or range of cells and follow the menu entry Format Cells > Custom. There are many user-defined formats and also the user can create his own custom formats.

 

 

 

 

 

Format Patterns with Text Formula

The format function can directly change the format of a value inside VBA code. We can also use excel Text formula to get the same result using WorksheetFunction.Text.

Sub FormatExample_8()
MsgBox Format(7.8, "000.00")
'Result is: 007.80
MsgBox WorksheetFunction.Text(7.8, "000.00")
'Result is: 007.80

MsgBox Format(7.8, "###.##")
'Result is: 7.8
MsgBox WorksheetFunction.Text(7.8, "###.##")
'Result is: 7.8
End Sub