How to organize my VBA code

[nelson41412]

Hello,

I'm entirely self taught and I like to say that I'm starting to get "dangerous" with my abilities. However, I don't have a mentor and no one at my company does similar work in Microsoft VBA's so I might be committing some coding crimes. I'm looking for tips on how to organize code, how to organize modules, what should be global vs. private, when does it make sense to create a new sub and call programs vs just a big sub, etc. Any references or suggestions would be very helpful!

For an idea of my level of capability, I'm working on a one click/point of entry solution that will create a set of data for an entry based on existing formulas, add that set of data to an excel database, then run a mail merge in a word file for a particular 'address', then save the printed file to a set location.

 

[Micron]

Organize is sort of a loose term I think, and what works for you might not be attractive to someone else. You posted this in "other applications" so not sure if you had any other app specifically in mind. The majority of my vba is in Access. I tend to group procedures into modules and use module names that describe the intent (e.g. mdlUserFunctions, mdlDbFunctions and the like) although sometimes the purpose of procedure would fit in more than one place.

Public variables (aka Global) should be kept to a minimum IMO. Suggest research variable scope in vba, esp. where it covers Static variables. A key factor should be developing with reusability in mind, even to the point of creating code libraries if you might end up with lots of code that can be used over several projects. F'rinstance, a procedure that can accept a form object as a parameter can be much more powerful than one that only accepts a form property such as its name. Not only that, it becomes reusable as you could pass any form to it. Same might go for objects such as listboxes or other controls. Another example of a reusable procedure might be the case of calling one of the msoFileDialog pickers. It might make for a long list of parameters in the procedure as there are several properties that you might want to employ so the balance of one procedure vs repeating the same code every time might be driven by factors such as usage and complexity desired. Keep in mind that only a function can return a value, so if in the dialog case you want to know if .Show was true or not, you have to use a function. You must also use functions if you specify a custom procedure as an event property (again, basing that on Access).
HTH some. FWIW, I am self taught also.