Dear all

I am building a data checking tool using macros. We have one we
currently use that I built directly in Excel using lookups, If
statements etc, but the data range is so large and it is at the extent
of complexity taking many minutes to recalc.

So I have turned to trying to achieve the same task using macros
instead.. Basically the way the macro works is to look up a code,
using offsets it compares the value with some pre-determined values.
If it finds an error it then lists the error onto another sheet and
the user makes the correction in a third party system, reloads the
data and rechecks.

So far all seems OK....

My question is as follows:-

Do I create lots of mini subs for each group of data tests I want to
do and then call each of them from a primary sub or do I consecutively
list each data test in fewer subs.
Is there a performance impact.

I think for ease of maintaining the code and future debugging etc it
is better to break the subs into smaller sections but I bow to the
knowlegable ones.

Thanks in advance

John

One of the advantages of using multiple sub-routines instead of large ones is
the conservation and efficient use of memory. As each sub-routine closes it
releases any memory that it had used while executing. If everything is
crammed into one big procedure, then the memory has to reatain all of the
variables and temporary values until it finishes which causes stacking and
potential slowdown in execution.

I try to break my larger projects into small sub-routines because it allows
for easy trouble shooting or modifications. If there is a problem, I
generally know where to looks for the cause .



"Planner" wrote:

Dear all

I am building a data checking tool using macros. We have one we
currently use that I built directly in Excel using lookups, If
statements etc, but the data range is so large and it is at the extent
of complexity taking many minutes to recalc.

So I have turned to trying to achieve the same task using macros
instead.. Basically the way the macro works is to look up a code,
using offsets it compares the value with some pre-determined values.
If it finds an error it then lists the error onto another sheet and
the user makes the correction in a third party system, reloads the
data and rechecks.

So far all seems OK....

My question is as follows:-

Do I create lots of mini subs for each group of data tests I want to
do and then call each of them from a primary sub or do I consecutively
list each data test in fewer subs.
Is there a performance impact.

I think for ease of maintaining the code and future debugging etc it
is better to break the subs into smaller sections but I bow to the
knowlegable ones.

Thanks in advance

John