Mastering VBA for Microsoft Office 2013

Book Preface

Visual Basic for Applications (VBA) is a powerful tool that enables you to automate tasks in Microsoft Offi ce applications. Automating can save you and your colleagues considerable time and effort. Getting more work done in less time is usually good for your self-esteem, and it can do wonderful things for your job security and your career.

What Can I Do with VBA?

You can use VBA to automate almost any action that you can perform interactively (manually) with an Offi ce 2013 application. For example, in Word, VBA can create a document, add text to it, format it, edit it, and save it.

In Excel, you can automatically integrate data from multiple workbooks into a single workbook. PowerPoint’s VBA can create a custom presentation, including the latest data drawn from a variety of sources with no human intervention. And in Access you can create new tables, populate them with data, and send the table up to the cloud.

VBA performs actions faster, more accurately, more reliably, and far more cheaply than any human. You can specify conditions for making a decision, then let VBA make those decisions for you in the future. By adding decision-making structures and loops (repetitions) to your code, you can go far beyond the range of actions that any human user can perform and finish the job in less than a second.

Beyond automating actions you would otherwise perform manually, VBA gives you the tools to create user interfaces for your code—message boxes, input boxes, and user forms—windows containing graphical objects that you can use to create forms and custom dialog boxes to display to the user.

Using VBA, you can also create custom applications that run within the host application. For example, you could build within PowerPoint a custom application that automatically creates presentations for you.

VBA can also communicate between applications. For example, Word can’t do much in the way of mathematical calculations on sets of data: that’s Excel’s specialty. So, you can make Word start Excel running, perform some calculations, and then put the results into a Word document. Similarly, you could send graphs from Excel to PowerPoint or Outlook. You get the picture.

Because VBA provides a standard set of tools that differ only in the specializations of the host applications, once you’ve learned to use VBA in one application, you’ll be able to apply that knowledge quickly to using VBA in another application. For example, you might start by learning VBA in order to manipulate Excel and then move on to using your VBA skills with Outlook. You’ll need to learn the components particular to Outlook, because they’re different from Excel’s features, but you’ll be up to speed rapidly. It’s like shopping. Once you understand the basics, going to a hardware store differs from going to a bookstore only in the particulars.

As with any programming language, getting started with VBA involves a learning curve—but you’ll be surprised how many tools VBA provides to help you quickly learn the fundamentals.