Excel VBA Programming For Dummies. Dick Kusleika

Чтение книги онлайн.

СКАЧАТЬ your desk for an actual robot. Just feed the special codes into VBA and it does the work.

      

A FEW WORDS ABOUT TERMINOLOGY

      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 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.

      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 СКАЧАТЬ