VBA Text

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Text is a worksheet function in Excel, but it can also be used in VBA while using the range property. This function is similar to the Worksheet function and takes the same number of arguments, which are the values one must convert and a specified number format.

Excel VBA Text Function

The TEXT is the function available with the worksheet, but unfortunately, it is not a built-in function in Excel VBA. Instead, we need to use the worksheet function class object in VBA to access this function. The TEXT function in Excel converts a value to a specified number format.

One of the problems with this function is arguments. Whenever we use the VBA worksheet functionVBA Worksheet FunctionThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more class, we do not get to see the clear-cut syntax much like in our worksheet. Instead, it just says “Arg1” and “Arg2.”

vba text function
  • Arg1 is the value we need to apply the formatting to.
  • Arg2 is the formatting we need to apply, and we need to specify the formatting code.
VBA Text

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Text (wallstreetmojo.com)

Examples of VBA Text Function in Excel

Below are examples of Excel VBA TEXT functions.

You can download this VBA Text Excel Template here – VBA Text Excel Template

Example #1

Let me show you a simple example of TEXT in VBA Excel. But, first, look at the below code in Visual BasicCode In Visual BasicVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code:

Sub Text_Example1()

Dim FormattingValue As String
Dim FormattingResult As String

FormattingValue = 0.564

FormattingResult = WorksheetFunction.Text(FormattingValue, "hh:mm:ss AM/PM")

MsgBox FormattingResult

End Sub
vba text example 1.1

Firstly, we have declared two variables as a string in VBA.

Dim FormattingValue As String

Dim FormattingResult As String

For the first variable, we have assigned the formatting number we need to format.

FormattingValue = 0.564

Now, for another variable, we have assigned the TEXT function.

FormattingResult = WorksheetFunction.Text(FormattingValue, "hh:mm:ss AM/PM")

If you observe, we have applied the formatting of time, i.e., “hh:mm:ss AM/PM.”

Then, finally, we applied a VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more to show the result.

MsgBox FormattingResult

When we run the code TEXT function, we will apply the time format to the number 0.564 and display the result below.

vba text example 1.3

So, we got the time as “01:32:10 PM”.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example #2

Similar to the date format example, we have made some minor changes in this example. Below is the code.

Code:

Sub Text_Example2()

Dim FormattingValue As String
Dim FormattingResult As String

FormattingValue = 43585

FormattingResult = WorksheetFunction.Text(FormattingValue, "DD-MMM-YYYY")

MsgBox FormattingResult

End Sub
vba text example 2.1

From the previous code, we have changed the formatting value from 0.565 to 43585 and changed the formatting style to “DD-MMM-YYYY.”

It will apply the formatting to the number 43585 as the date, and the result is as follows.

example 2.2

Apply Formatting to Cells

We have seen simple examples. Now, look at how to work with cells in a worksheet. For this example, look at the below data.

example 3.1

For all these numbers, we need to apply the time format. This code will apply the formatting.

Code:

Sub Text_Example3()

Dim k As Integer

For k = 1 To 10
Cells(k, 2).Value = WorksheetFunction.Text(Cells(k, 1).Value, "hh:mm:ss AM/PM")
Next k

End Sub
example 3.2

This code will loop through 10 cells and apply the formatting below.

example 3.3

Using the VBA TEXT function, we can apply number formatting to the cells.

Recommended Articles

This article has been a guide to VBA Text Function. We discussed using the Text function in Excel VBA and some practical examples. Also, you can learn more about VBA from the following articles: –