# Strings

Fill in the blank

Fixed value

A ______________ is a fixed value, like 3.14, 0.41, or 512.

Not graded. So why do it?
Fill in the blank

What is it?

In programming, a _________ is a piece of memory, with a name.

Not graded. So why do it?
Fill in the blank

Numeric expression

What would this show? Try it without using Excel.

• sP = 1000
• sR = 0.02
• sN = 5
• sFv = ((1 + sR) ^ sN - 1) / sR * sP
• MsgBox sFv

Not graded. So why do it?
Fill in the blank

Numeric expression: Rochester drift

What would this show?

• sFlug = (2 + 1) ^ (1 + 6 / (7 - 1))
• MsgBox sFlug

Not graded. So why do it?
Multiple choice

+, -, /, and * are _________________.

A

Numeric expressions

B

Numeric operators

C

Confusing

D

Not graded. So why do it?

Lesson contents

### 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:

• Dim sCircumference As Single
• Dim sArea As Single
•
• sCircumference = sRadius * 2 * 3.14159
• sArea = 3.14159 * sRadius * 2

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!"
• "Past, present, and future walk into a bar. It was tense."

So...

• Cells(2, 2) = "Are goats cool?"
• Cells(2, 3) = "Yes! Not as cool as dogs, though."

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

### Declaring string variables

Like this:

• Dim tFirstName as String
• Dim tLastName as String
• Dim tFullName As String

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

You can put strings into variables.

• tFirstName = "Willow"
• tLastName = "Rosenberg"

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:

• sMealCost = InputBox("Meal cost:")

`"Meal cost:"` is a string constant.

You could use a variable there instead:

• tPrompt = "Meal cost:"
• sMealCost= InputBox(tPrompt)

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:

• sTip = sMealCost * 0.15

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

• tFirstName = "Willow"
• tLastName = "Rosenberg"
• tFullName = tFirstName & tLastName
• MsgBox tFullName

This would show:

Ray

Wait, the names are jammed together. We want a space between them.

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

• tFirstName = "Willow"
• tLastName = "Rosenberg"
• tFullName = tFirstName & " " & tLastName
• MsgBox tFullName

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.

• Dim sNumNewTweets As Single
• Dim tMessage As String
• sNumNewTweets = 6
• tMessage = "You have " & sNumNewTweets & " new tweets."
• MsgBox tMessage

This would show:

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

• Dim tMessage As String
• tMessage = "It was the best of thymes." & vbCrLf & "It was the wurst of thymes."
• MsgBox tMessage

​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:

• Dim tMessage As String
• tMessage = "It was the best of thymes." & vbCrLf _
•   & "It was the wurst of thymes."
• MsgBox tMessage

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...

• tStuff = Trim(" Trim me ")

... 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.

Fill in the blank

What would this be?

• "Now " & LCase("Is ") + Trim(" the winter of our") & UCase(" discontent.")

Not graded. So why do it?
Fill in the blank

What would this be?

• UCase("red") & " " & LCase(Trim("Leather ")) & ", " & "YELLOW " & LCase(Trim("Leather "))

Not graded. So why do it?

### String I/O

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

• Dim tDogName as String
• tDogName = Cells(3, 4)
• tDogName = tDogName & " the Dog"
• Cells(4, 5) = tDogName

You can also use dialogs.

• Dim tDogName as String
• tDogName = InputBox("Dog name?")
• tDogName = tDogName & " the Dog"
• MsgBox tDogName

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...

• Dim sDogCuteness as Single
• sDogCuteness = "very cute"

... 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:

• Dim tZipCode as String
• tZipCode = InputBox("What is your zip code?")
• MsgBox "Zip code: " & tZipCode

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

Exercise

Wrestling announcements

Wrestling announcers introduce wrestlers by reading cards, with text like:

"In the red corner, it's ROSIE 'THE RIVETER' MATHIESON!"

The corner is in the first phrase. The second phrase has the wrestler's first name, wrestling name, and last name. They're in uppercase, so the announcer remembers to SHOUT. And there's a bang (!) at the end.

Write a workbook that starts like this:

The user types in data about a wrestler:

Pressing the button shows the card for the announcer, in a dialog:

• Spacing is as shown. Make sure there aren't extra spaces in the announcement, even if the user types them in the cells.
• The corner name is lowercase.
• The first, last, and wrestling names are uppercase.
• The quotes and bang are there.

Use the IPO pattern. Add a comment for each part (e.g., an `&#39; Input` comment). Use meaningful variable names.

Another example: