Dick Kusleika - Excel VBA Programming For Dummies

Staying Excel compatible

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.

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.

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 than struggle through a tedious sequence of commands, you can simply click a button and then hop on over to TikTok and kill some time while your macro does the work.

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

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 enters a list of all salespeople who work for your company.

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.

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. However, it doesn’t require a camera, and the battery never needs to be recharged.

Do you often issue the same sequence of Excel 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 sitting in the next cubicle will be really impressed.

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.

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. You’ll be surprised by how easy this is. (You can explore 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.

You might be 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. Add-ins are just like regular Excel workbooks except they don’t have any worksheets the users can see. They’re all VBA and they usually automate tasks on other workbooks.

VBA provides a ton of benefits that are tempered with a few disadvantages you’ll want to keep in mind. One advantage of VBA, for example, is that you can use it to automate almost anything you do in Excel. All you have to do is write instructions in VBA, and Excel carries out those instructions when instructed to do so. The previous section, “ Knowing What VBA Can Do,” describes some specific tasks you can accomplish by using VBA. The following sections helps you decide whether VBA is the best tool to achieve the results you want.