reliability of macro results

[KHashmi316]

I'm having some reliability issues with a "super-macro" I'm currently using. This macro -- which calls on several sub-macros -- is quite process-heavy and operates on worksheets that often have thousands of rows. Much of the "processing" involves search and replace or search and delete. E.g., replace "&" with "and".

Unfortunately, the "super-macro" will randomly miss a replace or delete. The problem does not crop up frequently, but the results do cause problems with programs this worksheet ultimately interfaces with.

I have removed speed-optimizing code which I thought may be a culprit. I.e.:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[ your code ]

Application.ScreenUpdating = True
Application.Calculation=xlCalculationAutomatic

But the reliability remains unchanged.

Any info on how I can improve reliability is appreciated!

[Nigel]

Check you data, then check it again, inevitably in these situations the code
you thought acted on data correctly comes across a piece of corrupt or
unexpected piece of data that causes your code to fail. Even a space in the
wrong place (difficult to see if they appear at the end of a string!) can
cause problems.

It is unlikely to be 'random' , although it may appear to be! - try setting
up the same start conditions and run the code again, put a conditional watch
in to check for value changes that are invalid, and also use breaks to halt
code execution at the appropriate place.

Chunk up you code into more manageable blocks - maybe it is the same
sub-routine causing the problem - check each using the above approach.

Hope the above helps. Good luck.
--
Cheers
Nigel



"KHashmi316" <[email protected]> wrote
in message news:[email protected]...
>
> I'm having some reliability issues with a "super-macro" I'm currently
> using. This macro -- which calls on several sub-macros -- is quite
> process-heavy and operates on worksheets that often have thousands of
> rows. Much of the "processing" involves search and replace or search
> and delete. E.g., replace "&" with "and".
>
> Unfortunately, the "super-macro" will randomly miss a replace or
> delete. The problem does not crop up frequently, but the results do
> cause problems with programs this worksheet ultimately interfaces
> with.
>
> I have removed speed-optimizing code which I thought may be a culprit.
> I.e.:
>
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
> [ your code ]
>
> Application.ScreenUpdating = True
> Application.Calculation=xlCalculationAutomatic
>
> But the reliability remains unchanged.
>
> Any info on how I can improve reliability is appreciated!
>
>
> --
> KHashmi316
>
>
> ------------------------------------------------------------------------
> KHashmi316's Profile:
http://www.excelforum.com/member.php...o&userid=10439
> View this thread: http://www.excelforum.com/showthread...hreadid=380775
>

[KHashmi316]

Hi, Nigel:


The "super-macro" is quite a lengthy chunk of program, so tracing thru it is going to be bit of a task.

IAC, I think this will initially be a needle-in-a-haystack troubleshoot ... at least until I have run the macro a few dozen more times to build up enough empirical evidence to speculate on a culprit. Since the macro can take up to an hour to complete -- per worksheet -- this may take a while.

Thx for your reply!

Check you data, then check it again, inevitably in these situations the code
you thought acted on data correctly comes across a piece of corrupt or
unexpected piece of data that causes your code to fail. Even a space in the
wrong place (difficult to see if they appear at the end of a string!) can
cause problems.

It is unlikely to be 'random' , although it may appear to be! - try setting
up the same start conditions and run the code again, put a conditional watch
in to check for value changes that are invalid, and also use breaks to halt
code execution at the appropriate place.

Chunk up you code into more manageable blocks - maybe it is the same
sub-routine causing the problem - check each using the above approach.

Hope the above helps. Good luck.
--
Cheers
Nigel



"KHashmi316" <[email protected]> wrote
in message news:[email protected]...
>
> I'm having some reliability issues with a "super-macro" I'm currently
> using. This macro -- which calls on several sub-macros -- is quite
> process-heavy and operates on worksheets that often have thousands of
> rows. Much of the "processing" involves search and replace or search
> and delete. E.g., replace "&" with "and".
>
> Unfortunately, the "super-macro" will randomly miss a replace or
> delete. The problem does not crop up frequently, but the results do
> cause problems with programs this worksheet ultimately interfaces
> with.
>
> I have removed speed-optimizing code which I thought may be a culprit.
> I.e.:
>
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
> [ your code ]
>
> Application.ScreenUpdating = True
> Application.Calculation=xlCalculationAutomatic
>
> But the reliability remains unchanged.
>
> Any info on how I can improve reliability is appreciated!
>
>
> --
> KHashmi316
>
>
> ------------------------------------------------------------------------
> KHashmi316's Profile:
http://www.excelforum.com/member.php...o&userid=10439
> View this thread: http://www.excelforum.com/showthread...hreadid=380775
>