Excel Add-In start up problem

[nkrishnan1]

I have dropped an Excel Add-In file (which I developed using Excel VBA automation) with the extension .xla in the AddIns folder under the Microsoft Office directory. The Excel Add-in mananger can correctly find the add-in and clicking Ok installs it correctly and loads custom menus I created to run the add-in. But, when I run the add-in to see it in action, I see an "Excel.exe Application Error" (suggests access violation of some form). This problem is not resolved even if I reboot the machine and try all over again.

Now, here's the MYSTERY => If I compile the code all anew in the VBA development environment, and then run the add-in , it all works fine there on. But, my goal here is to deliver this add-in as a distributable on any client machine, and source code is not available (password protected). There are two .dll references in my VBA project which I had to add for using the Ref Edit control and the ListView control. I vaguely suspect these are somehow not being early-bound when running the add-in, but somehow linking fine after one clean compilation.


Anyone got any clues what could be going wrong here ? I believe add-in code is compiled ahead of time and irrespective of machines on which it is compiled on. (Refer

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcel9/html/xlcraddns.asp)

Thanks in advance for any suggestions !
NK

 

[nkrishnan1]

I'd also like to add another question to this thread.

Is this the right way of distrubuting add-in commercially. Is there a better and foolproof method without having to worry about Excel version, VBA versions on client machines..

Thanks
Narayanan Krishnan

 

[RobBroekhuis]

I've distributed (to a very small audience) my add-ins as .xls files. When opened, the only sheet visible is a worksheet with some instructions and a button to "install as an addin". The installation process is then done programmatically. I don't know if this would get around your problem, but it might.
Rob

 

[nkrishnan1]

Hi Rob,

Thanks for your reply. Where can I find the instructions / .xls files ?
Looking for any other folks who might have run into a similar problem when distributing .xla files successfully in pre-compiled form ready to run.

Thanks

 

[RobBroekhuis]

My code is quite simple. The macro below is called by a commandbutton on the "front page" screen of my .xls.

Sub InstallAsAddIn()
Dim s As String, oldname As String
If ThisWorkbook.IsAddin Then Exit Sub
ThisWorkbook.IsAddin = True
s = Application.StartupPath
s = Left(s, Len(s) - 13) + "addins\HTE toolbox.xla" '***
'if necessary, remove older version of toolbox
On Error Resume Next
AddIns("HTE toolbox&quot.Installed = False
Kill s
On Error GoTo 0
oldname = ThisWorkbook.FullName
ThisWorkbook.SaveAs s, xlAddIn
ThisWorkbook.IsAddin = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs oldname, xlWorkbookNormal
Application.DisplayAlerts = True
Application.AddIns.Add s
AddIns("HTE toolbox&quot.Installed = True
SkipDeleteMenus = True
ThisWorkbook.Close
End Sub

The gyrations around saving in different formats were necessary to get Excel to properly close the .xls. I forget exactly what went wrong without them. The line marked '*** may need some finetuning, depending on which operating systems you'll be distributing to. The SkipDeleteMenus variable allows me to not remove custom menu items (which normally occurs when the workbook closes), but shouldn't occur if the addin is installed in its place.
I hope this helps (someone)
Rob