How to make spreadsheet with VBA compatible from 2013 back to 2010?

Hello - I have (what started out as) a 2010 xlsm, with object library references to v14.0 Office and PowerPoint (and VBA, Excel and OLE). Then I learned that this workbook will be used by both Office 2010 & 2013 users (over SharePoint).

When I copy it over to a laptop running  Office 2013, all the Office ref's automatically update to version 15.0

But when I move *that* copy back over to my Office 2010 laptop, the Office ref goes back to 14.0, but the PowerPoint stays at 15.0.

I put in some code to check the PP ref:
    If ThisWorkbook.VBProject.References("PowerPoint").IsBroken Then
but it errors out with "Error loading dll"

Is there a way to check and fix ref's  when the workbook opens?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
The best way is to remove the reference to the PPT library and use late binding in your code.

This will involve changing all declarations like:

Dim ppt as PowerPoint.Application


Dim ppt as Object

and then changing all of the references to PowerPoint constants used with the PPT objects to their numeric values.  This way, VBA will look for the appropriate reference depending on the version of Office installed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
If compatibility is required across different MSO app versions, delete all references to external Office apps from your project and use late binding. For example, for PowerPoint, use something like this:

Dim oPPT as Object ' Application (late binding)
Dim oPres as Object ' Presentation (late binding)
Dim oSld as Object ' Slide (late binding)
Dim oShp as Object

Set oPPT = CreateObject (PowerPoint.Application)
Set oPres = oPPT.Presentations.Add
Set oSld = oPres.Slides.Add(1, ppLayoutTitleOnly) ' Note : pp enumerations need re-declaring locally when reference is removed

Open in new window

You can also use a compiler #Const to enable early binding so that IntelliSense can be used during development and then switch back to late binding prior to deployment.
mlagrangeAuthor Commented:
That got it! Took a little Find & Replace'ing, but it's working now, on both versions.
Thanks very much!
Dale FyeOwner, Developing Solutions LLCCommented:
Glad I could help.

I'm with Jamie, when I develop across versions of Office, I always develop in the lowest version with the references set.  Then, before I deploy, I remove all of the external references and use late binding.  This can be tedious if you have used a lot of constants that are particular to a particular app, but I try to do that when I'm doing the developing.  Use the constant, then immediately replace it with the value and leave the constant remarked at the end of the line (that way I don't have to look up the constant values again later).
mlagrangeAuthor Commented:
I can see that now; that's the way I'll do it from now on.

Thanks to you both.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.