Microsoft Office Tutorials and References
In Depth Information
What VBA Can Do for You
WHAT VBA cAn do for you
Everyone reading this topic uses Excel for their own needs, such as financial budgeting, forecasting,
analyzing scientific data, creating invoices, or charting the progress of their favorite basketball team.
One thing all readers have in common is the need to automate some kind of frequently encountered
task that is either too time-consuming or too cumbersome to continue doing manually. That’s where
VBA comes in.
The good news is, utilizing VBA does not mandate that you first become a world-class professional
programmer. Many VBA commands are at your disposal and, as this topic will show you, are
relatively easy to implement and customize for your everyday purposes.
Anything you can do manually you can do with VBA, but faster and with a minimized risk of
human error. Many things that Excel does not allow you to do manually, you can do with VBA. The
following sections describe a handful of examples of what VBA can do for you.
Automating a recurring Task
If you find yourself needing to produce weekly or monthly sales and expense reports, a macro can
create them in no time lat, in a style and format you (and more importantly, your boss) will be
thrilled with. And if the source data changes later that day and you need to produce the updated
report again, no problem — just run the macro again!
Automating a repetitive Task
When faced with needing to perform the same task on every worksheet in your workbook, or in
every workbook in a particular file folder, you can create a macro to “loop” through each object
and do the deed. You learn how to repeat actions with various looping methods in Lesson 9.
running a Macro Automatically if Another Action Takes place
In some situations you’ll want a macro to run automatically, so you don’t have to worry about
remembering to run it yourself. For example, to automatically refresh a pivot table the moment
its source data changes, you can monitor those changes with VBA, assuring that your pivot table
always displays real-time results. This is called “event” programming, which is cool stuff, and is
discussed in Lessons 11 and 12.
creating your own Worksheet functions
You can create your own worksheet functions, known as “User Defined Functions,” to handle custom
calculations that Excel’s built-in functions do not provide for, or would be too complicated to use even
if such functions were available. For example, you’ll see how to add up numbers in cells that are
formatted a certain color. UDFs, as these custom functions are called, are covered in Lesson 16.
simplifying the Workbook’s look and feel for other users
When you create a workbook for others to use, there will inevitably be users who know little
to nothing about Excel, but who will still need to work in that file. You can build a customized