VBA - What Is It, And Should You Learn It?

Intermediate or Advanced Office users may well be asking themselves "what next?" after they've mastered many of the more complex tools in most applications. Visual Basic is something that many people have heard of, but aren't sure exactly what it is, or if it's relevant to them. VBA is a programming language, so if you're merely looking to gain further knowledge of Office without tweaking it directly with your own macros or additions to applications, it might not be for you.

VBA is, understandably, a derivative of Visual Basic, which you can code on a standalone basis, whereas VBA can be used within a "host" application (i.e. most MS Office packages, especially Word and Excel). More advanced users will recognise the use of VBA in existing applications such as Microsoft Visio, and Visual Basic is used in some non-Microsoft products such as AutoCAD and WordPerfect. Some Office applications have their own simple programming languages, for example, WordBasic for MS Word. However, you can do a lot more with VBA: it works very well within the standard Office applications.

Think of each Office application as a template you can alter, and you can start to understand how to use VBA. Applications are broken down into objects - for example, the menu bar in Excel is an object, as is the header and footer feature in Word. Each object has properties that you can alter, from a small level (making a sentence italicised), to a large level: editing the menu bar selections to suit yourself. All VBA does, in essence, is allow you to change the properties of such objects, perhaps where the existing functions don't shortcut in the way you need. For example, if you frequently use the Verdana font in 36 point bold text, because it's the corporate "look" you use in your stationary, you can program a hotkey to immediately give the object (the text) the properties (bold, size, etc) that you want - without having to go through the different menu items individually.

Here's another example on how you can use VBA. Excel has a "weekday" function that will return each day of the week as a number (1 for Sunday, or Monday if you prefer, 2 for Tuesday and so on). However it might be more useful (especially when sharing your workbook) to have the names of the days shown, in case others don't understand the numbers referring to days. There isn't a function to do this, so you need a User-defined one, or UDF, that you can program yourself in VBA. It isn't immediately obvious how you would use VBA, but actually, all Office programs have a VBA editor already built in.

If you'd think that someone, somewhere, would have already thought up an answer to the VBA problem you have (and need some code for) - you'd be right! There are literally thousands of sites with pieces of code, user-defined functions, macros and other helpful bits and pieces to get you going with VBA. If you get more experience, you could think about adding some yourself, if you think you've found the ultimate tweak to office to make it work better. If it's useful and effective, go ahead and share it!