Excel VBA Programming For Dummies. Dick Kusleika
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.
Inserting a bunch of text
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.
Automating a task you perform frequently
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.
Automating repetitive operations
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.
Creating a custom command
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.
Creating a custom button
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.
Developing new worksheet functions
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.
Creating custom add-ins for Excel
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.
Getting the Most from VBA
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.
Knowing what VBA does best
Here are some benefits of automating a task by using VBA:
Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.)
Excel performs the task much faster than you can do it manually. (Unless, of course, you’re Clark Kent.)
If you’re a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you, no matter how careful you are).
If you set up things properly, someone who doesn’t know anything about Excel can perform the task by running the macro.
You can do things in Excel that are otherwise impossible — which can make you a very popular person around the office.
For long, time-consuming tasks, you don’t have to sit in front of your computer and get bored. Excel does the work while you hang out at the water cooler.
Recognizing the disadvantages of using VBA
Using VBA can present some disadvantages (or potential disadvantages). Understanding these limitations upfront helps you avoid running down rabbit trails to nowhere. Most people use VBA to save time, not waste it!
Keep the following points in mind when deciding whether to use VBA:
Other people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand-alone program, but that isn’t possible. (And probably never will be.)
Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging (fixing errors) and, if others are using your macros, providing technical support.
VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you might discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel. For more information on the importance of Excel compatibility, see the section “Ensuring Excel Compatibility,” later in this chapter.
Understanding VBA Concepts
Just to let you know what you’re in for, here’s a quick-and-dirty summary of what VBA is all about.
You perform actions in VBA by writing (or recording) code in a VBA module. You view and edit VBA modules by using the Visual Basic Editor (VBE).
A VBA module consists of Sub procedures. A Sub procedure has nothing to do with underwater vessels or tasty sandwiches. Rather, it’s a chunk of computer code that performs some action on or with objects (discussed in a moment). The following example shows a simple Sub procedure called AddEmUp. This amazing program, when executed, displays the result of 1 plus 1: Sub AddEmUp() Sum = 1 + 1 MsgBox "The answer is " & SumEnd SubA Sub procedure that doesn’t perform properly is said to be substandard.
A VBA module can also have Function procedures. A Function procedure returns a single value. You can call it from another VBA procedure or even use it as a function in a worksheet formula. An example of a Function procedure (named AddTwo) follows. This Function accepts two numbers (called arguments) and returns the sum of those values: Function AddTwo(arg1, arg2) AddTwo = arg1 + arg2End FunctionA Function procedure that doesn’t work correctly is said to be dysfunctional.
VBA manipulates objects. Excel provides dozens and dozens of objects that