Page 10 - Microsoft Office Excel 2003 Programming Inside Out

Table of Contents

The Sheets and Worksheets Collections . . . . . . . . . . . . . . . . . . . . . . . . . 149
Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Worksheet Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

Chapter 8
Ranges and Cells 161
Basic Range Manipulations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Finding the Active Range. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Selecting a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Referring to Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Referencing Ranges on the Active Worksheet . . . . . . . . . . . . . . . . . 166
Referencing Ranges on an Inactive Worksheet . . . . . . . . . . . . . . . . 167
Referencing Cells in a Range. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Referencing Cells Using the Offset Property . . . . . . . . . . . . . . . . . . 167
Defining a Range Using the Cells Property . . . . . . . . . . . . . . . . . . . 172
Referencing Columns and Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Referencing Non-Contiguous Groups of Cells . . . . . . . . . . . . . . . . . 172
Manipulating Groups of Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Resizing Cells Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Joining Two Ranges Together. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Detecting Empty Cells. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Using Named Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Defining a Named Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Changing Notation Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Reserved Range Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Copying Data Between Ranges and Arrays . . . . . . . . . . . . . . . . . . . 185
Getting Data Entry Right the First Time . . . . . . . . . . . . . . . . . . . . . . . . . 187

Part 4
Advanced VBA

Chapter 9
Manipulating Data with VBA 193

Manipulating Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Determining if the Value in a Cell Is Text . . . . . . . . . . . . . . . . . . . . 193
Preparing String Data for Processing . . . . . . . . . . . . . . . . . . . . . . . 195
Determining the Number of Characters in a String . . . . . . . . . . . . . 197
Concatenating Text from Two or More Cells or Variables . . . . . . . . . 198
Returning the First or Last Several Characters from a String . . . . . . 200
Returning Characters from Arbitrary Positions in a String. . . . . . . . . 202
Finding a String Within Another String . . . . . . . . . . . . . . . . . . . . . . 202
Manipulating Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Performing Summary Calculations . . . . . . . . . . . . . . . . . . . . . . . . . 206
Performing Financial Calculations . . . . . . . . . . . . . . . . . . . . . . . . . 208
x