VBA String Functions - Part 2

FORMAT Function  (VBA)

In Microsoft Excel, the FORMAT function takes an expression and returns it as a formatted string.

Syntax

The syntax for the FORMAT function is:

Format ( expression, [ format ] )
expression is the value to format.

format is optional. It is the format to apply to the expression. You can either define your own format or use one of the named formats that Excel has predefined such as:

VBA Function Example

The FORMAT function can only be used in VBA code in Microsoft Excel. Here are some examples of what the FORMAT function would return:

Format("210.6", "#,##0.00") would return '210.60'
Format("210.6", "Standard") would return '210.60'
Format("0.981", "Percent") would return '98.10%'
Format("1267.5", "Currency") would return '$1,267.50'
Format("Sep 3, 2003", "Short Date") would return '9/3/2003'

Dim sValue As String
sValue = Format("0.981", "Percent")

In this example, the variable called sValue would now contain the value of '98.10%'.

Concatenate with & ( VBA)

To concatenate multiple strings into a single string in Microsoft Excel, you can use the "&" operator to separate the string values.

Syntax

The syntax for the "&" operator is:

string1 & string2 [& string3 & string_n]
string1 to string_n are the string values to concatenate together.

VBA Function Example

The "&" operator can be used to concatenate strings in VBA code. For example:

Dim sValue As String
sValue = "Alpha" & "bet"

The variable sValue would now contain the value "Alphabet".