How to use Format Function in Excel VBA

Welcome to Excel Avon

Format Function in Excel VBA

Format function in VBA one may use to format the given values in the desired format. The FORMAT is a VBA function available only in VBA, not in the worksheet. This function has two mandatory arguments: input taken in the form of a string, and the second argument is the type of format we want to use. We can also use our date, time, and number formatting codes, like how we use them in worksheet formatting. The value returned by the FORMAT function is the string.

To understand the function, look at the below syntax of the function.

Format (Expression, [Format], [fisrtdayofweek as vbsunday], [Fisrtweekofyear as vbFirstWeekofYear]=vbFirstJan1)

Arguments-

Expression: The value we want to format. In VAB technicality, it is called Expression.

Format: What format do you want to apply to the selected expression?

[First Day of Week]: It is optional. If this argument or parameter is omitted, it assumes Sunday (VbSunday) as the first day of the week.

[First Week of the Year]: It is optional. If this argument or parameter is omitted, it assumes January 1st (vbFirstJan1) as the first week of the year.

To understand for Format Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

format-in-excel-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

format-function-in-Excel-VBA

To understand Format Function in Excel VBA, we will write a subroutine for Format Function in Excel VBA after inserting the module in VBE.

Sub UseFormatFunction()

End Sub

After writing the subroutine we will define the variable

Sub UseFormatFunction() 
Dim inpVal As Variant
Dim Result as String
End Sub

Now we will store the time in inpVal

Sub UseFormatFunction() 
Dim inpVal As Variant
Dim Result as String

inpVal = Now 
End Sub

Now we will use format function with expression and write format.

Sub UseFormatFunction() 
Dim inpVal As Variant
Dim Result as String

inpVal = Now
Result = Format(inpVal, "dd-mmm-yy hh:mm:ss")
End Sub

Use debug. Print for printing result

Sub UseFormatFunction() 
Dim inpVal As Variant
Dim Result as String

inpVal = Now
Result = Format (inpVal, "dd-mmm-yy hh:mm:ss")

debug.Print Result
End Sub

Click Run button,

format-function-in-excel

Now you can see that the time which we had stored is printed in the immediate window.

format-function-in-excel.1

Now we will use ‘dddd’ for the name of day in the format and also add AM/PM.

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String 

inpVal = Now 
Result = Format (inpVal, "dd, dddd mmm-yy hh:mm AM/PM") 

debug.Print Result 
End Sub

Click the Run button

format-function-in-excel

We can see that we have printed the day name and 12-hour format along with the date.

format-function-in-excel (2)

Now we will only write the time format

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String 

inpVal = Now 
Result = Format (inpVal, "hh:mm AM/PM") 

debug.Print Result 
End Sub

Click the Run button,

format-function-in-excel

Now you can see the time format printed.

format-function-in-excel (3)

Now I will store some numeric value in InpVal

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String

inpVal = 100000 
End Sub

We will print the stored value in number format and use the ‘#’ symbol which work like the 0-digit placeholder.

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String

inpVal = 100000
Result =Format(InpVal, "#,000.00")
End Sub

Now we debug. will write print and print the result

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String

inpVal = 100000
Result =Format(InpVal, "#,000.00")

debug.Print Result 
End Sub

Click the Run button,

format-function-in-excel

The result has been printed in the immediate window which was done in number format.

format-function-in-excel (4)

If you make some changes to the numeric value stored in inpVal,

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String

inpVal = 10000
Result =Format (InpVal, "#,000.00") 

debug. Print Result 
End Sub

Click the Run button,

format-function-in-excel

The result is printed on immediate window

format-function-in-excel (5)

Let’s store some other value in inpval and use the number format in the format function.

Sub UseFormatFunction() 
Dim inpVal As Variant 
Dim Result as String 

inpVal = 89 
Result =Format (InpVal, "0000") 

debug. Print Result 
End Sub

Click the Run button,

format-function-in-excel

This will print 4 digit (according to 4 zeros) of number

Excel VBA Format Function

Store more value in inpval, and use currency format with placeholder ‘#’ and use symbol ‘$’.

Sub UseFormatFunction() 
Dim inpVal As Variant
Dim Result as String

inpVal = 89000
Result =Format (InpVal, "$ #,000.00") 

debug. Print Result 
End Sub

Click the Run button,

format-function-in-excel

Now you can see, we have printed the value in currency format.

format-function-in-excel (6)

So, I hope you have understood How to use format Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Format Function in Excel VBA