VBA Takes Longer Each Time Through

E

EricG

I have a fairly simple VBA PowerPoint tool that reads in some data from an
Excel worksheet and "charts" it by creating a new PowerPoint slide and then
adding drawing objects, text boxes, etc to the slide, creating a simple
schedule chart. I usually have an Excel file with two or three worksheets
that I want to chart, and run times are generally very quick. However...

I recently discovered that if I run this routine through several times in a
row during a single run (using the same Excel data for each run and creating
exactly the same slide each run), then the run time to create a new slide
increases dramatically as the VBA runs. An example is given below, where I
ran my routine 40 times, creating 40 new slides (starting with a blank PPT
presentation). The final slide took sixteen (16) times longer to draw than
the first!

I can't tell if this is a VBA issue or a PowerPoint issue. My code is long,
ugly and not worth posting here. Do any of you have any ideas that would
explain why the per-slide run times increase so much when I run through the
loop 40 times versus one or two times?

Time to Create Slide (sec):
1.48
2.11
2.34
2.67
2.84
3.11
3.31
3.62
4.03
4.39
4.64
5.01
5.34
5.79
7.04
7.65
8.37
8.70
9.15
11.29
11.39
12.74
13.52
13.85
14.33
15.32
15.75
16.55
17.41
18.24
18.71
19.32
19.84
20.95
21.26
21.71
22.16
22.91
23.62
24.01

Thanks in advance,

Eric
 
M

Matti Vuori

=?Utf-8?B?RXJpY0c=?= said:
I can't tell if this is a VBA issue or a PowerPoint issue. My code is
long, ugly and not worth posting here. Do any of you have any ideas
that would explain why the per-slide run times increase so much when I
run through the loop 40 times versus one or two times?
Click to expand...

The first thing I would check is that the macro closes all Excel and other
objects properly so that you don't at the end have 40 hidden instances of
Excel running.
 
E

EricG

Thanks for your response. I only have four objects that I "set":

Public xlObj as Object = the Excel application. This is set once at the
start, then set to Nothing at the end.

Public offScreenPres As Presentation = a hidden presentation used to draw
all the slides (hidden to speed things up).

Public offScreenSlide As Slide = the slide in the offScreenPres that is
being drawn to. Once a slide is complete, it is copied to the active
(showing) presentation, and offScreenSlide is set to Nothing.

Public offScreenShape As Shape = used for any shape that is added to
offScreenSlide. This is used many, many times when creating a lot of slides.
I tried setting = Nothing after each "set offScreenShape = (something), and
that didn't seem to make a difference in the drawing time - it still took
longer and longer with each new object added.

Also, the memory used becomes extreme according to Task Manager.
 
R

reversed

Hi Eric

I have had the same issue with PP2007 since it came out. Manipulating
shapes takes longer and longer each time code runs UNLESS you manipulate
something manually on the presentation/slide. This can simply be typing
one character into the Notes Pane. If that miraculously speeds up your
code, then you've the same issue I have. It is also in PP2010 and I've
'sent a frown' to Microsoft, and posted here...

http://social.technet.microsoft.com...t/thread/5761df91-64a2-49e1-9d4c-1dcafaee9c83

....but with no luck so far.

Hope this helps

James
 
E

EricG

I finally found this post again! It's so hard to find them after while.

Thanks for your response. I used your code snippet to duplicate exactly
what I'm seeing, and to duplicate your thought that going back to the
PowerPoint application and doing something manually "resets" the problem. I
wish I could find a VBA solution, though, because it slows down my utilities
to an unacceptable level.

Thanks,

Eric
 
Top