Concatenating strings in VBA

Question

I'm maintaining an application written in Microsoft Access with VBA.

I'm glancing over my code and have just noticed I have subconsciously been concatenating strings together with the plus (+) symbol instead of the ampersand. It's been a few years since I've coded in VB6. Could this cause any issues?

Everything seems fine and it will only take a few minutes to fix, I'm just curious as to whether I'm technically doing anything wrong.

score 13 · Accepted Answer · answered Oct 12 '11 at 17:16

13

The ampersand is explicitly a string operation, while the plus is overloaded:

Dim num1 As Integer
num1 = RandomNumberBetween(1, 9)

Dim num2 As Integer
num2 = RandomNumberBetween(1, 9)

Dim randomAge As String 'trying to get a random age between 11 and 99

' works
randomDate = "Your age is " & num1 & num2 

'broken
randomDate = "Your age is " + num1 + num2

When used with numbers the plus sign will add.

answered Oct 12 '11 at 17:16

tcarvin

10,715
3
31
52

4

It gets even more chancy when you have a null with plus. – Fionnuala Oct 12 '11 at 17:29
Good point, appending a string to a null with the ampersand safely results in a value that is just that string – tcarvin Oct 12 '11 at 18:28

score 4 · Answer 2 · answered Oct 13 '11 at 07:21

Some examples, from the VBA immediate window (the difference between the third and fourth is particularly vexing):

Print "5" & 6
56

Print 5 & 6
56

Print "5" + 6
 11 

Print "5" + "6"
56 

Print "Five" & 6
Five6

Print "Five" + 6 'Type mismatch

Print "5" & Null
5

Print "5" + Null
Null

Eddy · Answer 3 · 2011-10-13T22:33:20.347

1

This can cause issues.

If you use the plus or ampersand to concatenate string values the results are identical

If you use a plus to concatenate a string with a non string value it will throw an error

If you use an ampersand sign vba will try to 'stringify' the values before concatenating.

So string_value + int_value + date_value will error and string_value & int_value & date_value works fine

edited Oct 13 '11 at 22:33

answered Oct 12 '11 at 17:21

Eddy

5,320
24
40

1

Your example is (partly) correct, but the sentences above it are backwards. If you use ampersand to concatenate a string with a non-string value, the non-string value is converted to a string and concatenated. If you use plus, you get an error, unless the string value is numeric, in which case it is converted to a number, and the values are added. Also, if you use plus and one of the operands is null, the result is null; this doesn't happen with amepersand. – phoog Oct 13 '11 at 07:13
Thanks I fixed the swapped sentences to eliminate any confusion. – Eddy Oct 13 '11 at 22:34

Concatenating strings in VBA

3 Answers3

Linked