Addin Auto_Open code executes only in debug mode

M

Matthew Pfluger

I am building a setup workbook that installs some custom addins. One of the
addins is giving me problems. The addin contains an Auto_Open procedure as
follows (note the DEBUG line at the end):

Public Sub Auto_Open()

' Initialize variables
Const szSOURCE As String = "Auto_Open" ' Module location
string for errorhandler
Dim bOpenFailed As Boolean ' Errorhandler
variable if commandbar code failed

On Error GoTo ErrorHandler
Application.ScreenUpdating = False

' Do stuff: build commandbars, register UDFs, etc.

ErrorExit:
On Error Resume Next

ResetAppProperties
If bOpenFailed Then ShutdownApplication

Debug.Print "I completed the startup code!!!"

Exit Sub

ErrorHandler:
bOpenFailed = True
Resume ErrorExit
End Sub

The Setup Workbook contains this code snippet in its Install procedure:
' Attempt to uninstall addin to make sure it's gone (see uninstall code
below)
On Error Resume Next
If Not bUninstallAddin(sAddinName) Then Err.Raise glHANDLED_ERROR
On Error GoTo ErrorHandler

' Install addin
With AddIns.Add(Filename:=sAddinSourceFilepath, CopyFile:=bCopyToLibrary)
.Installed = True
End With

' *************** UNINSTALL CODE ************
' Attempt to close addin in case it is open
On Error Resume Next
AddIns(sAddinName).Installed = False
On Error GoTo ErrorHandler

' Delete registry keys (if any)
If Not bDeleteAddinRegistryKey(sAddinName) Then Err.Raise glHANDLED_ERROR
' *************** UNINSTALL CODE ************

I know from testing that the Uninstall code completely uninstalls the addin,
including running its Auto_Close script.

If the addin has been previously uninstalled, the Install code executes
completely, including running the addin's Auto_Open script. If I step
through the Install procedure, the addin installs completely, including
running the Auto_Open script. I know the Auto_Open script runs because the
DEBUG line prints a record of the execution in the Immediate Window.

However, in the case where the addin is already installed and I run the
Install macro without stepping through, the addin is installed at the end,
but the Auto_Open macro doesn't run (no record in Immediate Pane). A check
in the Add-In Manager and the VBE reveals that the addin is in fact open, but
why wouldn't the Auto_Open script run under these circumstances?

I know this is a little OCD, but the end user could initiate this situation
and would likely freak out, so I'm trying to cover all bases. Thanks for any
input.
Matthew Pfluger
 
P

Peter T

It's difficult to say what's going wrong as either you've posted only
partial code or code is odd, or perhaps I simply don't follow.

That said, there are scenarios in which either the ThisWorkbook open/close
events are not triggered, or the Auto_Open/Close routines are not triggered.
It's rare that both sets fail to run, other than with automation (and
there's a way to accommodate for that too if required).

What you can do is include both pairs of open/close routines. In Each of the
open routines do something like this
Public gbOpenDone as Boolean ' in a normal module

If not gbOpenDone then
gbOpenDone = true
' open stuff
end if

and similar in the close pair.

Instead of a global boolean flag you could set a global long to some code
number that gives extended info about which routine has fired, only need one
variable to cover both pairs of open/close routines.

Regards,
Peter T
 
M

Matthew Pfluger

Peter T,

Thanks again for the help. I decided to include a function that determines
if an addin is already installed and cancels the Install routine if it is.
That seemed to be the easiest solution, especially since some of the addins
aren't mine to change.

Thanks,
Matthew Pfluger
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top