The direct answer: in Excel VBA, you concatenate strings with the ampersand operator (&), for example Range("A1") = "Hello " & "World". You can also use the Concatenate worksheet function inside VBA via Application.WorksheetFunction.Concatenate, but & is faster, simpler, and the standard choice for VBA code. This article explains both methods, how to handle numbers and dates, common errors, and practical examples you can adapt.
Why Use & Instead of the Concatenate Function in VBA
In Excel worksheets, you have the CONCATENATE function and its modern replacement CONCAT. In VBA, the ampersand operator is the native way to join text. It is a true operator, so it works in any expression, not just inside a function call. The Concatenate worksheet function can be called from VBA, but it requires the Application.WorksheetFunction prefix and does not accept range references the way it does in a cell—you must pass individual arguments. For example, Application.WorksheetFunction.Concatenate("A", "B") works, but it is verbose. The & operator also handles numbers and dates automatically by converting them to their text representation, which is usually what you want.
Basic Concatenation with the Ampersand Operator
The ampersand operator joins two or more expressions into a single string. Here is a simple macro that combines first and last names:
Sub ConcatenateNames()
Dim firstName As String
Dim lastName As String
Dim fullName As String
firstName = Range("A1").Value
lastName = Range("B1").Value
fullName = firstName & " " & lastName
Range("C1").Value = fullName
End Sub
Notice the space inside the quotes: " ". That is how you add a space between values. You can add any literal text, punctuation, or line breaks (using vbCrLf for a new line). The result in C1 would be, for example, “John Smith”.
Using the Concatenate Worksheet Function in VBA
If you prefer the function style, you can call the worksheet function from VBA. This is useful when you want to mirror a formula that already exists in a cell, but it is rarely necessary. Here is an example:
Sub UseConcatenateFunction()
Dim result As String
' Joins the values in A1, B1, and C1 with no separators
result = Application.WorksheetFunction.Concatenate(Range("A1").Value, Range("B1").Value, Range("C1").Value)
Range("D1").Value = result
End Sub
Note that Concatenate does not insert separators; you must add them as extra arguments, like Application.WorksheetFunction.Concatenate(Range("A1").Value, " ", Range("B1").Value). The newer CONCAT function is not available as a worksheet function in VBA’s WorksheetFunction object in older versions, but you can use Application.Evaluate("CONCAT(...)") if needed. In practice, the ampersand is more straightforward.
Concatenating Numbers, Dates, and Booleans
When you use &, VBA converts the right operand to a string using the current locale settings. For example, a date might appear as “1/15/2024” or “15/01/2024” depending on your system. If you need a specific format, use the Format function:
Sub ConcatenateWithFormat()
Dim price As Double
Dim saleDate As Date
Dim message As String
price = 19.99
saleDate = DateSerial(2024, 1, 15)
message = "Price: " & Format(price, "$#,##0.00") & " on " & Format(saleDate, "yyyy-mm-dd")
Range("A1").Value = message
' Result: Price: $19.99 on 2024-01-15
End Sub
For numbers, you can also use Str() to convert, but it adds a leading space for positive numbers, so Format is safer. Booleans convert to “True” or “False” automatically.
Concatenating Ranges and Arrays
To join values from multiple cells in a range, you can loop through the range and build a string. This is common for creating a comma-separated list. Here is an example that joins all non-empty cells in column A:
Sub ConcatenateRange()
Dim cell As Range
Dim output As String
For Each cell In Range("A1:A10")
If cell.Value <> "" Then
If output = "" Then
output = cell.Value
Else
output = output & ", " & cell.Value
End If
End If
Next cell
Range("B1").Value = output
End Sub
This avoids adding a trailing comma. For a large range (thousands of cells), consider reading the values into an array first for speed, but for typical use a loop is fine.
Common Errors and How to Avoid Them
One frequent mistake is using the plus sign (+) instead of &. The plus sign works for numeric addition, but when used with strings it can cause a Type Mismatch error if one operand is a number. Always use & for concatenation. Another error is forgetting to declare variables or not converting numbers before concatenation—though & handles that automatically. A third issue is the ByRef argument type mismatch when passing a range to a function that expects a string; always use .Value or .Text to extract the string. Finally, when building long strings, watch out for line breaks in the VBA editor—you can use the underscore _ to continue a line, but ensure there is a space before it.
Concatenation with Line Breaks and Special Characters
To insert a new line in a cell, use vbCrLf (carriage return + line feed) or vbLf (line feed only). For example:
Sub MultiLineMessage()
Dim msg As String
msg = "Line 1" & vbCrLf & "Line 2"
Range("A1").Value = msg
' Remember to set Wrap Text on the cell to see both lines
End Sub
Special characters like quotes need doubling: to include a double quote in a string, use two quotes in a row. For example, "He said ""Hello""" produces He said "Hello". Tabs can be inserted with vbTab.
Performance Tips for Large Data
If you concatenate thousands of values in a loop, the repeated string concatenation can slow down your macro. A more efficient approach is to use the Join function with an array. First, load the range into an array, then use Join with a delimiter:
Sub FastConcatenate()
Dim arr As Variant
Dim i As Long
Dim output As String
arr = Range("A1:A10000").Value
ReDim values(1 To UBound(arr, 1)) As String
For i = 1 To UBound(arr, 1)
values(i) = CStr(arr(i, 1))
Next i
output = Join(values, ",")
Range("B1").Value = output
End Sub
This is much faster because it avoids repeated memory reallocations. For even larger data, consider writing to a text file or using the StringBuilder class, but Join is usually sufficient.
Frequently Asked Questions
Can I use the CONCATENATE function directly in VBA? Yes, you can call Application.WorksheetFunction.Concatenate, but the ampersand operator is more idiomatic and faster. The function is mainly useful when you want to mimic a worksheet formula.
How do I concatenate a range of cells with a separator in VBA? Use a loop to build the string, as shown above, or load the range into an array and use Join for speed. The loop gives you more control over skipping blanks.
Why does my concatenation show a date as a number? When you concatenate a date with &, VBA converts it to the underlying serial number if you don’t use Format. Always use Format(date, "mm/dd/yyyy") to display a readable date.
What is the difference between & and + in VBA? The & operator always concatenates, converting numbers to strings. The + operator tries to add numbers; if you use it with strings, it may cause a type mismatch error. Always use & for joining text.
Can I concatenate with a line break in a cell? Yes, use vbCrLf or vbLf in your string, and ensure the cell has Wrap Text enabled to see the new line.
How do I avoid the “Type Mismatch” error when concatenating? Ensure you are using & and that you have converted any objects (like Range) to their values with .Value. Also, declare variables with explicit types to catch issues early.
Remember to save a copy of your workbook before running any macro, because VBA actions cannot be undone with Ctrl+Z.
Related
- Excel Sheet Checklist Template: How to Build One
- Excel Sales Tracking Template: Free XLS & How to Use
- Excel Schedule Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Dictionary of Arrays: How to Store and Use DataYes—you can store arrays as values in an Excel VBA Dictionary. On Windows, create a Scripting.Dictionary, assign an…
- VBA Quotes in Strings: How to Include Double QuotesTo put a double quote inside a VBA string, double it: write "" inside the surrounding string literal.…
- Excel VBA Average Function: Syntax, Examples, and TipsIn Excel VBA, calculate an average by calling Excel’s built-in function: use Application.WorksheetFunction.Average(range) when you want a numeric…
- Excel VBA Open File Dialog: A Practical GuideTo show an Open dialog in Excel VBA, use Application.FileDialog(msoFileDialogFilePicker), set its title and filters, and call .Show.…
- Excel UserForm Command Button: Setup and VBA GuideAn Excel UserForm command button is a clickable control that runs VBA when the user selects it. Add…
- Excel VBA: Find a Row with Specific Text in a ColumnTo find a row containing specific text in Excel VBA, use the range’s Find method, then read the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.