Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2012, 06:40 AM
BBBenj BBBenj is offline PowerPoint 2007: my macro takes a loooooong time to proceed Windows 7 32bit PowerPoint 2007: my macro takes a loooooong time to proceed Office 2007
Novice
PowerPoint 2007: my macro takes a loooooong time to proceed
 
Join Date: Mar 2012
Posts: 3
BBBenj is on a distinguished road
Default [SOLVED] PowerPoint 2007: my macro takes a loooooong time to proceed

Coucou !

First of all, I wantto apologize if there are some mismatchs with words, as I'm a french user and I don't know some specific words used in VBA or in PowerPoint. But I'll try to be as precise as possible.

Here is the main reason why I'm asking you some help :

I'd like to draw stellar maps with PowerPoint. I allready have a functionnal macro in PowerPoint, not one I wrote by myself but I optimized it yet and therefore I understand every piece of code. This program works very well but it takes a long, very long, really loooooooong time to proceed!

So, the point is to find how to mimize working time.


What are the basic data:
--> A big bunch of data, in fact, as I have to manage avec 2.5millions lines of data. Yes I know it's pretty much. It's all about stars.
- Coordinates: Right Ascension (RA) and Declinasion (DEC), numbers with 10 digits (and a "." somewhere!)
- Magnitude: like "xx.xx". If you don't know about magnitude, low number = bright stars = fewer stars. High number = fainter stars = more and more stars.
- Double stars (or not): just one bit
- Variable stars (or not): just one bit
- Name: text of about 20 caracters, not in use for now



--> Stars are ranked from brighter to fainter, from magnitude -1.6 to 15.


-->
For test purpose, I used only a fragment of the original data, extracting the 19.378 brightest stars (up to magnitude 7.2). The complete file weights over 220Mb, as the test file weights only 3Mb.

--> I currently use Excel to store the datas, but I don't mind using another application it the need arises.


So, the challenge is to draw stellar maps using PowerPoint. First step, I have to write the coordonates of the center of the map and the map scalling. Then the macro takes on, drawing the stars. I usually need to select stars up to 8.5 magnitude, about 90.000 stars (out of 2.5millions). But, on specific spots, I'll need to zoomify, therefore using 11 or 12 magnitude stars, then more than a million stars are potentially concerned.

In order to test my macros, I simulated an Orion centered map (but it could be anywhere else!). I modified the magnitude selection (therefore the number of stars concerned), to check the time it takes in that test file.

Here is the way my macro proceeded:
1. Open Excel test file, pick base datas (found in the Excel file: map center, scalling, number of stars concerned).
Time needed: less than 1s. No impact of "magnitude filter". Of course, I used a 3Mb test file, with the complete 220Mb file, it would take longer!

2. Draw the map's frame:
Time needed: about 0.02s. Still no impact of "magnitude filter". Logical.

3. "Mercator modified projection" Runtime (correct word? A bit of code called by several macros in my template) in order to fix the graphical center of my map.
Time needed: 0.000s. Nothing. Even if it's readable with 8 digits...

4. Begin the sequence For i=1 to # of stars! Note that the number of stars is determined at the openning of the Excel file). For each star, PowerPoint does:
4.1. Read RA and DEC of that star
4.2. "Mercator" runtime to modify coordinates according that projection (same runtime as said before, time needed negligeable)
4.3. Test if the star is showing in the map's frame, if it's not so then "End If". Next star, please!
4.4. Draw the star, depending upon its type:
4.4.1. "Normal" star = 1 object (disc with magnitude depending diameter)
4.4.2. "Only" variable star = 2 objects (disc with magnitude depending diameter and thin circle around it)
4.4.3. "Only" double star = 2 objects (disc with magnitude depending diameter and thin line across it)
4.4.4. variable and double star = 3 objects (disc with magnitude depending diameter, thin line across it and thin circle around it)

5. End on "If... Then". Goto step 4., till all corresponding stars not tested.
Time needed: THAT IS THE PROBLEM! It doesn't vary as expected, propotionnaly to the number of tested star, neither to the number of drawn stars. Let me show you something:
-> Magnitude up to 3.0. Only 155 stars out of 19.378. 18 drawn on the map. Test speed: 243 stars/s. Drawing speed: 28.2 stars/s
-> Magnitude up to 4.0. Only 478 stars out of 19.378. 44 drawn on the map. Test speed: 230 stars/s. Drawing speed: 21.2 stars/s
-> Magnitude up to 5.0. Only 1.567 stars out of 19.378. 147 drawn on the map. Test speed: 162 stars/s. Drawing speed: 15.2 stars/s
-> Magnitude up to 6.0. Only 5.093 stars out of 19.378. 462 drawn on the map. Test speed: 86 stars/s. Drawing speed: 7.8 stars/s
-> Magnitude up to 7.0. Only 15.918 stars out of 19.378. 1.223 drawn on the map. Test speed: 50 stars/s. Drawing speed: 3.8 stars/s

6. Close Excel.
Time needed: 0.000s. No time.

About 10 tests for each magnitude.

So, at this point, I notice the more objects drawn, the more time it will need to draw one more object. Strange, isn't it?!

So, I tried to explore an other side of this problem: does the number of objects dramaticaly rise treatment time? And it is a clear "YES"...

Lets see how I proceeded. Before, between each test, I deleted all the objects to start with a blank page. Then, I ran the macro at 4.0 magnitude (remember, 478 stars tested, 44 drawn).
1st step: start from scratch, blank page: 2.07s to draw 44 stars
2nd step (no delete, with the resulting objects from 1st step): 3.37s to draw 44 stars
3rd step (same thing): 4.35s to draw 44 stars
4th step (again): 5.29s to draw 44 stars

What is the point? The more objects PowerPoint has drawn, the more time it takes per object... It's amazing! Note that I limited the test to a small number of stars (478/44), reducing RAM impact. OK, it may not be only 44 objects, as there are many double, variable, and double and variable stars. OK, lets say... 80 objects? No more. So there must have something else ruining my macros.

So, please, if you have only a bit of advice, you'll be welcome, as I have about 150 general maps to draw, may be a thousand smaller ones and it's going to turn that project into crap.



SO, ANY ADVICE WILL BE WELCOME!!!!!!



--> I don't think there is a major problem with my code, and if it could be useful, I can copy it here.

--> If I have to use another application than Excel to store my data, I'll do it. But I'm not a master concerning data extraction from other application. But I can learn!!

-> Neithertheless, I don't think the main problem comes from datas stored in Excel, but in the drawing itself... As I'll need from 6.000 to 10.000 objects per map, you'll easily see my problem.


Sorry if my message is a bit long, I though necessary to tell you a bit about my goal in order to receive fine help.

So, now, what do you propose?!

Thanks a lot, of course...

Last edited by BBBenj; 03-31-2012 at 05:30 PM.
Reply With Quote
  #2  
Old 03-31-2012, 05:29 PM
BBBenj BBBenj is offline PowerPoint 2007: my macro takes a loooooong time to proceed Windows 7 32bit PowerPoint 2007: my macro takes a loooooong time to proceed Office 2007
Novice
PowerPoint 2007: my macro takes a loooooong time to proceed
 
Join Date: Mar 2012
Posts: 3
BBBenj is on a distinguished road
Default Solved!

Coucou !

After a lot of research, here, there, everywhere, after testing, and testing and testing, finally I know what is going wrong.

Here is my initial code:
Code:
           ActiveWindow.Selection.SlideRange.Shapes.AddShape(msoShapeOval, x1, y1, x2, y2).Select
            With ActiveWindow.Selection.ShapeRange
               .Fill.Visible = msoTrue
               .Fill.Solid
               .Fill.ForeColor.SchemeColor = ppForeground
               .Line.Weight = 0.5
               .Line.Visible = msoTrue
               .Line.ForeColor.SchemeColor = ppBackground
               .Line.BackColor.RGB = RGB(255, 255, 255)
            End With
I found what was responsible of the total slow down of my macro: SELECT. It seems to work as if it selects ALL the object on my slide... Therefore the more objects, the more time to select all of them!

So, here is the solution, very close to initial code:
Code:
                With ActiveWindow.Selection.SlideRange.Shapes.AddShape(msoShapeOval, x1, y1, x2, y2)
                     .Fill.Visible = msoTrue
                     .Fill.Solid
                     .Fill.ForeColor.SchemeColor = ppForeground
                     .Line.Weight = 0.3
                     .Line.Visible = msoTrue
                     .Line.ForeColor.SchemeColor = ppBackground
                     .Line.BackColor.RGB = RGB(255, 255, 255)
                 End With
Now, everything goes well and I'm happy as I divided working time by a THOUSAND!!!

I hope it will help someone else.
Reply With Quote
  #3  
Old 04-01-2012, 11:51 AM
JohnWilson JohnWilson is offline PowerPoint 2007: my macro takes a loooooong time to proceed Windows 7 64bit PowerPoint 2007: my macro takes a loooooong time to proceed Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

Good advice. It's rarely necessary to SELECT in PowerPoint and it's a very good idea to avoid it in code.
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #4  
Old 04-01-2012, 12:08 PM
BBBenj BBBenj is offline PowerPoint 2007: my macro takes a loooooong time to proceed Windows 7 32bit PowerPoint 2007: my macro takes a loooooong time to proceed Office 2007
Novice
PowerPoint 2007: my macro takes a loooooong time to proceed
 
Join Date: Mar 2012
Posts: 3
BBBenj is on a distinguished road
Default

Coucou !

Please, can you tell me more? What's the point with this "SELECT" instruction???
Reply With Quote
Reply

Tags
graphics, macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
PowerPoint 2007: my macro takes a loooooong time to proceed need help with macro date and time for dispatch sheets Jamesb8488 Word 1 04-28-2011 02:54 PM
PowerPoint 2007: my macro takes a loooooong time to proceed Can you open two powerpoint presentations at same time? Frogggg PowerPoint 3 11-21-2010 08:13 PM
Insert real-time weather and news updates in a Microsoft Powerpoint 2000 arandles PowerPoint 0 07-17-2010 05:23 AM
PowerPoint 2007: my macro takes a loooooong time to proceed mail merge takes and age williebear Outlook 1 05-27-2009 11:32 PM
Printer takes a rest before printing last page rtankersley Word 0 10-01-2008 05:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft