WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Text

VBA Text

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

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. The text function in Excel converts a value to a specified number format.

One of the problems with this function as arguments. Whenever we use the 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”.

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

Examples of VBA Text Function in Excel

Below are the examples of Excel VBA Text Function.

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. Look at the below code in Visual Basic.

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 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course
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.

MsgBox FormattingResult

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

vba text example 1.3

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

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, 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.

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

Like this, by using the VBA TEXT function, we can apply number formatting to the cells.

Recommended Articles

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 –

  • VBA Collection Object
  • TextBox in VBA
  • InStr Function in VBA
  • VBA Course Free Online
7 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel, VBA & Power BI,  Learn VBA

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Text Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More