EDUCBA

Home VBA VBA Resources VBA String and Text Functions VBA String
 

VBA String

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA String

Excel VBA String Function

Excel VBA String Function is the most basic function used in VBA. As for numbers, we have an integer function in excel, so for characters and text, we can use VBA String. We can perform and use VBA Strings in many ways. With the help of function VBA String, we can calculate the length of text, combine 2 different words or sentences, and print the text message in a message box.

 

 

The formula for String Function in Excel VBA

VBA String has the following syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of String Function

As we can see in the syntax, it uses Number as Long, which has no limit and the characters such as alphabets and words.

How to Use Excel VBA String Function?

We will learn how to use a VBA String function with few examples in excel.

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

VBA String Function – Example #1

First, we will see the simple example where we will apply and use a string function.

Follow the below steps to use the String function in VBA.

Step 1: Go to VBA, and from the Insert menu tab, open a new module as shown below.

VBA String Example 1-1

Once we do that, we get the new Module window.

Step 2: Now start writing Sub-category in the performed function or any other name.

Code:

Sub VBA_String1()

End Sub

VBA String Example 1-2

Step 3: Now define a dimension “Dim A as String”. This will allow us to use character or text in our next command line.

Code:

Sub VBA_String1()

Dim A As String

End Sub

VBA String Example 1-3

Step 4: Now, we can store any text or character or sentence in defined string A. Let’s consider the sentence “How are you doing?” as shown below.

Code:

Sub VBA_String1()

Dim A As String

A = "How are you doing?"

End Sub

VBA String Example 1-4

Step 5: Now, to see this text somewhere, we need to print it in a message box. For this, we will use the MsgBox function as shown below.

Code:

Sub VBA_String1()

Dim A As String

A = "How are you doing?"

MsgBox A

End Sub

VBA String Example 1-5

Step 6: Now, compile and run the code. We will get a message box saying “How are you doing?” in the excel window.

Result of Example 1

This is how we can use text or character by using the String function of VBA.

VBA String Function – Example #2

Now in this example, we will see and print a part of the text with the help of the String function in VBA.

Follow the below steps to use the String function in VBA.

Step 1: Write Subcategory in any name or in performed function’s name as shown below.

Code:

Sub VBA_String2()

End Sub

Example 2-1

Step 2: Now define a dimension “DIM” A or any other character as STRING as shown below.

Code:

Sub VBA_String2()

Dim A As String

End Sub

VBA String Example 2-2

Step 3: In defined dimension A store some value of character or text. Let’s store a text as “Sample Text”, as shown below.

Code:

Sub VBA_String2()

Dim A As String

A = "Sample Text"

End Sub

VBA String Example 2-3

Step 4: Again, to see the value stored in defined dimension A as String, we will use the message box. To print a message, consider the MsgBox command; as we have discussed, to get a portion of the text, so use LEFT with the number of characters you want. Let’s say 3. This we print the First 3 letters from the left into the message box.

Code:

Sub VBA_String2()

Dim A As String

A = "Sample Text"

MsgBox Left("Sample Text", 3)

End Sub

VBA String Example 2-4

Step 5: Now, compile and run the complete code.

Result of Example 3

We will get a message box with the message “Sam”, as shown below. As we have selected LEFT with 3 character limit for the message box, so I have printed the message Sam which is the first 3 letters of “Sample Text”.

In the same way, we can use the RIGHT, MID function to get the character from different sides as we did for the LEFT side.

VBA String Function – Example #3

In this example, we will see how the String function is used to calculate the length of defined and stored characters. This is also as easy as printing a message in the message box.

Follow the below steps to use the String function in VBA.

Step 1: Writing Sub-category in the name of the performed function as shown below.

Code:

Sub VBA_String3()

End Sub

Example 3-1

Step 2: Now define dimension A as String. Here we can use anything in place of “A”. The string will store the values given for A as a character.

Code:

Sub VBA_String3()

Dim A As String

End Sub

VBA String Example 3-2

Step 3: Now, to calculate the length of characters store in dimension A, use the LEN function as shown below. We will assign the defined dimension.

Code:

Sub VBA_String3()

Dim A As String

A = Len (

End Sub

VBA String Example 3-3

Step 4: Let’s say that character or text in “Sample Text” same as we used in example-2. And remember to quote this text in inverted commas, as shown below.

Code:

Sub VBA_String3()

Dim A As String

A = Len("Sample Text")

End Sub

VBA String Example 3-4

Step 5: At last, for printing the message stored in dimension A, we will use the MsgBox command, which will print the complete message and length of character stored in dimension A String.

Code:

Sub VBA_String3()

Dim A As String

A = Len("Sample Text")

MsgBox A

End Sub

VBA String Example 3-5

Step 6: Once done, compile and run the code.

Result of Example 3

We will get the length of characters store in A string as 11, which includes space as well.

VBA String Function – Example #4

In this example, we will see how to use 2 different strings in one message box. For this, we need to define 2 separate dimensions as String, and we will be using one single message box to print the text stored in both the dimensions.

Follow the below steps to use the String function in VBA.

Step 1: Write a sub-category in the name of the defined function as shown below.

Code:

Sub VBA_String4()

End Sub

Example 4-1

Step 2: Now define dimension “DIM” A and B as String in 2 separated line of code. For each dimension we define, we need to use a separate new line for it, as shown below.

Code:

Sub VBA_String4()

Dim A As String
Dim B As String

End Sub

VBA String Example 4-2

Step 3: Now store any character of values as text in both defined strings. Here we are using “First Name ” and “Last Name” just for text sample. As we can see in the below screenshot, we have given space after “First Name “ so that both strings should not get overlapped when we see these in the message box.

Code:

Sub VBA_String4()

Dim A As String
Dim B As String

A = "First Name "
B = "Last Name"

End Sub

VBA String Example 4-3

We can use anything or any value stored in A and B, and in fact, we can see any alphabets or words for defining a string.

Step 4: Now, to see the values stored in A and B, we will use MsgBox command and use “&” between A and B so that we would see both the values together in the same message box as shown below.

Code:

Sub VBA_String4()

Dim A As String
Dim B As String

A = "First Name "
B = "Last Name"

MsgBox (A & B)

End Sub

VBA String Example 4-4

Step 5: Once done, compile and run the code.

Result of Example 4

We will get a message box having “First Name” and “Last Name” together in the same box separated by a space.

Benefits of Excel VBA String Function

  • This is the easiest and basic function of VBA, where we can use any length of character and text.
  • A string can be used to print the text and characters in many different forms, as we have seen in the above examples.

Things to Remember

  • Always use inverted commas to quote the message while assigning it to any string.
  • Remember to save the file in Macro-Enabled Excel to avoid losing written code.

Recommended Articles

This has been a guide to VBA String Function. Here we discussed VBA String and how to use Excel VBA String Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Match
  2. VBA RGB
  3. VBA Number Format
  4. VBA XML