VBA FORMAT Function

VBA Format Function

Overview of VBA Format Function:

VBA Format function is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. VBA Format function returns a formatted string from a string expression. This function has one required parameter and three optional parameters. If Format argument is left blank, then the function behaves like the CSTR function.

This function could be used as a VBA function and can’t be used as a Excel Worksheet function. The VBA Format function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Format function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Format function, where we can use this Format function and real-time examples in VBA.

Syntax of VBA Format Function

The syntax of the VBA Format function is

Format(Expression, [Format], [FirstDayOfWeek],[FirstWeekOfYear])

Note: This Format function returns a string.

Parameters or Arguments

This function has one mandatory parameter and three optional parameters for the Format Function.
Where
Expression: The Expression is a mandatory argument. It represents an expression which you want to format.

Format: The Foramt is an optional argument. It represents the user defined or below specified built-in format. It is applied to an Expression.

VBA Date Formats: Here are the following list of built-in Date formats.

Format Description
General Date It displays date as defined in your system general Date settings. It displays short date and short time.
Long Date It displays date as defined in your system Long Date settings
Medium Date It displays date as defined in your system Medium Date settings
Short Date It displays date as defined in your system Short Date settings
Long Time It displays time as defined in your system Long time settings
Medium Time It displays time as defined in your system Medium time settings
Short Time It displays time as defined in your system Short time settings

VBA Number Formats: Here are the following list of built-in Number formats.

Format Description
General Number It displays a number without any thousand separators.
Currency It displays a number with thousand separators and two decimal places.
Euro It displays a number with the euro currency symbol.
Fixed It displays at least one integer digit and two decimal places number.
Standard It dispplays a number with thousand separators, at least one integer digit and two decimal places.
Percent It displays the number to the percentage form and adds % sign and rounds it up to two decimal places.
Scientific It displays a number in scientific notation.
Yes/No It displays No if the number is equal to zero or Yes otherwise.
True/False It displays False if the number is equal to zero or True otherwise.
On/Off It displays Off if the number is equal to zero or On otherwise.

VBA Text Formats: Here are the following list of Text and Memo formats.

Format Symbol Description
@ Text character is required.
& Text character is not required.
< Convert all characters to lowercase.
> Convert all characters to uppercase.

FirstDayOfWeek: The FirstDayOfWeek is an optional argument. It represents the first day of week. This argument uses the default value vbSunday (Sunday).

VBA Constant Value Description
vbUseSystem 0 Uses the NLS API setting (The first day of the week specified in system settings)
VbSunday 1 Sunday
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

FirstWeekOfYear: The FirstWeekOfYear is an optional argument. It represents the first week of the year. This argument uses the default value vbFirstJan1 (1st January).

VBA Constant Value Description
vbUseSystem 0 Uses the NLS API setting.
vbFirstJan1 1 The week that conatins 1st Jan in the year.
vbFirstFourDays 2 The first week that conatins atleast 4 days in the year.
vbFirstFullWeek 3 The first full week of the year.

Where we can apply or use the VBA Format Function?

We can use this VBA Format function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Format Date and Time

Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Date and Time Format function.

'Format Date and Time
Sub VBA_Format_Function_Ex1()

    Dim sDate As String, sTime As String
    Dim sDateTime As String
    Dim sOutput As String, sOutput1 As String, sOutput2 As String
    
    sDate = Date:    sTime = Time
        
    sDateTime = sDate & " " & sTime
    
    sOutput = Format(sDateTime)
    
    MsgBox "General Date & Time Format : " & vbCrLf & sOutput, vbInformation, "VBA Format Function"
    
    '--------------------------------------------------------------------------------------
    sOutput1 = Format(sDate, "Medium Date")
    sOutput2 = Format(sTime, "Medium time")
        
    MsgBox "Medium Date Format : " & sOutput1 & vbCrLf & "Medium Time Format : " & sOutput2, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput1 = Format(sDate, "Long Date")
    sOutput2 = Format(sTime, "Long time")
        
    MsgBox "Long Date Format : " & sOutput1 & vbCrLf & "Long Time Format : " & sOutput2, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput1 = Format(sDate, "dddd mm/dd/yyyy")
    sOutput2 = Format(sTime, "hh:mm:ss AMPM")
        
    MsgBox "User defined Date Format : " & sOutput1 & vbCrLf & "User defined Time Format : " & sOutput2, vbInformation, "VBA Format Function"
     
End Sub

Output: Here is the screen shot of the first example output.
VBA Format Function

Example 2: Format Numbers and Currency

Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Number and Currency Format function.

'Format Numbers and currencies
Sub VBA_Format_Function_Ex2()

    Dim sValue As String, sValue1 As String
    Dim sOutput As String
    
    sValue = 0.1234: sValue1 = 12345
    
    sOutput = Format(sValue) ' General Number Format
    
    MsgBox "General Number Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "Standard") '
    
    MsgBox "Standard Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "Fixed") '
    
    MsgBox "Fixed Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue, "Currency") '
    
    MsgBox "Currency Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue, "Percent") '
    
    MsgBox "Percent Format : " & sOutput, vbInformation, "VBA Format Function"
     
End Sub

Output: Here is the screen shot of the second example output.
VBA Format Function

Example 3: Format Text/String

Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Text/String Format function.

'Format Text/String
Sub VBA_Format_Function_Ex3()

    Dim sValue As String, sValue1 As String
    Dim sOutput As String
    
    sValue = "Welcome to VBAF1": sValue1 = "999999999"
    
    sOutput = Format(sValue, ">")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
   sOutput = Format(sValue, "<")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
      sOutput = Format(sValue1, "@@@@@@@@@")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "@@@-@@@-@@@")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "@@@")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
   sOutput = Format(sValue1, "@@@-&&&-@@@")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    
End Sub

Output: Here is the screen shot of the third example output.
VBA Format Function

Example 4: User Defined Format

Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA User Defined Format function.

'User Defined Format
Sub VBA_Format_Function_Ex4()

    Dim sValue As String, sValue1 As String
    Dim sOutput As String
    
    sValue = 12345.678: sValue1 = 0.1357
    
    sOutput = Format(sValue, "0.000")
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue, "##,##0") '
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue, "$##,##0.00") '
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue, "£##,##0.00") '
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "0%") '
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
    '--------------------------------------------------------------------------------------
    sOutput = Format(sValue1, "0.00%") '
    
    MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function"
     
End Sub

Output: Here is the screen shot of the fourth example output.
VBA Format Function

Instructions to use Macro Codes

Here are the step by step instructions to use the VBA Format function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

List of VBA Functions

2 thoughts on “VBA Format Function”

Leave a Comment

Your email address will not be published. Required fields are marked *