I work in an environment in which I am often asked to troubleshoot VB/VBA code written by other people.
This is the VB/VBA equivalent of the database server post in thread669-1323229. I've started to put together a set of key points and advice for including on my intranet pages to give people a chance to try things out for themselves before approaching me.
1. Ensure that the phrase "Option Explicit" is in the declarations section at the top of each module.
This will help to avoid typographical errors in code while coding as well as picking up non declared variables.
2. Always declare data types for variables, sub and function parameters and function return types. It aids in troubleshooting and also helps reduce application memory requirements.
3. (Access only) Don't use macros. The feature has been deprecated and will not receive new functionality in future versions, in fact it may be removed. Everything that is available as a macro option is available as a DoCmd.RunCommand option.
4. Use the Compile option on the Debug menu (Access/Excel) or Start with Full Compile on the Run menu in (VB 6). This will pick up any syntax errors in your code and make your application run a little faster.
5. When accessing a database backend, generally DAO will be faster for an Access database (MDB/MDE file) or ADO for client/server systems such as SQL Server and MySQL.
6. Always precede DAO or ADO objects with the appropriate type to avoid ambiguity when declaring objects in your code.
7. Always check any data entered by users or read from forms/spreadsheets for sensible values before passing it to the database engine for action.
Can anybody think of anything else worth including?
I've got a mix of apps in Excel VBA, Access VBA and VB 6 with different people writing code in different systems but there's so much crossover between them in general syntax I'm hoping that one document will cover everything.
John
This is the VB/VBA equivalent of the database server post in thread669-1323229. I've started to put together a set of key points and advice for including on my intranet pages to give people a chance to try things out for themselves before approaching me.
1. Ensure that the phrase "Option Explicit" is in the declarations section at the top of each module.
This will help to avoid typographical errors in code while coding as well as picking up non declared variables.
2. Always declare data types for variables, sub and function parameters and function return types. It aids in troubleshooting and also helps reduce application memory requirements.
3. (Access only) Don't use macros. The feature has been deprecated and will not receive new functionality in future versions, in fact it may be removed. Everything that is available as a macro option is available as a DoCmd.RunCommand option.
4. Use the Compile option on the Debug menu (Access/Excel) or Start with Full Compile on the Run menu in (VB 6). This will pick up any syntax errors in your code and make your application run a little faster.
5. When accessing a database backend, generally DAO will be faster for an Access database (MDB/MDE file) or ADO for client/server systems such as SQL Server and MySQL.
6. Always precede DAO or ADO objects with the appropriate type to avoid ambiguity when declaring objects in your code.
7. Always check any data entered by users or read from forms/spreadsheets for sensible values before passing it to the database engine for action.
Can anybody think of anything else worth including?
I've got a mix of apps in Excel VBA, Access VBA and VB 6 with different people writing code in different systems but there's so much crossover between them in general syntax I'm hoping that one document will cover everything.
John
