Teaching Excel How To Speak PowerPoint
In order to control PowerPoint (or any other program) from inside Excel's Visual Basic Editor, we need to teach Excel how to speak PowerPoint's language. PowerPoint uses terms like "Slides" and "Presentation" that simply do not exist in Excel's own code language. Luckily, we can teach Excel a language by simply checking off a box (I wish I could learn foreign languages that easily!).
The Visual Basic Editor's Reference List
The VBE automatically searches your computer for any available VBA application Object Definitions. A few examples of objects in different programs are
- Workbooks, Worksheets, Ranges (Excel)
- Presentations, Slides, ShapeRanges (PowerPoint)
- Documents, Paragraphs (Word)
The developers code definitions for objects that are unique to their programs and these definitions are what the Visual Basic Editor looks for. To get to the list of VBA references the editor has found, you need to select the Tools menu and click on References from within the VBE.
After clicking References, you should see the References Dialog Box. In the listbox you will see all the different VBA References the editor could find on your computer. If there is a reference to an application that is missing from the listbox, you can click the Browse button and navigate to the reference file. Also, notice the Priority spin buttons to the right of the reference listbox. The two spin buttons allow you to organize your references in order of scope. This is useful in handling situations when two references have an object with the same name. The reference that is closest to the top will take precedence if you write the object name in you code.
You may also notice in the above screenshot that there are a bunch of references to VBAProject. This is because you can create your own objects (via Class Modules) and therefore the Reference Dialog Box will also pickup your VBA projects in case there are are any definitions written within them.
In order to teach Excel how to speak PowerPoint's language all we have to do is check the reference called Microsoft PowerPoint 12.0 Object Library. This will probably be hard to find at first but most of the references are organized in alphabetical order. Once you have checked the PowerPoint reference, that reference will rise towards the top of the list the next time you open up the Reference Dialog Box.
Can Excel Really Speak PowerPoint Now?
Well let's test this out! Create a new subroutine in the Excel VBA editor and call it "SpeakPowerPoint". Now try to dimension a variable to a PowerPoint-only type, like a slide. You should start to see some new vocabulary words showing up in the VBA intellisense box. Have you ever seen SlideShowTransition in Excel before?
How You Can Use This Functionality
Hopefully your mind is churning a little bit around the usefulness of being able to control other programs from inside Excel. Some ways I currently use this functionality is by having Excel create emails in Outlook that mail my workbook file when it is updated every month. I also have monthly spreadsheets that can send their graphs over to a PowerPoint presentation with the simple click of a button. The automation possibilities are endless!
How Will You Use This?
I am curious if you have Excel files that interact with other software programs. If this is completely new to you, what tasks do you think you can automate by controlling other programs through VBA? Leave a comment below!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris "Macro" Newman :)