Excel VBA Programming For Dummies E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 467

Veröffentlichungsjahr: 2015

Beschreibung

Take your data analysis and Excel programming skills to new heights In order to take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). This 4th edition of 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 get the lowdown on the essential elements and concepts for programming with Excel, discover techniques for handling errors and exterminating bugs, working with range objects, controlling program flow, and much more. With the release of Microsoft Office 2016, Excel will see changes in its operating system, and this fun, hands-on guide will make it easier than ever to harness the power of Visual Basic for Applications and create custom applications and macros on the world's most popular spreadsheet tool. Packed 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 in no time at all! * Provides step-by-step instructions for creating VBA macros to maximize productivity * Helps to increase efficiency by demonstrating how to customize your applications so they look and work the way you want * Fully updated for Excel 2016 * All sample programs, VBA code, and worksheets are available at dummies.com If you're a beginning to intermediate VBA programmer looking to get up to speed on creating customized solutions with Excel applications, Excel VBA Programming For Dummies, 4th Edition makes it easier.

Leseprobe

Excel® VBA Programming For Dummies®, 4th Edition

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

Media and software compilation copyright © 2015 by John Wiley & Sons, Inc. All rights reserved.

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. Excel is a registered trademark 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. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.

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: 2015950121

ISBN 978-1-119-07739-8 (pbk); ISBN 978-1-119-07741-1 (ebk); ISBN 978-1-119-07744-2 (ebk)

Excel® VBA Programming For Dummies®

Visit http://dummies.com/extras/excelvbaprogramming to view this book's cheat sheet.

Table of Contents

Cover

Introduction

About this Book

Obligatory Typographical Conventions Section

Check Your Security Settings

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part I: Getting Started with Excel VBA Programming

Chapter 1: What Is VBA?

Okay, So What Is VBA?

What Can You Do with VBA?

Advantages and Disadvantages of VBA

VBA in a Nutshell

An Excursion into Versions

Chapter 2: Jumping Right In

First Things First

What You’ll Be Doing

Taking the First Steps

Recording the Macro

Testing the Macro

Examining the Macro

Modifying the Macro

Saving Workbooks That Contain Macros

Understanding Macro Security

Revealing More about the NameAndTime Macro

Part II: How VBA Works with Excel

Chapter 3: Working in the Visual Basic Editor

What Is the Visual Basic Editor?

Working with the Project Window

Working with a Code Window

Customizing the VBA Environment

Chapter 4: Introducing the Excel Object Model

Excel Is an Object?

Climbing Down the Object Hierarchy

Wrapping Your Mind around Collections

Referring to Objects

Diving into Object Properties and Methods

Finding Out More

Chapter 5: VBA Sub and Function Procedures

Understanding Subs versus Functions

Naming Subs and Functions

Chapter 6: Using the Excel Macro Recorder

Is It Live, or Is It VBA?

Recording Basics

Preparing to Record

Relative or Absolute?

What Gets Recorded?

Recording Options

Is This Thing Efficient?

Part III: Programming Concepts

Chapter 7: Essential VBA Language Elements

Using Comments in Your VBA Code

Using Variables, Constants, and Data Types

Using Assignment Statements

Working with Arrays

Using Labels

Chapter 8: Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

Some Useful Range Object Properties

Some Useful Range Object Methods

Chapter 9: Using VBA and Worksheet Functions

What Is a Function?

Using Built-In VBA Functions

Using Worksheet Functions in VBA

Using Custom Functions

Chapter 10: Controlling Program Flow and Making Decisions

Going with the Flow, Dude

The GoTo Statement

Decisions, Decisions

Knocking Your Code for a Loop

Using For Each-Next Loops with Collections

Chapter 11: Automatic Procedures and Events

Preparing for the Big Event

Where Does the VBA Code Go?

Writing an Event-Handler Procedure

Introductory Examples

Examples of Activation Events

Other Worksheet-Related Events

Events Not Associated with Objects

Chapter 12: Error-Handling Techniques

Types of Errors

An Erroneous Example

Handling Errors Another Way

Handling Errors: The Details

An Intentional Error

Chapter 13: Bug Extermination Techniques

Species of Bugs

Identifying Bugs

Debugging Techniques

About the Debugger

Bug Reduction Tips

Chapter 14: VBA Programming Examples

Working with Ranges

Changing Excel Settings

Working with Charts

VBA Speed Tips

Part IV: Communicating with Your Users

Chapter 15: Simple Dialog Boxes

UserForm Alternatives

The MsgBox Function

The InputBox Function

The GetOpenFilename Method

The GetSaveAsFilename Method

Getting a Folder Name

Displaying Excel’s Built-in Dialog Boxes

Chapter 16: UserForm Basics

Knowing When to Use a UserForm

Creating UserForms: An Overview

Working with UserForms

A UserForm Example

Chapter 17: Using UserForm Controls

Getting Started with Dialog Box Controls

Dialog Box Controls: The Details

Working with Dialog Box Controls

Dialog Box Aesthetics

Chapter 18: UserForm Techniques and Tricks

Using Dialog Boxes

A UserForm Example

More UserForm Examples

A Dialog Box Checklist

Chapter 19: Accessing Your Macros through the User Interface

Customizing the Ribbon

Customizing Shortcut Menus

Part V: Putting It All Together

Chapter 20: Creating Worksheet Functions — and Living to Tell about It

Why Create Custom Functions?

Understanding VBA Function Basics

Writing Functions

Working with Function Arguments

Introducing Wrapper Functions

Working with Functions That Return an Array

Using the Insert Function Dialog Box

Chapter 21: Creating Excel Add-Ins

Okay … So What’s an Add-In?

Why Create Add-Ins?

Working with Add-Ins

Understanding Add-In Basics

Looking at an Add-In Example

Part VI: The Part of Tens

Chapter 22: Ten VBA Questions (and Answers)

Chapter 23: (Almost) Ten Excel Resources

The VBA Help System

Microsoft Product Support

Websites

Excel Blogs

Google

Bing

Local User Groups

My Other Books

Chapter 24: Ten VBA Do’s and Don’ts

Do Declare All Variables

Don’t Confuse Passwords with Security

Do Clean Up Your Code

Don’t Put Everything in One Procedure

Do Consider Other Software

Don’t Assume That Everyone Enables Macros

Do Get in the Habit of Experimenting

Don’t Assume That Your Code Will Work with Other Excel Versions

Do Keep Your Users in Mind

Don’t Forget About Backups

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

xiii

xiv

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

65

66

67

68

69

70

71

72

73

74

75

76

77

79

80

81

82

83

84

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

131

132

133

134

135

136

137

138

139

140

141

142

143

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

172

173

174

175

176

177

178

179

180

181

182

183

184

185

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

212

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

254

255

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

371

372

373

375

376

377

378

379

392

Introduction

Greetings, prospective Excel programmer… .

Thanks for buying my book. I think you’ll find that it offers a fast, enjoyable way to discover the ins and outs of Microsoft Excel programming. Even if you don’t have the foggiest idea of what programming is all about, this book can help you make Excel jump through hoops in no time. (Well, it will take some time.)

Unlike most programming books, this one is written in plain English, and even normal people can understand it. Even better, it’s filled with information of the “just the facts, ma’am” variety — not the drivel you might need once every third lifetime.

About this Book

Go to any large bookstore (in-person or online), and you’ll find many Excel books (far too many, as far as I’m concerned). A quick overview can help you decide whether this book is really right for you. This book

Is designed for intermediate to advanced Excel users who want to get up to speed with Visual Basic for Applications (VBA) programming.

Requires no previous programming experience.

Covers the most commonly used commands.

Is appropriate for Excel 2013 or Excel 2016.

Just might make you crack a smile occasionally.

If you’re using Excel 2000, XP, or 2003, this book is not for you. If you’re using Excel 2007 or 2010, it might be okay, but some things have changed. You'd probably be better off with the previous edition.

Oh, yeah — this is not an introductory Excel book. If you’re looking for a general-purpose Excel book, check out either of the following books, which are all published by Wiley:

Excel 2016 For Dummies,

by Greg Harvey

Excel 2016 Bible,

by John Walkenbach (yep, that’s me)

These books are also available in editions for earlier versions of Excel.

Notice that the title of this book isn’t The Complete Guide to Excel VBA Programming For Dummies. I don’t cover all aspects of Excel programming — but then again, you probably don’t want to know everything about this topic. If you consume this book and find that you’re hungry for a more comprehensive Excel programming book, you might try Microsoft Excel 2016 Power Programming with VBA, by John Walkenbach, also published by Wiley. And yes, editions for older versions of Excel are also available.

Obligatory Typographical Conventions Section

All computer books have a section like this. (I think some federal law requires it.) Read it or skip it.

Sometimes, I refer to key combinations — which means you hold down one key while you press another. For example, Ctrl+Z means you hold down the Ctrl key while you press Z.

For menu commands, I use a distinctive character to separate items on the Ribbon or menu. For example, you use the following command to create a named range in a worksheet:

Formulas  ⇒  Defined Names  ⇒  Define Name

Formulas is the tab at the top of the Ribbon, Defined Names is the Ribbon group, and Define Name is the actual command.

The Visual Basic editor still uses old-fashioned menus and toolbars. So I might tell you to choose Tools  ⇒  Options. That means choose the Tools menu and then choose the Options menu item.

Excel programming involves developing code — that is, the instructions Excel follows. All code in this book appears in a monospace font, like this:

Range("A1:A12").Select

Some long lines of code don’t fit between the margins in this book. In such cases, I use the standard VBA line-continuation character sequence: a space followed by an underscore character. Here’s an example:

Selection.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False

When you enter this code, you can type it as written or place it on a single line (omitting the space and underscore combination).

Check Your Security Settings

It's a cruel world out there. It seems that some scam artist is always trying to take advantage of you or cause some type of problem. The world of computing is equally cruel. You probably know about computer viruses, which can cause some nasty things to happen to your system. But did you know that computer viruses can also reside in an Excel file? It's true. In fact, it’s relatively easy to write a computer virus by using VBA. An unknowing user can open an Excel file and spread the virus to other Excel workbooks and to other systems.

Over the years, Microsoft has become increasingly concerned about security issues. This is a good thing, but it also means that Excel users need to understand how things work. You can check Excel's security settings by choosing the File  ⇒  Options  ⇒  Trust Center  ⇒  Trust Center Settings command. There is a plethora of options in there, and people have been known to open that dialog box and never be heard from again.

If you click the Macro Settings tab (on the left side of the Trust Center dialog box), your options are as follows:

Disable all macros without notification.

Macros will not work, regardless of what you do.

Disable all macros with notification.

When you open a workbook with macros, you see the Message Bar open with an option you can click to enable macros, or (if the Visual Basic Editor window is open) you get a message asking if you want to enable macros.

Disable all macros except digitally signed macros.

Only macros with a digital signature are allowed to run (but even for those signatures you haven’t marked as trusted, you still get the security warning).

Enable all macros.

All macros run with no warnings. This option is not recommended because potentially dangerous code can be executed.

Consider this scenario: You spend a week writing a killer VBA program that will revolutionize your company. You test it thoroughly and then send it to your boss. He calls you into his office and claims that your macro doesn't do anything at all. What's going on? Chances are, your boss's security setting does not allow macros to run. Or maybe he chose to go along with Microsoft's default suggestion and disable the macros when he opened the file.

Bottom line? Just because an Excel workbook contains a macro, it is no guarantee that the macro will ever be executed. It all depends on the security setting and whether the user chooses to enable or disable macros for that file.

To work with this book, you need to enable macros for the files you work with. My advice is to use the second security level. Then, when you open a file that you've created, you can simply enable the macros. If you open a file from someone you don't know, you should disable the macros and check the VBA code to ensure that it doesn't contain anything destructive or malicious. Usually, it's pretty easy to identify suspicious VBA code.

Another option is to designate a trusted folder. Choose File  ⇒  Options  ⇒  Trust Center  ⇒  Trust Center Settings. Select the Trusted Locations option and then designate a particular folder to be a trusted location. Store your trusted workbooks there, and Excel won't bug you about enabling macros. For example, if you download the sample files for this book, you can put them in a trusted location.

Foolish Assumptions

People who write books usually have a target reader in mind. For this book, my target reader is a conglomerate of dozens of Excel users I’ve met over the years (either in person or out in cyberspace). The following points more or less describe my hypothetical target reader:

You have access to a PC at work — and probably at home. And those computers are connected to the Internet.

You’re running Excel 2013 or Excel 2016.

You’ve been using computers for several years.

You use Excel frequently in your work, and you consider yourself to be more knowledgeable about Excel than the average bear.

You need to make Excel do some things that you currently can’t make it do.

You have little or no programming experience.

You understand that the Help system in Excel can actually be useful. Face it — this book doesn't cover everything. If you get on good speaking terms with the Help system, you'll be able to fill in some of the missing pieces.

You need to accomplish some work, and you have a low tolerance for thick, boring computer books.

Icons Used in This Book

Don’t skip information marked with this icon. It identifies a shortcut that can save you lots of time (and maybe even allow you to leave the office at a reasonable hour).

This icon is also used to let you know that the code being discussed is available on the web. Download it to eliminate lots of typing. See “Beyond the Book” for more information.

This icon tells you when you need to store information in the deep recesses of your brain for later use.

This icon flags material that you might consider technical. You may find it interesting, but you can safely skip it if you’re in a hurry.

Read anything marked with this icon. Otherwise, you may lose your data, blow up your computer, cause a nuclear meltdown — or maybe even ruin your whole day.

Beyond the Book

Hungry for more?

Sample files:

This book has its very own website where you can download the example files. To get these files, point your web browser to

http://dummies.com/extras/excelvbaprogramming

Please note that this URL is case-sensitive and uses all lowercase letters. If you don’t type it exactly, it won’t work.

Having the sample files will save you a lot of typing. Better yet, you can play around with them and experiment with various changes. In fact, I highly recommend playing around with these files. Experimentation is the best way to master VBA.

Cheat Sheet:

The Cheat Sheet is a handy list of common VBA statements for Excel programming. You can find it at

http://dummies.com/extras/excelvbaprogramming

Updates:

Occasionally, we have updates to our technology books. If this book does have technical updates, they will be posted at

http://dummies.com/extras/excelvbaprogramming

Again, this URL is case-sensitive.

Where to Go from Here

Reading this introduction was your first step. Now it’s time to move on and become a programmer. (There’s that p word again!)

If you’re a programming virgin, I strongly suggest that you start with Chapter 1 and progress through the book until you’ve discovered enough to do what you want to do. Chapter 2 gives you some immediate hands-on experience, so you have the illusion that you’re making quick progress.

But it’s a free country (at least, it was when I wrote these words); I won’t sic the Computer Book Police on you if you opt to thumb through randomly and read whatever strikes your fancy.

I hope you have as much fun reading this book as I did writing it.

Part I

Getting Started with Excel VBA Programming

Visit www.dummies.com to explore more great Dummies content online.

In this part …

Get to know Visual Basic for Applications.

See examples of some of the things you can do with VBA.

Travel back in time and see what Excel was like in the olden days.

Work through a real-live Excel programming session.

Get a handle on how Excel deals with macro security.

Chapter 1

What Is VBA?

In This Chapter

Getting a conceptual overview of VBA

Finding out what you can do with VBA

Discovering the advantages and disadvantages of using VBA

Getting the lowdown on what VBA is

Taking a mini lesson on the history of Excel

If you're eager to jump into VBA programming, hold your horses. This chapter is completely devoid of any hands-on training material. It does, however, contain some essential background information that assists you in becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the overall scheme of the universe. It's not as boring as you might think, so please try to resist the urge to jump to Chapter 2.

Okay, So What Is VBA?

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company that tries to get you to buy a new version of Windows every few years. Excel, along with the other members of Microsoft Office, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people like you and me use to develop programs that control Excel.

Imagine an intelligent robot that knows all about Excel. This robot can read instructions, and it can also operate Excel very fast and accurately. When you want the robot to do something in Excel, you write up a set of robot instructions by using special codes. Then you tell the robot to follow your instructions while you sit back and drink a glass of lemonade. That’s kind of what VBA is all about — a code language for robots. Note, however, that Excel does not come with a robot or lemonade.

A few words about terminology

Excel programming terminology can be a bit confusing. For example, VBA is a programming language, but it also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro, or is it a program? Excel’s Help system often refers to VBA procedures as macros, so I use that terminology. But I also call this stuff a program.

I use the term automate throughout this book. This term means that a series of steps are completed automatically. For example, if you write a macro that adds color to some cells, prints the worksheet, and then removes the color, you have automated those three steps.

By the way, macro does not stand for Messy And Confusing Repeated Operation. Rather, it comes from the Greek makros, which means large — which also describes your paycheck after you become an expert macro programmer.

What Can You Do with VBA?

You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:

Analyzing scientific data

Budgeting and forecasting

Creating invoices and other forms

Developing charts from data

Keeping lists of things such as customers’ names, students’ grades, or holiday gift ideas (a nice fruitcake would be lovely)

Yadda, yadda, yadda

The list could go on and on, but I think you get the idea. My point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing virtually every reader has in common is the need to automate some aspect of Excel. That, dear reader, is what VBA is all about.

For example, you might create a VBA program to import some numbers and then format and print your month-end sales report. After developing and testing the program, you can execute the macro with a single command, causing Excel to automatically perform many time-consuming procedures. Rather than struggle through a tedious sequence of commands, you can click a button and then hop on over to Facebook and kill some time while your macro does the work.

In the following sections, I briefly describe some common uses for VBA macros. One or two of these may push your button.

Inserting a bunch of text

If you often need to enter your company name, address, and phone number in your worksheets, you can create a macro to do the typing for you. You can extend this concept as far as you like. For example, you might develop a macro that automatically types a list of all salespeople who work for your company.

Automating a task you perform frequently

Assume you’re a sales manager and you need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you can develop a VBA program to do it for you. Your boss will be impressed by the consistently high quality of your reports, and you’ll be promoted to a new job for which you are highly unqualified.

Automating repetitive operations

If you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks. The nice thing about this is that Excel never complains about being bored. Excel’s macro recorder is similar to recording live action on a video recorder. But it doesn’t require a camera, and the battery never needs to be recharged.

Creating a custom command

Do you often issue the same sequence of Excel menu commands? If so, save yourself a few seconds by developing a macro that combines these commands into a single custom command, which you can execute with a single keystroke or button click. You probably won't save that much time, but you'll probably be more accurate. And the guy in the next cubicle will be really impressed.

Creating a custom button

You can customize your Quick Access toolbar with your own buttons that execute the macros you write. Office workers tend to be very impressed by buttons that perform magic. And if you really want to impress your fellow employees, you can even add new buttons to the Ribbon.

Developing new worksheet functions

Although Excel includes hundreds of built-in functions (such as SUM and AVERAGE), you can create custom worksheet functions that can greatly simplify your formulas. I guarantee you’ll be surprised by how easy this is. (I show you how to do this in Chapter 20.) Even better, the Insert Function dialog box displays your custom functions, making them appear built-in. Very snazzy stuff.

Creating custom add-ins for Excel

You’re probably familiar with some of the add-ins that ship with Excel. For example, the Analysis ToolPak is a popular add-in. You can use VBA to develop your own special-purpose add-ins. I developed my Power Utility Pak add-in by using only VBA, and people all around the world pay me real money so they can use it.

Creating complete, macro-driven applications

If you’re willing to spend some time, you can use VBA to create large-scale applications complete with a custom Ribbon tab, dialog boxes, onscreen help, and lots of other accoutrements. This book doesn't go quite that far, but I'm just telling you this to impress you with how powerful VBA really is.

Advantages and Disadvantages of VBA

In this section, I briefly describe the good things about VBA — and I also explore its darker side.

VBA advantages

You can automate almost anything you do in Excel. To do so, you write instructions that Excel carries out. Automating a task by using VBA offers several advantages:

Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.)

Excel performs the task much faster than you can do it manually (unless, of course, you’re Clark Kent).

If you're a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you or me).

If you set things up properly, someone who doesn’t know anything about Excel can perform the task by running the macro.

You can do things in Excel that are otherwise impossible — which can make you a very popular person around the office.

For long, time-consuming tasks, you don't have to sit in front of your computer and get bored. Excel does the work while you hang out at the water cooler.

VBA disadvantages

It’s only fair that I give equal time to listing the disadvantages (or potential disadvantages) of VBA:

You have to know how to write programs in VBA (but that’s why you bought this book, right?). Fortunately, it’s not as difficult as you might expect.

Other people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand-alone program, but that isn’t possible (and probably never will be).

Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging and, if others are using your macros, technical support.

VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel.

An Excursion into Versions

If you plan to develop VBA macros, you should have some understanding of Excel’s history. I know you weren’t expecting a history lesson when you picked up this book, but bear with me. This is important stuff that might make you a hit at the next nerd party.

Here are all the major Excel for Windows versions that have seen the light of day, along with a few words about how they handle macros:

Excel 2:

The original version of Excel for Windows was called Version 2 (rather than 1) so that it would correspond to the Macintosh version. Excel 2 first appeared in 1987, but nobody uses it anymore, so you can pretty much forget that it ever existed.

Excel 3:

Released in late 1990, this version features the XLM macro language. Nobody uses this version, either.

Excel 4:

This version hit the streets in early 1992. It also uses the XLM macro language. Perhaps 10 to12 people still use this version. (They subscribe to the philosophy

If it ain’t broke, don’t fix it

.)

Excel 5:

This one came out in early 1994. It was the first version to use VBA (but it also supports XLM). I haven't heard from anyone in years who uses Excel 5.

Excel 95:

Technically known as Excel 7 (there is no Excel 6), this version began shipping in the summer of 1995. It has a few VBA enhancements, and it supports the XLM language. It's rarely used anymore.

Excel 97:

This version (also known as Excel 8) was born in January 1997. It has

many

enhancements, and it features an entirely new interface for programming VBA macros. Excel 97 also uses a new file format (which previous Excel versions cannot open). Occasionally, I run into someone who still uses this version.

Excel 2000:

This version’s numbering scheme jumped to four digits. Excel 2000 (also known as Excel 9) made its public debut in June 1999. It includes only a few enhancements from a programmer’s perspective. Excel 2000 is also rarely used.

Excel 2002:

This version (also known as Excel 10 or Excel XP) appeared in late 2001. Perhaps this version’s most significant feature is the ability to recover your work when Excel crashes. People still use it.

Excel 2003:

Of all the Excel upgrades I've ever seen (and I’ve seen them all), Excel 2003 has the fewest new features. In other words, most hard-core Excel users (including yours truly) were very disappointed with Excel 2003. As I write this, Excel 2003 is still a commonly used version. It's also the last “pre-Ribbon” version of Excel.

Excel 2007:

Excel 2007 signaled the beginning of a new era. Excel 2007 dumped the old menu and toolbar interface and introduced the Ribbon. I, for one, was disappointed to discover that you can't modify the Ribbon by using VBA. But it had enough new features to satisfy me, such as a new file format and support for much larger worksheets — more than a million rows.

Excel 2010:

Microsoft outdid its corporate self with this version. This version has some slick new features (such as sparkline graphics), and it also performs quite a bit better in some areas. And if you need really, really huge workbooks, you can install the 64-bit version. But again, I was disappointed because there's still no way to modify the Ribbon using VBA.

Excel 2013:

This was the first version of Excel to feature a single document interface (SDI). That means each workbook has its own window. Excel 2013 introduced a few other features, such as Flash Fill, and a slew of new worksheet functions. I was surprised to discover that you

still

can't modify the Ribbon using VBA.

Excel 2016:

The latest version is the one I used while I was writing this edition of the book. It has quite a few new features, including Power Query and several new chart types. Excel 2016 is also available in an online version and for mobile devices, but these versions do not support VBA. The Ribbon is still around — but you

still

can't modify it using VBA!

This book is written for the desktop versions of Excel 2013 and Excel 2016. If you don't have one of those versions, you run the risk of getting confused in a few places.

So what’s the point of this mini history lesson? If you plan to distribute your Excel/VBA files to other users, it’s vitally important that you understand which versions of Excel they use. People using older versions won’t be able to take advantage of features introduced in later versions. For example, if you write VBA code that references cell XFD1048576 (the last cell in a workbook), those who use a version prior to Excel 2007 will get an error because those pre-Excel 2007 worksheets had only 65,536 rows and 255 columns (the last cell is IV65536).

Excel 2010 and later also have some new objects, methods, and properties. If you use these in your code, users with an older version of Excel will get an error when they run your macro — and you'll get the blame.

The good news is that Microsoft has made available an Office Compatibility Pack, which allows users of Excel 2003 and Excel XP to open and save workbooks in the new file format. This product (which is free, by the way) doesn't give these older versions the new features. It just lets them open and save files in the newer file format.

Chapter 2

Jumping Right In

In This Chapter

Developing a useful VBA macro: A hands-on, step-by-step example

Recording your actions by using Excel's macro recorder

Examining and testing recorded code

Changing a recorded macro

Dealing with macro security issues

I’m not much of a swimmer, but I have found that the best way to get into a cold body of water is to jump right in — no sense prolonging the agony. By wading through this chapter, you can get your feet wet immediately but avoid getting in over your head.

By the time you reach the end of this chapter, you may start feeling better about this Excel programming business, and you’ll be glad you took the plunge. This chapter provides a step-by-step demonstration of how to develop a simple but useful VBA macro.

First Things First

Before you can call yourself an Excel programmer, you must go through the initiation rites. That means you need to make a small change so Excel will display a new tab at the top of the screen: Developer. Getting Excel to display the Developer tab is easy (and you only have to do it one time). Just follow these steps:

Right-click any part of the Ribbon and choose Customize the Ribbon from the shortcut menu.

In the Customize Ribbon tab of the Excel Options dialog box, locate Developer in the second column.

Put a check mark next to Developer.

Click OK.

You're back to Excel with a brand-new tab: Developer.

When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that's you!). Figure 2-1 shows how the Ribbon looks when the Developer tab is selected in Excel 2016.

Figure 2-1: The Developer tab is normally hidden, but it's easy to unhide.

What You’ll Be Doing

In this section, I describe how to create your first macro. The macro that you’re about to create does this:

Types your name in a cell

Enters the current date and time in the cell below

Formats both cells to display bold

Changes the font size of both cells to 16 point

This macro won't be winning any prizes in the Annual VBA Programmer's Competition, but everyone must start somewhere. The macro accomplishes all these steps in a single action. As I describe in the following sections, you start by recording your actions as you go through these steps. Then you test the macro to see whether it works. Finally, you edit the macro to add some finishing touches. Ready?

Taking the First Steps

This section describes the steps you take prior to recording the macro. In other words, you need to make a few preparations before the fun begins:

Start Excel, if it's not already running.

If necessary, create a new, empty workbook.

Pressing Ctrl+N is my favorite way to do that.

Click the Developer tab, and take a look at the Use Relative References button in the Code group.

If the color of that button is different from the other buttons, you're in good shape. If the Use Relative References button is the same color as the other buttons, you need to click it to enable this option.

I explain more about the Use Relative References button in Chapter 6. For now, just make sure that the option is turned on. When it’s turned on, the Use Relative References button will be a different color.

Recording the Macro

Here comes the hands-on part. Follow these instructions carefully:

Select a cell.

Any cell will do.

Choose Developer  ⇒  Code  ⇒  Record Macro or click the macro recording button on the status bar.

The Record Macro dialog box appears, as shown in Figure 2-2.

Enter a name for the macro.

Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.

Click the Shortcut Key box, and enter Shift+N (for an uppercase N) as the shortcut key.

Specifying a shortcut key is optional. If you do specify one, you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N.