Load Add-Ins

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I'm simply trying to load an add-in when my wkb opens and then run a sub
that resides with the add-in. CODE 1 resides in my test.xls file. When I
open it, it gives an error "sub not found" error on the line that has "Call
showMessage". The sub showMessage() is the only sub within the
"C:\data\test_addin.xla" add-in.

What is driving me insane is that my Auto_Open() sub will load the add-in
fine as long as I don't call my sub showMessage() that resides in the
add-in. But, once I run it without any calls to the add-in, I can go to the
immediate window and run the showMessage() sub that resides in the add-in.
It just won't auto run.

CODE 2 shows the simple code within the add-in.

It seems to me that excel will auto-load an add-in on startup, but will not
allow any calls to that add-in until the whole workbook opens and all
opening events are completed. Am I trying to do something that is
impossible?

If anyone wishes to re-create this problem, comment out "Call showMessage"
in CODE 1 and it loads the "C:\data\test_addin.xla" fine. Uncomment it and
it doesn't load the add-in and causes the error.



' CODE 1: *************************************
Sub Auto_Open()

Dim tempStr As String
tempStr = "C:\data\test_addin.xla"
If Dir(tempStr) = "" Then
MsgBox "You do not have the Test Add-In installed."
End
End If

Call loadAddIn
Call showMessage

End Sub
Sub Auto_Close()

Call removeAddIn

End Sub

Sub loadAddIn()

Dim bFound As Boolean

On Error Resume Next

For Each Ref In ThisWorkbook.VBProject.References
i = i + 1
If Ref.Name = "test_AddIn" Then
MsgBox "Test Add-In installed."
bFound = True
Exit For
End If

Next Ref

'if no reference then set a reference.
If bFound = False Then _
ThisWorkbook.VBProject.References.AddFromFile _
"C:\data\test_addin.xla"
MsgBox "Add-In should be added from file."
On Error GoTo 0

End Sub

Sub removeAddIn()

With ThisWorkbook.VBProject
Set Ref = .References("test_AddIn")
If Not Ref Is Nothing Then
.References.Remove Ref
End If
End With

End Sub


' CODE 2: *************************************

Sub showMessage()
MsgBox "Add-In: showMessage()"
End Sub
 
Hi Scott,

Since you're placing a call to a procedure in another (the addin) workbook,
you should reference that workbook in the call, otherwise VBA assumes the
procedure is in the workbook containing the call.

HTH
Regards,
Garry
 
Back
Top