VBA format function in Excel

This function is used to convert a string into any other format supported by Excel.

There are a number of ways by which a string can be expressed and this preference

changes from one user to another.So there is a need to display a data according to the

need of the user.The format() function fulfills this requirement.

Syntax

Format(expression,format_type)

Here, The 'expression' represents the string to be formatted and the 'format_type' specifies, new format type.

The fomat type involves Date format, Time format, Currency and Custom formats.

The following example illustrates these formats.

  1. Sub formats()
  2. Dim MyTime, MyDate, MyStr
  3. MyTime = #8:32:14 PM#
  4. MyDate = #3/1/1986#
  5. MsgBox "Various date formats"
  6. MsgBox Format(Date, "Short Date")
  7. MsgBox Format(Date, "Long Date")
  8. MsgBox Format(MyDate, "dddd, mmm d yyyy")
  9. MsgBox "Various Time Formats"
  10. MsgBox Format(MyTime, "h:m:s")
  11. MsgBox Format(MyTime, "hh:mm:ss AMPM")
  12. MsgBox "User defined formats"
  13. MsgBox Format(5459.4, "##,##0.00")
  14. MsgBox Format(334.9, "###0.00")
  15. MsgBox Format(5, "0.00%")
  16. MsgBox Format("HELLO", "<")
  17. MsgBox Format("This is it", ">")
  18. End Sub

The screenshot of the editor:

excel vba format function

Output

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

excel vba format function

 

You can find similar Excel Questions and Answer hereunder

1) How to convert a number or string into a data with the cdate function

2) What are the various functions of the worksheet in Excel VBA

3) How can I clear all formats (formats alone not data) from a range of cells?

4) How can worksheet functions be accessed in VBA?

5) Concatenate number with text but keeping in number format in Excel

6) How to modify strings in VBA, what are the functions you can manipulate strings with

7) How do I have proper (Capitalize the first letter in each word of a text) text in cells ?

8) Various important math functions in VBA (trigonometry, algebra, exponential, �)

9) What is variable scope in VBA, how do variable can be reach from other function or routines in VBA

10) What are the date and time function in Excel VBA

 

Here the previous and next chapter