Contents >

Excel Standards

Standards are very useful if others will have to modify your code or you just want better quality code. Stadards, also called best practices, are techniques that over time have proved better than not using them.

Standards are way to prevent known mistakes from happening are usually are created with experience.

1. Comment your code
2. Indent your code
3. Keep module size to a page
4. Test your code
5. Don't hardcode column letters, it makes it hard to change the code
6. Use Option Explicit
7. Don't use CInt function use CLng
8. Don't use integer type as you can run into cases where value exceeds 32k
9. Be careful using Excel 2007 syntax as it may not run on Excel 2003
10. Copy Cells and then delete instead of cut and paste. Reduce number of #REF errors
11. Make sure the page prints nicely, including proper heading settings
12. Use native Excel functions instead of coding a similar VBA function
13. Generalize any recorded macro code
14. Develop a series of unit tests so you can refactor the code and make sure you didn't break anything
15. When changing a sheet compare the output of the previous working sheet to make sure you didn't break anything
16. Have someone else that didn't write the code test it
17. Use the internal sheet name so if user changes sheet name it won't break your code
18. Test with different option settings to make sure different user options doesn't break your code
19. Handle ##### issues by doing autofit
20. Handle #DIV/0! errors
21. Handle 1004 error during Activeworkbook.Save if file is read only or if a recovered file
22. Keep multiple versions of a spreadsheet so if current one because corrupted and unusable you can go back to a previous version
23. Strange date results can appear if Date 1904 option is set
24. Have a shared repository to store Excel VBA code. If a bug is found, make sure to change all code that uses the routine
25. Deal with case where user doesn't turn on macros and they're needed
26. Protect cells that user isn't supposed to change
27. Run spelling check on your sheets
28. Test with different Excel options
blog comments powered by Disqus

Sign In
Sign Up

Contact Us
Terms and Conditions

Tweet List

Link to us