Strings

What's a string?

String variables can store any characters you can type on the keyboard, like "very cute" and "12 Smith St., Apt #23". They can store letters, numbers, and special characters (like $,.? and #).

String constants

A constant is a fixed value. Here are some numeric constants:

• 13
• -15.3
• 65536

Here's some code using numeric constants:

No matter what you put in the input box, 2 and 3.14159 aren't going to change. They're constant.

Here are some string constants.

• "Goats are cool!"
• "Circle radius?"
• "Past, present, and future walk into a bar. It was tense."

So...

... will always put the same strings into the cells.

Declaring string variables

Like this:

The first character is "t" for "text."

You can put strings into variables.

You can use a string variable whenever you could use a constant.

For example, with the InputBox() function, you give it a prompt, text to like this:

You could do it like this:

"Meal cost:" is a string constant.

You could use a variable there instead:

Check out the official documentation for InputBox:

It says the prompt should be a string. It doesn't matter whether the string comes from a constant, a variable, or something else.

String operators

In the tip program, we used two operators: + (add) and * (multiply). Like:

Strings have only one operator: &, called concatenation. It hooks strings together. For example:

This would show:

Oh, right. A bug. Let's change it to:

When working out tFullName, VBA says to itself:

"Self, start with the value of tFirstName, then stick a space on the end, then stick tLastName on the end of that. Put the result in tFullName."

Now we get:

That's better. If you want a space, you need to add one.

We can also concatenate numeric variables with strings.

This would show:

If you concatenate a vbCrLf into a string, it will be split into separate lines on output. For example:

​Hey! Keep your horrible puns away from the kids!

Sorry.

When I use vbCrLf, I split the VBA statement (the code) into multiple lines, to remind me of what the output will look like:

The underscore tells VBA that the statement will be continued on the next line.

String functions

VBA has a bunch of useful string functions. We'll use only a few.

Trim()	Remove leading and trailing spaces
LCase()	Convert a string to lowercase
UCase()	Convert a string to uppercase

Trim() removes leading and trailing spaces. So...

... would remove the spaces from the left and right of the string, giving Trim me.

LCase("Bilbo Baggins") converts characters to lowercase. That would be bilbo baggins.

UCase("Don't shout!") would be DON'T SHOUT!. That's right, uppercase.

String I/O

You can get strings from cells, and put them into cells, just like numeric data.

You can also use dialogs.

People often add extra spaces when they're typing text. Like this:

There's a space at the end of the dog name. Quite natural, for someone good at typing.

So, use Trim() on user text input, to strip away excess spaces. Unless the spaces matter for some reason, but that's rare in business programming.

Type mismatch

If you try this...

... you'll get...

That's because sDogCuteness is a numeric variable, and you can't put text into a numeric variable.

String variables don't have that problem, since they can contain anything you can type on the keyboard.

Sometimes it makes sense to use strings when you expect people to type in numbers. Check this example:

It would run like this:

It makes sense to store zip codes as strings, since you would not do math on zip codes. Adding two zip codes gives you... nothing meaningful.

Exercise