Contents

Acknowledgments xiii

Introduction xv

Chapter 1 Introduction to Spreadsheet Automation 1

Understanding Macros 2

Common Uses for Macros 2

Planning a Macro 3

Recording a Macro 5

Running the Macro 8

Modifying the Macro 8

Adding Comments 12

Analyzing the Macro Code 13

Cleaning Up the Macro Code 14

Testing the Modified Macro 15

Two Levels of Macro Execution 16

Improving Your Macro 17

Renaming the Macro 20

Other Methods of Running Macros 20

Saving Macros 26

Printing Macros 27

Storing Macros in the Personal Macro Workbook 27

Opening Workbooks Containing Macros 29

The Visual Basic Editor Window 31

Understanding the Project Explorer Window 31

Understanding the Properties Window 32

Understanding the Code Window 33

Other Windows in the Visual Basic Editor Window 35

What's Next 36

Chapter 2 First Steps in Visual Basic for Applications 37

Understanding Instructions, Modules, and Procedures 38

Assigning a Name to the VBA Project 39

Renaming the Module 40

Calling a Procedure from Another Project 41

Understanding Objects, Properties, and Methods 43

Learning about Objects, Properties, and Methods 45

Syntax Versus Grammar 48

Breaking Up Long VBA Statements 51

Understanding VBA Errors 51

In Search of Help 54

On-the-fly Syntax and Programming Assistance 55

Using the Object Browser 60

Using the VBA Object Library 66

Locating Procedures with the Object Browser 68

Using the Immediate Window 68

Obtaining Information in the Immediate Window 71

Learning about Objects 72

Doing Things with Spreadsheet Cells 73

Using the Range Property 73

Using the Cells Property 73

Using the Offset Property 75

Other Methods of Selecting Cells 77

Selecting Rows and Columns 77

Obtaining Information about the Worksheet 78

Entering Data in a Worksheet 78

Returning Information Entered in a Worksheet 78

Finding Out about Cell Formatting 79

Moving, Copying, and Deleting Cells 80

Doing Things with Workbooks and Worksheets 80

Doing Things with Windows 82

Managing the Excel Application 83

What's Next 83

Chapter 3 Understanding Variables, Data Types, and Constants 85

Saving Results of VBA Statements 86

What Are Variables? 86

Data Types 87

How to Create Variables 88

How to Declare Variables 89

Specifying the Data Type of a Variable 92

Assigning Values to Variables 94

Forcing Declaration of Variables 98

Understanding the Scope of Variables 99

Procedure-Level (Local) Variables 100

Module-Level Variables 100

Project-Level Variables 102

Lifetime of Variables 102

Understanding and Using Static Variables 102

Declaring and Using Object Variables 104

Using Specific Object Variables 106

Finding a Variable Definition 106

Using Constants in VBA Procedures 107

Built-in Constants 108

What's Next 110

Chapter 4 VBA Procedures: Subroutines and Functions 111

About Function Procedures 112

Creating a Function Procedure 112

Executing a Function Procedure 115

Passing Arguments 118

Specifying Argument Types 120

Passing Arguments by Reference and by Value 122

Using Optional Arguments 123

Locating Built-in Functions 125

Using the MsgBox Function 127

Using the InputBox Function 134

Using the InputBox Method 138

Using Master Procedures and Subprocedures 142

What's Next 145

Chapters Decision Making with VBA 147

Relational and Logical Operators 148

If.. .Then Statement 149

Decisions Based on More Than One Condition 152

The If.. .Then.. .ElseIf Statement 158

Nested If.. .Then... Statements 160

Select Case Statement 161

Using Is with the Case Clause 163

Specifying a Range of Values in a Case Clause 164

Specifying Multiple Expressions in a Case Clause 165

What's Next 166

Chapter 6 Repeating Actions in VBA 167

Watching a Procedure Execute 173

While.. .Wend Loop 174

For.. .Next Loop 174

For Each.. .Next Loop 177

Exiting Loops Early 178

Nested Loops 179

What's Next 180

Chapter 7 Managing Lists and Tables of Data with VBA 181

Understanding Arrays 182

Declaring Arrays 184

Array Upper and Lower Bounds 185

Using Arrays in VBA Procedures 185

Arrays and Looping Statements 187

Using a Two-Dimensional Array 189

Static and Dynamic Arrays 190

Array Functions 193

The Array Function 193

The IsArray Function 194

The Erase Function 195

The LBound and UBound Functions 195

Errors in Arrays 196

Parameter Arrays 198

What's Next 199

Chapter 8 Manipulating Files and Folders with VBA 201

Manipulating Files and Folders 202

Finding Out the Name of the Active Folder (the CurDir Function) 202

Changing the Name of a File or Folder (the Name Function) 203

Checking the Existence of a File or Folder (the Dir Function) 204

Finding Out the Date and Time the File Was Modified

(the FileDateTime Function) 207

Finding Out the Size of a File (the FileLen Function) 207

Returning and Setting File Attributes (the GetAttr and SetAttr Functions) 208

Changing the Default Folder or Drive (the ChDir and

ChDrive Statements) 210

Creating and Deleting Folders (the MkDir and RmDir Statements) . . . 211

Copying Files (the FileCopy Statement) 212

Deleting Files (the Kill Statement) 214

Writing to and Reading from Files (Input/Output) 215

File Access Types 215

Working with Sequential Files 215

Working with Random Access Files 224

Working with Binary Files 230

Modern Methods of Working with Files and Folders 232

Finding Information about Files with the WSH 234

Properties of the File Object 239

Properties of the Folder Object 240

Properties of the Drive Object 241

Creating a Text File Using WSH 242

Performing Other Operations with WSH 244

What's Next 247

Chapter 9 Controlling Other Applications with VBA 249

Launching Applications 250

Moving between Applications 254

Controlling Another Application 255

Other Methods of Controlling Applications 258

Understanding Automation 258

Understanding Linking and Embedding 258

Linking and Embedding with VBA 260

COM and Automation 261

Understanding Binding 261

Late Binding 261

Early Binding 262

Establishing a Reference to an Object Library 263

Creating Automation Objects 265

Using the CreateObject Function 265

Creating a New Word Document Using Automation 266

Using the GetObject Function 267

Opening an Existing Word Document 268

Using the New Keyword 269

Using Automation to Access Microsoft Outlook 270

What's Next 271

Chapter 10 Dialog Boxes and Custom Forms 273

Excel Dialog Boxes 274

File Open and File Save As Dialog Boxes 277

GetOpenFilename and GetSaveAsFilename Methods 281

Creating Forms 283

Tools for Creating User Forms 285

Placing Controls on a Form 290

Sample Application 1: Info Survey 290

Adding Buttons, Check Boxes, and Other Controls to a Form 292

Changing Control Names 296

Setting Other Control Properties 297

Preparing a Worksheet to Store Custom Form Data 298

Displaying a Custom Form 300

Setting the Tab Order 300

Understanding Form and Control Events 301

Writing VBA Procedures to Respond to Form and Control Events 303

Writing a Procedure to Initialize the Form 304

Writing a Procedure to Populate the List Box Control 306

Writing a Procedure to Control Option Buttons 306

Writing Procedures to Synchronize the Text Box with the Spin Button. . 308

Writing a Procedure that Closes the User Form 308

Transferring Form Data to the Worksheet 309

Using the Info Survey Application 310

Sample Application 2: Students and Exams 310

Using MultiPage and TabStrip Controls 311

Writing VBA Procedures for the Students and Exams Custom Form. . . 314

Using the Students and Exams Custom Form 318

What's Next 322

Chapter 11 Custom Collections and Class Modules 323

Working with Collections 324

Declaring a Custom Collection 326

Adding Objects to a Custom Collection 326

Removing Objects from a Custom Collection 328

Insert: Module or Class Module? 329

Creating Custom Objects 329

Creating a Class 330

Variable Declarations 330

Defining the Properties for the Class 331

Creating the Property Get Procedures 332

Creating the Property Let Procedures 333

Creating the Class Methods 334

Creating an Instance of a Class 335

Event Procedures in the Class Module 336

Creating the User Interface 337

Watching the Execution of Your VBA Procedures 346

What's Next 349

Chapter 12 Creating Custom Menus and Toolbars with VBA 351

Toolbars 352

Using the CommandBar Object 353

Creating a Custom Toolbar 354

Deleting a Custom Toolbar 357

Using the CommandBar Properties 357

Working with CommandBar Controls 357

Working with Menus 364

Menu Programming 365

Creating a Submenu 368

Modifying a Built-in Shortcut Menu 370

Creating a Shortcut Menu 371

What's Next 374

Chapter 13 Debugging VBA Procedures and Handling Errors 375

Testing VBA Procedures 376

Stopping a Procedure 376

Using Breakpoints 378

Using the Immediate Window in Break Mode 381

Using the Stop Statement 383

Adding a Watch Expression 384

Using Quick Watch 387

Using the Locals Window and the Call Stack Dialog Box 388

Stepping through VBA Procedures 390

Stepping through a Procedure 391

Stepping Over a Procedure 392

Setting the Next Statement 393

Showing the Next Statement 394

Stopping and Resetting VBA Procedures 394

Understanding and Using Conditional Compilation 394

Navigating with Bookmarks 397

Trapping Errors 398

What's Next 403

Chapter 14 Event Programming in Microsoft Excel 2002 405

Introduction to Event Procedures 406

Enabling and Disabling Events 408

Event Sequences 409

Worksheet Events 409

Workbook Events 415

Chart Events 427

Embedded Chart Events 431

Events Recognized by the Application Object 432

Query Table Events 436

What's Next 438

Chapter 15 Using Excel with Microsoft Access 439

Object Libraries 440

Setting Up References to Object Libraries 442

Connecting to Microsoft Access 443

Using Automation to Connect to a Microsoft Access Database 443

Using DAO to Connect to a Microsoft Access Database 447

Using ADO to Connect to a Microsoft Access Database 448

Performing Microsoft Access Tasks from Excel 449

Creating a New Microsoft Access Database 450

Opening a Microsoft Access Form 451

Opening a Microsoft Access Report 454

Running a Microsoft Access Query 455

Calling a Microsoft Access Function 459

Retrieving Microsoft Access Data into an Excel Worksheet 459

Retrieving Data with the GetRows Method 459

Retrieving Data with the CopyFromRecordset Method 461

Retrieving Data with the TransferSpreadsheet Method 463

Using the OpenDatabase Method 464

Creating a Text File from Microsoft Access Data 466

Creating a Query Table from Microsoft Access Data 468

Using Microsoft Access Data in Excel 470

Creating an Embedded Chart from Microsoft Access Data 470

Transferring the Excel Spreadsheet to an Access Database 472

Linking an Excel Spreadsheet to a Microsoft Access Database 472

Importing an Excel Spreadsheet to a Microsoft Access Database 474

Placing Excel Data in an Access Table 474

What's Next 476

Chapter 16 Excel and the Internet 477

Creating Hyperlinks Using VBA 478

Creating and Publishing HTML Files Using VBA 482

Web Server — Storing and Opening Workbooks 488

Web Queries 488

Creating and Running Web Queries with VBA 490

Web Queries with Parameters 493

Dynamic Web Queries 497

Refreshing Data 499

Excel and Active Server Pages 499

Creating an ASP Script 501

Installing Internet Information Services (IIS) or Personal Web Server . . 504

Creating a Virtual Directory 505

Running Your First ASP Script 508

Generating a Tab-delimited File on the Web Server 509

Creating an Excel File from User Input 514

Printing Excel Data to an Internet Browser using the GetString Method 523

Creating Charts in ASP 525

What's Next 530

Chapter 17 XML and Excel 2002 531

What is XML? 532

XML Support in Excel 2002 533

Creating XML Spreadsheet Files with VBA 534

Viewing the XML Source File in Notepad 536

Well-Formed XML Documents 539

Viewing the XML Source File in Internet Explorer 540

Building XML Files Outside of Microsoft Excel 2002 542

The XML Flattener 543

Formatting XML Data with Stylesheets 545

Linking an XML Document to a Stylesheet 550

Viewing XML Documents Formatted with Stylesheets 550

Using an XSLT Template 553

XML Data Islands 555

Using VBScript to Transform the Contents of XML Data Islands 557

Saving a Range of Cells as an XML Document 559

The XML Document Object Model 561

Transform XML into HTML with an XSL Stylesheet Programmatically . 563

Using VBScript and XML DOM to Transform XML Documents 566

Working with XML Document Nodes 567

Retrieving Information from Element Nodes 569

XML via ADO 573

Saving an ADO Recordset as XML to Disk 573

Two Types of XML Files 574

Applying an XSL Stylesheet 576

Transforming Attribute-Based XML Data into an HTML Table 578

Loading an ADO Recordset 580

Saving the ADO Recordset to XML in Memory 581

Saving the ADO Recordset into the XML DOMDocument Object 584

XML and ASP 586

Posting Excel XML Data to a Web Server 591

What's Next 613

Appendix A—Programming PivotTables and PivotCharts 615

Appendix B—Programming Special Features 643

Appendix C—Introduction to Using and Programming Smart Tags 661

Appendix D—Microsoft Office XP Web Components 675

Index 698

0 0

Post a comment