Need help with VBA execution performance problem

[SaeOngJeeMa]

Hey Excel Experts,
I have an Excel file with a lot of VBA code behind it. I've been steadily
adding new functions and subs over the last few months and have noticed that
the debugger, in particular, is running very slowly. When I step through any
line of code (even the simplest ones such as IF i = 1, or CASE ELSE) it takes
over 1 second. I haven't done any benchmarking on the code execution itself
but it also seems to be running very slow.

Is there any way to easily determine whether there's a problem with the code
(memory leakage, variables not closing properly at the end of a procedure,
other things)? Any other ideas on how to tackle this problem? BTW this
project has about 900 public constants. I'm not sure if that's part of the
problem because I have another Excel file with almost no public constants but
has the same performance problems. Thanks in advance for any help you can
provide.

Best Regards,
Dean

 

[Alan]

This may help. Save your code modules to text. Delete all the modules. Close
and save Excel. Open Excel. Install all your modules, pasting the text back
in each module. Save. This seems to clear Excel's "memory". I have had
strange things happen after stepping through code with F8 for hours. This
always seems to bring things back on track. There is also a program called
CodeCleaner that will do this for you. Google it.

Also, check through all the code in all modules and make sure there isn't an
incomplete code line that is shown in red.

Regards,

Alan

 

[Bob Phillips]

Try Rob Bovey's code cleaner at
http://www.appspro.com/Utilities/CodeCleaner.htm

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SaeOngJeeMa]

Alan and Bob,
That worked great. THANK YOU!!!!!!!!!!!!!!!!!
Best Regards,
Dean