Excel VBA 24-Hour Trainer

2. Auflage 2015
ISBN: 978-1-118-99137-4
Verlag: Wiley

Buch, Englisch, 504 Seiten, Format (B × H): 185 mm x 236 mm, Gewicht: 816 g

Master VBA automation quickly and easily to get more out of Excel

Excel VBA 24-Hour Trainer, 2nd Edition is the quick-start guide to getting more out of Excel, using Visual Basic for Applications. This unique book/video package has been updated with fifteen new advanced video lessons, providing a total of eleven hours of video training and 45 total lessons to teach you the basics and beyond. This self-paced tutorial explains Excel VBA from the ground up, demonstrating with each advancing lesson how you can increase your productivity. Clear, concise, step-by-step instructions are combined with illustrations, code examples, and downloadable workbooks to give you a practical, in-depth learning experience and results that apply to real-world scenarios.

This is your comprehensive guide to becoming a true Excel power user, with multimedia instruction and plenty of hands-on practice.
* Program Excel's newest chart and pivot table object models
* Manipulate the user interface to customize the look and feel of a project
* Utilize message boxes, input boxes, and loops to yield customized logical results
* Interact with and manipulate Word, Access, PowerPoint, and Outlook from Excel

If you're ready to get more out of this incredibly functional program, Excel VBA 24-Hour Trainer, 2nd Edition provides the expert instruction and fast, hands-on learning you need.

Autoren/Hrsg.

Urtis, Tom

Fachgebiete

• Mathematik | Informatik EDV | Informatik Programmierung | Softwareentwicklung Microsoft Programmierung
• Mathematik | Informatik EDV | Informatik Programmierung | Softwareentwicklung Programmier- und Skriptsprachen
• Mathematik | Informatik EDV | Informatik Business Application Tabellenkalkulation Microsoft Excel

Inhaltsverzeichnis

INTRODUCTION xxvii

PART I: UNDERSTANDING THE BASICS

LESSON 1: INTRODUCING VBA 3

What Is VBA? 3

A Brief History of VBA 4

What VBA Can Do for You 5

Liabilities of VBA 8

Try It 9

LESSON 2: GETTING STARTED WITH MACROS 11

Composing Your First Macro 11

Running a Macro 21

Try It 22

LESSON 3: INTRODUCING THE VISUAL BASIC EDITOR 25

What Is the VBE? 25

Try It 30

LESSON 4: WORKING IN THE VBE 33

Toolbars in the VBE 33

Macros and Modules 33

Understanding the Code 36

Editing a Macro with Comments and Improvements to the Code 37

Try It 44

PART II: DIVING DEEPER INTO VBA

LESSON 5: OBJECT-ORIENTED PROGRAMMING: AN OVERVIEW 49

What "Object-Oriented Programming" Means 49

The Object Model 50

Try It 53

LESSON 6: VARIABLES, DATA TYPES, AND CONSTANTS 55

What Is a Variable? 55

Assigning Values to Variables 56

Why You Need Variables 56

Data Types 57

Forcing Variable Declaration 59

Understanding a Variable's Scope 61

Try It 64

LESSON 7: UNDERSTANDING OBJECTS AND COLLECTIONS 67

Workbooks 67

Cells and Ranges 69

Try It 71

LESSON 8: WORKING WITH RANGES 75

Working with Noncontiguously Populated Ranges 77

Try It 82

LESSON 9: MAKING DECISIONS WITH VBA 85

Understanding Logical Operators 85

Choosing Between This or That 88

Getting Users to Make Decisions 92

Try It 94

PART III: BEYOND THE MACRO RECORDER: WRITING YOUR OWN CODE

LESSON 10: REPEATING ACTIONS WITH LOOPS 101

What Is a Loop? 101

Nesting Loops 110

Try It 111

LESSON 11: PROGRAMMING FORMULAS 113

Understanding A1 and R1C1 References 113

Programming Your Formula Solutions with VBA 118

Try It 124

LESSON 12: WORKING WITH ARRAYS 127

What Is an Array? 127

The Option Base Statement 130

Boundaries in Arrays 132

Declaring Arrays with Fixed Elements 132

Declaring Dynamic Arrays with ReDim and Preserve 133

Try It 134

LESSON 13: AUTOMATING PROCEDURES WITH WORKSHEET EVENTS 137

What Is an Event? 137

Worksheet Events: An Overview 138

Examples of Common Worksheet Events 141

Try It 144

LESSON 14: AUTOMATING PROCEDURES WITH WORKBOOK EVENTS 149

Workbook Events: An Overview 149

Examples of Common Workbook Events 153

Try It 158

LESSON 15: HANDLING DUPLICATE ITEMS AND RECORDS 161

Deleting Rows Containing Duplicate Entries 161

Working with Duplicate Data 167

Try It 173

LESSON 16: USING EMBEDDED CONTROLS 181

Working with Form Controls and

ActiveX Controls 181

Try It 191

LESSON 17: PROGRAMMING CHARTS 199

Try It 208

LESSON 18: PROGRAMMING PIVOTTABLES AND PIVOTCHARTS 213

Creating a PivotTable Report 213

Understanding PivotCaches 226

Manipulating PivotFields in VBA 230

Manipulating PivotItems with VBA 231

Creating a PivotTables Collection 231

Try It 232

LESSON 19: USER-DEFINED FUNCTIONS 237

What Is a User-Defi ned Function? 237

UDF Examples That Solve Common Tasks 239

Volatile Functions 243

Try It 248

LESSON 20: DEBUGGING YOUR CODE 251

What Is Debugging? 251

What Causes Errors? 252

Weapons of Mass Debugging 254

Trapping Errors 264

Try It 266

PART IV: ADVANCED PROGRAMMING TECHNIQUES

LESSON 21: CREATING USERFORMS 271

What Is a UserForm? 271

Creating a UserForm 272

Designing a UserForm 273

Adding Controls to a UserForm 274

Showing a UserForm 280

Where Does the UserForm's Code Go? 281

Closing a UserForm 281

Try It 283

LESSON 22: USERFORM CONTROLS AND THEIR FUNCTIONS 285

Understanding the Frequently Used UserForm Controls 285

Try It 301

LESSON 23: ADVANCED USERFORMS 305

The UserForm Toolbar 305

Modal versus Modeless 306

Disabling the UserForm's Close Button 307

Maximizing Your UserForm's Size 308

Selecting and Displaying Photographs on a UserForm 308

Unloading a UserForm Automatically 309

Pre-sorting the ListBox and ComboBox Items 310

Populating ListBoxes and ComboBoxes with Unique Items 312

Displaying a Real-Time Chart in a UserForm 314

Try It 315

LESSON 24: CLASS MODULES 321

What Is a Class? 321

What Is a Class Module? 322

Creating Your Own Objects 323

An Important Benefi t of Class Modules 323

Creating Collections 326

Class Modules for Embedded Objects 326

Try It 330

LESSON 25: ADD-INS 335

What Is an Excel Add-In? 335

Creating an Add-In 336

Converting a File to an Add-In 341

Installing an Add-In 342

Creating a User Interface for Your Add-In 346

Closing Add-Ins 349

Removing an Add-In from the Add-Ins List 349

Try It 350

LESSON 26: MANAGING EXTERNAL DATA 353

Creating QueryTables from Web Queries 353

Creating a QueryTable for Access 356

Using Text Files to Store External Data 359

Try It 361

LESSON 27: DATA ACCESS WITH ACTIVEX DATA OBJECTS 365

Introducing ADO 365

An Introduction to Structured Query Language (SQL) 368

Try It 371

LESSON 28: IMPRESSING YOUR BOSS (OR AT LEAST YOUR FRIENDS) 373

Selecting Cells and Ranges 373

Filtering Dates 376

Setting Page Breaks for Specifi ed Areas 379

Using a Comment to Log Changes in a Cell 380

Using the Windows API with VBA 381

Scheduling Your Workbook for Suicide 382

Try It 382

PART V: INTERACTING WITH OTHER OFFICE APPLICATIONS

LESSON 29: OVERVIEW OF OFFICE AUTOMATION FROM EXCEL 391

Why Automate Another Application? 391

Understanding Offi ce Automation 392

Try It 395

LESSON 30: WORKING WITH WORD FROM EXCEL 399

Activating a Word Document 399

Creating a New Word Document 402

Copying an Excel Range to a Word Document 402

Printing a Word Document from Excel 403

Importing a Word Document to Excel 404

Try It 405

LESSON 31: WORKING WITH OUTLOOK FROM EXCEL 409

Opening Outlook 409

Composing an E-mail in Outlook from Excel 410

Putting It All Together 413

E-mailing a Single Worksheet 415

Try It 415

LESSON 32: WORKING WITH ACCESS FROM EXCEL 419

Adding a Record to an Access Table 419

Exporting an Access Table to an Excel Spreadsheet 423

Creating a New Table in Access 426

Try It 427

LESSON 33: WORKING WITH POWERPOINT FROM EXCEL 431

Creating a New PowerPoint Presentation 431

Copying a Worksheet Range to a PowerPoint Slide 432

Copying Chart Sheets to PowerPoint Slides 433

Running a PowerPoint Presentation from Excel 435

Try It 436

INDEX 441

Über Autor(innen)

Tom Urtis is an Excel Microsoft Excel MVP, developer, and programmer with 30 years of experience in business management and developing spreadsheet and database applications for companies of all sizes. He is an Excel instructor and frequent contributor to newsgroups in the Excel community.

Wrox guides are crafted to make learning programming languages and technologies easier than you think. Written by programmers for programmers, they provide a structured, tutorial format that will guide you through all the techniques involved.