Hello,
I'm working on a Function to remove all comments from the code in a
given Workbook's modules, to reduce file size (The spreadsheet will be Routed
to multiple recipients so I need to keep the file size fairly small). This
Function is in a separate spreadsheet containing tools to manage the
spreadsheet to be Routed. At first, this Function was working perfectly.
However, as the lines of code in the main spreadsheet grew, the Function has
stopped responding. Stepping through the code showed that it was always
freezing on the same line of code:
..ReplaceLine lLine, tempLine
where lLine is a Long indicating the number of the line to be replaced,
tempLine is a string containing that line of code, minus the comments.
..ReplaceLine is uses the following line:
With aWorkBook.VBProject.VBComponents(lCounter).CodeMod ule
where aWorkbook is the Workbook passed in as a parameter to the Function,
and lCounter is a Long used to loop through all the VBComponents.
I don't understand why ReplaceLine would fail after a certain number of
lines have been processed. The line of code for which ReplaceLine freezes up
remains constant if the code/comments for the given Workbook do not change,
but removing some code from the given Workbook causes ReplaceLine to freeze
up on a different line. So it seems the number of lines replaced may be the
cause of this problem. The total lines processed before it freezes is about
1350.
Any ideas why ReplaceLine would freeze up, and how to work around this
problem? Is there a simpler way to remove all comments? Any
ideas/suggestions would be appreciated!

Thanks.

Shadhi