How can I concatenate strings in VBA?

Question

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?

Joey · Accepted Answer · 2009-11-13 08:04:40Z

178

& 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"

edited Nov 13, 2009 at 8:04

answered Nov 13, 2009 at 7:34

Joey

357k88 gold badges704 silver badges699 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adjit Over a year ago

what about for a 2 digit integer? like 13 & "2"? would that be 132?

Joey Over a year ago

@adjit: Yes, it would. By the way, you can easily test that.

Patrick Honorez · Accepted Answer · 2018-06-05 15:48:39Z

5

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.

answered Jun 5, 2018 at 15:48

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Comments

Community · Accepted Answer · 2015-08-20 04:41:57Z

2

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.

edited Aug 20, 2015 at 4:41

CommunityBot

11 silver badge

answered Nov 13, 2009 at 7:38

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

1 Comment

SendETHToThisAddress Over a year ago

That only works in Excel, not other applications that use VBA.

Collectives™ on Stack Overflow

How can I concatenate strings in VBA?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related