0

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.

IronKirby
  • 708
  • 1
  • 7
  • 24
  • Your question is directed to a bunch of programmers and programmers work with ones and zeroes. Provide some real world examples together with expected results and options and we might be able to offer informed opinion(s). –  Aug 18 '15 at 04:24
  • 2
    Bunching common tasks together is usually a good idea. It shouldn't make the code any messier as long as you are still separating out the various tasks into their own functions and calling them from the main function that is triggered by the user. – Mike D. Aug 18 '15 at 04:25
  • Is there a need for 3 separate clicks? Does the functionality have to be kept distinct for any reason (I agree that "everything should be made as simple as possible", but not if it removes useful options) – paul bica Aug 18 '15 at 04:43
  • 1
    Thanks everyone! I've attached some screen-shots of the 2 different functions as an example. I'm thinking I can just combine both static and monthly calculations into one 'click' macro, but if its bad practice, please let me know early on so I don't start creating poor coding standards. – IronKirby Aug 18 '15 at 04:48
  • 2
    I don't see a need for user interaction between pulling the static data and performing the calculations (unless the static data is not ready, but in that case calculations are not possible either). Also, you should combine the functionality but not the code, just create a new Sub that calls the 2 separate functions one after the other - the code should remain modularized as much as possible, with each function performing one clear task, and only one (divide and conquer strategy) – paul bica Aug 18 '15 at 05:20
  • Like @paulbica said is there any reason why you have to let each sub run separately by clicking? If they all have to run after each other you could use one click button which then 1 by one calls the subs that have to be processed. You can keep the subs separate this way, but can also merge them into 1 and only call 1 sub. – DragonSamu Aug 18 '15 at 05:22
  • 2
    Looking at your screenshots, http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros has some very good advice and is worth a read. – Tim Williams Aug 18 '15 at 05:24
  • 1
    When it come to users, think the "iphone way" (first of the name). If the user can do everything in 1 click, don't do more, that's all. The only time you can add multiple click is when you want to do a "Star Trek first generation" remake – Maxime Porté Aug 18 '15 at 06:05
  • Thanks everyone! I'll take note of these tips: - Simplicity is key (iPhone approach) - If modules are similar group them (if possible) – IronKirby Aug 19 '15 at 04:14

1 Answers1

0

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!

IronKirby
  • 708
  • 1
  • 7
  • 24
  • I'd keep separate projects separate, but by the same token keep one project in one workbook. only split a single project into separate modules if it makes sense to do so, I.e. if you have a portion of code, an algorithm maybe, that is completely encapsulated and separate from your main project. Otherwise, one project, one workbook, one module. Of course a single project can encompass multiple routines and functions, even separate macros. The user interfase should always be as simple and fool proof as possible, and test for (and handle) obvious user errors. – Coder375 Aug 19 '15 at 08:27