What are common anti-patterns when using VBA
- by Ahmad
I have being coding a lot in VBA lately (maintenance and new code), specifically with regards to Excel automation etc. = macros.
Typically most of this has revolved around copy/paste, send some emails, import some files etc. but eventually just ends up as a Big ball of mud
As a person who values clean code, I find it very difficult to produce 'decent' code when using VBA. I think that in most cases, this is a direct result of the macro-recorder. Very helpful to get you started, but most times, there are one too many lines of code that achieve the end result. Edit: The code from the macro-recorder is used as a base to get started, but is not used in its entirety in the end result
I have already created a common addin that has my commonly used subroutines and some utility classes in an early attempt to enforce some DRYness - so this I think is a step in the right direction. But I feel as if it's a constant square peg, round hole situation.
The wiki has an extensive list of common anti-patterns and what scared me the most was how many I have implemented in one way or another.
The question
Now considering, that my mindset is OO design, what some common anti-patterns and the possible solutions
when designing a solution (think of this - how would designing a solution using Excel and VBA be different from say a .net/java/php/.../ etc solution) ; and
when doing common tasks like copying data, emailing, data importing, file operations... etc
An anti-pattern as defined by Wikipedia is:
In software engineering, an anti-pattern (or antipattern) is a pattern that may be commonly used but is ineffective and/or counterproductive in practice
