programatically assigning references

Question

I have a workbook that requires some specific references (otherwise it has a continuous loop of errors that can only be escaped by killing Excel in task manager).

I searched for how to set up references programmatically, and found a couple of sources of information, but I still have some questions.

The lowest computer that will need to run this workbook is on XP 32-bit running Office 2007. My machine is Win7x64 running office 2010.

Here is a great resource for finding the relevant GUIDS:

http://www.ozgrid.com/forum/showthread.php?t=22483 (the third code block in the OP) which gives me the following results (I removed the paths in the final column of results):

VBA	Visual Basic For Applications	{000204EF-0000-0000-C000-000000000046}	4	1
Excel	Microsoft Excel 14.0 Object Library	{00020813-0000-0000-C000-000000000046}	1	7
stdole	OLE Automation	{00020430-0000-0000-C000-000000000046}	2	0
Office	Microsoft Office 14.0 Object Library	{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}	2	5
atpvbaen.xls	0	0
MSForms	Microsoft Forms 2.0 Object Library	{0D452EE1-E08F-101A-852E-02608C4D0BB4}	2	0
PowerPoint	Microsoft PowerPoint 14.0 Object Library	{91493440-5A91-11CF-8700-00AA0060263B}	2	10

So I have a couple of questions;

(1) since this is giving me the code for my machine, how do I determine reference for earlier versions to make sure it will work on the other machines?

(2) Why doesn't atpvbaen have a GUID?

Thanks!

Answer 1

(1) I don't know and I don't need to know, because I use late binding always, the code runs on every compatible office version!

And you are only able to set the reference if the security settings allows the access to the VBproject, which is normally not the case.

I recommend to remove the reference to PP from your Excel file and use late binding. If you like to have the intellisense during the development, change the declaration like this:

Sub Test()

  #If Develop Then

  Dim S As PowerPoint.Slide

  #Else

  Dim S As Object

  #End If

End Sub

Right click your VBAproject, select properties and add this in to the arguments field for conditional compiling:

  Develop = 1

and set the reference to Powerpoint. After development remove the reference and change the line to

  Develop = 0

Of course, you need to add all missings constants as public to a normal module in this case.

(2) It's an AddIn, only registered files have a GUID.

If your code needs that AddIn, use the code below.

Andreas.

Option Explicit

Private Function IsInstalled(ByVal AddInFName As String) As Boolean

  Dim A As AddIn

  On Error Resume Next

  For Each A In AddIns

    If StrComp(A.Name, AddInFName, vbTextCompare) = 0 Then

      IsInstalled = A.Installed

      Exit Function

    End If

  Next

End Function

Private Function AddInInstall(ByVal AddInFName As String, _

    ByVal Install As Boolean) As Boolean

  Dim A As AddIn

  On Error Resume Next

  For Each A In AddIns

    If StrComp(A.Name, AddInFName, vbTextCompare) = 0 Then

      If Install <> A.Installed Then A.Installed = Install

      AddInInstall = A.Installed

      Exit Function

    End If

  Next

End Function

Private Sub Workbook_Open()

  If Not IsInstalled("ATPVBAEN.XLAM") Then

    If Not AddInInstall("ATPVBAEN.XLAM", True) Then

      MsgBox "AddIn ATPVBAEN.XLAM must be installed!", vbCritical

      ThisWorkbook.Close False

    End If

  End If

End Sub