What is VBA

What is VBA

VBA stands for Visual Basic for Applications. VBA is Microsoft’s programming language for a number of the Office suite of applications including Excel, Word, Outlook and PowerPoint (VBA is also used in a number of 3rd party applications such as AutoCad). Computer code written in VBA is often referred to as macros which simply means an action or set of actions that can be repeated

Office applications on both Windows and macOS devices can use VBA though there are many differences to some elements of the language between the two platforms – the focus of this site is VBA running on Windows devices. VBA has been available in the Office applications suite since Office 5.0 released in 1994.

Why use VBA

Typically, to automate a complex task that is carried out multiple times in order to reduce manual repetition, eliminate the opportunity for errors and increase the speed by which data can be processed or amended. Examples are to transform and summarise lists of data, to automatically scrape data from the web, to add or update tables, headers, footers, hyperlinks, bookmarks, pictures, shapes, or to send multiple emails.

When can VBA code run

VBA code can run in response to explicit user actions (for example clicking a button in the ‘ribbon’ menu, a button embedded in an Excel worksheet or Word document, a keyboard shortcut), when ‘events’ occur (such as a file opening or closing, an Excel worksheet or cell being selected, a slideshow beginning, an email arriving) or on a schedule (at specific times or at a specific frequency).

How to create VBA code

There are two methods to create VBA code. In Excel or Word (or in older versions of PowerPoint - up to version 2010) you can use the macro recorder. In all of the Office applications that use VBA, you can manually create VBA code. The macro recorder is great for quickly capturing brief snippets of code but has little flexibility. Manually creating VBA code is what this site is about, so …

How to manually create VBA code

To manually create VBA code, first you need access to the VBE – the Visual Basic Editor. There are two ways to access the VBE:

• In Excel, Word or PowerPoint, select File > Options > Customize Ribbon, then under the ‘Main Tabs’ heading find the check the ‘Developer’ option then click OK … this will add a ‘Developer’ tab to the ribbon … click this tab and then click the ‘Visual Basic’ button

• Press the Alt and F11 keys at the same time (if this doesn’t work, check that your function keys are acting as function keys, they often can have multiple purposes, you may have to press an ‘Fn’ key, or similar, at the same time)

Welcome to the VBE … it will look a bit like this (though the number of windows and exact layout may differ):

You’re still not quite ready to start creating VBA code though … before you can do that you have to add a ‘module’. To do this:

• In the ‘Project’ window of the VBE (in the top/left in the above image), highlight the document / workbook / presentation you want to add the VBA code to … its name will be in parenthesis and preceded by either ‘VBAProject’ or just ‘Project’ depending on which Office application you are using (see image above)

• In the menu, select Insert > Module … the ‘Project’ window will now show ‘Module1’ being highlighted and the main (largest) window will have the cursor positioned in it and will be either entirely blank or, depending on your default settings, may include the text ‘Option Explicit’

Now to add some VBA code:

• Type the following text (below ‘Option Explicit’ if it is present): Sub MyFirstMacro

• Ensuring you have a space between ‘Sub’ and ‘MyFirstMacro’ and no spaces within ‘MyFirstMacro’, press your enter key and the VBE will add parenthesis after MyFirstMacro, a blank line and then the text End Sub … this is all good and is the VBE helping us to write code

• In the blank line, type the following text: MsgBox "Hello World, the time is " & Time

• ‘Sub’ stands for subroutine and a subroutine is a type of procedure … the full procedure you have entered should read like this:

• You’ve just written a VBA macro, well done! With the module and the procedure added, the VBE should now look like this:

How to run VBA code

To run the procedure from within the VBE:

• Ensure the cursor is on any of the 3 lines from the ‘Sub’ line to the ‘End Sub’ line, then either

  • In the menu select Run > Run Sub, or

  • Just press the F5 key

• Well done, now you know what time it is

To run the procedure from the user interface:

• Switch back to the specific Excel workbook, Word document or PowerPoint presentation (whichever you started with at the first bullet point, above) then either

  • In the Developer tab of the ribbon, click the Macros button, or

  • Press the Alt and F8 keys at the same time

• This will display the ‘Macros’ dialog (see below image)… find and select the MyFirstMacro macro (if you see lots of macros listed here, don’t worry … you may have AddIns installed that include other macros available from the user interface … to limit the number of macros, in the ‘Macros in’ drop-down, select the relevant workbook, document or presentation) and then click the ‘Run’ button

… there are many other ways to run VBA code from the user interface, for example from a ribbon button or from a button embedded in a document or worksheet. One of the simplest to set up is to run the macro from a QAT (Quick Access Toolbar) button which you can do as follows:

• Click the small down-pointing arrow on the right of the QAT (when you hover over this arrow, you will see the text 'Customize Quick Access Toolbar') then select 'More Commands'

• You will now be in the 'Customize the Quick Access Toolbar' dialog - under 'Choose commands from' select 'Macros'

• Find and select your macro ... it may be prefixed with the name of the file you added it to and an exclamation mark

• Click 'Add >>' and the name of the macro will be copied to the right-hand box

• Optionally, to customize the icon and name of the button in the QAT, with the name of the macro highlighted in the right-hand box, click Modify and select an icon and type a display name

• Click OK to close the 'Customize the Quick Access Toolbar' dialog

• You will now have a button for your macro in the QAT

Next steps?

If you want to learn more about VBA, you could:

• Read the official Microsoft documentation (online)

• Get books about VBA

• Download some tools to make writing VBA quicker

• Ask questions about VBA to experts

... for all of these, see the Help Yourself page of my website.