Introduction

Greetings, prospective Excel programmer…

You no doubt have your reasons for picking up a book on VBA programming. Maybe you got a new job (congratulations). Maybe you’re trying to automate some of the repetitive data crunching tasks you have to do. Maybe you’re just a nerd at heart. Whatever the reason, thank you for choosing this book.

Inside, you find everything you need to get up and running with VBA fast. Even if you don’t have the foggiest idea of what programming is all about, this book can help. Unlike most programming books, this one is filled with information designed to include just what you need to know to quickly ramp your VBA programming skillset.

About This Book

Go to any large bookstore (in person or online), and you’ll find many Excel books. 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 recent versions of Excel.
• Just might make you crack a smile occasionally.

If you’re using an older version of Excel, this book might be okay, but some things have changed. You’d probably be better off with the preceding 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 both published by Wiley:

• Excel 2019 For Dummies, by Greg Harvey
• Excel Bible, by Michael Alexander and Dick Kusleika

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. This book doesn’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 2019 Power Programming with VBA, also published by Wiley. And yes, editions for older versions of Excel are also available.

To make the content more accessible, I divided this book into six parts:

• Part 1, Starting with Excel VBA Programming
• Part 2, Employing VBA with Excel
• Part 3, Programming Concepts
• Part 4, Communicating with Your Users
• Part 5, Putting It All Together
• Part 6, The Part of Tens

Typographical conventions

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 Ribbon tool you click.

The Visual Basic Editor still uses old-fashioned menus and toolbars. So Tools ⇒    Options means choose the Tools menu and then choose the Options menu item.

Excel programming involves developing code — that is, the instructions VBA 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).

Macro security

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 File ⇒    Options ⇒   Trust Center ⇒   Trust Center Settings. 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 VBA macros without notification. Macros will not work, regardless of what you do.
• Disable VBA 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 VBA 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 VBA 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. They call you into their office and claim that your macro doesn’t do anything at all. What's going on? Chances are, your boss’s security setting doesn’t allow macros to run. Or maybe they chose to go along with Microsoft’s default suggestion and disable the macros when they opened the file.

Bottom line? Just because an Excel workbook contains a macro does not 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 as 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. The following points more or less describe the hypothetical target reader for this book:

• You have access to a PC at work — and probably at home. And those computers are connected to the internet.
• You’re running a fairly recent version of Excel.
• 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

Throughout this book, icons in the margins highlight certain types of valuable information that call out for your attention. Here are the icons you’ll encounter and a brief description of each.

The Tip icon marks tips and shortcuts that can save you a great deal of time (and maybe even allow you to leave the office at a reasonable hour).

Remember icons mark the information that’s especially important to know. To siphon...