Hello,
I've been working on a spreadsheet to be used for requesting changes in
access to "Systems" (Email, network login, and other applications requiring a
login, as well as VoiceMail, physical keys, etc). The core functionality of
this tool is to get the spreadsheet to the right System Administrators via
Excel's Routing Slip feature. Aside from a few minor details, the
spreadsheet is finished. However, the file is being sent to multiple
recipients, and therefore takes up a significant amount of space in the Email
system (250k+ per recipient, per request form). For this reason, I am
working on a tool (also written in Excel/VBA) that removes all the comments,
blank lines, and leading white-space from the 1500+ lines of code.
At first, Excel would crash every time. I found out that Excel does NOT
like it when some code causes the header for any function (even one in
another WorkBook) to be replaced, even when replaced with the exact same line
of code. Naturally, I modified the code so it doesn't touch lines that have
no leading white-space and made sure I put no comments on those lines. The
code worked fine when there were only about 1000 lines of code; but now it
causes Excel to freeze up, usually on a "debug.print" line.
I've found that adding a "Stop" statement that only executes once
(after removing the comments from half of the VBComponents), then stepping
through one or more lines, I can let the code Run (F5) and complete the task.
The placement or timing of the Stop statement don't seem to make much
difference, as long as the code doesn't process more than 1000 lines without
a Stop. Maybe the VBA engine just gets tired? heheh.
Any ideas why Excel would freeze up/stop responding/etc (but not crash)
when using ReplaceLine and DeleteLine on the CodeModule for a VBComponent in
a separate WorkBook? Any suggestions on a work-around or other method for
removing the comments? ANY help would be most welcome!

-Shadhi

P.S. I've tried using "DoEvents" after before/after processing each
VBComponent, and also adding an "Application.Wait" line instead of the Stop.
The ONLY thing that seems to work is causing VBA to Step (F8) through at
least one line in the middle of the process.