Return to VBA Code Examples

VBA Text Function

This tutorial will demonstrate how to use the Text Function in VBA.

The Text function is used in an Excel worksheet to return a portion of a string in a specified format. The Text function is not a VBA function, but can be used in VBA by referring to the Worksheet Function object.

WorksheetFunction.Text

The syntax in VBA would therefore be as follows:

=WorksheetFunction.Text (Arg1, Arg2)

where Arg1 is the original string and Arg2 is the format of the string we want returned.

In the above example, the string variable dte is storing a date.  The Text function then returns the month portion of the date.

VBA Text ReturnMonth

Formatting Numbers with the Text Function

We can use the text function to format numbers in our VBA code.

The string returned in the above example would be $75,896.13.

VBA Text ReturnCurrency

Other examples of number formatting using the Text Function are:

However, Excel does have a built in VBA function that we can use instead of the Text function if we wish to format dates and numbers within VBA. This is known as the Format function.

The VBA Format Function

Taking the example above, rather than using WorksheetFunction.Text, we can just use the Format Function to achieve the same results.

Similarly, we  can use the Format Function to format date portions of a string.

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!