To Call or not to Call

[Chimpestry]

What is the best practice ..... to write longish macros or to write shorter macros and use Call statements. I recently completed a project that imported data (from Quicken) into Excel, prepped that data and then added it to a Excel Table/Database. I broke each of the different prep types into separate Macros and then used Call to link the prepping Macros to a "master" Macro. Is this better or worse in terms of execution and speed than combining all the lines together in one long(ish) Macro for all the prep work? The smaller Subs are 20 to 50 lines each (not including comments) and could be consolidated into a single sub of say 350 to 400 lines.

[protonLeah]

Break the big one up; but, use descriptive names for the called subs to make the logic apparent.

[6StringJazzer]

If you have a VBA application where you are starting to worry about optimizing the stack, then you into some heavy stuff. There are many ways to optimize VBA for Excel and Sub calls is rarely a major culprit in performance problems. More common is the failure to take advantage of structures specific to Excel, such as doing massive calculations using cell references, instead of loading data into arrays for processing. And even then you have to have a big, long-running application before you see improvements from the user's perspective.

The rule of thumb is that you should first structure your code in a way that is logical, and minimizes the impact of recoding if you have to make a change later. No point in saving 10 seconds of run time by spending extra hours to code something, and even more hours when you have to go back in 6 months to modify the code and ask, "Now, what was I thinking when I coded this?"

If it ain't broke, don't fix it. Are you experiencing a performance problem? If not, don't consolidate the code. If you are, look at other issues first. Post your file here and we can help out.

(Note that I moved this to the VBA subforum.)

[mc84excel]

My suggestion = Write a main macro which calls sub macros.

1. Placing all the code into one long macro in order to reduce Call statements will not save you noticeable speed because the speed impact of using Call is negligible (see http://www.excelforum.com/excel-prog...e-fastest.html ).

2. If you keep the sub macros generic you are creating reusable code (which can save you time in future projects)

3. If there are a lot of variables in your code, IMO it would be best to avoid long macros because of the increased overhead. By moving as many as possible to sub macros, they are only used when needed and lose scope as soon as the sub macro ends.

4. I second what 6StringJazzer says. If your code is running that slow then there are more important things to optimize to improve your speed.