Follow @dporton

Learn VBA : Concatenation

In this article we are going to discover how we add or concatenate things together. When we write our scripts adding things together is something that we will do lots of times so it’s best to understand the few simple rules that we need to follow so we don’t fall into some common traps.

The first thing you need to do is to create our script so start up Professional and create a new mrs script. Once you have saved the document , let’s call it HelloWorld.mrs type in the following.

Debug.Log("Hello World")

Save and run your script and what you will see is the words “Hello World” in the output tab in Professional. Debug.Log is used to help us understand what is happening in our program. For example add the following to you script,

Dim sName
sName = "John"
Debug.Log("Hello " + sName)

When you save and run this script you will now see that in the output window you have the world “Hello John”. So what have we done, well we have declared a string variable called sName and then we are storing the value “John” in it and finally we add the two strings together and show them in the output window. Next change your code to look like this,

Dim sName
Dim iAge
sName = "John"
iAge = 41
Debug.Log("Hello " + sName + ", are you " _
	 + iAge + " years old?")

This time we have created another variable to store a number. The number being stored is what we think Johns age is. We have also add a continuation line into the code “_”. What this tells the program is that after the letters “, are you “the program needs to look on the next line to see the rest of the command. Using this feature allows us to see all our code on the screen at once and means we don’t have to scroll when we have long lines. So what we are expecting to get is the following,

Hello John, are you 41 years old?

but what we get is

Execute Error(9): Type mismatch error, converting 'Hello John, are you ' from Text to Double

The reason for this is due to the language not allowing us to add numbers to strings, debug.log requires a string, to get around this what we should be doing is to use the following function ctext() which will take our number and convert it to a string. So our code should look like this,

Debug.Log("Hello " + sName + ", are you " _
       + ctext(iAge) + " years old?")

there are several other commands like ctext or “Convert to Text” the ones you are likely to come across when you are scripting are cDouble,cLong,cDate & cCategorical. cCategorical is not a normal VBA command , but one that IBM SPSS has added to their version of VBA so that we can handle the categorical question type.

Leave a Comment

%d bloggers like this: