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.
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
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:
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.
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).
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:
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.
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:
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.
Hungry for more?
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.
Again, this URL is case-sensitive.
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.