Reduce file size by removing comments from code?

[Shadhi]

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.

 

[Robin Hammond]

Get Rob Bovey's CodeCleaner. It has an option to remove comments.

Robin Hammond
www.enhanceddatasystems.com

 

[Shadhi]

Hello Robin,
Thanks for the suggestion. Admittedly, I haven't checked out the
CodeCleaner, but I believe it is most likely a stand-alone, non-scriptable
product. While that would be perfect for most people, I don't think it would
work well with this project as it would add another step to the process and
another program to keep track of. I have found another solution to the
problem of Excel hanging when removing comments from 1000+ lines of code;
simply displaying a message box! I suspect the problem has something to do
with Excel automatically checking the syntax of the lines of code as they are
entered/replaced.
My suggestion to anyone else who may encounter this peculiar problem:
keep a running count of the lines of code processed, then use a test similar
to the following:

If (lLinesProcessed Mod lMaxNonStopLines) > lMaxNonStopLines then
MsgBox "The Excel Engine is tired and needs a break." & vbNewLine & _
"Click OK to put that lazy engine back to work!"
end if

lMaxNonStopLines would be a constant indicating how many lines the engine
could process before causing Excel to "freeze up" (determined by trial and
error?). In my project, I found that number to be between 800 and 1000.

Hope someone finds this helpful!

Shadhi