Next Level Office Skills With VBA

Are you an Office guru? Can you sprinkle reference fields through documents and pivot tables in worksheets? If so, then the next level is getting to grips with VBA.

VBA or (Visual Basic for Applications) is a programming language built-in to most Microsoft Office applications. Using VBA you can extend the capabilities of Word, Excel and others beyond the functions that you can find on the ribbon by writing simple code.

VBA is available to anyone with a desktop version of Microsoft Word, Excel and other Office applications. All you have to do is open up a new blank document or workbook and press Alt + F11. VBA doesn't require any extra software to be installed on your computer or approved by your IT department, it's there and ready to go anywhere Office is.

So what are some of the cool things you can do with VBA?

Automate Repetition

You can write code to automate operations (especially those performed in a repetitive manner). For example, if you need to add the same header to several documents every day, or ensure the same font is used for the heading of each document, you can write code in VBA to automate these tasks and get them done quickly and accurately. If you need your monthly sales data worksheet to be formatted in the same way each month you can create a macro to move everything around, bold some cells and colour others, add a chart, and do as many other operations as you'd like at the press of a button.

Create User Interfaces

You can build powerful user interfaces to take input and perform tasks. Using custom dialog boxes (these are known as UserForms in VBA) you can add as many controls as you need, such as text boxes, drop down lists and tick boxes.

Using these forms you can build multi-step wizards to guide your users through entering data correctly, or provide ways to filter and interact with the data in a spreadsheet, or even build complex, multi-form applications.

Forms in VBA

Cross-Application Communication

You can use VBA to write programs that communicate across applications. For example, using VBA you can pull Word data and charts into a PowerPoint presentation, or insert sales figures from Excel into a Word document at the press of a button.

VBA at Watsonia Publishing

We use VBA quite a bit here at Watsonia Publishing. We have literally tens of thousands of Microsoft Word documents that make up our courseware library and use VBA forms to allow authors to enter custom properties to organise them all (things like the objective for each chapter and the student files required to complete an exercise). Writing all of those topics in a consistent way would also be quite tedious without the macros we've written to align screenshots, group objects and more.

Getting Started with VBA

Getting started with VBA can be a bit daunting (as you might have seen if you pressed Alt + F11 earlier!). There are two Watsonia Publishing manuals that are perfect for the VBA beginner working with Word and Excel 2013: