Macros/VBA Function - Format

Hi VBA/Macro Learners,

Today we will see how to  use FORMAT Function in VBA to get modify the format of Numbers,Dates,Times & String.

Syntax: FORMAT(expression,format,[firstdayofweek],[firstweekofyear])

'Ex1: Modify the Format of Numbers using FORMAT function

SUB Format_Numbers()

MsgBox format(8686836999, "####-###-###")

'Output: '8686-836-999'

MsgBox format(143.5, "#,##.00")

'Output: '143.50'

MsgBox format(1729.4, "Currency")

'Output: '$1,729.40'

MsgBox format(0.9156, "Percent")

'Output: '91.56%'

MsgBox format(1729.4, "Standard")

'Output: '1,729.40'

End Sub

'Ex2: Modify the Format of Text using FORMAT function

Sub FORMAT_Text()

MsgBox format("EXCEL IS MAGIC", "<")

'Output: 'excel is magic' (convert text to lower case)

MsgBox format("excel is magic", ">")

'Output: 'EXCEL IS MAGIC' (convert text to upper case)

End Sub

'Ex3: Modify the Format of Dates using FORMAT function

Sub FORMAT_Dates()

MsgBox format("3/21/2017", "Short Date")

'Output: '3/21/2017'

MsgBox format("3/21/2017", "Medium Date")

'Output: '21-Mar-17'

MsgBox format("3/21/2017", "Long Date")

'Output: 'Tuesday, March 21, 2017'

MsgBox format("3/21/2017", "mmm dd")

'Output: 'Mar 21'

End Sub

'Ex4: Modify the Format of Time using FORMAT function

Sub FORMAT_Time()

MsgBox format("6:30:45", "Short Time")

'Output: '06:30'

MsgBox format("6:30:45", "LONG Time")

'Output: '6:30:45 AM'

MsgBox format("6:30:45", "MEDIUM Time")

'Output: '06:30 AM'

MsgBox format("14:30:45", "HH:MM")

'Output: '14:30'

End Sub