VBA Developer's Handbook


The VBA Developer's Handbook is the essential resource for developers working with any application that employs the VBA programming language. Written by recognized VBA experts, this book provides detailed coverage of a wide range of specific VBA programming challenges. Its careful, step-by-step instruction and thousands of lines of code offer answers while teaching you to devise new solutions, creatively and efficiently. Learn product-independent VBA programming. Instruction applies equally to all environments ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (15) from $36.10   
  • New (1) from $133.18   
  • Used (14) from $36.09   
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
Seller since 2008

Feedback rating:



New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.


Ships from: Chicago, IL

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Sort by
Sending request ...


The VBA Developer's Handbook is the essential resource for developers working with any application that employs the VBA programming language. Written by recognized VBA experts, this book provides detailed coverage of a wide range of specific VBA programming challenges. Its careful, step-by-step instruction and thousands of lines of code offer answers while teaching you to devise new solutions, creatively and efficiently. Learn product-independent VBA programming. Instruction applies equally to all environments using a Visual Basic 5.0 host, including Microsoft Office 97. This book is vital to your progress as a VBA developer, whether you build stand-alone applications or automate or customize commercial products using their built-in VBA programmability. Master the full range of VBA functionality. Accomplish everything possible using VBA, from the basic to the advanced. You'll find full coverage of built-in VBA functions and strategies for expanding them with custom procedures. Learn to write reusable classes, create well-tuned VBA applications, and investigate and document your work.

The authors of this book are two-thirds of the writing team that produced the bestselling Access Developer's Handbook. The book focuses on VBA for the entire Office Suite. Learning to use VBA as described in the book will enable developers to combine VBA, OLE Automation (Object Linking and Embedding), and ActiveX controls. The CD includes reusable code from the book, and third-party shareware and demos.

Read More Show Less

Product Details

  • ISBN-13: 9780782129786
  • Publisher: Wiley
  • Publication date: 4/1/2001
  • Series: Developer's Handbook Series
  • Edition description: REV
  • Edition number: 2
  • Pages: 1104
  • Product dimensions: 7.55 (w) x 9.00 (h) x 2.32 (d)

Read an Excerpt

VBA Developer's Handbook

By Ken Getz

John Wiley & Sons

ISBN: 0-7821-2978-1

Chapter One

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

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:

WARNING 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?

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.

TIP Online help for VBA states that a fixed-length string can be up to [2.sup.16] (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.

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

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.

WARNING 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 =, <, and so on), the Like operator, and the StrComp function. In addition, you can specify the method of comparison for each module using the Option Compare statement in the declarations area.

Option Compare

The Option Compare statement, if it's used at all, must appear in a module before any procedures, and it tells VBA how you want to make string comparisons within the module. The choices are as follows:

Option Compare Binary Comparisons are made based on the internal sort order of the characters, using their binary representation. In this situation, characters are treated case sensitively (that is, A isn't the same as a).
Option Compare Text Comparisons are made based on the text sort order of the current locale. Characters are treated, at least in English, case insensitively.
Option Compare Database Is available only in Microsoft Access. Comparisons are made based on the locale ID of the current database.

TIP If you don't specify an Option Compare setting, VBA uses Option Compare Binary. In that case, if you attempt to perform a simple comparison between A and a, you'll get a False return value. If you're working with strings and performing comparisons, make sure you're aware of the Option Compare setting for the module.

Comparison Operators

You can use the simple logical operators to compare two strings, like this:

If strText1 < strText2 Then ...

In this case, VBA performs a character-by-character comparison according to the Option Compare setting in force in the current module. The result of the comparison will most likely change, based on that setting. You can use the set of simple comparison operators shown here.

Operator Description

< Less than

<= Less than or equal to

>= Greater than or equal to

= Equal to

<> Not equal to

In addition, VBA supplies the Like operator for comparing two strings. This operator allows you to specify wildcards, character lists, and character ranges in the comparison string, not just fixed characters. The following is a listing of all the options for the comparison string using the Like operator:

Characters in Pattern Matches in String

? Any single character

* Zero or more characters

# Any single digit (0-9)

[charlist] Any single character in charlist

[!charlist] Any single character not in charlist

TIP The string containing the wildcard information must be on the right-hand side of the Like operator. That is, unlike many mathematical operators, this one is not commutative: The order of the operands is significant.

For example, the following code fragment would compare a string with a template that checks for valid Canadian Postal codes:

strTemp = "W1F 8G7" If strTemp Like "[A-Z]#[A-Z] #[A-Z]#" Then ' You know strTemp is a valid Canadian Postal Code End If

To check whether the single character in strTemp was a vowel, you could use this expression:

If strTemp Like "[AEIOUaeiou]*" Then ' You know the first character in strTemp is a vowel End If

If you want to see whether the word stored in strTemp doesn't start with a vowel, you could use an expression like this:

If strTemp Like "[!AEIOUaeiou]*" Then ' You know the word in strTemp doesn't start with a vowel End If

You'll find the Like operator to be invaluable when you need to validate input. Rather than parse the string yourself, you can use wildcards to allow various ranges of characters.

NOTE The behavior of the Like operator depends on the Option Compare setting. Unless you specify otherwise, each module uses Option Compare Binary (case-sensitive comparisons).
TIP There are a number of issues you need to be aware of when using the Like operator (sorting, order of the characters within the range, and so on). Be sure to check out the online help for this topic for more information.

Using the StrComp Function

The StrComp function provides a way for you to compare strings, overriding the Option Compare statement within a given module. To use StrComp, you specify the two strings and a comparison method (binary, text, or database), and the function returns a value indicating how the two strings compared. In general, you call StrComp like this:

intRetVal = StrComp(strText1, strText2, CompareOption)

The two text strings can be any string expressions. The CompareOption value should be one of the items from Table 1.2 or a locale ID integer that specifies a local sort order for comparisons. Depending on the parameters, StrComp returns one of the values from Table 1.3.

TIP The CompareOption parameter for StrComp is optional. If you omit it, VBA uses the option selected by the Option Compare setting for the module. If you omit the Option Compare, of course, VBA will use binary comparisons (vbBinaryCompare).
WARNING Online help incorrectly supplies a fourth value, not shown in Table 1.2 (vbUse-CompareOption, -1). This value doesn't appear to work in the current version of VBA. For any function that takes a comparison option as a parameter, you may not use the vbUseCompareOption value. (No matter what the online help tells you.)

Using the StrComp function, even if you normally perform case-sensitive comparisons, you can override that requirement for one comparison:

If StrComp(strText1, strText2, vbTextCompare) = 0 Then ' You know that strText1 and strText2 are the same, as far ' as the text comparison goes. End If

Converting Strings

Rather than provide individual functions to convert strings from one format to another, VBA includes the single StrConv function. This function allows you to specify a string, as well as a conversion parameter indicating the conversion you'd like to make. In general, you call the function like this:

strOutput = StrConv(strInput, Conversion, [LocaleID])

where strInput is the string to be converted; Conversion is a value from the following table; and LocaleID (optionally) specifies the Windows LocaleID to use for the conversion. (If you don't specify a locale ID, VBA will use the current locale's information in order to perform the conversion.) StrConv returns the converted string as its return value.

Constant Description

vbUpperCase Converts the string to uppercase characters.

vbLowerCase Converts the string to lowercase characters.

vbProperCase Converts the first letter of every word in the string to uppercase.

vbUnicode Converts the string to Unicode using the default code page of the system.

vbFromUnicode Converts the string from Unicode to the default code page of the system.

As you can see, the StrConv function performs two basic tasks: converting the case (upper, lower, proper) of strings and converting strings from ANSI to Unicode and back.

TIP If you're working in a Japanese or other Far East locale, you'll want to check out the options for StrConv that are available only in those locales. See the VBA online help for more information.

Creating Strings: The Space and String Functions

VBA provides two functions that make it easy for you to create specific strings. The Space function lets you create a string consisting only of spaces; you indicate the number of spaces, and VBA does the rest. The general syntax looks like this:

strOut = Space(lngSpaces)

Although this function has many uses, we've used it most often in two particular situations:

Creating string buffers when calling external DLLs (the Windows API, in particular) Padding strings so they're left or right justified within a buffer of a particular size You can use an expression like this to create a 10-character string of spaces: strTemp = Space(10)

If you need more flexibility, you can use the String function to create a string of any number of a specified character. For this function, you specify the number of characters you need and the specific character or ANSI value to repeat:

strOut = String(lngChars, strCharToRepeat) ' or

strOut = String(lngChars, intCharToRepeat)

For example, either of the following fragments will return a string containing 10 occurrences of the letter a. (The ANSI value for a is 97.)

strOut = String(10, "a")

strOut = String(10, 97)

Although you're unlikely to need this particular string, the following code fragment creates a string consisting of one A, two Bs, three Cs, and so on.

Dim intI As Integer

Dim strOut As String

For intI = 1 To 26 strOut = strOut & String(intI, Asc("A") + intI - 1) Next intI


Excerpted from VBA Developer's Handbook by Ken Getz Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More Show Less

Table of Contents

Ch. 1 Manipulating Strings 3
Ch. 2 Working with Dates and Times 91
Ch. 3 Working with Numbers 163
Ch. 4 Searching and Sorting in VBA 211
Ch. 5 Using VBA Class Modules 259
Ch. 6 Creating Dynamic Data Structures Using Class Modules 311
Ch. 7 Professional Development with VBA 363
Ch. 8 Using the VBA IDE as an Automation Server 417
Ch. 9 Retrieving and Setting System Information 471
Ch. 10 Working with the Registry 551
Ch. 11 The Windows Networking API 587
Ch. 12 Working with Disks and Files 653
Ch. 13 Adding Multimedia to Your Applications 753
App. A The Reddick VBA Naming Conventions 825
App. B Accessing DLLs and the Windows API 845
Index 884
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted April 11, 2004

    Required VBA reading!

    Excellent book for anyone interested in VBA software development. In-depth coverage of class modules; along with useful, meaningful code.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)