It is my aim that a lot of content on this site will rely on VBA. VBA is basically just a simple language which Excel can understand. Foreign languages have different sentence structures so that the message is understood. VBA is the same, the challenge is learning the correct sentence structure so that the message is understood by Excel. But before we can get stuck-in we first have to turn on the Developer Ribbon. This is the place where all of the VBA tools are kept. Frustratingly the Macro recorder and VBA editor is not activated automatically on install (that is the case is Excel 2010 at least it may be different in other versions).
Look at the top of your Excel Window if you see the word “Developer”, then happy days, you’re ready to go. However, if the Developer ribbon is not there, just follow these instructions.
Turning on the Developer Ribbon
File -> Options -> Customize Ribbon
A new window will open, ensure the Developer option is ticked in the box.
That’s it. At the top of the Excel window you should now see the word Developer.
Writing your first VBA Macro
Click Developer -> Visual Basic (or press ALT+F11)
The VBA Editor will appear. Double-click one of the worksheets in your active workbook.
Enter the following code into the window on the right.
Sub MyFirstMacro() MsgBox "Hello new world!" End Sub
Whilst still in the VBA Editor press the F5 key. The Run Macro window will appear
Select the macro and click Run.
Your VBA macro will run – If you’ve been successful a message box will appear. You have just coded your first macro!
Triggering the VBA code from a button
What if you want to run your VBA code without going into the VBA editor every time? Let’s add a button to the worksheet, which will run the macro when it is clicked.
Close the VBA Editor.
In Excel: Developer -> Insert -> Button
Click the workbook and drag an area to create the button.
The Assign Macro window will appear. Select your Macro and click Assign.
Now you can click the button to activate your Macro – Give it a try.
That’s it! You have just written your first VBA Macro (it wasn’t so scary after all). You have just taken the first step of a very exciting journey.