Save your workbook before you proceed any further. If you’re using Excel 2007, you’ll need to make sure it’s saved as a macro-enabled workbook (*.xlsm). Keep this copy as a backup: VBA code can be extremely destructive and there’s no Undo facility to fall back on!
Select Developer > Visual Basic from the Excel menu bar to open the Visual Basic Environment (VBE) or press Alt-F11.
The VBE comprises four main types of window:-
- Project Explorer: a tree view of any Excel workbooks currently open as well as some system objects. If this is not immediately visible, select View > Project Explorer or press Ctrl-R.
- Properties Window: a view of the properties of the selected object. If this is not immediately visible, select View > Properties Window or press F4.
- Immediate Window: a window where you can interact with the VBE. If this is not immediately visible, select View > Immediate Window or press Ctrl-G.
- Code windows: one for each open workbook and one for each worksheet. Any or all of them could be closed and may not be visible. If you cannot see the code window you require, go to the Project Explorer and double-click the workbook (ThisWorkbook) or worksheet (Sheetx) you are interested in.
There may also be UserForms but they are currently outside the scope of this document.
If you have been given some VBA code to install in your workbook, you should have been given instructions on which module it should be placed in. There are three main types of code module:-
- Workbook: called ThisWorkbook. Contains code which relates to the workbook as a whole including event handlers which detect when the workbook is opened, closed, etc.
- Worksheet: called Sheetx, where x is the sheet number. This is followed by the sheet name in brackets, initially set to Sheetx but can be renamed by the user. This code which relates to a specific worksheet including event handlers which detect when the workbook is activated, changed, etc.
- Public (external) modules: code which is available to all worksheets.
Each module is structured in a specific way: failure to follow this structure will result in your code not executing. A typical module will contain the following, in this order:-
- Up to three Option statements (in any order):-
- An Option Explicit statement. Although not strictly necessary, this is highly advisable as it promotes good coding practice and assists you in detecting spelling errors.
- An Option Compare {type} statement, where {type} will be Binary or Text. (In MS Access it may also be Database.)
- An Option Base {number} statement which determines the default LBound() of any arrays you declare.
- One or more Dim, Const, Public or Global statements. These occur before the first Sub or Function block and are ‘global’ variables and constants which are available to all procedures and functions in this module only.
- A number of Sub and/or Function blocks, each one starting with a Sub {name(arguments…)} or Function {name} (arguments…) line and ending with one and only one End Sub or End Function line.
There may also be a number of lines starting with a single apostrophe and coloured green: these are comment lines which are ignored by VBA and may be found anywhere in the module: at the start, at the end, and inside or between Sub and Function blocks.
There should not be any lines in red text: these indicate unresolved syntax errors and will stop your code being compiled. When you attempt to leave a line which you have created or edited and it contains a syntax error, you will receive a message warning you of a compilation error. This can be dismissed, allowing you to correct the error or move to a different line, however you must return and correct the faulty line before you attempt to execute the code otherwise your code will not compile.
Program Design
A comprehensive guide to program design is outside the scope of this document, however there are a few simple guidelines to help you produce code which can be tested and maintained with as little effort as possible.
- Use Option Explicit to catch any undeclared variables.
- Declare (Dimension) variables and constants with names which reflect their type, e.g. strSurname, intRecordCount, dteDateOfBirth. This will make your code more readable and help you pick up type conversion errors whilst you’re coding. (sSurname, iRecordCount, dDateOfBirth are equally acceptable.) This will make your code more readable.
- If you have a segment of code which is repeated several times, consider making it a separate Sub or Function. If they require different inputs at different stages of the program, pass these inputs to the routine via ‘arguments’ (sometimes called ‘parameters’, although these are not strictly the same thing). Know the difference between passing arguments ByRef and ByVal and learn about Optional…Variant.
- Do not use over-lengthy lines of code: if it can’t be viewed within the width of the screen, break a line in a sensible place with a continuation character (underscore – “_”).
- Use comments to introduce each Sub or Function – what it does, how it does it, etc – and briefly comment any code which isn’t self-explanatory. Remember someone else may have to read and make sense of it.
- Don’t run the same code unnecessarily. For example, if you have to set a variable to a value which isn’t going to change, do it before you launch into those lengthy nested loops.