Am i using too many Subs?

**VBitteritus** · 08-29-2012, 11:03 AM

Hey guys,
I have this Macro which i wrote for work; i read about the first 60 pages of a vba for dummies book and after that i started googling like a maniac and combining pieces of code to make it do what i need it to do. It works, and im sure there are more efficient ways to go about doing this.

Currently I'm using several Subs to maintain some sort of flow with the script.

Sub repeatQue() will ask the user to define how many reccords they wish to create; it will then setup the necessary global variables accordingly. And call on the next Sub
Sub startUpM() will count the amount of rows it needs to calculate a price for (the price generated fluctuates depending on prices generated by several formulas; most of them generated through cell formulas) once its done counting it will again set various variables as defined by several cell calculations.
Sub fillListM() is where the price generation occurs and is dependent upon startUpM to know which row height it should be working on and where to get certain data. Once done it will update a row height variable and call upon the next sub called repeatM
Sub repeatM() will check if the row height has reached its limit; if this is true it will call upon the sup endMacros or repeat the fillListM macro to calculate another line
Sub endMacros() will publis, save and close an .scv file and will define whether the process needs to be repeated to create additional files depending on how many times a repeat process was asked for in the subRepeatQue. If it needs to repeat the process it will update a global variable and restart sub StartupM; If the global repeat variable repeats 0 the process will finally be ended.

To be honest im already really happy that I managed to get this far and its great to see it work. But when I set the repeat Variable to 30 , which would create 30 files I get an error message (actually I just tried it again with 30 so I could quote the error message just now and It worked) but I read somewhere that the error message can be caused by using and repeating too many subroutine loops. (I’m sorry as I can’t recreate the error I can’t give the exact error message.)
I’m basically wondering if I should be using something other than subroutines to do this.

This is my code, sorry about the mess.

Please Login or Register  to view this content.

**Richard Buttrey** · 09-01-2012, 11:57 AM

Hi,

Difficult to comment with certainty without knowing all that you are trying to do and the process you are wanting to control. If we had the workbook and some descriptive notes it would be easier.

That said I have to disagree with John456852 and my answer is No.

All other things being equal in my opinion it's much better to break a process down into several procedures rather than shoehorn everything into one. Doing that tends to lead to 'spaghetti code' which is a nightmare to understand, maintain and debug.

Far better to have many procedures rather than a few. Reasons? Well it's far easier to debug and test, and far easier to read and understand. You lose nothing since you can always call the next procedure from the preceding one, although my default style is to have a default 'Sub Control' procedure which acts as a program flow. In the main it is just a list of procedures and variable changes where necessary.

Regards

**VBitteritus** · 09-03-2012, 03:20 AM

Thanks for the feedback guys, im just wondering what can i use other than subroutines that will do the same trick? As ive no idea how i would go about telling vba to repeat a certain part of code until a certain condition no longer applies. At the moment im just checking for a condition and if that condition is valid i tell it to move to a subroutine, at the end of that subroutine i then redirect it to another or the previous subroutine and so forth; until eventually vba is directed to an END when all conditions are met. Im using a lot of incrementing variables and limit conditions to trigger a bunsh of repeats.

So currently ill run the fillistM sub routine 5 times for each time the repeatM sub routine is used , but the repeatM sub routine can be called on about 30 or 100s of times if i wanted, (so if i run repeatM 30 times that means that fillistM will have run 150 times before ending)

**Richard Buttrey** · 09-03-2012, 05:06 AM

I'm still not entirely clear and it would be easier if we could see the request in the context of the workbook as I said before.

You repeat sections of code by using
For..Next loops where you know in advance the number of iterations. e.g.

For x = 1 to 10
'your macro
Next x

or Do..Loop, Do Until...Loop, Do ...Loop Until, Do While...Loop, Do...Loop While constructions.

Hope that helps

Regards

**VBitteritus** · 09-03-2012, 05:16 AM

STAFFEL EMULATOR MATTIAS11.xlsmThanks; Yeh i think the loop thing could help in this case im attaching the workbook as requested; sorry i didnt do it before.

Keep in mind that most likely the macro will crash for you as it requires a specific directory path to save the files in and it would be scary freaky if you would just so happen to have that same directory path. :P
Also try not to laugh too much at my code; im pretty sure it feels bad enough about itself as is ^^

**VBitteritus** · 09-07-2012, 10:51 AM

STAFFEL EMULATOR MATTIAS12.xlsmWhen my product increased by about 6 fold the code became to hard to handle and i was getting constant overflow errors.

For those who might be interested, i ended up having to change my 6 step program into something a lot more linear, which i was able to do thanks to the FOR .. NEXT function. I ended up drawing a flow chart to simplify my thought process. Even printed out my code and stuck it together so i could make notes on it and draw arrows of subs that would dissapear and where other code would be moved to etc; the whole ordeal was terribly exciting :P. Anyways wanted to post the current result, in case it might help someone in the future. i removed 2 sub routines and the whole thing seems a lot more straightforward now. I still have a loop in there which i could technically also replace with a FOR NEXT, im assuming its possible to have a loop within a loop just as you could have an if then , statement within an if then statement?

In any case heres the updated file and thanks again for the help. Ill try to be more clear next time.