Sep 09 2012

The Basics of Visual Basic for Applications

Visual Basic for Applications (VBA) is the form of the Visual Basic programming language that is embedded into Microsoft Office . It is commonly used to develop macros to automate common tasks within Office applications. VBA also supports common objects such as forms, modules, class modules, and reports. This means that it can be used to design useful applications that are more complex than simple macros.

If you’re new to VBA, let’s take a quick look at the basics to help you better understand it and how it might help your business.

What is Visual Basic Programming?

Visual Basic is a high level programming language that was designed to be easier to learn and use than other languages created around the same time. Over the years it has grown to have most of the capabilities of other languages also considered mainstream.

The VBA version of Visual Basic removes some of the lower level features, but still preserves enough to be very powerful for creating applications with Microsoft Office. In other words, while Visual Basic is a programming language, VBA is a scripting offshoot of that language which is used within Microsoft Office.

What are Macros?

I mentioned that VBA can be used to develop macros. But what are they?

Macros involve a small amount of code linked to a keyboard shortcut that the user can remember instead of having to navigate the interface to perform a set of steps. For example in a database application I worked on, we needed to allow users to search for an order number easily. So we wrote a function that would pop up a box to allow a user to type in an order number to search for, and then bring up that order. We assigned it to Control+Shift+O. Normally macros are appropriate for short, simple actions.

Moving Beyond Macros

For a business that already owns Microsoft Office, VBA’s more advanced capabilities mean that internal tools can be developed without purchasing other software.

These tools can be compiled –meaning employees can’t access the underlying code. Or they can remain uncompiled and Office will compile the code on demand. That second option allows a developer to watch the code as its being run for easy debugging and customization.

VBA allows for development of code that is far more powerful and allows for more detailed control than a simple macro can handle, and will allow the development of more complex tools.The use of VBA is also more maintainable.  Imagine trying to go through a hundred macros to change a behavior.

Another benefit is that if you’re using MS Access, a macro can only manipulate whole sets of data at once, but VBA’s finer level of control allows you to manipulate one record at a time.

Is Professional VBA Development Right for You?

I have seen simple data entry software written using VBA and Microsoft Access and I’ve seen immense and complicated enterprise level applications written in VBA being used to run very successful businesses, such as Order Management software. So it can be useful to businesses who needs custom data entry or more complex data analysis and business logic who already owns Microsoft Office or was thinking about buying Microsoft Office.

When deciding whether to use VBA, consider the following questions:

  • How complex is what I want to accomplish?
  • Has what I want to do already been done in Office?
  • Do I need a fine control of detail?
  • Do I want error messages to be more understandable than what Office provides?
  • If working with MS Access, do I need to only work with one record at a time?

What kinds of applications have you implemented in VBA? What made you decide to use VBA for your project? What challenges did you encounter? Leave a comment!