[eBook Code] Excel 2013 Power Programming with VBA (eBook Code, 1st)

· 제목 : [eBook Code] Excel 2013 Power Programming with VBA (eBook Code, 1st) 
· 분류 : 외국도서 > 컴퓨터 > 데스크탑 애플리케이션 > 스프레드시트
· ISBN : 9781118491805
· 쪽수 : 1104쪽

Introduction 1

Part I: Some Essential Background

Chapter 1: Excel in a Nutshell 11

About Excel 11

Thinking in Terms of Objects 11

Workbooks 12

Excel’s User Interface 16

Data Entry 25

Formulas, Functions, and Names 25

Selecting Objects 27

Formatting 28

Protection Options 29

Charts 32

Shapes and SmartArt 33

Database Access 33

Internet Features 35

Analysis Tools 36

Add-Ins 36

Macros and Programming 37

File Format 37

Excel’s Help System 38

Chapter 2: Formula Tricks and Techniques 39

About Formulas 39

Calculating Formulas 40

Cell and Range References 41

Using Names 45

Formula Errors 51

Excel Auditing Tools 52

Array Formulas 61

Counting and Summing Techniques 64

Lookup Formulas 66

Working with Dates and Times 69

Creating Megaformulas 71

Chapter 3: Understanding Excel Files 75

Starting Excel 75

File Types 76

Excel File Compatibility 80

Protected View 81

Using AutoRecover 82

Working with Template Files 83

Inside an Excel File 86

The OfficeUI File 91

The XLB File 92

Add-In Files 93

Excel Settings in the Registry 93

Chapter 4: Essentials of Spreadsheet Application Development 97

What Is a Spreadsheet Application? 97

Steps for Application Development 98

Determining User Needs 99

Planning an Application That Meets User Needs 100

Determining the Most Appropriate User Interface 102

Concerning Yourself with the End User 106

Other Development Issues 113

Part II: Understanding Visual Basic for Applications

Chapter 5: Introducing Visual Basic for Applications 119

Getting Some BASIC Background 119

Delving into VBA 120

Covering the Basics of VBA 121

Introducing Visual Basic Editor 124

Working with Project Explorer 127

Working with Code Windows 129

Customizing the VBE Environment 137

The Macro Recorder 143

About Objects and Collections 151

Properties and Methods 153

The Comment Object: A Case Study 156

Some Useful Application Properties 164

Working with Range Objects 166

Things to Know about Objects 173

Chapter 6: VBA Programming Fundamentals 177

VBA Language Elements: An Overview 177

Comments 179

Variables, Data Types, and Constants 181

Assignment Statements 195

Arrays 197

Object Variables 199

User-Defined Data Types 201

Built-in Functions 202

Manipulating Objects and Collections 205

Controlling Code Execution 208

Chapter 7: Working with VBA Sub Procedures 227

About Procedures 227

Executing Sub Procedures 230

Passing Arguments to Procedures 241

Error-Handling Techniques 245

A Realistic Example That Uses Sub Procedures 250

Chapter 8: Creating Function Procedures 269

Sub Procedures versus Function Procedures 269

Why Create Custom Functions? 270

An Introductory Function Example 271

Function Procedures 275

Function Arguments 281

Function Examples 282

Emulating Excel’s SUM function 298

Extended Date Functions 302

Debugging Functions 304

Dealing with the Insert Function Dialog Box 305

Using Add-ins to Store Custom Functions 311

Using the Windows API 311

Chapter 9: VBA Programming Examples and Techniques 317

Learning by Example 317

Working with Ranges 318

Working with Workbooks and Sheets 348

VBA Techniques 353

Some Useful Functions for Use in Your Code 361

Some Useful Worksheet Functions 367

Windows API Calls 385

Part III: Working with UserForms

Chapter 10: Custom Dialog Box Alternatives 395

Before You Create That UserForm 395

Using an Input Box 395

The VBA MsgBox Function 401

The Excel GetOpenFilename Method 406

The Excel GetSaveAsFilename Method 410

Prompting for a Directory 410

Displaying Excel’s Built-In Dialog Boxes 411

Displaying a Data Form 413

Chapter 11: Introducing UserForms 417

How Excel Handles Custom Dialog Boxes 417

Inserting a New UserForm 418

Adding Controls to a UserForm 419

Toolbox Controls 419

Adjusting UserForm Controls 424

Adjusting a Control’s Properties 425

Displaying a UserForm 429

Closing a UserForm 432

Creating a UserForm: An Example 433

Understanding UserForm Events 440

Referencing UserForm Controls 446

Customizing the Toolbox 448

Creating UserForm Templates 450

A UserForm Checklist 451

Chapter 12: UserForm Examples 453

Creating a UserForm “Menu” 453

Selecting Ranges from a UserForm 456

Creating a Splash Screen 458

Disabling a UserForm’s Close Button 460

Changing a UserForm’s Size 460

Zooming and Scrolling a Sheet from a UserForm 462

ListBox Techniques 464

Using the MultiPage Control in a UserForm 483

Using an External Control 484

Animating a Label 486

Chapter 13: Advanced UserForm Techniques 491

A Modeless Dialog Box 492

Displaying a Progress Indicator 496

Creating Wizards 505

Emulating the MsgBox Function 511

A UserForm with Movable Controls 515

A UserForm with No Title Bar 517

Simulating a Toolbar with a UserForm 519

Emulating a Task Pane with a UserForm 521

A Resizable UserForm 522

Handling Multiple UserForm Controls with One Event Handler 527

Selecting a Color in a UserForm 530

Displaying a Chart in a UserForm 532

Making a UserForm Semitransparent 533

An Enhanced Data Form 535

A Puzzle on a UserForm 538

Video Poker on a UserForm 539

Part IV: Advanced Programming Techniques

Chapter 14: Developing Excel Utilities with VBA 543

About Excel Utilities 543

Using VBA to Develop Utilities 544

What Makes a Good Utility? 545

Text Tools: The Anatomy of a Utility 545

More about Excel Utilities 563

Chapter 15: Working with Pivot Tables 565

An Introductory Pivot Table Example 565

Creating a More Complex Pivot Table 571

Creating Multiple Pivot Tables 576

Creating a Reverse Pivot Table 579

Chapter 16: Working with Charts 583

Getting the Inside Scoop on Charts 583

Creating an Embedded Chart 586

Creating a Chart on a Chart Sheet 588

Modifying Charts 589

Using VBA to Activate a Chart 591

Moving a Chart 592

Using VBA to Deactivate a Chart 592

Determining Whether a Chart Is Activated 593

Deleting from the ChartObjects or Charts Collection 594

Looping through All Charts 596

Sizing and Aligning ChartObjects 598

Creating Lots of Charts 600

Exporting a Chart 603

Changing the Data Used in a Chart 605

Using VBA to Display Arbitrary Data Labels on a Chart 611

Displaying a Chart in a UserForm 615

Understanding Chart Events 617

Discovering VBA Charting Tricks 625

Animating Charts 632

Creating an Interactive Chart without VBA 637

Working with Sparkline Charts 641

Chapter 17: Understanding Excel’s Events 645

What You Should Know about Events 645

Getting Acquainted with Workbook-Level Events 653

Examining Worksheet Events 661

Checking Out Chart Events 670

Monitoring with Application Events 672

Using UserForm Events 676

Accessing Events Not Associated with an Object 678

Chapter 18: Interacting with Other Applications 685

Starting an Application from Excel 685

Activating an Application with Excel 690

Running Control Panel Dialog Boxes 691

Using Automation in Excel 693

Sending Personalized E-Mail via Outlook 704

Sending E-Mail Attachments from Excel 707

Chapter 19: Creating and Using Add-Ins 711

What Is an Add-In? 711

Understanding Excel’s Add-In Manager 714

Creating an Add-in 716

An Add-In Example 717

Comparing XLAM and XLSM Files 723

Manipulating Add-Ins with VBA 729

Optimizing the Performance of Add-ins 735

Special Problems with Add-Ins 736

Part V: Developing Applications

Chapter 20: Working with the Ribbon 743

Ribbon Basics 743

Using VBA with the Ribbon 747

Customizing the Ribbon 753

Creating an Old-Style Toolbar 775

Chapter 21: Working with Shortcut Menus 779

CommandBar Overview 779

Using VBA to Customize Shortcut Menus 786

Shortcut Menus and Events 796

Chapter 22: Providing Help for Your Applications 801

Help for Your Excel Applications 801

Help Systems That Use Excel Components 804

Displaying Help in a Web Browser 812

Using the HTML Help System 814

Chapter 23: Developing User-Oriented Applications 821

What Is a User-Oriented Application? 821

the Loan Amortization Wizard 822

Application Development Concepts 832

Part VI: Other Topics

Chapter 24: Compatibility Issues 837

What Is Compatibility? 837

Types of Compatibility Problems 838

Avoid Using New Features 839

But Will It Work on a Mac? 841

Dealing with 64-bit Excel 842

Creating an International Application 843

Chapter 25: Manipulating Files with VBA 851

Performing Common File Operations 851

Displaying Extended File Information 861

Working with Text Files 863

Text File Manipulation Examples 867

Zipping and Unzipping Files 879

Working with ADO 882

Chapter 26: Manipulating Visual Basic Components 885

Introducing IDE 885

The IDE Object Model 888

Displaying All Components in a VBA Project 891

Listing All VBA Procedures in a Workbook 892

Replacing a Module with an Updated Version 894

Using VBA to Write VBA Code 896

Adding Controls to a UserForm at Design Time 898

Creating UserForms Programmatically 902

Chapter 27: Understanding Class Modules 911

What Is a Class Module? 911

Example: Creating a NumLock Class 912

More about Class Modules 918

Example: A CSV File Class 921

Chapter 28: Working with Colors 927

Specifying Colors 927

Understanding Grayscale 933

Experimenting with Colors 935

Understanding Document Themes 937

Working with Shape Objects 943

Modifying Chart Colors 948

Chapter 29: Frequently Asked Questions about Excel Programming 953

Getting the Scoop on FAQs 953

General Excel Questions 954

Visual Basic Editor 958

Procedures 960

Functions 964

Objects, Properties, Methods, and Events 967

Security-Related Issues 977

UserForms 978

Add-Ins 982

User Interface 984

Part VII: Appendixes

Appendix A: VBA Statements and Functions Reference 989

Invoking Excel functions in VBA instructions 992

Appendix B: VBA Error Codes 999

Appendix C: This Book’s Website 1003

Index 1019