Excel Macros For Dummies
2nd Edition
Published on 21. April 2017
Book
Paperback/Softback
312 pages
978-1-119-36924-0 (ISBN)
Description
Save time and be way more productive with Microsoft Excel macros
Looking for ready-made Excel macros that will streamline your workflow? Look no further! Excel Macros For Dummies, 2nd Edition helps you save time, automate, and be more productive-even with no programming experience at all. Each chapter offers macros you can implement right away, with practical exercises that extend your knowledge and help you understand the mechanics at work. You'll find over 70 of the most effective Excel macros for solving common problems and explanations of why and where to use each one-plus invaluable guidance and step-by-step instruction for applying them effectively.
Microsoft Excel is the world's leading spreadsheet application, and it supports VBA macros that allow you to customize the program and automate many common tasks. In no time, you'll learn how to customize your applications to look and work exactly the way you want them to, with simple, friendly walk-throughs that directly apply to real-world tasks. Follow it through from start to finish, or quickly look up problems as they occur. It's the perfect desk reference for all of your Microsoft Excel macros needs!
* Immediately implement the given Excel macros
* Work with workbooks, worksheets, and ranges
* Clean data, automate reporting, and send email from Excel
* Streamline your workflow
If you have an Excel problem, there's a macro to solve it-and now, you don't need to be a programmer to customize it for your own use!.
Looking for ready-made Excel macros that will streamline your workflow? Look no further! Excel Macros For Dummies, 2nd Edition helps you save time, automate, and be more productive-even with no programming experience at all. Each chapter offers macros you can implement right away, with practical exercises that extend your knowledge and help you understand the mechanics at work. You'll find over 70 of the most effective Excel macros for solving common problems and explanations of why and where to use each one-plus invaluable guidance and step-by-step instruction for applying them effectively.
Microsoft Excel is the world's leading spreadsheet application, and it supports VBA macros that allow you to customize the program and automate many common tasks. In no time, you'll learn how to customize your applications to look and work exactly the way you want them to, with simple, friendly walk-throughs that directly apply to real-world tasks. Follow it through from start to finish, or quickly look up problems as they occur. It's the perfect desk reference for all of your Microsoft Excel macros needs!
* Immediately implement the given Excel macros
* Work with workbooks, worksheets, and ranges
* Clean data, automate reporting, and send email from Excel
* Streamline your workflow
If you have an Excel problem, there's a macro to solve it-and now, you don't need to be a programmer to customize it for your own use!.
More details
Other editions
New editions
Richard Kusleika | Michael Alexander
Excel Macros For Dummies
Book
05/2022
Wiley
€38.50
Shipment within 15-20 days
Additional editions
Michael Alexander
Excel Macros For Dummies
E-Book
02/2017
2nd Edition
Wiley
€20.99
Available for download
Michael Alexander
Excel Macros For Dummies
E-Book
02/2017
2nd Edition
Wiley
€20.99
Available for download
Previous edition
Michael Alexander
Excel Macros For Dummies
Book
06/2015
Wiley
€28.90
Article exhausted; check for reprint
Person
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.
Content
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 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