Text is a worksheet function in excel but it can also be used in VBA while using the range property with it, the function for this function is similar to the worksheet function and it takes the same number of arguments which are the values which needs to be converted and a specified number format.
Excel VBA Text Function
TEXT is the function available with worksheet but unfortunately, it is not a built-in function in Excel VBA. In order to access this function, we need to use the worksheet function class object in VBA. “Text function converts a value to a specified number format”.
One of the problems with this function is arguments. Whenever we use VBA worksheet function class we don’t get to see the clear cut syntax much like in our worksheet. It just says “Arg1” and “Arg2”.
- 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.
Examples of VBA Text Function in Excel
Below are the examples of Excel VBA Text Function.
Let me show you a simple example of TEXT in VBA Excel. Look at the below code in Visual Basic.
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
Firstly I have declared two variables as a string in VBA
Dim FormattingValue As String Dim FormattingResult As String
For the first variable, I have assigned the formatting number which we need to format.
FormattingValue = 0.564
Now for another variable, I have assigned the TEXT function.
FormattingResult = WorksheetFunction.Text(FormattingValue, "hh:mm:ss AM/PM")
If you observe I have applied the formatting of time i.e. “hh:mm:ss AM/PM”
Then finally I have applied a VBA message box to show the result.
When I run the code TEXT function will apply time format to the number 0.564 and display the result as below.
So, we got the time as “01:32:10 PM”.
Similar to the date format example, we have made some minor changes in this example. Below is the 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
From the previous code, I have changed the Formatting Value from 0.565 to 43585 and changed the formatting style as “DD-MMM-YYYY”.
This will apply the formatting to the number 43585 as the date and the result is as follows.
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.
For all these numbers we need to apply the time format. For this below code will apply the formatting.
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
This code will loop through 10 cells and apply the formatting as below.
Like this, by using VBA TEXT function we can apply number formatting to the cells.
This has been a guide to VBA Text Function. Here we discussed how to use Text Function in Excel VBA along with some practical examples. You can learn more about VBA from the following articles –