Excel VBA: The Basics - Tutorial 1

Adding VBA to your Excel spreadsheet can massively improve your efficiency especially if it is used for a lot of repetitive and labourious tasks. In this first VBA tutorial you'll learn the basics of how to create macros.

VBA is often a great addition to a spreadsheet and one that will leave your colleagues and customers stunned at your abilities. 

What is VBA?

Visual Basic for Applications (VBA) is a programming language used across a whole host of applications including Excel, Word, Powerpoint and much more. The fact that it's a "programming language" often puts a lot of people off learning, but you don't have to be a programmer to achieve some incredible things in Excel VBA. In fact, you can even get Excel to write the code for you by recording macros.

What is a Macro?

A macro is a predetermined instruction to your computer to carry out a certain task. In Excel VBA, these can either be written by you from scratch in the Visual Basic Environment (VBE) or simply recorded.

How to Record a Macro

Recording a macro is very simple. Follow these steps to get started:

1. Open up a fresh spreadsheet in Excel

2. Open up 'Excel Options'

    In Excel 2010 or 2013 click File >> Options

    In earlier versions click Tools >> Excel Options

3. Tick the Developer Tab option

     In Excel 2010 or 2013 click Customize Ribbon >> Developer

     In Excel 2007 click Popular >> Show Developer Tab in Ribbon

4. You will notice a new Tab in the Ribbon called "Developer". Click Developer >> Record Macro

5. You will be asked to name your macro, name it "myFirstMacro" and press OK.

You're now recording your first macro!

Almost anything you do from this point will be recorded and can be called upon for use later in an instance.

6. Click in Range A1 and enter the value "Hello World" and press enter.

7. Now go back to the developer tab and click Stop Recording.

8. Now delete the value in Range A1.

9. Click on the Macros button in the developer tab and select "MyFirstMacro" from the menu that appears then click Run.

The "Hello World" value reappears instantly! Now, this isn't exactly world changing stuff at the moment, but you should be able to see just how much potential VBA has for automating your processes in the future.

The best way to learn is a bit of trial and error, try recording a few more macros and see how they play out. If you have any questions, tweet us @FMDAConsultancy

In the next tutorial we'll look at the Visual Basic Environment and writing your first macro from scratch.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.