Back to basics. Excel (VBA) Macros 101

I have a friend who is just starting out in the professional Excel world.  I thought it would be a good opportunity to write about my advice on starting out with VBA Macros from the very beginning and post it here.

My first advice would be to have a good play with the Macro recorder.   This is generally viewed as a bad idea for the experienced developer but for the beginner it is a godsend.     It actually writes code for you, in front of your eyes as you perform key actions you may wish to automate.   I think this is a pretty rare feature and a great way to start looking at VBA code.  This is also super useful for non programmers / Comp Sci grads.

Macro Recorder :

In Excel go to Tools -> Macro -> Record New Macro.  You will then get an option to give the Macro a name and also a choice of where to store the macro.  Lets leave the store as ‘This Workbook’ and come back to the options later.   The same with the Shortcut key as you should think about this carefully so that none of the Excel defaults your target audience may use will be overridden.

Once you have done this you should get a small floating toolbar with 2 buttons on it.  I would drag this to join / dock with the other toolbars at the top to avoid losing it.  The two buttons are Stop Recording and Relative reference.   You will only need to worry about Stop Recording at the moment.

The next thing to do is to open up the VBE ( Visual Basic Editor )   Tools -> Macro -> Visual Basic Editor, or as most people do after about 5 mins keystroke Alt+F11.

I am hoping here that people reading this will have a fairly default setup in Excel ( I am not a user of 2007 which is a shame if this is all different )   The Project Explorer window should be on the top left and have tree format display style.  VBAProrject (YourBookName) -> Microsoft Excel Objects -> Modules.

If you look in Modules (expand using the + if necessary ) it will be in Module 1 with the Sub of the name you gave it. ( Macro 1 if unchanged )

As you start to perform actions on the sheet you will see the VBA to replicate this action added to the Sub for this Macro.   A Sub is one of 2 places where code resides in VBA ( the other is Function), there is a subtle difference between a Sub and a Macro in that a Sub can be written on its own and used by other parts of

You will start to get a feel of what calls are made under the hood. Even simple stuff like selecting cells, copying and pasting and making charts at the start will help a lot. Then once you have this you can start to recognise what is going on with the ones you are supplied. This will be a little bit harder as you won’t have been there when they are created, but you will be able to read it a little better as you will have got over some of the basics by reading your own.

Note to newbies:

The main thing to remember is that the macro recorder is basically just that; recording your actions and then replaying them.  If  you plan to release this to other people it is likely there will be trouble ahead.     VBA, unlike the Excel formulas in sheets does not change when the sheet is changed / reformatted etc so if the user adds a row or a column your Macro is going to go wrong in most cases, charts will be out of whack, data will be in the wrong place etc

There are ways around this using relative referencing, and I will come to that in 102.  I still use the Macro recorder sometimes as it’s a great way of reminding myself of the syntax to do something quickly i.e. importing data like text / .csv files.  I usually amend that and then insert of copy of this into the actual Sub I am working on and give it a clean up.