Manipulating Strings

[Pages:92]COPYRIGHTED MATERIAL

chapter 1

Manipulating Strings

Understanding how string values are stored and used in VBA Using the built-in VBA string-handling functions Searching for and replacing text Gathering information about strings Converting strings Working with substrings

2

Chapter 1 ? Manipulating Strings

Almost any VBA application will need t o handle string (text) data at one

point or another. VBA itself provides a useful set of string-handling functions, but the functionality of other functions as a whole is not nearly as full-featured as that provided by other, more text-centric programming languages. This chapter first makes a quick pass through the existing functions and then provides many useful routines to add to your string-handling bag of tricks. Surely, no chapter on this topic could cover every possible combination of useful functions, but the ones we've provided here should give you a good start in writing your own VBA solutions.

The sample files you'll find on the CD-ROM that accompanies this book are listed in Table 1.1:

T A B L E 1 . 1 : String-Handling Functions

Filename

Description

STRINGS.XLS STRINGS.BAS TESTSTR.BAS PROPER.MDB PROPER.TXT PROPER.XML STRINGS.VBP STRINGS.MDB

Excel file with sample functions Text file with sample functions Text file with test procedures Access 2000 database, containing sample for dhProperLookup Text version of sample for dhProperLookup XML-based recordset for dhProperLookup Visual Basic project with sample code Access 2000 database, containing sample functions

Because the modules for this chapter take advantage of ADO, you'll need to make sure your own project includes a reference to the Microsoft ActiveX Data Object 2.1 Library before you import the StringsBAS module into the project. Use the Tools References menu (or Project References menu, in Visual Basic) to add the necessary reference. Otherwise, your code will not compile once you've added the Strings module to your project.

How Does VBA Store Strings?

3

How Does VBA Store Strings?

A VBA string is simply a collection of bytes. To make it easier for VBA to work with strings, each string also maintains its own information about its length. In addition, unlike other programming languages, VBA takes care of creating, destroying, and resizing string buffers. You needn't worry about how VBA finds strings in memory, whether they're contiguous in memory, or how or when VBA reclaims the memory of the string used once you're done with it.

VBA provides two types of strings: fixed-length and dynamic. Fixed-length strings are those you declare with a fixed size, like this:

Dim strFixed As String * 100

In this case, strFixed will always contain exactly 100 characters, no matter how many characters you've placed into it. When VBA first creates the variable, at runtime, it fills the variable with 100 spaces. From then on, if you attempt to retrieve the length of the string, the output will always be 100:

Debug.Print Len(strFixed)

VBA fills the extra positions with spaces. You'll need to use the Trim function in order to use the string in any other expression (see the section "Working with Portions of a String" later in this chapter for more information). Fixed-length strings can be no longer than 65,526 characters.

Online help for VBA states that a fixed-length string can be up to 216 (or 65,536) characters long. Not so--if you attempt to create one with more than 65,526 characters, VBA won't compile your code.

Dynamic strings, on the other hand, have no fixed size. As you add or remove characters from these objects, VBA takes care of locating memory in which to place the text and allocates and deallocates memory as necessary for your text. To declare a dynamic string, you use a declaration like this:

Dim strDynamic As String

In this case, if you retrieve the length of the string, the result will accurately reflect the amount of text you've placed into the variable. Dynamic strings can contain up to around two billion characters.

4

Chapter 1 ? Manipulating Strings

How do you decide which type of string to use? Dynamic strings require a bit more processing effort from VBA and are, accordingly, a bit slower to use. On the other hand, you make up the time by not needing to use the Trim function to remove excess space every time you use the string. As you'll see by working through the examples in this chapter, we use fixed-length strings only when it's necessary. When working with a single character at a time, it makes sense to use a fixed-length string declared to contain a single character. Because you know you'll always have only a single character in the string, you'll never need to trim off excess space. You get the benefits of a fixed-length string without the extra overhead.

Unicode versus ANSI

The 32-bit Windows "universe" supports two character storage mechanisms: ANSI and Unicode. The ANSI storage standard uses a single byte for every character, with only 256 different characters allowed in any ANSI character set. If you want to display characters from a different set of 256, you must load a separate code page. This limitation makes it difficult to create internationalized applications. Windows 95 and Windows 98 use this approach for compatibility with previous versions of Windows. The Unicode standard allows for 65,536 characters, each taking up two bytes. The Unicode character set includes just about all the known written characters and ideograms in a single entity. In this way, an application that embraces the Unicode standard can support (once its text has been translated) just about any written language. Windows NT and Windows 2000 support the Unicode standard.

No matter what operating system you're using, VBA stores strings internally in Unicode format. That is, every character takes up two bytes of space. When VBA needs to communicate with Windows 95 or Windows 98 (when you include Windows API calls in your code, for example), it must first convert strings to ANSI format. This happens automatically when you use the ANSI version of a Windows API call that involves strings. The only other time you'll care about how VBA stores strings is when you want to convert a string into an array of bytes--a useful technique that we'll take advantage of a few times in this chapter. In this case, a string containing five characters becomes an array of bytes containing ten bytes. For example, a string containing the text Hello would contain the following ten bytes, once converted to a byte array:

72 0 101 0 108 0 108 0 111 0

Unicode versus ANSI

5

Each pair of bytes (72 and 0 for the H, for example) represents the Unicode storage for a single character. However, if you were running Microsoft Excel in Korea, for example, and were entering text in your native language, the second byte wouldn't be 0. Instead, it would be a value that combined with the first byte to represent the character you'd typed.

Using Strings and Byte Arrays

Because it's often faster and simpler to work with arrays of bytes than to work with individual characters in a string (and you'll find some examples in this chapter that use this technique), VBA provides a simple way to convert strings into byte arrays and back. Simply assigning a string to a byte array variable causes VBA to copy the data into the array. When you're done working with the array, you can assign it right back into the string variable. For example, the following code fragment copies data from a string into a byte array, performs processing on the array, and then copies the array back into the string:

Sub StringToByteArray() Dim strText As String Dim aByt() As Byte Dim intI As Integer strText = "Hello" ' VBA allows you to assign a string into ' a byte array and then back again. aByt() = strText For intI = LBound(aByt) To UBound(aByt) Debug.Print aByt(intI); Next intI Debug.Print strText = aByt() Debug.Print strText

End Sub

Although you won't use this technique often, if you need to process each byte of a string, it's the best solution.

6

Chapter 1 ? Manipulating Strings

In previous versions of Basic, many programmers used string variables to contain binary data (that is, non-textual data, such as bitmaps, sound files, and so on). In VBA, this isn't necessary, nor is it advisable. Instead, use arrays of bytes for nontextual data. Because VBA performs ANSI-to-Unicode conversions on the fly, you're almost guaranteed that your non-text data will be ruined once you place it into a string variable.

Using Built-In String Functions

VBA provides a large number of string-handling functions. This section introduces many of those functions, broken down by the area of functionality, and discusses the most useful of the built-in functions. The remainder of the chapter provides techniques that combine the built-in functions to perform tasks for which you would otherwise need to write custom code.

Comparing Strings

VBA provides three ways for you to compare the contents of one string with another: comparison operators (such as =, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download