All the Office 2010 applications allow users to create their own Visual Basic code to carry out particular actions in the Application. But why do you need to do this given that each application comes with a host of powerful features? The answer lies in how these features are used. This article summarises three different ways VBA can be used in Office 2010. These points all apply to Word 2010, Excel 2010, PowerPoint 2010, Outlook 2010, Project 2010 and Publisher 2010. Access 2010 creates VBA code a little differently and will be covered in other articles.

Repeating Actions

Firstly VBA can be used to repeat a series of actions lots of times. You can record the VBA to carry out the actions once, and then use the code to carry out the same action as many times as you like.

For example In Excel 2010 you might want to add five different functions at the bottom of every column of a twelve column table you are currently working in. You would record the VBA to carry out these steps using an example table, and then you would use the same VBA code to do this again for any opened Excel table.

In Word 2010 you might want to insert a table with three columns and five rows with a certain title and table formatting at the top of every page in a twenty page document. You would record the VBA code to do this for one page, then set the VBA to repeat for all document pages. The code could then be rerun for any Word document to carry out the same actions.

In PowerPoint you could create five new slides all at once with a different design layout for each slide. So you would do this once, saving the VBA code to carry out all these steps. Then in the future run the same code again to create another set of new slides.

In Publisher you might want to create a customised booklet with ten pages, containing different themes on add and even pages and a particular image at the top of each page. You would record the VBA code to do this, give it a name and save the code. Then later, run the code again to re-create another new publication.

In Outlook you might want to export your address list to an Excel table, add today's date, and then save table. You would record these steps once, save the VBA code, and then whenever you wanted to backup your Outlook address book to Excel, run the code again.

You could carry out all these actions using existing Office 2010 commands but it would take quite some time to complete the job. VBA lets you organise these jobs more efficiently so that they could be done by clicking a single button.

Adding features

Secondly VBA lets you add features to the application such as forms, prompts or alert messages which would appear when certain conditions are met, or could be used for example to remind the user to save the work to a particular location. Or you could In Excel 2010 create a data entry form which gives the user some data entry instructions and then enters the data in the required cells.

In Word 2010 you could create a prompt to show when a certain number of words have been entered and in PowerPoint 2010 you could create a panel with selectable items to allow a user to select slides from one or more different presentations and then add them to the current presentation.

Work between Office 2010 applications

Thirdly VBA lets you work with different Office 2010 applications at the same time. So in Excel 2010 you could copy ten charts in a spreadsheet, open Word 2010, create a new document and paste all the charts into a Word table. In Word 2010 you could use VBA to copy each paragraph of a twenty paragraph document into PowerPoint 2010 with each paragraph on a separate slide.

Creating VBA code using macro recorder

You can start creating VBA code by using the built in macro recorder available in all the Office 2010 applications. In Word, Excel, Outlook, Publisher and Project you can do this by choosing the View tab and clicking on the down arrow on the Macros button and select Record Macro, give it a name and then record the required actions. In PowerPoint you click the Macro button, give it a name, and then click on Create. Once the actions are finished click on the Stop Recording macro button at the bottom left of the status bar.

You can store macros either in the current document only or in a special file to make the macro available for all files in that application.

You can view the recorded Macro by selecting the Macro as described above, then clicking on View Macros. Then select the macro and choose Edit. The Visual Basic window opens showing the VBA code created by the macro recorder. Close the window to return to the application.

You can also open the VBA window in any Office 2010 application by choosing ALT+F11 key. If there aren't any macros previously written, the VBA window will only show the Project and Properties panels on the left of the screen. If you do have some macros already written, the VBA window will also open a Code panel showing you the VBA code for the macros.

You can copy VBA between files within an application and you can make a macro stored only in a single document, available to all documents in that application.

To summarise, VBA in Office 2010 offers many benefits and this article has focussed on three main areas of repeating actions, adding features and working between Office applications and then introduced use of the macro recorder and the VBA window. An excellent way to find out much more about VBA in Office 2010 would be to attend one of the many available instructor lead training courses.