Dick Kusleika - Excel VBA Programming For Dummies

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.

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.

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 off the most important information in each chapter, just skim through these icons.

The Technical Stuff icon marks information of a highly technical nature that you can normally skip over.

The Warning icon tells you to watch out! It marks important information that may save you from losing data and ruining your whole day.

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

https://www.dummies.com/go/excelvbaprogrammingfd6e

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, experimentation is the best way to master VBA.

In addition, this book comes with a free access-anywhere Cheat Sheet that includes keyboard shortcuts related to Excel VBA programming. To get this Cheat Sheet, simply go to www.dummies.com and type VBA Excel Programming For Dummies Cheat Sheetin the Search box and click on the Cheat Sheets tab.

This book contains everything you need to learn VBA programming at a mid-advanced level. The book starts off with the basics of recording macros and builds, chapter by chapter.

If you’re completely new to Excel macros, start with Part 1to get a refresher on the fundamentals of recording macros. If you have experience recording macros, but want to better understand the VBA behind them, read to Parts 2and 3. There, you gain a concise understanding of how VBA works, along with the basic foundation you need to implement your own code.

Finally, if you’re familiar with programming concepts and just want to get a quick run-through of some of the more advanced techniques like creating your custom functions and add-ins, feel free to jump to Part 4.

Part 1

IN THIS PART …

Get to know Visual Basic for Applications.

Work through a real-live Excel programming session.

Chapter 1

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