Excel VBA Programming For Dummies. Dick Kusleika
alt="Technicalstuff"/> The Technical Stuff icon marks information of a highly technical nature that you can normally skip over.
Beyond the Book
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 Sheet in the Search box and click on the Cheat Sheets tab.
Where to Go from Here
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 1 to 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 2 and 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
Starting Excel VBA Programming
IN THIS PART …
Get to know Visual Basic for Applications.
Work through a real-live Excel programming session.
Chapter 1
Getting to Know VBA
IN THIS CHAPTER
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. Just like a great heart surgeon has to take freshman biology, you must learn some basic concepts and terminology so that the more practical aspects you do later make sense.
Understanding VBA Basics
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 use to write programs that automate Excel, such as a program to format a spreadsheet into a monthly report. In the next section, I discuss in more detail the types of tasks you can automate with VBA.
Imagine a robot that knows all about Excel. This robot can read instructions, and it can also operate Excel quickly and accurately. When you want the robot to do something in Excel, you write 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. With VBA, you don’t need the extra chair at your desk for an actual robot. Just feed the special codes into VBA and it does the work.
Excel programming terminology can be a bit confusing. For example, VBA is a programming language, but it also serves as a macro language. In this context, a macro is a set of instructions Excel performs to imitate keystrokes and mouse actions. 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 that terminology is used in this book. But you can also call this stuff a program.
You’ll see 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 doesn’t 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.
Knowing What VBA Can Do
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
Building charts from data
Keeping lists of things such as customers’ names, students’ grades, or holiday gift ideas (a nice fruitcake would be lovely)
The list could go on and on, but you get the idea. The 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 writing and testing the program, you can execute the macro with a single command, causing Excel to automatically perform many time-consuming procedures. Rather