How can I concatenate strings in VBA?

ExcelVba

Excel Problem Overview

This question comes from a comment under Range.Formula= in VBA throws a strange error.

I wrote that program by trial-and-error so I naturally tried + to concatenate strings.

But is & more correct than + for concatenating strings?

Excel Solutions

Solution 1 - Excel

& is always evaluated in a string context, while + may not concatenate if one of the operands is no string:

"1" + "2" => "12"
"1" + 2   => 3
1 + "2"   => 3
"a" + 2   => type mismatch

This is simply a subtle source of potential bugs and therefore should be avoided. & always means "string concatenation", even if its arguments are non-strings:

"1" & "2" => "12"
"1" &  2  => "12"
 1  & "2" => "12"
 1  &  2  => "12"
"a" &  2  => "a2"

Solution 2 - Excel

The main (very interesting) difference for me is that:
"string" & Null -> "string"
while
"string" + Null -> Null

But that's probably more useful in database apps like Access.

Solution 3 - Excel

There is the concatenate function. For example 

=CONCATENATE(E2,"-",F2)
But the & operator always concatenates strings. + often will work, but if there is a number in one of the cells, it won't work as expected.

Categories
Recommended Excel Solutions
Recommended Vba Solutions

Previous Solution:

best way to clear contents of .NET's StringBuilder

C#.NetOptimization.Net 3.5

Next Solution:

LINES and COLUMNS environmental variables lost in a script

BashShellTerminalEnvironment Variables

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionilya n.View Question on Stackoverflow
Solution 1 - ExcelJoeyView Answer on Stackoverflow
Solution 2 - ExceliDevlopView Answer on Stackoverflow
Solution 3 - ExcelwallykView Answer on Stackoverflow