Visual Basic for Applications Programming / VBA macros - Three Horrible Traps, and How to Avoid Them
Experienced programmers will know that you typically spend 10% of your time writing the first half of a system, then the other 90% writing the second half. One of the main reasons for this is the need to eliminate bugs during testing. Three bugs, however, stand out. These are the little beauties which can catch you out as a trainer - the ones which you scratch your head trying to solve, while your delegates wait patiently for you to find the answer.
The good news: it's easy to avoid making the mistakes in the first place! Here are the 3 bugs, with notes on how to avoid each.
Bug One - Calling your Module and Procedure the Same Name
This is possibly the worst one. Suppose that you add a new module in VBA (perhaps in an Excel workbook), and use the Properties window to rename it:
Module1 ==> SelectCell
You then create a macro with the same name - for example:
Sub SelectCell
MsgBox "Aaargh!"
End Sub
You try to run the macro and get weird error messages (having said which, it's possible that this has been solved in the latest version of Excel).
The solution? Always call your modules names beginning with module - then it's highly unlikely that you'll ever make this mistake again!
Bug Two - Calling your Variables Reserved Names
When you're creating variable names, avoid using reserved words. For example:
DIM Select AS Integer
is just asking for trouble. Sometimes (as for this example) Microsoft Excel or other applications will highlight your error immediately, but sometimes this can create untraceable errors.
The solution is easy: either use single-character variable names (not recommended) or compound words. For example:
DIM FileCount AS Integer
If your variable names always contain two or more words in camel case (whereby you capitalise the first letter of each word), your code will be easier to read and you'll avoid this horrible source of bugs.
Bug Three - Inserting Code Within a Workbook or Worksheet
Code in VBA should always go in a module (unless you know about event-handling macros, classes or user forms,in which case you can make an exception for yourself).
If you double-click on ThisWorkbook, Sheet1, Sheet2, Sheet3 or any of the other objects shown in Project Explorer in VBA, you'll be able to write code: but you many not always be able to run it, and it may behave very oddly.
The solution to this one? Always make sure that you type your code into a separate module, and avoid the temptation to double-click on the built-in workbook and worksheet objects in project explorer unless you know eactly what you are doing.
The good news: it's easy to avoid making the mistakes in the first place! Here are the 3 bugs, with notes on how to avoid each.
Bug One - Calling your Module and Procedure the Same Name
This is possibly the worst one. Suppose that you add a new module in VBA (perhaps in an Excel workbook), and use the Properties window to rename it:
Module1 ==> SelectCell
You then create a macro with the same name - for example:
Sub SelectCell
MsgBox "Aaargh!"
End Sub
You try to run the macro and get weird error messages (having said which, it's possible that this has been solved in the latest version of Excel).
The solution? Always call your modules names beginning with module - then it's highly unlikely that you'll ever make this mistake again!
Bug Two - Calling your Variables Reserved Names
When you're creating variable names, avoid using reserved words. For example:
DIM Select AS Integer
is just asking for trouble. Sometimes (as for this example) Microsoft Excel or other applications will highlight your error immediately, but sometimes this can create untraceable errors.
The solution is easy: either use single-character variable names (not recommended) or compound words. For example:
DIM FileCount AS Integer
If your variable names always contain two or more words in camel case (whereby you capitalise the first letter of each word), your code will be easier to read and you'll avoid this horrible source of bugs.
Bug Three - Inserting Code Within a Workbook or Worksheet
Code in VBA should always go in a module (unless you know about event-handling macros, classes or user forms,in which case you can make an exception for yourself).
If you double-click on ThisWorkbook, Sheet1, Sheet2, Sheet3 or any of the other objects shown in Project Explorer in VBA, you'll be able to write code: but you many not always be able to run it, and it may behave very oddly.
The solution to this one? Always make sure that you type your code into a separate module, and avoid the temptation to double-click on the built-in workbook and worksheet objects in project explorer unless you know eactly what you are doing.
Source...