Excel 2003 VBProject Missing reference

[John Howard]

Within Excel 2003 this macro:

Private Sub Workbook_Open()

Dim wkBook As Workbook
Dim refCurr As Object
Dim i As Integer

Set wkBook = ThisWorkbook

'Deselects 'Missing'(IsBroken) references from the VBProject References
dropdown list.
For i = wkBook.VBProject.References.Count To 1 Step -1
Set refCurr = wkBook.VBProject.References(i)

If refCurr.IsBroken Then
wkBook.VBProject.References.Remove refCurr
End If
Next

End Sub

fails to remove the VBProject Reference
MISSING: Microsoft Powerpoint 12.0 Object Library.
and returns Runtime Error 48, Error in loading DLL.

What code will programatically remove the missing reference?

The workbook was originally developed in Excel 2007 xlsm format and will
continue to be distributed far and wide with instruction to Save As an xls
file if pre 2007 versions are to be used.

I am understanably averse to having users unprotect the VB Editor to allow
manual reference removal.

And so wil be most grateful for any forthcoming solution.

Thanks in anticipation

 

[Barb Reinhardt]

I'm just starting to look at this myself, but I think that using late binding
might solve your problem. I just "threw" this together and didn't set the
ref for PowerPoint. I ran this in Excel. I think it's probably easier to
develop with early binding and then convert to late binding.

Sub test()
Dim PPTApp As Object

On Error Resume Next
Set PPTApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0

If PPTApp Is Nothing Then
Set PPTApp = CreateObject("PowerPoint.application")
End If
Debug.Print PPTApp.Name

PPTApp.Quit

End Sub

 

[John Howard]

Hi Barb,

Thanks for you prompt response.
I think however that we might be at cross purposes.
I am not up with Late / Early binding (a bit advanced for me), but I gather
your code just determines the presence or not of Powerpoint on the local
machine.

What I am trying to do is programaitcally deslect the "MISSING: Powerpoint
12.0" entry in the VBProject References drop down list.

 

[Peter T]

I can't comment on your particular case, except to say in some scenarios it
can be difficult if not impossible to remove a missing reference,
particularly programmatically (impossible if the security setting in user's
Excel does not allow Trust access to VB project).

When it's not possible to compile the project in the lowest version of any
user it's always best avoid the issue altogether, remove the reference your
end and convert to Late Binding as Barb suggested.

In general that means doing two things
- Change all object declarations from
Dim ppObj As Some-PP-Object-Type
to
Dim ppObj As Object

and do similar with any procedure arguments

The other thing is to replace any named pp constants with their intrinsic
values. If you have many, declare them as public constants at the top of a
normal module, and leave them as is in your code, eg

Public Const ppActionEndShow As Long = 6

To the constant values, in a project that includes the reference look them
up in Object Browser F2,
or in the immediate window
?ppActionEndShow hit enter
or in the existing code rt-click the constant and "Quick Info"

Head all your module Option Explicit and to a Tools/Compile to highlight any
undeclared variables.

Regards,
Peter T

 

[John Howard]

Thanks Peter,
You have obviously put a lot of thought ino my issue and it is much
appreciated.

I am not up to speed on Late / Early binding but you have prompted me to now
cacth up.