VBA Blogs: Going Through the Visual Basics – Part 1

We thought we’d run an elementary series going through the rudiments of Visual Basic for Applications (VBA) as a springboard for newer users.  This first part starts with the most basic of Visual Basics.

VBA (Visual Basic for Applications) is a programming language developed by Microsoft and is built into Excel and other Office programs.  It enables building your own functions (known as User Defined Functions or UDFs for short), automating processes and interacting with libraries to work with the Windows operating system.  It is also used to write some of the custom add-ins for Office applications.

In Office for Mac, VBA was upgraded in 2016 but some features and certain commands do not function correctly and herein lies the problem: it doesn’t work everywhere – but lots of people use it as a readily accessible porthole for Microsoft Office and often Excel in particular.

You can’t say “VBA” without talking about macros.  A macro is simply a sequence of actions that can be executed together, written in VBA.  Office applications (apart from PowerPoint) allow the “recording of macros” where every single step performed is recorded and translated to Visual Basic code.  After recording / writing a macro, these bits of code can be reused to automate these actions.

This is all well and good, but macros are disabled by default in the Office applications.  This is because VBA scripts may be automatically executed whenever a spreadsheet is opened – and that’s not good given all those nasty people out there.  Some scripts can be malicious and cause issues on your machine.  For example, some VBA scripts can delete files and your Outlook email address book.

To change the security settings for macros, these may be accessed in ‘Excel Options’ (ALT + T + O) in the ‘Trust Center’ group in the resulting left-hand column of the dialog box.  Once there, click on the ‘Trust Center Settings…’ button, viz.

Under the ‘Macro Settings’ group, a set of options is presented:

Excel defaults to ‘Disable all macros with notification’. This means that whenever a workbook has a macro (only .xls, .xlsb and .xlsm files may have macros) a pop-up warning window will appear before opening a file:

This is a little confusing for older users of Excel as the default is similar to Excel 2003, even if the setting starts with ‘Disable’. It’s not the only thing you have to do, mind you.

To fully utilise VBA in Excel, you need to have access to the ‘Developer’ tab, which shows the VBA tools available. By default, the Developer tab of the Ribbon is not made visible. To enable this as well, right-click on the Ribbon and select ‘Customize the Ribbon…’:

The ‘Excel Options’ menu will pop up. On the right-hand side, you’ll see “Customize the Ribbon” where you can click which tabs are to be visible. Click on the check box next to ‘Developer’ and select ‘OK’.

Then we’ll see the ‘Developer’ tab on the screen.

You’re good to go!