Administrator To

17 Arrays 379

Declare an Array 379

Multidimensional Arrays 380

Fill an Array 380

Empty an Array 382

Arrays Can Make It Easier to Manipulate Data, But Is That All? 383

Dynamic Arrays 385

Passing an Array 386

Next Steps 386

18 Text File Processing 387

Importing from Text Files 387

Importing Text Files with Less Than 65,536 Rows 387

Reading Text Files with More Than 65,536 Rows 394

Writing Text Files 398

Next Steps 399

19 Using Access as a Back End to Enhance Multi-User Access to Data 401

ADO Versus DAO 402

The Tools of ADO 404

Adding a Record to the Database 404

Retrieving Records from the Database 406

Updating an Existing Record 408

Deleting Records via ADO 410

Summarizing Records via ADO 410

Other Utilities via ADO 411

Checking for Existence of Tables 411

Checking for Existence of a Field 412

Adding a Table on the Fly 413

Adding a Field on the Fly 413

Next Steps 414

20 Creating Classes, Records,and Collections 415

Inserting a Class Module 415

Trapping Application and Embedded Chart Events 416

Application Events 416

Embedded Chart Events 418

Creating a Custom Object 420

Using a Custom Object 421

Using Property Let and Property Get to Control How Users Utilize Custom Objects . . . .422

Collections 424

Creating a Collection in a Standard Module 424

Creating a Collection in a Class Module 425

User-Defined Types (UDTs) 429

Next Steps 432

21 Advanced UserForm Techniques 433

Using the UserForm Toolbar in the Design of Controls on UserForms 433

Controls and Collections 434

More UserForm Controls 436

Toggle Buttons 436

Tabstrip 436

RefEdit 438

Modeless Userforms 438

Hyperlinks in Userforms 439

Adding Controls at Runtime 440

Resizing the Userform on the Fly 441

Adding a Control on the Fly 442

Sizing on the Fly 442

No AutoComplete Funtionality 442

Adding Other Controls 442

Adding an Image on the Fly 443

Putting It All Together 444

Using a Scrollbar as a Slider to Select Values 446

Adding Help Tips to Controls 447

Accelerator Keys 447

Control Tip Text 448

Tab Order 448

Coloring the Active Control 448

Transparent Forms 451

Next Steps 452

22 Windows Application Programming Interface (API) 453

What Is the Windows API? 453

Understanding an API Declaration 453

Using an API Declaration 454

API Examples 455

GetComputerName 455

FilelsOpen 456

Retrieve Display Resolution Information 457

Disable the Excel X for Closing the Application 458

Disable the X for Closing a Userform 459

Running Timer 460

Clickable Links on Forms 461

Playing Sounds 462

Retrieving a File Path 462

Finding More API Declarations 466

Next Steps 466

23 Handling Errors 467

What Happens When an Error Occurs 467

Debug Error Inside Userform Code Is Misleading 468

Basic Error Handling with the On Error GoTo Syntax 470

Multiple Error Handlers 472

Generic Error Handlers 472

Handling Errors by Choosing to Ignore Them 472

Suppressing Excel Warnings 473

Encountering Errors on Purpose 474

Train Your Clients 474

Errors While Developing Versus Errors Months Later 475

Runtime Error 9: Subscript out of Range 475

RunTime Error 1004:Method 'Range'of Object '_Global'Failed 476

The Ills of Protecting Code 477

More Problems with Passwords 478

Errors Caused by Different Versions 478

Next Steps 479

24 Using Custom Menus to Run Macros 481

Creating a Custom Menu 481

Deleting and Creating the Custom Menu 482

Adding a Single Menu Item 483

Breaking Items into Groups 484

Adding a Fly-out Menu 485

Creating a Custom Toolbar 487

Deleting and Creating the Toolbar 487

Adding Buttons to the Toolbar 488

Using FaceiD Codes to Add Icons to the Toolbar 489

Adding Drop-downs to the Toolbar 489

Remembering a Toolbar's Position 490

Other Ways to Run a Macro 491

Keyboard Shortcut 491

Attach a Menu to a Command Button 492

Attach a Macro to an ActiveX Control 494

Next Steps 496

25 Add-Ins 497

Characteristics of Standard Add-Ins 497

Converting an Excel Workbook to an Add-In 498

Using Save As to Convert a File to an Add-In 499

Using the VB Editor to Convert a File to an Add-In 499

Having Your Client Install the Add-In 500

Standard Add-Ins Are Not Secure 502

Closing Add-Ins 502

Removing Add-Ins 502

Using a Hidden Workbook as an Alternative to an Add-In 503

Next Steps 504

26 Case Study: Designing an Excel Application 505

About Tushar Mehta 505

Using Excel for More Than Number Crunching 506

The Solution 506

Implementing the Solution in Excel and VBA 507

Pass 1—Top-Down Concepts 508

Pass 1—Key Components Defined 509

Pass 2—Top-Down Code 510

Pass 2—Key Components 511

Pass 3—Top-Down Code Completed 513

Pass 3—Key Components Completed 515

Summary 516

Next Steps 516

Index 517

About the Authors

Bill Jelen, "Mr. Excel," is an accomplished Excel author and the principal behind the leading Excel Web site, As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His Web site hosts more than 10 million page views annually. Prior to founding, Jelen spent 12 years in the trenches—working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio, with his wife Mary Ellen and sons Josh and Zeke.

Tracy Syrstad works as a programmer and consultant for the MrExcel Consulting team. She remembers the painful trek up the VBA learning curve while developing applications for co-workers at a former job. She is co-editor of Holy Macro! It's 1,900 Excel VBA Examples CD and editor of Dreamboat On Word. She lives on an arboreous acreage in eastern South Dakota with her husband John and dog General.


Dedicated to Mary Ellen Jelen

Dedicated to John Syrstad, whose confidence in me gave me strength to take this path


Thank you to Mala Singh of XLSoft Consulting for expertise with Chapter 10. Tom Urtis for technical editing. Jerry Kohl for many of the ideas in this book. Jeanette Garcia, Barb Jelen, and Doug and Stacy Jefferies for technical assistance. Zeke, Josh, and Mary Ellen Jelen for their patience. Tom Macioszek for peer review. Chad Rothschiller at Microsoft for teaching me everything there is to know about Excel XML. Dave Gainer, Steve Zaske, Eric Patterson, and Joe Chirilov at Microsoft. Loretta Yates, Sean Dixon, Margo Catts, Andy Beaster, Greg Wiegand, Amy Sorokas, Kim Spilker, Erika Millen, Kathy Bidwell, Cindy Teeters, Michelle Mitchell, and Gary Adair at Pearson. Ivana Taylor for her marketing brilliance. All the readers and MVPs and clients. Dan Bricklin, Bob Frankston, and Mitch Kapor for being spreadsheet pioneers. William Brown at Waterside. Pam Gensel for macro lesson number 1. Robert F. Jelen for being my first programming fan. Robert K. Jelen for inspiration. Bonnie Hilliard for P.R. Laurelle Riippa at PW. Leo LaPorte and Fawn Luu at TechTV Dan Poynter. Craig Crossman at Computer America. Walter Mossberg at the Wall Street Journal.

Thank you to Cort Chilldon-Hoff for offering support and being a sounding board when I was stumped. Juan Pablo González Ruiz for offering his expertise, especially with the functions found in Chapter 4. Daniel Klann, Dennis Wallentin, Ivan F. Moala, Juan Pablo González, Masaru Kaji, Nathan P. Oliver, Richie Sills, Russell Hauf, Suat Mehmet Ozgur, Tom Urtis, Tommy Miles, Wei Jiang for their contributions to Chapter 13. Chris Lemair for introducing me to Excel and the power of its macros. Anne Troy who introduced Bill and I in the first place. The Computer America chat room for distracting me when I was supposed to be working on the book.

We Want to Hear from You!

As the reader of this book, you are our most important critic and commentator. We value your opinion and want to know what we're doing right, what we could do better, what areas you'd like to see us publish in, and any other words of wisdom you're willing to pass our way.

As an associate publisher for Sams, I welcome your comments. You can email or write me directly to let me know what you did or didn't like about this book—as well as what we can do to make our books better.

Please note that I cannot help you with technical problems related to the topic of this book. We do have a User Services group, however, where I will forward specific technical questions related to the book.

When you write, please be sure to include this book's title and author as well as your name, email address, and phone number. I will carefully review your comments and share them with the authors and editors who worked on the book.

Email: [email protected]

Mail: Michael Stephens Associate Publisher Sams Publishing 800 East 96th Street Indianapolis, IN 46240 USA

For more information about this book or another Sams title, visit our Web site at Type the ISBN (0789731290) or the title of a book in the Search field to find the page you're looking for.

0 0

Post a comment