Home and Learn - Free Excel VBA Course

4.1 Working with Strings of Text in Excel VBA

One variable type that we haven't touched upon yet is the As String type. As its name suggest, it is used to hold strings of text. You'll need to work with strings of text quite a lot in Excel VBA, so it's well worth getting the hang of.

 

Setting up a variable to hold text is quite straightforward. You simply Dim a variable As String:

Dim MyString As String

To store text inside of your variable you need to surround it with double quotes:

MyString = "Some text"

Even if you place numbers between double quotes they still gets treated as text and not Integers:

MyString = "25"

The above line means store 25 as text, and NOT store the number 25.

You can place text into a cell on your spreadsheet:

Dim MyString As String

MyString = "Some text"

ActiveCell.Value = MyString

And you can get text out of cell on your spreadsheet:

Dim MyString As String

MyString = ActiveCell.Value

Quite often, though, you'll need to do something with the text that you get from a cell on a spreadsheet. For example, you may need to take a full name from one cell and place the first name in another cell and the surname in yet another. To do things like this, you need to know how to use Excel VBA's built-in string functions. The functions we'll study are these:

LCase, UCase
Trim and Len
Space
Replace
StrReverse
InStr, InStrRev
Left, Right
Mid

As you can see, there are quite a lot of them. And this is not even a full list!

 

In the next few lessons, we'll take a look at the above list of methods and how to use them in your Excel VBA code. First up is changing case.

< With

Next Lesson: 4.2 Change Case >