Engineers-Excel.com

Macro Overdose



I have found that often people who learn macros are tempted to apply the skill to almost any new spreadsheet. In fact, I did that too when I just learnt VBA. It is easy to get carried away by the power and flexibility of macros.

"When you have a hammer, everything looks like a nail."

Many macros get written for applications that could otherwise be easily done with Excel features. Between Excel's built in features and macros, the former should be preferred because:

1. They are more efficient
2. They are much faster
3. Spreadsheets with macros are often more difficult to troubleshoot and maintain.

Macros should normally be used when no other option seems to work. I have observed that typical excel features that are overlooked by users when they use macros are:

1. Conditional formatting
2. Data Validation
3. Pivot Tables
4. Data Tables

In addition, it is also useful to learn to use the following handy Excel functions:

1. VLOOKUP and HLOOKUP : Very useful for searching for data in tables
2. OFFSET function: For referencing a specified number of rows and columns from a specified cell.

It also helps to check some of the resources on the internet, you may find an easier way to get the job done.