VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company run by the richest man in the world. Excel, along with the other members of Microsoft Office 2003, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people use to develop programs that control Excel.
Don't confuse VBA with VB (which stands for Visual Basic). VB is a programming language that lets you create standalone executable programs (those EXE files). Although VBA and VB have a lot in common, they are different animals.
You're probably aware that people use Excel for thousands of different tasks. Here are just a few examples:
- Keeping lists of things, such as customer names, students' grades, or holiday gift ideas
- Budgeting and forecasting
- Analyzing scientific data
- Creating invoices and other forms
- Developing charts from datatabmark
The list could go on and on, but you get the idea. Excel is used for a wide variety of things, and everyone reading this article 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 is what VBA is all about.
For example, you might create a VBA program to format and print your month-end sales report. After developing 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 grab a cup of joe and let your computer do the work — which is how it's supposed to be, right?
Here are some brief descriptions of some common uses for VBA macros. One or two of these may push your button.
Inserting a text string
If you often need to enter your company name into 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 types a list of all salespeople who work for your company.
Automating a task you perform frequently
Assume you're a sales manager and 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 sound on a tape recorder. But it doesn't require a microphone.
Creating a custom command
Do you often issue the same sequence of Excel menu 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.
Creating a custom toolbar button
You can customize the Excel toolbars with your own buttons that execute the macros you write. Office workers tend to be very impressed by this sort of thing.
Creating a custom menu command
You can also customize Excel's menus with your own commands that execute macros you write. Office workers are even more impressed by this.
Creating a simplified front end
In almost any office, you can find lots of people who don't really understand how to use computers. (Sound familiar?) Using VBA, you can make it easy for these inexperienced users to perform some useful work. For example, you can set up a foolproof data-entry template so you don't have to waste your time doing mundane work.
Developing new worksheet functions
Although Excel includes numerous 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. Even better, the Insert Function dialog box displays your custom functions, making them appear built in. Very snazzy stuff.
Creating complete, macro-driven applications
If you're willing to spend some time, you can use VBA to create large-scale applications complete with custom dialog boxes, onscreen help, and lots of other accoutrements.
Creating custom add-ins for Excel
You're probably 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.