How to create a reference to an Excel add-in using VBA

[hscowan]

Hi,

I have code that is generated in VBA to add modules to the workbook
module - this works fine.

The problem is that the workbook code invokes functions & subroutines
that are contained in the add-in, so far not so bad - all that should
be required for the workbook code to function is a reference to the
add-in. I don't want to make the reference thru the VB editor, I want
it to be done programatically.

I thought that this should work, but it just gets a "subscript out of
range" error. I know that the name is spelled correctly. Any ideas why
this is bombing?


Code:
--------------------

' create the reference to the addin so we can access the

If AddIns("SabaFunctions").Installed = False Then
AddIns("SabaFunctions").Installed = True
End If

 

[BrianB]

Excel Help gives a slightly different method:-
'-------------------------------------------------
Set a = AddIns("Solver Add-In")
If a.Installed = True Then
MsgBox "The Solver add-in is installed"
Else
MsgBox "The Solver add-in is not installed"
End If
'------------------------------------------------------

If you run this code, you get the file name, so perhaps you may need to
use that instead :-

'------------------------------
Sub test()
rw = 1
For Each a In AddIns
Cells(rw, 1).Value = a.FullName
Cells(rw, 2).Value = a.Installed
rw = rw + 1
Next
End Sub
'---------------------------------

 

[hscowan]

Well thanks for the examples, they did shed some light on the matter
mainly that this is NOT the way to set the reference via code.

- The "subscript out of range" error was due to the fact that whe
using the syntax in any of the examples here, you are referring to th
"name" of the add-in. When attempting to "Install" the add-in you mus
use the "title" of the add-in! SabaFunctions was the name, but th
title was "Space Air Balance Analysis". The name and title may be an
likely are different!


- What this really does is "install" the add-in. It does not create th
reference as would be by selecting "Tools / References" and the
checking the box beside the desired add-in, from within the VB editor.

*So, the search continues for: How to set the "Reference" to an add-i
for a workbook via VBA code.*

thanks,
Scot

 

[hscowan]

Well, this will add a reference to the indexed project, so I know this
is on the right track.

Code:
--------------------

Debug.Print Application.VBE.VBProjects(3).References.Count
[Application.VBE.VBProjects(3).References.AddFromFile "C:\Documents and Settings\Scott\Application Data\Microsoft\AddIns\SABA.xla"
Debug.Print Application.VBE.VBProjects(3).References.Count

I noted the reference appear in the project explorer window as the proc
ran.

Now if I can figure out how to add a reference for a specific project,
without using an index to identify the project.

Does any one have an idea of a way to improve on the above code???

thanks,
Scott

 

[hscowan]

Well if anyone is interested, here is what I found to work...



Code
-------------------


Sub Add_Reference_to_Addin()
' this routine will identify and add a reference to the current workbook (project)
' if open "unsaved" workbooks exist, they will generate a path error (error 76)
' Optimally this will accept the path to the addins for the particular OS/computer

Dim a As Object
Dim idx As Integer

'Set a = AddIns("Space Air Balance Analysis") 'this is the title of the add-in
For Each a In Application.VBE.VBProjects
idx = idx + 1

On Error Resume Next

If a.Filename = ThisWorkbook.FullName Then

If Err.Number <> 0 Then

' Err.Source & " Index: " & idx & vbNewLine & _
' vbNewLine & _
' " Error number " & Err & ": " & Err.Description, _
' , "Error Generated: " & " by: " & _
' Err.Source
' On Error GoTo 0 ' reset error trapping
GoTo bottom
End If

' ----------- BELOW MATCHED -------------
MsgBox a.Name & " Index: " & idx & vbNewLine _
& vbTab & " Project Filename: " & a.Filename & vbNewLine _
& vbTab & "Workbook Filename: " & ThisWorkbook.FullName, , _
"Workbook Matched Project name."

' set the reference to this project/workbook
' this is the same as setting it from the VBE/tools/references
Application.VBE.VBProjects(3).References.AddFromFile *"C:\Documents and Settings\Scott\Application Data\Microsoft\AddIns\SABA.xla"*


End If

bottom:
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo 0 ' reset error trapping
Next

End Sub

-------------------



best regards,
Scott :cool