Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    References in vba (Excel xp)

    How do I delete or add references to libraries like MS Powerpoint in my vba code from Excel? Is there a way to do it from a sub routine? Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: References in vba (Excel xp)

    In general, I don't find it very useful to manipulate references in code. But if you want to do it, set a reference (manually!) to the Microsoft Visual Basic for Applications Extensibility 5.3 library. This will make the References collection available in code: ActiveWorkbook.VBProject.References. The References collection has three methods:

    AddFromFile("filename") adds a reference to the library specified by the filename supplied.
    AddFromGUID("guidstring") adds a reference to the library specified by the globally unique ID (GUID); you can find these GUIDs in the Windows Registry. If this doesn't mean anything to you, don't bother with it.
    Remove(reference) removes a reference.

    Example:

    Dim ref As Reference
    Set ref = ActiveWorkbook.VBProject.References.AddFromFile("C :Program FilesMicrosoft OfficeOfficemsppt.olb")
    ...
    ActiveWorkbook.VBProject.References.Remove(ref)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References in vba (Excel xp)

    I would agree that the usefulness of this is limited. Normal runtime processing should never call for this. References are built into the application itself and when you distribute your application these references will always work (if the referenced files are available on the client machine, but this should be catered for in your distribution mechanism). The only eventuality I can see which would call for this is maintenance work, e.g. you want run through a hundred different spreadsheets and alter their construction.

    (Hope this comment helps someone in some way! <img src=/S/compute.gif border=0 alt=compute width=40 height=20> )

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References in vba (Excel xp)

    or perhaps if you were writing a virus... <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: References in vba (Excel xp)

    Take a look at This Thread which dealt with adding references at runtime.

    Rather than using ActiveWorkbook.VBProject. or ActiveDocument.VBProject, you can use Application.VBE.ActiveVBProject,which would enable the code to run in all Office apps. Furthermore, you would not need to set an explicit reference to the VBA Extensibility Library in order to run the code. The following example (swiped from the above thread) actually sets a reference to the VBA Extensibility Library using the GUID. <pre>Sub AddVBEEXT_Ref()
    On Error GoTo ErrHandler
    Application.VBE.ActiveVBProject.References. _
    AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
    On Error GoTo 0
    Exit Sub
    ErrHandler:
    If Err <> 32813 Then
    MsgBox "ERROR setting VBA Extensibility Reference" & _
    vbCrLf & "Error No : " & Err.Number & " " _
    & Err.Description, vbExclamation
    End If
    End Sub</pre>

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •