Page 219 - Microsoft Office Excel 2003 Programming Inside Out

Chapter 9

Manipulating Data with VBA

Manipulating Text . . . . . . . . . . . . . . . . . 193 Manipulating Dates and Times . . . . . . . 213
Manipulating Numbers . . . . . . . . . . . . . 206

When you’re given a workbook filled with data, you’re going to want to ask questions about
the data. What is the sum of the sales for a day? Or a month? Or a year? How much time
elapsed between a customer’s orders? And is the data you’re about to write to a text file in the
proper format to be read into a database? You can answer all of these questions, and
many more, using the text, number, and date/time processing functions available to you in
Excel VBA.
In this chapter, you will learn how to do the following:

● Prepare text strings for processing.
● Concatenate, find, and modify strings.
● Perform summary calculations on numerical data.
● Make financial decisions using Excel calculations.
● Work with dates and times in Excel.

Manipulating Text
Although you might normally think of Excel as a number crunching financial application, it’s
actually quite a versatile program. Not only can you perform myriad calculations on your
numerical data, but you can also handle any text that comes along with that data. Whether
you want to perform a simple task such as displaying a welcome message after a user opens a
workbook or import data from a text file into an Excel worksheet, you can do so using the
text-handling procedures available to you in Excel VBA and as worksheet formulas.

Determining if the Value in a Cell Is Text

The first step in manipulating a text value without generating an error is determining whether
the variable or cell value you want to work with is, in fact, a text value. You can determine
whether the value in a cell, a variable, or the value typed into an input box, is text by process­
ing the string with the ISTEXT function. For example, you can use the ISTEXT function to
guard against data entry errors, such as when someone types the wrong sort of data into a cell
or a UserForm. This sort of problem often occurs after you’ve changed a procedure or put a

193
Part 4: Advanced VBA