This is part of a series called “VBA4Play,” written in conjunction with Excel game developer, Cary Walkin, for his blog. Many thanks to Cary for inviting me to write a few tutorials for his series.
My development approach is different from Cary’s. To be sure, there are many different development styles when in comes to Excel and, for that matter, in the software development world. However, my principles are ones I hold fast to. You may disagree with them, and I welcome you to challenge me in the comments should you feel inclined. But it’s far more important that you understand these principles and why I believe in them. Think of it as an Elements of Style for Excel development. (For the unfamiliar, The Elements of Style is English style guide with a list of rules and principles to English writing style.)
You can do most everything with VBA, true; but you shouldn’t use VBA if you don’t have to. For example, it’s tempting to store your all your program’s global variables in a module. This method brings the advantage of accessibility: the variables can be accessed by any procedure. However, these variables are also “freed” from memory whenever your code errors out, or whenever you tell Excel to “Restart” your code. When this memory is dumped, you must start over: usually, you must create a “new” or “restore” method that reinitiates these variables. I don’t prefer this method.