Teaching Excel How To Speak PowerPoint
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.
Becoming Bilingual
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!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.