excelvbacontrol-flow

Best option for control flow in VBA?


I'm updating a macro and need help with the control flow. I'm not a programmer by trade, so control flow isn't something I've encountered before.

The current version of the macro works as follows:

  1. Check if datasets A & B are present in 1st workbook. If neither, exit.
  2. Extract dataset A into arrays.
  3. Extract dataset B into arrays.
  4. Create 2nd workbook & format it, then paste datasets A & B.
  5. Extract dataset C (monthly sum of A & B) into array.
  6. Create 3rd workbook & format it, then paste dataset C.

The current version only works if both A & B are present, but I need it to work if only either A or B is present. At first I figured I could copy/paste the relevant sections and just skip to them using GoTo statements, but it seems that's bad practice.

How do I navigate the code handling 4 possible scenarios? (A+B, neither, only A, only B). For context, I call a separate sub to create & format the 2nd & 3rd workbooks.

I've considered these options, but unsure which is more appropriate:

  1. GoTo statements
  2. Select Case statement
  3. Have a sub to check for which scenario applies, then call a separate sub for the applicable scenario.

I'm looking for whichever method is cleanest, easiest to maintain, and quicker to run (if the differences aren't negligible). I'm leaning towards separate subs, but wanted to check here first.


Solution

  • If I were you I would create a variable "scenario", and I woudl assign it a value depending on the case you are in. Ex : If scenario = 1 then Call (your procedure for case A+B for instance)

    Elseif Scenario = 2 then Call (your procedure for case A only etc..). I really recomand to avoid the GoTo procedures which are not that much effective in my opinion.