Introduction

Visual Basic for Applications (VBA) is a programming language that exists in all the major Microsoft applications (Excel, Word, PowerPoint, Visio, Access etc.). If you have Microsoft Excel then you already have Excel VBA – there’s no extra cost involved. It allows the user to write instructions ‘behind-the-scenes’ to control the applications. It’s incredibly versatile and gives the user more power and flexibility to work more effectively and efficiently.

Programs written in VBA can be shared among users, customised and improved. Typically they are used to automate laborious tasks, save time and achieve greater accuracy than if done manually. If you find yourself doing the same tedious operations in Excel on a regular basis then VBA them – it’ll do them dramatically quicker and thus give you back your time to do the more important jobs around the office.

If you are already using Macros in Excel or Word then you’re already using VBA except that the application is writing the code for you. (It records what you do in the form of a VBA program so that it can replicate your actions). Macros are fine but they only do exactly what they recorded you doing – writing your own program means you have total control and flexibility.

Some of the things you can do with VBA (Excel):

Write your own Worksheet functions
Although there are many functions available in Excel, often a company needs custom functions that match their specific needs. Whereas in the past you may have used 2 or 3 functions to get the result you needed you can use VBA to write a single function.

Create more powerful functions
In Excel you can apply up to 3 different conditional formats (change the cell appearance depending on it’s value). In VBA you can create as many conditional formats as you want or even change the appearance of one cell based on the value of one or more other cells.

Control other Applications
VBA written in one application can control another. For example, Excel could search a computer or network, find a Word document, copy paragraphs containing certain words and put them in a spreadsheet or another Word document.

Develop Unique Dialog Boxes
If you’re already using Excel, Word or any other major Microsoft Application then you’re using Dialog Boxes (File Open, Tools Options, Format Font etc.). With VBA you can create your own boxes to allow your users to select options and enter data.

Create simplified Front-ends
If your team are all working on the same spreadsheets on a regular basis then you could create a series of Toolbar buttons or a Dialog Box so they have easy access to your running your VBA code.

Read Data Files
The Microsoft Office applications (Word, Excel etc.) are quite versatile when it comes reading unusually formatted files but they do require the files to be consistent (each line of information must be laid-out the same). VBA gives you the power to read data files that are less consistent and that require more flexibility to interpret them.

Use Dynamic Link Libraries (DLLs)
All Microsoft applications are actually made-up of one main program and many smaller programs. This means that they start-up quicker (because only those you need at start-up and loaded from your hard-disk). Splitting-up the applications into smaller chunks also allows Microsoft to update your applications by just sending you the smaller/updated programs. These smaller programs are stored in files with a suffix DLL. Your VBA programs can access the DLL programs to give you more power and flexibility.

Create Spreadsheets automatically
You VBA code could open Word documents, PowerPoint presentations, Databases, files on your computer/network, prompt the user for information, read Office settings and create new spreadsheets automatically.

Distribute VBA
The code you write using VBA can be contained in an ‘Add-In’ and sent around your organisation to be used by everyone. Often Add-Ins are used by large software companies to link Excel, Word, etc. to their own software products. For example: Adobe (Acrobat) and Sage to name just two.

Trigger your VBA code to run automatically
using:

• The Menu Bar
• A Shortcut on the Keyboard
• A Toolbar button
• A button you’ve placed on a spreadsheet/document
• A picture or graphic Object
• A function (as previous mentioned)
• When the User does something in the application, for example:
• Opens a Workbook or Worksheet
• Creates a new Workbook or Worksheet
• Closes a Workbook or Worksheet
  and many more

View VBA Course Details

Microsoft and VBA (Visual Basic for Applications) are trade marks of Microsoft Corporation