Excel VBA Programming For Dummies

Take your Excel programming skills to the next level

To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools and operations for the Visual Basic Editor.

Inside, you'll find an overview of the essential elements and concepts for programming with Excel. In no time, you'll discover techniques for handling errors and exterminating bugs, working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, readers will be creating Excel applications custom fit to their unique needs!

• Fully updated for the new Excel 2019

• Step-by-step instructions for creating VBA macros to maximize productivity

• Guidance on customizing your applications so they work the way you want

• All sample programs, VBA code, and worksheets are available at dummies.com

Beginning VBA programmers rejoice! This easy-to-follow book makes it easier than ever to excel at Excel VBA!

Autorentext

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at www.datapigtechnologies.com.

Klappentext

• Extend the power of Excel with VBA Programming
• Build your own applications and Excel Add-ins

• Work smarter with VBA Automation Work smarter with Excel VBA programming! Inside, you'll find everything you need to quickly get started with VBA programming. Whether you're looking to be more productive, automate those redundant tasks, or create your own killer application, Excel VBA Programming For Dummies provides the essential elements and concepts for programming with Excel. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, you'll be creating Excel applications custom fit to your unique needs! Inside

• Take the grind out of routine data crunching tasks
• Create macros that manipulate, scrub, and shape data
• Maximize your productivity
• Make dynamically generated PivotTables and Charts
• Extend Excel's power with custom worksheet functions
• Build your own applications

Zusammenfassung
Take your Excel programming skills to the next level

To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools and operations for the Visual Basic Editor.

Inside, you'll find an overview of the essential elements and concepts for programming with Excel. In no time, you'll discover techniques for handling errors and exterminating bugs, working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, readers will be creating Excel applications custom fit to their unique needs!

• Fully updated for the new Excel 2019
• Step-by-step instructions for creating VBA macros to maximize productivity
• Guidance on customizing your applications so they work the way you want
• All sample programs, VBA code, and worksheets are available at dummies.com
  Beginning VBA programmers rejoice! This easy-to-follow book makes it easier than ever to excel at Excel VBA!

Inhalt

Introduction 1

About This Book 1

Obligatory Typographical Conventions Section 2

Check Your Security Settings 3

Foolish Assumptions 4

Icons Used in This Book 5

Sample Files Online 5

Where to Go from Here 6

Part 1: Getting Started with Excel VBA Programming 7

Chapter 1: What Is VBA? 9

Okay, So What Is VBA? 9

What Can You Do with VBA? 10

Inserting a bunch of text 11

Automating a task you perform frequently 11

Automating repetitive operations 11

Creating a custom command 11

Creating a custom button 12

Developing new worksheet functions 12

Creating custom add-ins for Excel 12

Advantages and Disadvantages of VBA 12

VBA advantages 12

VBA disadvantages 13

VBA in a Nutshell 13

Excel Compatibility 16

Chapter 2: Jumping Right In 17

First Things First 17

What You'll Be Doing 18

Taking the First Steps 18

Recording the Macro 19

Testing the Macro 21

Examining the Macro 21

Modifying the Macro 23

Saving Workbooks That Contain Macros 24

Understanding Macro Security 24

Revealing More about the NameAndTime Macro 26

Part 2: How VBA Works with Excel 29

Chapter 3: Working in the Visual Basic Editor 31

What Is the Visual Basic Editor? 31

Activating the VBE 32

Understanding VBE components 32

Working with the Project Window 34

Adding a new VBA module 35

Removing a VBA module 36

Exporting and importing objects 36

Working with a Code Window 37

Minimizing and maximizing windows 37

Creating a module 38

Getting VBA code into a module 39

Entering code directly 39

Using the macro recorder 42

Copying VBA code 45

Customizing the VBA Environment 45

Using the Editor tab 45

Using the Editor Format tab 48

Using the General tab 49

Using the Docking tab 50

Chapter 4: Introducing the Excel Object Model 51

Excel Is an Object? 52

Climbing Down the Object Hierarchy 52

Wrapping Your Mind around Collections 53

Referring to Objects 54

Navigating through the hierarchy 55

Simplifying object references 56

Diving into Object Properties and Methods 56

Object properties 58

Object methods 59

Object events 61

Finding Out More 61

Using VBA's Help system 61

Using the Object Browser 62

Automatically listing properties and methods 63

Chapter 5: VBA Sub and Function Procedures 65

Understanding Subs versus Functions 65

Looking at Sub procedures 66

Looking at Function procedures 66

Naming Subs and Functions 67

Executing Sub procedures 68

Executing the Sub procedure directly 70

Executing the procedure from the Macro dialog box 70

Executing a macro by using a shortcut key 71

Executing the procedure from a button or shape 72

Executing the procedure from another procedure 74

Executing Function procedures 75

Calling the function from a Sub procedure 75

Calling a function from a worksheet formula 76

Chapter 6: Using the Excel Macro Recorder 79

Recording Basics 80

Preparing to Record 81

Relative or Absolute? 82

Recording in absolute mode 82

Recording in relative mode 83

What Gets Recorded? 84 Recording Options 86&l...