Microsoft Office Tutorials and References

In Depth Information

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