Return to VBA Code Examples

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.

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 (\).

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.

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.

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.

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

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.

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 cells custom menu screen

 

 

 

 

 

VBA Programming | Code Generator does work for you!

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

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.