VBA Programming Series...24

Working with strings

Excel can work with both numbers and text, so it should come as no surprise that VBA has this same power. Text is often referred to as a string. You can work with two types of strings in VBA:

✓ Fixed-length strings are declared with a specified number of characters. The maximum length is 65,526 characters. That’s a lot of characters! As a point of reference, this article contains about half that many characters.

✓ Variable-length strings theoretically can hold as many as two billion characters. If you type five characters per second, it would take you about 760 days to bang out two billion characters — assuming you don’t take any breaks to eat or sleep.

When declaring a string variable with a Dim statement, you can specify the maximum length if you know it (it’s a fixed-length string) or let VBA handle it dynamically (it’s a variable-length string). The following example declares the MyString variable as a string with a maximum length of 50 characters. (Use an asterisk to specify the number of characters, up to the 65,526 character limit.) YourString is also declared as a string, but its length is unspecified:

Dim MyString As String * 50

Dim YourString As String

When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA. VBA doesn’t like that.