Bottlenecks in an Excel VBA application

[Guest]

Hi everyone,

I need to evaluate what parts of an Excel Application are slower than others
so that I can work on them to make the overall performance better. The
application was built over a 5 year period by someone I don't know and is now
in Excel 2000.

Is there a tool (an application) that can easily identify the lines of code
in an Excel application that cause (or may cause) a performance problem. I'm
looking for a tool like Golden that can be used to evaluate an Oracle
application or query.

If that does not exist, is there another way to pinpoint the parts of an
application that should be optimized or checked?

The application contains about 24000 lines of VBA code in 22 modules and
uses many Excel files with different extensions (.xls, .xlt, .xla).

Thank you for your help.

 

[Harald Staff]

Hi Jac

I think you need a skilled person for this. An experienced Excel developer
should be able to do this within reasonable time -assuming the code is
"somewhat standard" and not spaghetti.

Best wishes Harald

 

[Guest]

Hi Harald,

What you say is right and I am exactly that person. But I am not an MVP nor
an expert and I thought that a tool could speed up my work.

Do you (or anyone) know of Internet sites where would be listed the dos and
don'ts in good Excel VBA development?

Thank you again for your concern.

 

[Rob van Gelder]

It's not the easy way - it's the hard way, but...
I have speed testing routines on my website.

 

[Harald Staff]

Hi Harald,

What you say is right and I am exactly that person. But I am not an MVP nor
an expert and I thought that a tool could speed up my work.

Hi again Jack

Apologies <g>. I don't know of any place where those are collected. These
are the things I look for first, maybe what you already do:
- Calculation turned off when entering into cells ?
- Screenupdating turned off while code does things to sheets ?
- Any unneeded Select or Activate actions ? (Do a Search for those words and
see if there are any and what they do if so)
- Any undeclared or lousy declared memory eating variables or collections ?
- Efficient object coding ? ("Set Ws = ThisWorkbook.Sheets(1)" instead of
looking for and activating Workbook and Sheet1 again and again by code)
- Any meaningless tests ? (Like all cells in a range instead of all cells
with numeric constants ?)
- Any inefficient tests ? (Test for the least likely first. Say we want to
find all swedish male persons in the world by testing everybody. This
if male then
if swedish then
here the inner test runs billions of times
but this
if swedish then
if male then
here the inner test runs only 9 million times)

Also, for a start I place MsgBoxes all over the code to see which parts that
runs fast and which that I have to wait for.

I can't imagine how a program could spot these things. But lots of clever
people are in here, so if someone can do it, it would be fantastic.

Best wishes Harald

 

[Guest]

Thank you again, Harald.

Your help is very appreciated but remains the hard way to do the job. I have
over 30 modules of code in different workbooks, worksheets, addins and
templates and over 25000 lines of code. The code is well built and the
application works fine but it was developed over a 5 year period and some
optimization is now needed for performance over the network.

I sure will use your comments on good VBA programming and check for all
those possible problems you wrote about.

Thank you again.

 

[pfsardella]

I wish that I had the magic tool to save you the hours of work that
you'd like to avoid. Here are a few links that cover some issues.

Chip Pearson
http://www.cpearson.com/excel/optimize.htm

Charles Williams
http://www.decisionmodels.com/optspeed.htm

Optimizing for Size and Speed
http://www.microsoft.com/officedev/articles/Opg/013/013.htm

In this article we'll disclose the ideal methods for maximizing
loading performance and minimizing file size
http://archive.baarns.com/excel/develop/vbaperfm.asp


HTH
Paul

 

[Tom Ogilvy]

What you describe is a profiler:

http://www.google.com/search?hl=en&lr=&safe=off&c2coff=1&q=profiler+code+VBA&btnG=Search

 

[Guest]

Thank you Tom,

That is exactly what I was looking for.

 

[Guest]

Hi Rob,

Thank you for the reference to your site. That will surely help me when I
get more time to check it out. What I have seen so far is very interesting.

 

[Guest]

Hi Tom,

I have downloaded the VBA Profiler from Downwood.com and tried to use it. I
followed thye instructions in the documentation but I get an error when I
click on the first button.

They say in the documentation to open the project we want to profile, to add
a reference to the VBA Profiler.xla and to import a form, frmVBAProfiler,
which I did. Then they say to Strat the form (F5) ant to click on the
ProfileCode Button. Whan I do that, I get an error that is not documented.
The error (the message is in french) says that this object mechanism cannot
be dealt with (ActiveVBProject). Here i sthe code procedure that bugs.

' *******************************************
Private Sub btnInstall_Click()
On Error GoTo Process_Error
Set BandwoodProfiler.ActiveProject = Application.VBE.ActiveVBProject
'Optional, if you wish to trace the profilers actions, then add the
'following two lines.
'BandwoodProfiler.TraceFileName = "C:\Trace.txt"
'BandwoodProfiler.TraceEnabled = True

BandwoodProfiler.InstrumentCode False
Unload frmVBAProfiler
Exit Sub

Process_Error:
If Err.Number = 6068 Then
MsgBox "Programmatic access to Visual Basic Project is not trusted."
+ vbCrLf + vbCrLf + _
"adjust the security settings in Tools | Macro dialog." +
vbCrLf + _
"On the 'Trusted Sources' tab check the" + vbCrLf + _
"'Trust Access to Visual Basic Project' property.", _
vbCritical + vbOKOnly, "Critical Error"
Else
MsgBox "General Error (" & Err.Number & ") - " & Err.Description, _
vbCritical + vbOKOnly, "Critical Error"
End If
End Sub
' ********************************************
The bug happens on the second line, right after On error...

The object ActiveVBProject is not recognized in the following line of code:

Set BandwoodProfiler.ActiveProject = Application.VBE.ActiveVBProject

Would you have an idea what the problem could be?

Another thing that bugs in my system is that I cannot set the options of my
Excel Application anymore. When I try to cange tabs, I get an error message
saying that the file "Somefile.xls" cannot be accessed. That is a file I
deleted sometime ago because It was not needed anymore.

Could that have something to do my other problem?

Thank you for your concern.

 

[Guest]

Hi Paul,

Your comment is very pertinent and interesting. I found many informations
that will help me.

Thank you very much.