XL: Text Concatenation Causes Error in Visual Basic Module

ID: Q106500

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, if you attempt to concatenate a text variable and a string without inserting a space after the variable, you may receive the following error message:

Expected: To or list separator or )

MORE INFORMATION

In general, when you use spaces in Visual Basic code, these spaces do not affect the meaning of the code. However, if you enter the following code


   Text="is a"
   MsgBox("This "&Text&" Test")

and you leave out a space between the variable name "Text" and the ampersand (&), you will receive the error message:

Expected: To or list separator or )

This error occurs because when an ampersand appears after a variable name, it is interpreted as a type-declaration character. In this example, the ampersand after the variable name "Text" indicates that Text is being declared as a Long data type. When the ampersand is interpreted this way, the rest of the line of code does not make sense. When you insert a space between the text variable and the ampersand, the ampersand character is recognized as the text concatenation operator and the error is eliminated.

You can also use the plus sign (+) for text concatenation as in the following example:


   Text="is a"
   MsgBox("This " + Text + " Test")

In the above example, spaces are added automatically.

REFERENCES

For more information about type-declaration characters, in Visual Basic Help, choose the Search button and type:

type-declaration characters

Additional query words:

Keywords : kbprg
Version : MACINTOSH:5.0; WINDOWS:5.0,7.0
Platform : MACINTOSH WINDOWS
Issue type :