MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

how to concatenate string variables

  Asked By: Viveka    Date: Feb 21    Category: MS Office    Views: 2904

I need to define a few string variables to create another string.
And an ampersand doesn't seem to work here for me,
here are examples:

c = a&b Compile error ! Expected: end of statement
Sub HowToConcatenateStringVariables()

Dim a, b, c As String

a = "abc"
b = "123"
c = a&b&"another string" Compile error ! Expected: end of statement

Debug.Print c

End Sub

Your help would be much appreciated.



11 Answers Found

Answer #1    Answered By: Julio Morgan     Answered On: Feb 21

I find that it sometimes helps to put a space before and after the &.

Let us know whether this works!

Answer #2    Answered By: Opal Alexander     Answered On: Feb 21

You've got to have a space on both sides of the ampersand.

Answer #3    Answered By: Coleman Smith     Answered On: Feb 21

The concatination operator for string  is + therefore
see my below codes,

c = a + b + "another string"

Try this, it works for me, let me know if this works for you.

Answer #4    Answered By: Benny Torres     Answered On: Feb 21

OK, so to clarify, both + and & can be used to perform string  concatenation.
& is preferred, because it specifically applies to strings, whereas + can be
used for both numbers and strings, which can lead to code ambiguity.

If you use &, you must put a space in front of the & when you type it. The
VBA editor will automatically reformat it so it has spaces on both sides.
You can type the + with no spaces around it, and the editor will put them
both in.

To show an example of the ambiguity, the following two functions produce
different results:

Sub s()
Dim a As String
Dim b as Integer
Dim c As Integer
a = "5"
b = "6"
c = a + b
Debug.Print c
End Sub

Sub s2()
Dim a As String
Dim b as String ' b is now a String also
Dim c As Integer
a = "5"
b = "6"
c = a + b
Debug.Print c
End Sub

whereas, using & always yields "56".

Answer #5    Answered By: Ulfah Hashmi     Answered On: Feb 21

Let's put it a little more clearly. + is best regarded as a mathematical
operator for addition. Using it for string  work is possible merely for
convenience and as a left over from the earliest VB versions in which string
variables were always cast using the $ symbol and it was relatively easy for
the programmer to see he was mixing data types. If you attempt to use it
with a combination of string and non-string data, you're going to have
problems. The error  you will get from this operation is "Type Mismatch" and
the understanding you should have from that error is that an operation was
attempted using at least 2 different data types.

So, the understanding to take is:
Ampersand is the native string concatenator for all the pre-.Net VB
variations. + is best left for math work.

Answer #6    Answered By: Adaulfo Fischer     Answered On: Feb 21

Couple problems there. One of them is a little sneaky and not the source
of your error  but you should be aware of it. The declaration of your
variables will result in a and b being Typed as Variant and c as a string.

The source of the reported problem, though, is that you've no spaces
before or after the ampersand.

Sub MakeAString()

Dim a As String
Dim b As String
Dim c As String

a = "abc"
b = "def"
c = a & b & "another string"

Debug.Print c

End Sub

Answer #7    Answered By: Nicholas Wells     Answered On: Feb 21

+ isn't the "official" concatenation operator, and indeed it doesn't always
work properly. E.g. the following code fails

Private Sub CommandButton1_Click()
Dim a As Integer: a = 111
Dim b As Integer: b = 222
Dim c As String: c = a + b + "another string"
Range("a1").Value = c
End Sub

The error  is a type mismatch on the c = a + b + "another string"

However, ampersands work  fine:

Private Sub CommandButton1_Click()
Dim a As Integer: a = 111
Dim b As Integer: b = 222
Dim c As String: c = a & b & "another string"
Range("a1").Value = c
End Sub

sets A1 to 111222another string  as you'd expect.

Answer #8    Answered By: Lily Brown     Answered On: Feb 21

Prefixing a variable with &H would mean that VB expects a hexadecimal value
and prefixing with &O or just & would lead VB to expect an octal value.

This would appear to be where the problem with spaces in between variables
and & comes from.

Answer #9    Answered By: Umaiza Hashmi     Answered On: Feb 21

That's an excellent observation and it is the root of the problem.

Answer #10    Answered By: Barachias Levi     Answered On: Feb 21

Prefixes suffixes still seem to apply in VB today as a hangover from those
"former years"... Hehehe.

A lot of functions have a dual result... Left(....) returns a variant of
type string  while Left$(...) returns a string.

As has been noted here beforer though, these "retro" functions are going to
disseapear in the future, one of the reasons probably being to bring
applications more in line with one another. Soooo... Using Left(...) instead
of Left$(...) along with other $[Function] functions, will probably make
your applications slightly more future proof.

Answer #11    Answered By: Naomi Lee     Answered On: Feb 21

And last but not least, the suffixes for data types *still* apply in VB
though they aren't used any more.

$ for a String variable
% for a Integer variable
! For a Single-precision variable
# for a Double-precision variable

Didn't find what you were looking for? Find more on how to concatenate string variables Or get search suggestion and latest updates.