Large loop with subroutines and more loops inside ...

[datanibbler]

Hi,

I have a question rgd. how to keep this orderly:

- I have a code containing two subroutines, which are called a number of times when parsing a list in Excel.

=> Could I put both sheets (I like to separate stuff, so there's one sheet for each of those subroutines) before the actual start of the processing?

=> That would help in keeping all the code halfway orderly because both subroutines are needed to clean the data from one worksheet and the Excel_file that is the root of all evil here has six worksheets - so I need to put the whole thing into one meta_loop with six iterations.

Any other suggestions for keeping it tidy and legible by non-techies are welcome as well.

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

you could use a for each loop like this:

for each worksheet in 'sheet1', 'sheet2', ...

     Load ....

     call ...

next

- Marcus

[datanibbler]

Hi Marcus,

yes, of course - but the syntax is not my issue.

My concern is keeping it all halfway orderly and legible. I always split my script into one tab for every distinct workstep. So if I do a meta_loop around it all like this, the start and end of that loop will be a few tabs distant from one another.

Well, I guess that can't be helped then. I'll just insert comments in the script to make it clearer.

Best regards,

DataNibbler

[Peter_Cammaert]

A good programming practice is to slim down your main loop to something short and understandable. If you can split the body of your main loop in different steps, and those steps are organized on different tabs, convert each tab into a SUB with a descriptive name, and your main loop (at the end) will become something like:

// Begin execution

CALL Initialize;          // tab 2

FOR this and that

   CALL SetupWorksheet;   // tab 3

   CALL Process;          // tab 4

   CALL Aggregate;        // tab 5

   CALL Finalize;         // tab 6

NEXT

CALL ExportAllResidentData; // tab 7

EXIT Script;

This structure allows you to provide explanations about the program logic in the right places

Another advantage is that in this way, you can hide the details of your SUBs in hidden sheets, while the main control - configuration - step logic remains visible and easy-to-change on the unhidden MAiN sheet.

Best,

Peter

[Colin-Albert]

You can split a loop across multiple tabs without a problem.

When I do that I add a number prefix to my tabs to identify the tab grouping.

e.g.

1-Main

1-mapping tables

2-Loop starts

3-load xxx

3-load yyy

3-load zzz

4-loop ends

5-other script

This means that I can promote and demote the order of tabs in the same section without any problems, but it becomes clear if a tab is moved out of the section by accident.

Peter's suggestion of separate sub-procedures is an even neater solution.

[datanibbler]

Hi Colin,

then I'm confident that I'm on a good way.

I am already numbering my tabs, but only in 01 (n times) and 02 (n times) and so on to make it clear which tabs have to go first and which ones build on the code in the 01_tabs.

Also, I have settled for putting both my subroutines first and then just the two calling_routines (they must be separate as each of them might or might not be needed).

However, there is a piece of code that has to be outside the two subroutines, so there's one more tab - maybe I can put that code together with the two calling_loops on one tab inside a big loop.

Thanks guys! I guess I'll find a way.