How to Concatenate in VBA

by Shawn McClain

When you concatenate multiple items, you essentially combine them to form a single string or number. So, for example, if you were to concatenate "over" and "time," you would get the single string "overtime." The Visual Basic for Applications, or VBA, programming language can easily concatenate strings within the code, and can also concatenate cells in an Excel worksheet.

Open the Microsoft Office 2010 program that you wish to create a VBA macro or function with. Once the program is open, press "Alt" and "F11" to launch the VBA console.

Select the module that you are working with from the list on the left side of the screen. Click on the right side of the screen, at the point in your code where you want to concatenate your items. If you don't have any existing code, click the "Insert" menu at the top of the console and select "Module" from the drop-down menu. Place the cursor on the right side of the screen and type in "sub name()" where "name" is the name of the macro you want to create.

Enter the object that you want to hold the result of your concatenation, followed by a space and then an equal -- "=" -- sign. This object can be a variable that you have defined earlier in the code, or it can be a range, like a range of Excel cells, in the Office program you are working with.

Place a space after the equals sign and enter the first item you want to concatenate. If the items is a string, remember to enclose it in quotes.

Place a space after the first item and then enter an ampersand, or "&," symbol. This is the command that concatenates your items. Place another space after the ampersand and enter your second item. Continue until you have placed an ampersand between all the items you want concatenated.

Tips

  • check Fox example, the following concatenates two variables by adding the second onto the end of the first:
  • check x = x & y
  • check The following adds two strings together, creating the word "backtrack" in cell "A1" of an Excel spreadsheet:
  • check Range("A1") = "back" & "track"
  • check The following combines the cells "B1" and "C1" into cell "A1," but includes a space between the items:
  • check Range("A1") = Range("B1") & " " & Range("C1")

About the Author

Shawn McClain has spent over 15 years as a journalist covering technology, business, culture and the arts. He has published numerous articles in both national and local publications, and online at various websites. He is currently pursuing his master's degree in journalism at Clarion University.