This is a guest post by Drew Kesler.
You’ve been there before. It’s almost 5:00, and you are going crazy trying to finish the presentation due for a monthly performance meeting the next morning. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course her change means you have to start all over with the copy and pastes…
There is always a better way! In the Oil and Gas industry, I constantly have monthly reports to assess the performance of our operating assets. Excel VBA makes it a cinch to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!
So, here it is – How to Save TONS of Time by Using an Excel VBA Macro to Build Your Presentation:
1. Build your charts in Excel
2. Create a new worksheet and paste in all the charts you need for the presentation.
3. Open VBA. To do this, you can either press ALT + F11, or you can take the following steps:
a. To show the developer tab, click on the Microsoft Office Button and click Excel Options.
b. Click Popular and then select the Show Developer tab in the Ribbon.
c. Click on the Developer tab in the ribbon and click Visual Basic.
4. In your VBA Editor window, click File => Insert => Module.
5. Paste the following code into the module (I included comments so you can customize it to your liking).
6. Click Tools => References.
Add the Microsoft PowerPoint Library.
7. Now all you need to do is go to Excel and run the CreatePowerPoint macro! To make this easy, draw a rectangle shape in your Excel worksheet which contains all the charts you want to export to PowerPoint.
8. Right click the rectangle and click Assign Macro.
9. Click on the CreatePowerPoint macro and press Okay.
10. That’s it! Just click your rectangle button then sit back and watch it run! You’ll have your presentation in no time!
Download the Example Workbook & Play with this Macro
Click here to download the example workbook and play with the macro.
Note: If you have an error with Power Point application activation, use this code instead.
AppActivate ("Microsoft PowerPoint") <-- if this doesn't work
AppActivate "PowerPoint" <-- use this
Thanks Drew
Thank you so much Drew for writing this insightful article and showing us how to automate PPT Creation thru Excel VBA. I have really enjoyed playing this idea. And I am sure our readers will also like it.
If you like this technique, say thanks to Drew.
How do you Automate PPT Creation?
During my day job, I used to make a lot of presentations. But each one was different. So I used to spend hours crafting them.
And nowadays, I hardly make a presentation. But I know many of you make PPTs day in day out. And this technique presented by Drew is a very powerful way to save time.
Do you use macros to automate creation of presentations? What are your favorite tricks & ideas? Please share using comments.
Learn More VBA – Sign-up for our VBA Class Waiting List
Chandoo.org runs a VBA Class that teaches you from scratch, how to build macros to save time & automate your work. We opened our first batch in May this year and had an excellent response. More than 650 students signed up and are now learning VBA each day.
If you want to learn VBA & advanced Excel, this is a very good class to join.
Click here for full information on VBA classes.
About the Author:
Drew Kesler specializes in process automation and data visualization. He currently performs analytics and modeling for the Oil and Gas industry. His most recent projects include using GIS mapping technology to visualize data and enhance interaction across organizations.
17 Responses to “Custom Number Formats – Colors”
You are right, Chandoo. I was playing with the colour numbers last week and some of them don't appear different from each other. Others are totally different from yours.
@Duncan
Each version of Excel, post 2003, renders colors slightly differently
Different language versions may also have different default color palettes
Hello in french
excel 2010
colo1 = couleur1 = black
[couleur1]; [couleur2]; etc..
@Hui, thank you very much again for this great post.
However - under Excel 2007, Hungarian version your solution does not work with color names. I've tried both English and Hungarian names, but drops an error message "not valid formats"
Do you have any idea how to solve this issue?
thanks in advance
@Andras
Without a Hungarian version of Excel 2003 I don't think I can assist
Have you tried using the colour numbers? I couldn't get the names to work (despite using an english version of excel). but it did work with the numbers though. I left out the "u" and was easily able to produce burgundy using [color9]
Here a possible solution: find an English version of Excel, write there the formats using English names, then open the file in the Hungarian version and see the translation.
In Excel 2007 I can't get the colour names to work e.g Sea Green but the numbers do e.g color3 - colour3 does not work so I must bow to the country that has stolen my language (ha ha!)
Hey chandoo, nice Tip!
Wouldn't be easier just apply some conditional formatting for negative numbers and another for positive numbers? Or there's some cases that you can't do that?
Unfortunately the TEXT function doesn't color the cell as number formatting does.
Hi Hui,
Great post Sir, love the new way of formatting with color numbers.
I am using 2007, and it leads me to the last color number 56.
Thanks Hui.
[…] explains how to set up custom number formats with a wide array of […]
Thanks Hui - works a treat!
Thank you, very helpful.
Trying to figure out if it is possible to apply color only to a part of the cell?
E.g. I have a value formatted as Accounting with a currency symbol.
Those I find somewhat distracting though necessary. If I could make them less obtrusive by coloring them gray while the number would stay black, that would be great. Tried tinkering with the format string, but didn't get the desired result. Single color for complete cell value works, but coloring just part of it could not be achieved. Maybe somebody managed that?
Exactly what I was looking for - thank you!
colour in the Australian doesn't work - we have to go American and no problem.
I always thought is was 56 colours notice you have 57. Cool.
thanks
Analir Pisani
Customised Microsoft Office Training Specialist
Sydney - Australia
http://www.azsolutions.com.au
Thank You!