Excel Macros For Dummies E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2015

Seitenzahl: 301

Beschreibung

Ready-made Excel macros that will streamline your workflow Excel Macros For Dummies helps you save time, automate,and be more productive, even with no programming experience at all.Each chapter offers practical macros that you can implement rightaway, with practical exercises that extend your knowledge and helpyou understand the mechanics at work. You'll find the mosteffective Excel macros for solving common problems, andexplanations of why and where to use each one, plus invaluableguidance and step-by-step instruction for applying themeffectively. Learn how to customize your applications to look andwork exactly the way you want them to, with simple, friendlywalk-throughs that directly apply to real-world tasks. Follow itthrough from start to finish, or quickly look up problems as theyoccur; the book's clear layout and organization makes it anirreplaceable desk reference, and all macro code is available fordownload from the companion website. Microsoft Excel is the world's leading spreadsheet application,and it supports VBA macros that allow you to customize the programand automate many common tasks. This book helps you take advantageof macros to get more done, and get it done better. * Grasp the fundamentals of VBA and macros * Work with workbooks, worksheets, and ranges * Clean data, automate reporting, and send email from Excel * Use tips and tricks that streamline your workflow If you have an Excel problem, there's a macro to solve it. Youdon't need to be a programmer, and you don't need to spend monthslearning code. Excel Macros For Dummies gives you the"recipes" you need, and the knowledge to apply themeffectively.

Leseprobe

Excel® Macros For Dummies®

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2015 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone]. For technical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number is available from the publisher.

ISBN 978-1-119-08934-6 (pbk); ISBN 978-1-119-08926-1 (ebk); ISBN 978-1-119-08935-3 (ebk)

Excel® Macros For Dummies®

Visit www.dummies.com/cheatsheet/excelmacros to view this book's cheat sheet.

Table of Contents

Cover

Introduction

About This Book

Foolish Assumptions

Icons Used In This Book

Beyond the Book

Where to Go from Here

Part I: Holy Macro Batman!

Chapter 1: Macro Fundamentals

Why Use a Macro?

Macro Recording Basics

Comparing Absolute and Relative Macro Recording

Other Macro Recording Concepts

Examples of Macros in Action

Chapter 2: Getting Cozy with Visual Basic Editor

Working in Visual Basic Editor

Working with the Project Window

Working with a Code Window

Customizing the VBA Environment

Chapter 3: The Anatomy of Macros

An Overview of the Excel Object Model

A Brief Look at Variables

Understanding Event Procedures

Error Handling in a Nutshell

Part II: Making Short Work of Workbook Tasks

Chapter 4: Working with Workbooks

Creating a Workbook from Scratch

Saving a Workbook when a Particular Cell Is Changed

Saving a Workbook before Closing

Protecting a Worksheet on Workbook Close

Unprotecting a Worksheet on Workbook Open

Opening a Workbook to a Specific Tab

Opening a Specific Workbook Defined by the User

Determining Whether a Workbook Is Already Open

Determining Whether a Workbook Exists in a Directory

Closing All Workbooks at Once

Printing All Workbooks in a Directory

Preventing the Workbook from Closing Until a Cell Is Populated

Creating a Backup of a Current Workbook with Today’s Date

Chapter 5: Working with Worksheets

Adding and Naming a New Worksheet

Deleting All but the Active Worksheet

Hiding All but the Active Worksheet

How the macro works

Unhiding All Worksheets in a Workbook

Moving Worksheets Around

Sorting Worksheets by Name

Grouping Worksheets by Color

Copying a Worksheet to a New Workbook

Creating a Workbook for Each Worksheet

Printing Specified Worksheets

Protecting All Worksheets

Unprotecting All Worksheets

Creating a Table of Contents for Your Worksheets

Zooming In and Out of a Worksheet with Double-Click

Highlighting the Active Row and Column

Part III: One-Touch Data Manipulation

Chapter 6: Feeling at Home on the Range

Selecting and Formatting a Range

Creating and Selecting Named Ranges

Enumerating through a Range of Cells

Inserting Blank Rows in a Range

Unhiding All Rows and Columns

Deleting Blank Rows

Deleting Blank Columns

Limiting Range Movement to a Particular Area

Selecting and Formatting All Formulas in a Workbook

Finding and Selecting the First Blank Row or Column

Chapter 7: Manipulating Data with Macros

Copying and Pasting a Range

Converting All Formulas in a Range to Values

Performing the Text to Columns Command on All Columns

Converting Trailing Minus Signs

Trimming Spaces from All Cells in a Range

Truncating Zip Codes to the Left Five Digits

Padding Cells with Zeros

Replacing Blanks Cells with a Value

Adding Text to the Left or Right of Your Cells

Cleaning Up Nonprinting Characters

Highlighting Duplicates in a Range of Data

Hiding All Rows Except Rows Containing Duplicate Data

Selectively Hiding AutoFilter Drop-Down Arrows

Copying Filtered Rows to a New Workbook

Displaying Filtered Columns in the Status Bar

Part IV: Macro-Charging Reports and Emails

Chapter 8: Automating Common Reporting Tasks

Refreshing All Pivot Tables in a Workbook

Creating a Pivot Table Inventory Summary

Adjusting All Pivot Data Field Titles

Setting All Data Items to Sum

Applying Number Formatting to All Data Items

Sorting All Fields in Alphabetical Order

Applying Custom Sort to Data Items

Applying Pivot Table Restrictions

Applying Pivot Field Restrictions

Automatically Deleting Pivot Table Drill-Down Sheets

Printing a Pivot Table for Each Report Filter Item

Creating a Workbook for Each Report Filter Item

Resizing All Charts on a Worksheet

Aligning a Chart to a Specific Range

Creating a Set of Disconnected Charts

Printing All Charts on a Worksheet

Chapter 9: Sending Emails from Excel

Mailing the Active Workbook as an Attachment

Mailing a Specific Range as an Attachment

Mailing a Single Sheet as an Attachment

Sending Mail with a Link to Your Workbook

Mailing All Email Addresses in Your Contact List

Saving All Attachments to a Folder

Saving Certain Attachments to a Folder

Part V: The Part of Tens

Chapter 10: Ten Handy Visual Basic Editor Tips

Applying Block Comments

Copying Multiple Lines of Code

Jumping between Modules and Procedures

Teleporting to Your Functions

Staying in the Right Procedure

Stepping through Your Code

Stepping to a Specific Code Line

Stopping Code at a Predefined Point

Seeing Beginning and Ending Variable Values

Turning Off Auto Syntax Check

Chapter 11: Ten Places to Turn for Macro Help

Letting Excel Write the Macro for You

Using the VBA Help Files

Pilfering Code from the Internet

Leveraging User Forums

Visiting Expert Blogs

Mining YouTube for Video Training

Attending Live and Online Training Classes

Learning from Microsoft Office Dev Center

Dissecting Other Excel Files in Your Organization

Asking Your Local Excel Genius

Chapter 12: Ten Ways to Speed Up Your Macros

Halting Sheet Calculations

Disabling Sheet Screen Updating

Turning Off Status Bar Updates

Telling Excel to Ignore Events

Hiding Page Breaks

Suspending Pivot Table Updates

Steering Clear of Copy and Paste

Using the With Statement

Avoiding the Select Method

Limiting Trips to the Worksheet

About the Author

Cheat Sheet

Advertisement Page

Connect with Dummies

End User License Agreement

Guide

Cover

Table of Contents

Begin Reading

Pages

i

ii

v

vi

vii

viii

ix

x

xi

xii

1

2

3

4

5

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

29

30

31

32

33

34

35

36

37

38

39

40

41

43

44

45

46

47

48

49

50

51

52

53

54

55

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

269

270

271

272

273

274

275

276

Introduction

In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.