Excel Macros for Dummies, 2nd Edition


Product Description
Product Details

Table of Contents

Introduction 1

About This Book 2

Foolish Assumptions 2

Icons Used in This Book 3

Beyond the Book 3

Where to Go from Here 4

Part 1: Holy Macro Batman! 7 Chapter 1: Macro Fundamentals 9

Why Use a Macro? 9

Macro Recording Basics 10

Examining the macro 12

Testing the macro 13

Editing the macro 13

Comparing Absolute and Relative Macro Recording 14

Recording macros with absolute references 14

Recording macros with relative references 17

Other Macro Recording Concepts 19

Macro-enabled file extensions 19

Macro security in Excel 2010 19

Trusted locations 20

Storing macros in your Personal Macro Workbook 21

Assigning a macro to a button and other form controls 22

Placing a macro on the Quick Access Toolbar 23

Examples of Macros in Action 24

Building navigation buttons 24

Dynamically rearranging PivotTable data 25

Offering one-touch reporting options 27

Chapter 2: Getting Cozy with the Visual Basic Editor 29

Working in the Visual Basic Editor 29

The VBE menu bar 30

The VBE toolbar 31

The Project window 31

The Code window 31

The Immediate window 31

Working with the Project Window 32

Adding a new VBA module 32

Removing a VBA module 33

Working with a Code Window 34

Minimizing and maximizing windows 34

Getting VBA code into a module 35

Customizing the VBA Environment 37

The Editor tab 38

The Auto Syntax Check option 38

The Require Variable Declaration option 38

The Editor Format tab 40

The General tab 41

The Docking tab 42

Chapter 3: The Anatomy of Macros 43

A Brief Overview of the Excel Object Model 43

Understanding objects 44

Understanding collections 45

Understanding properties 45

Understanding methods 46

A Brief Look at Variables 46

The common variable types 47

Understanding Event Procedures 49

Worksheet events 49

Workbook events 51

Error Handling in a Nutshell 53

On Error GoTo SomeLabel 53

On Error Resume Next 54

On Error GoTo 0 55

Part 2: Making Short Work of Workbook Tasks 57 Chapter 4: Working with Workbooks 59

Creating a New Workbook from Scratch 60

How it works 60

How to use it 61

Saving a Workbook when a Particular Cell Is Changed 61

How it works 62

How to use it 63

Saving a Workbook before Closing 64

How it works 64

How to use it 65

Protecting a Worksheet on Workbook Close 66

How it works 66

How to use it 67

Unprotecting a Worksheet on Workbook Open 68

How it works 68

How to use it 68

Opening a Workbook to a Specific Tab 69

How it works 69

How to use it 70

Opening a Specific Workbook Defined by the User 70

How it works 71

How to use it 72

Determining Whether a Workbook Is Already Open 72

How it works 73

How to use it 75

Determining Whether a Workbook Exists in a Directory 75

How it works 76

How to use it 77

Closing All Workbooks at Once 77

How it works 77

How to use it 78

Printing All Workbooks in a Directory 78

How it works 79

How to use it 80

Preventing the Workbook from Closing Until a Cell Is Populated 80

How it works 80

How to use it 81

Creating a Backup of the Current Workbook with Today's Date 82

How it works 82

How to use it 83

Chapter 5: Working with Worksheets 85

Adding and Naming a New Worksheet 85

How it works 85

How to use it 86

Deleting All but the Active Worksheet 87

How it works 87

How to use it 88

Hiding All but the Active Worksheet 88

How it works 89

How to use it 90

Unhiding All Worksheets in a Workbook 90

How it works 90

How to use it 91

Moving Worksheets Around 92

How it works 92

How to use it 93

Sorting Worksheets by Name 93

How it works 93

How to use it 95

Grouping Worksheets by Color 95

How it works 96

How to use it 97

Copying a Worksheet to a New Workbook 98

How it works 98

How to use it 98

Creating a New Workbook for Each Worksheet 99

How it works 99

How to use it 100

Printing Specified Worksheets 101

How it works 101

How to use it 101

Protecting All Worksheets 102

How it works 102

How to use it 103

Unprotecting All Worksheets 104

How it works 104

How to use it 105

Creating a Table of Contents for Your Worksheets 106

How it works 106

How to use it 109

Zooming In and Out of a Worksheet with Double-Click 109

How it works 109

How to use it 110

Highlighting the Active Row and Column 110

How it works 111

How to use it 112

Part 3: One-Touch Data Manipulation 113 Chapter 6: Feeling at Home on the Range 115

Selecting and Formatting a Range 116

How it works 116

How to use it 117

Creating and Selecting Named Ranges 118

How it works 120

How to use it 120

Enumerating Through a Range of Cells 121

How it works 121

How to use it 122

Inserting Blank Rows in a Range 122

How it works 122

How to use it 124

Unhiding All Rows and Columns 124

How it works 124

How to use it 124

Deleting Blank Rows 125

How it works 125

How to use it 126

Deleting Blank Columns 127

How it works 127

How to use it 129

Limiting Range Movement to a Particular Area 129

How it works 129

How to use it 130

Selecting and Formatting All Formulas in a Workbook 131

How it works 131

How to use it 133

Finding and Selecting the First Blank Row or Column 133

How it works 134

How to use it 136

Chapter 7: Manipulating Data with Macros 137

Copying and Pasting a Range 138

How it works 138

How to use it 139

Converting All Formulas in a Range to Values 139

How it works 139

How to use it 141

Text to Columns on All Columns 141

How it works 142

How to use it 144

Converting Trailing Minus Signs 144

How it works 144

How to use it 146

Trimming Spaces from All Cells in a Range 147

How it works 147

How to use it 148

Truncating ZIP Codes to the Left Five 149

How it works 149

How to use it 151

Padding Cells with Zeros 151

How it works 152

How to use it 154

Replacing Blanks Cells with a Value 154

How it works 154

How to use it 156

Appending Text to the Left or Right of Your Cells 156

How it works 156

How to use it 158

Cleaning Up Non-Printing Characters 158

How it works 159

How to use it 160

Highlighting Duplicates in a Range of Data 160

How it works 161

How to use it 162

Hiding All but Rows Containing Duplicate Data 162

How it works 163

How to use it 165

Selectively Hiding AutoFilter Drop-down Arrows 165

How it works 165

How to use it 167

Copying Filtered Rows to a New Workbook 167

How it works 167

How to use it 168

Showing Filtered Columns in the Status Bar 168

How it works 169

How to use it 171

Part 4: Macro-Charging Reports and Emails 173 Chapter 8: Automating Common Reporting Tasks 175

Refreshing All PivotTables in a Workbook 176

How it works 176

How to use it 177

Creating a PivotTable Inventory Summary 177

How it works 178

How to use it 180

Adjusting All Pivot Data Field Titles 181

How it works 181

How to use it 183

Setting All Data Items to Sum 183

How it works 183

How to use it 185

Applying Number Formatting for All Data Items 185

How it works 186

How to use it 189

Sorting All Fields in Alphabetical Order 189

How it works 189

How to use it 190

Applying a Custom Sort to Data Items 191

How it works 191

How to use it 192

Applying PivotTable Restrictions 192

How it works 192

How to use it 194

Applying Pivot Field Restrictions 194

How it works 194

How to use it 196

Automatically Deleting PivotTable Drill-Down Sheets 196

How it works 196

How to use it 198

Printing a PivotTable for Each Report Filter Item 200

How it works 200

How to use it 202

Creating a New Workbook for Each Report Filter Item 202

How it works 203

How to use it 205

Resizing All Charts on a Worksheet 205

How it works 205

How to use it 207

Aligning a Chart to a Specific Range 207

How it works 207

How to use it 209

Creating a Set of Disconnected Charts 209

How it works 209

How to use it 211

Printing All Charts on a Worksheet 211

How it works 211

How to use it 212

Chapter 9: Sending Emails from Excel 213

Mailing the Active Workbook as an Attachment 214

How it works 214

How to use it 215

Mailing a Specific Range as an Attachment 216

How it works 216

How to use it 218

Mailing a Single Sheet as an Attachment 218

How it works 218

How to use it 220

Sending Mail with a Link to Your Workbook 220

How it works 220

How to use it 222

Mailing All Email Addresses in Your Contact List 222

How it works 222

How to use it 224

Saving All Attachments to a Folder 224

How it works 224

How to use it 226

Saving Certain Attachments to a Folder 227

How it works 227

How to use it 229

Chapter 10: Wrangling External Data with Macros 231

Working with External Data Connections 231

Manually creating a connection 232

Manually editing data connections 235

Using Macros to Create Dynamic Connections 236

Iterating through All Connections in a Workbook 239

Using ADO and VBA to Pull External Data 240

Understanding ADO syntax 241

Using ADO in a macro 243

Working with text files 245

Part 5: Part of Tens 251 Chapter 11: Ten Handy Visual Basic Editor Tips 253

Applying Block Comments 254

Copying Multiple Lines of Code at Once 255

Jumping between Modules and Procedures 255

Teleporting to Your Functions 256

Staying in the Right Procedure 256

Stepping through Your Code 257

Stepping to a Specific Line in Your Code 258

Stopping Your Code at a Predefined Point 258

Seeing the Beginning and End of Variable Values 259

Turning Off Auto Syntax Check 260

Chapter 12: Ten Places to Turn for Macro Help 263

Let Excel Write the Macro for You 264

Use the VBA Help Files 264

Pilfer Code from the Internet 265

Leverage User Forums 265

Visit Expert Blogs 266

Mine YouTube for Video Training 267

Attend Live and Online Training Classes 267

Learn from the Microsoft Office Dev Center 267

Dissect the Other Excel Files in your Organization 268

Ask Your Local Excel Genius 268

Chapter 13: Ten Ways to Speed Up Your Macros 269

Halt Sheet Calculations 269

Disable Sheet Screen Updating 270

Turn Off Status Bar Updates 271

Tell Excel to Ignore Events 272

Hide Page Breaks 273

Suspend PivotTable Updates 273

Steer Clear of Copy and Paste 274

Use the With Statement 275

Don't Explicitly Select Objects 276

Avoid Excessive Trips to the Worksheet 277

Index 279

About the Author

Michael Alexander is a Microsoft Certified Application Developer (MCAD) who has been named a Microsoft MVP for his ongoing contributions to the Excel community. He has more than 15 years of experience in consulting and developing Office solutions, and shares basic Access and Excel tips on his free tutorial site, www.datapigtechnologies.com.

Ask a Question About this Product More...
Write your question below:
Look for similar items by category
How Fishpond Works
Fishpond works with suppliers all over the world to bring you a huge selection of products, really great prices, and delivery included on over 25 million products that we sell. We do our best every day to make Fishpond an awesome place for customers to shop and get what they want — all at the best prices online.
Webmasters, Bloggers & Website Owners
You can earn a 8% commission by selling Excel Macros for Dummies, 2nd Edition on your website. It's easy to get started - we will give you example code. After you're set-up, your website can earn you money while you work, play or even sleep! You should start right now!
Authors / Publishers
Are you the Author or Publisher of a book? Or the manufacturer of one of the millions of products that we sell. You can improve sales and grow your revenue by submitting additional information on this title. The better the information we have about a product, the more we will sell!
Back to top