VBA Takes Longer Each Time Through

  • Thread starter Thread starter EricG
  • Start date Start date
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
 
=?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.
 
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.
 
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
 
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
 
Back
Top