Consistency is key. If your requirement deviates from the standard procedures then don't expect the macro to run smoothly which is why you need to edit a macro by adding code that will ask questions of the routines trying to complete.

2. To automate a tricky task.

If your task is quite a lengthy procedure which may lead to user error then this would be another good reason to employ a macro.

In most cases, interactive macros will be key here (which of course can not be recorded) helping the user flow through multiple decision processes.

3. Help user access large blocks of data.

The amount of data that can be stored in Excel varies between versions. For instance in Excel 2003 you have 65,536 rows compared to Excel 2007 which contains 1,048,576. Remember, this is just one worksheet!

Managing large data sets can be clumsy and time consuming when carried out by the user manually and a macro can be as short as simply capturing the range should you wish to format, edit or print information as a simple task.

4. Perform math's not supported by menu commands or functions.

Though Excel provides a wealth of calculating functions for your convenience, it will be fair to say that not every mathematical process will have been provided for.

General users may not have the required knowledge to write complex formulae especially if this is used on a regular basis.

Creating your own functions therefore (User Defined Functions) is a macro which can not be recorded at all but provides a wrapper for general users to treat as a regular Excel function.

5. Environmental macros

What I call environmental macros are macros which are very short but simulate Excel commands that I wish to customise normally by attaching a keyboard shortcut to it.

Some Excel commands do not have keyboard shortcuts and each individual user will have their own working habits which they will typically custom build Excel accordingly.

It can be as simple as clearing all attributes (contents, formats and comments) to a range of cells not just deleting the contents only (DEL key).

6. Protect data from user errors.

Instead of allowing users to gain direct access to your data, protecting it via a macro will give you better control in how users can manage your Excel processes.

Viewing the data maybe required in most tasks and allowing users to protect and unprotect ranges, worksheets and workbooks (with or without passwords) to edit and format information can be controlled in decision making macros.

User form can also provide a level of protection and require macro VBA code too.

Do not 're-invent the wheel' in other words learn as much as possible about the general features of Excel to rule out if you really need to have a macro at all.

You may find a feature in Excel can do all your tasks in one simple step and you would have wasted time creating a macro in the first place.

Next Topic: writing macros

Want to teach yourself Access? Free online guide at About Access Databases

Note: If you intend to write macros for external organisations, be careful to check with the recipient that they can use macros at all as some firms disable macros altogether.
Additionally, security can be a problem too - seek you IT administrator.

Reasons to write macros

Note: If you intend to write macros for external organisations, be careful to check with the recipient that they can use macros at all as some firms disable macros altogether. Additionally, security can be a problem too - seek you IT administrator.

Note: If you intend to write macros for external organisations, be careful to check with the recipient that they can use macros at all as some firms disable macros altogether.
Additionally, security can be a problem too - seek you IT administrator.