How to use Format function in VBA?

The Format function in VBA (Visual Basic for Applications) is used to convert a value into a specified format. It’s particularly useful for formatting dates, times, numbers, and strings to display them in a user-friendly way or to prepare them for printing, reporting, or further processing.

Here’s how you can use the Format function in VBA:

Formatting Dates and Times

Date Formatting

You can format dates in a variety of ways using the Format function.

Dim formattedDate As String 
formattedDate = Format(Now, "mm/dd/yyyy") 
' Result: "01/14/2024" if Now = January 14, 2024

Time Formatting

Similarly, time can be formatted using different time format strings.

Dim formattedTime As String
formattedTime = Format(Now, "HH:MM:SS AM/PM")
' Result: "09:30:00 PM" for example

Formatting Numbers

Decimal Places

Format numbers to a specific number of decimal places.

Dim formattedNumber As String
formattedNumber = Format(123.4567, "0.00")
' Result: "123.46"


Format as currency

Dim formattedCurrency As String
formattedCurrency = Format(123.456, "Currency")
' Result: "$123.46" depending on the regional settings


Format a number as a percentage

Dim formattedPercentage As String
formattedPercentage = Format(0.876, "Percent")
' Result: "87.60%"

Formatting Numbers

Custom String Formats

You can also format strings to a particular pattern, like phone numbers or postal codes.

Dim formattedString As String
formattedString = Format("1234567890", "(###) ###-####")
' Result: "(123) 456-7890"

General Tips

  • The Format function is very versatile and allows for a wide range of formatting types.
  • The actual result can depend on your system’s regional settings, especially for currency and date formats.
  • You can use custom format strings to create your own formatting patterns.
  • Always test your format strings to ensure they produce the expected output, as incorrect format strings can lead to unexpected results or errors.

The Format function is a powerful tool in VBA for making data more readable and presentable. It’s widely used in scenarios involving UI display, reporting, and data processing in Excel, Access, and other VBA-supported applications.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

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