VBA Format

Excel VBA Format Function

Format function in VBA is used to format the given values in the desired format, this function can be used for formatting dates or numbers or any trigonometrical values, this function has basically two mandatory arguments, one is the input which is taken in the form of a string and the second argument is the type of format we want to use for example if we use Format (.99,” Percent”) this will give us the result as 99%.

In VBA we need to use the function called “FORMAT” to apply to format to cells. Excel formatting is one of the important concepts to master. The common formatting techniques we all use in our daily work are “date format, time format, number formatting, and other important formatting codes”. In regular excel worksheet we just simply hit the format excel cell option and perform the formatting duty by applying the appropriate formatting code. However, in VBA this isn’t that straight forward like our worksheet technique.

Syntax

• Expression: This is nothing but the value we want to format. In VAB technicality it is called as Expression.
• [Format]: What is the format you want to apply to the expression you have selected? We have two kinds of formatting here, one is the user-defined format and the second one is the built-in format.
  Here we have VBA Date Formats, Number Formats, and Text Formats.
  VBA Date Formats have a short date, long date, medium date, and general date.
  Number Formats has Currency, Standard, Percentage, Scientific, Yes or No, True or False, and On or Off
• [First Day of the Week]: What is the first day of your week? We can select any day from the list. Below is the list of days and appropriate codes.

• [First Week of the Year]: What is the first week of the year? This specifies the week that should be used as the very first week of the year.

How to Use?

Ok, let’s apply this function practically to understand the functionality of FORMAT function. Assume you have the number 8072.56489 and you want to apply number formatting to it. Follow the below steps to apply number formatting to it.

Step 1: Start an excel macro and define the variable as a “string” data type.

Code:

Sub Worksheet_Function_Example1()

  Dim K As String

End Sub

Step 2: Assign a value to k as our number i.e. 8072.56489

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = 8072.56489

End Sub

Step 3: Show “k” value in the VBA message box.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = 8072.56489
  MsgBox K

End Sub

Step 4: If you run this macro we will get below result.

The result is as it is we assigned the value to variable “k”. But we need to apply some formatting to this number to make it beautiful.

Step 5: Instead of directly assigning a value to “k” let’s use the FORMAT function.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(
  MsgBox K

End Sub

Step 6: Now for Expression assign the number 8072.56489.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(8072.56489,
  MsgBox K

End Sub

Step 7: In the formatting option we can either use built-in format or we can use our own formatting code. Now I will use a built-in formatting style as “Standard”.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(8072.56489, "Standard")
  MsgBox K

End Sub

Step 8: Now run this code and see the result of the message box.

Ok, we have got comma (,) as thousand separators and decimal is rounded up to two digits only.

Like this, we can use many other built-in formatting styles to apply the formatting. Below are some of the codes I have applied.

#1 – Currency Format

Code:

Sub Worksheet_Function_Example2()
  Dim K As String

  K = Format(8072.56489, "Currency")
  MsgBox K

End Sub

Result:

#2 – Fixed Format

Code:

Sub Worksheet_Function_Example3()
  Dim K As String

  K = Format(8072.56489, "Fixed")
  MsgBox K

End Sub

Result:

#3 – Percent Format

Code:

Sub Worksheet_Function_Example4()
  Dim K As String

  K = Format(8072.56489, "Percent")
  MsgBox K

End Sub

Result:

#4 – User-Defined Formats

Ok, now we will see some of the user-defined formats.

Code:

Sub Worksheet_Function_Example5()
  Dim K As String

  K = Format(8072.56489, "#.##")
  MsgBox K

End Sub

Result:

Code:

Sub Worksheet_Function_Example5()
  Dim K As String

  K = Format(8072.56489, "#,##.##")
  MsgBox K

End Sub

Result:

#5 – Date FORMAT

We have seen some of the important numbers of formatting techniques. Now we will have to use FORMAT function to format date in VBA.

I have written code to show the result of the date through the variable.

Code:

Sub Worksheet_Function_Example6()
  Dim K As String

  K = 13 - 3 - 2019
  MsgBox K

End Sub

When I run this code I would not get an accurate date rather result is pathetic.

In order to get the accurate dates, we need to assign the date format to it. The first thing is we need to do is supply the date in double-quotes and apply the date format.

Code:

Sub Worksheet_Function_Example6()
  Dim K As String

  K = Format("10 - 3 - 2019", "Long Date")
  MsgBox K

End Sub

If run this code now, I will get a proper long date.

“Long Date” is a built-in format, similarly you can use “short date”, and “medium date” options.

Things to Remember

• The value returned by FORMAT function is the string.
• We can also use our own date, time, and number formatting codes like how we use in worksheet formatting.
• FORMAT is a VBA function and available only in VBA not in the worksheet.

Recommended Articles

This has been a guide to VBA Format Function. Here we learned how to use VBA Format Function for currency, fixed, percentage, and date formatting along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

• VBA Data Type
• VBA DIR Function
• VBA MOD
• Create VBA InputBox