Search code examples
vbaexcelcode-standards

VBA Code Standardisation Practices

I've been applying VBA at work for a number of purposes. I have noted the more 'clicks' a user has to do for a form (with a number of macros), the higher the rate of error. I was wondering rather than having 3 separate functions requiring 3 separate clicks - should I bunch all these functions together into the one sub module for ease of use?

Clinical staff have indicated that this would work best with them, but in terms of code optimization and keeping different functions separate - wouldn't it make things very messy? As I'm still in the process of learning VBA, I thought I'd turn to the expert community which has helped me out a lot so far.

I'd appreciate any and all comments regarding your thoughts on this and how I can create best-practice VBA standards to adhere towards.

An example of this would be the static copy function I have created and the monthly Calculation function. For the monthly calculation to be completed it pulls data from a summary tab and compares this against the static data. However for the static data to first be captured - the user needs to click the static data macro. I've separated the static snap-shot function and the monthly-report function but was wondering if I could instead combine both of these into one function. Readability wise it's not problematic (as I have the comments explaining each section) - but standard wise, would it be unwise? MonthlyCalc Snapshot Static Copy Snapshot I apologize if this question is somewhat broad in nature.

Solution

  • After reading around a few forums I've picked up the following information:

    • Do not use global variables unless you have a specific reason to do so
    • Don't forget to initialize variables else you may run into sub-script errors
    • Keep your code simple (iPhone Approach - one click approach)
    • If modules are similar consider grouping the functions together into one
    • Macro recorder is your friend

    Thanks for the tips everyone!