EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Concatenate Strings

By Madhuri ThakurMadhuri Thakur

Secondary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String
Home VBA VBA Resources VBA String and Text Functions VBA Concatenate Strings

VBA Concatenate Strings

Excel VBA Concatenate Strings

Two or more substrings joining together to form a sentence or new strings are known as concatenation. We have seen concatenation in the worksheet either by using the ampersand operator or the addition operator. Even there is a separate function for concatenation in worksheet functions. But in VBA it is not that similar, in VBA there is no inbuilt function for concatenation of strings also neither can we use the worksheet functions for the same. The only method to concatenate strings in excel VBA is by using the & and + operator.

How to Concatenate Strings in VBA?

Below are the different examples to use the Concatenate Strings in Excel VBA.

Watch our Demo Courses and Videos

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

You can download this VBA Concatenate Strings Excel Template here – VBA Concatenate Strings Excel Template

VBA Concatenate Strings – Example #1

First, let us begin with the most basic concept of using the & or known as ampersand keystroke used to concatenation in VBA. For this, follow the steps below:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Now we can begin with our subprocedure to show how to concatenate strings in VBA.

Code:

Sub Example1()

End Sub

VBA Concatenate Strings Example1-1

Step 3: Now we need two string variables declared to show how concatenation is done.

Code:

Sub Example1()

Dim Str1, Str2 As String

End Sub

VBA Concatenate Strings Example1-2

Step 4: Let us assign some random values to the variables.

Code:

Sub Example1()

Dim Str1, Str2 As String
Str1 = "Good"
Str2 = "Boy"

End Sub

Random Values Example1-3

Step 5: Now we can use the & or ampersand operator to concatenate both variables.

Code:

Sub Example1()

Dim Str1, Str2 As String
Str1 = "Good"
Str2 = "Boy"
MsgBox Str1 & Str2

End Sub

VBA Concatenate Strings Example1-4

Step 6: Now when we execute the above code we can see the following result.

VBA Concatenate Strings Example1-5

The strings are concatenated together but there is no space available because we didn’t provide it. So this is another lesson that we need to concatenate space too as space is also a character.

VBA Concatenate Strings – Example #2

In the above example we have seen how we concatenated strings also we know that we can use + or addition operator to concatenate strings, can we use concatenation on numbers using the addition operator? Let’s find out. For this, follow the steps below:

Step 1: In the same module let us start another subprocedure as shown below.

Code:

Sub Example2()

End Sub

VBA Concatenate Strings Example2-1

Step 2: Declare two variables as an integer for the integer values.

Code:

Sub Example2()

Dim int1, int2 As Integer

End Sub

Declare Two Variables Example2-2

Step 3: Then assign some values to these integer variables.

Code:

Sub Example2()

Dim int1, int2 As Integer
int1 = 21
int2 = 23

End Sub

VBA Concatenate Strings Example2-3

Step 4: Now let us use the addition operator for the concatenation.

Code:

Sub Example2()

Dim int1, int2 As Integer
int1 = 21
int2 = 23
MsgBox int1 + int2

End Sub

Addition Operator Example2-4

Step 5: If we execute the now we will not get the desired result.

VBA Concatenate Strings Example2-5

Step 6: So, we cannot use the addition operator for concatenation when we use integers, we have to use the ampersand operator and also we need to provide a space.

Code:

Sub Example2()

Dim int1, int2 As Integer
int1 = 21
int2 = 23
MsgBox int1 & " " & int2

End Sub

Ampersand Operator Example2-7

Step 7: Now, let us execute the Code by pressing the F5 key or by clicking on the Play Button.

VBA Concatenate Strings Example2-6

VBA Concatenate Strings – Example #3

So, we cannot use addition operators for integers but we can use them if we treat integer values as string. In this example, we will use integer values as strings. For this, follow the steps below:

Step 1: In the same module let us start with the third example.

Sub Example3()

End Sub

VBA Concatenate Strings Example3-1

Step 2: Now declare two variables as String as shown below.

Code:

Sub Example3()

Dim Str1, Str2 As String

End Sub

VBA Concatenate Strings Example3-2

Step 3: Now, assign integer values to the string variables in double quotation marks.

Code:

Sub Example3()

Dim Str1, Str2 As String
Str1 = "21"
Str2 = "23"

End Sub

VBA Concatenate Strings Example3-3

Step 4: Now using the Msgbox function we will display both using the ampersand operator.

Code:

Sub Example3()

Dim Str1, Str2 As String
Str1 = "21"
Str2 = "23"
MsgBox Str1 & " " & Str2

End Sub

Ampersand Operator Example3-4

Step 5: Run the code by pressing the F5 key or by clicking on the Play Button.

VBA Concatenate Strings Example3-5

VBA Concatenate Strings – Example #4

Now let us note that there may be some circumstances that we need to use the values from worksheets to concatenate. We have two separate values in the sheet as follows. For this, follow the steps below:

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (85,919 ratings)

Step 1: So in the same module let us start another subprocedure for example 4.

Code:

Sub Example4()

End Sub

VBA Concatenate Strings Example4-1

Step 2: Now declare three variables as Strings.

Code:

Sub Example4()

Dim Str1, Str2, Str3 As String

End Sub

VBA Concatenate Strings Example4-2

Step 3: In the first three variables let us store the values from the worksheets.

Code:

Sub Example4()

Dim Str1, Str2, Str3 As String
Str1 = Range("A1").Value
Str2 = Range("B1").Value

End Sub

First Three Variables Example4-3

Step 4: Now we will use the addition operator for concatenation.

Code:

Sub Example4()

Dim Str1, Str2, Str3 As String
Str1 = Range("A1").Value
Str2 = Range("B1").Value
Str3 = Str1 + Str2

End Sub

Addition Operator Example4-4

Step 5: And we can put the new value in the other cell.

Code:

Sub Example4()

Dim Str1, Str2, Str3 As String
Str1 = Range("A1").Value
Str2 = Range("B1").Value
Str3 = Str1 + Str2
Range("C1").Value = Str3

End Sub

VBA Concatenate Strings Example4-5

Step 6: When we execute the code we can see the result.

VBA Concatenate Strings Example4-6

As explained above there is no inbuilt function for VBA to concatenate strings. Rather than the worksheet function, we use the & (ampersand) and + (addition) operators for it.

Things to Remember About VBA Concatenate Strings

There are few things which we need to remember about concatenate strings in VBA and they are as follows:

1. In the above four examples, we saw that there are two methods to concatenate strings in VBA.
Method 1: By using the ampersand (&) operator.
Method 2: By using the addition (+) operator.
2. We use ampersand and addition operator to concatenate strings in VBA.
3. There is no such function similar to the worksheet concatenate function in VBA.
4. Addition operators used on Integers will add the integers.
5. Space is also a character so between strings Space also needs to be concatenated.

Recommended Articles

This is a guide to VBA Concatenate Strings. Here we discuss How to Concatenate Strings in Excel VBA along with practical examples and downloadable excel template. You can also go through our other related articles to learn more –

  1. VBA SendKeys
  2. VBA On Error Goto
  3. VBA Msgbox Yes/No
  4. VBA SubString
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More