ExcelComAddInHelper.LoadComAddIn Help

[Mad1523]

Hey all,

From some of Govert's suggestions (below), I'm trying to implement an
ExcelComAddIn and I'm getting null reference errors:

"A first chance exception of type 'System.NullReferenceException'
occurred in ExcelDna.Integration"


Any ideas on how to properly implement and register a new com addin?
I'm using ExcelDna 0.29.


Govert:
"Make a class that derives from ExcelComAddIn and then call
ExcelComAddInHelper.LoadComAddIn(...) to load an instance. Override
OnDisconnection or OnBeginShutdown to be notified when Excel closes or
the add-in is removed."
"For Excel-DNA 0.29, any Excel version, add a new class that derived
from ExcelComAddin, load it in your AutoOpen with
ExcelComAddInHelper.LoadComAddIn(...), override the OnDisconnection or
OnBeginShutdown."

Code at (or below): https://gist.github.com/1079749


------------------------------------------------------------------
public class MyCom : ExcelDna.Integration.CustomUI.ExcelComAddIn
{
public MyCom()
{
}
public void OnConnection(object Application, ext_ConnectMode
ConnectMode, object AddInInst, ref Array custom)
{
}
public void OnDisconnection(ext_DisconnectMode RemoveMode, ref
Array custom)
{
}
public void OnAddInsUpdate(ref Array custom)
{
}
public void OnStartupComplete(ref Array custom)
{
}
public void OnBeginShutdown(ref Array custom)
{
}
}


public class AddIn : IExcelAddIn
{
private ExcelDna.Integration.CustomUI.ExcelComAddIn com_addin;

public AddIn()
{
}

public void AutoOpen()
{
com_addin = new MyCom();

ExcelDna.Integration.CustomUI.ExcelComAddInHelper.LoadComAddIn(com_addin);
}

public void AutoClose()
{
}

}
------------------------------------------------------------------

Thanks!!

[Govert van Drimmelen]

Hi,

Glad you tried it, and thanks for reporting the problem.
(Clearly I had not.)

The good news:
I found the problem: you need to set ExcelComAddIn.DnaLibrary
before you call LoadComAddIn(...).
The bad news:
ExcelComAddIn.DnaLibrary is marked internal, so you can't call it.
The good news:
You can call it via Reflection.

Also:
You need to mark the IDTExtensibility2 interface methods as
'override'
You need to be sure that your class is COM Visible.

I paste a .dna file that works for me below, and here: https://gist.github.com/1079888.

-Govert

<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Reflection;
using SWF = System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration.Extensibility;

public class MyCom : ExcelDna.Integration.CustomUI.ExcelComAddIn
{
public MyCom()
{
}

public override void OnConnection(object Application,

ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
{

SWF.MessageBox.Show("OnConnection");
}
public override void OnDisconnection(ext_DisconnectMode
RemoveMode, ref Array custom)
{
SWF.MessageBox.Show("OnDisconnection");
}
public override void OnAddInsUpdate(ref Array custom)
{
SWF.MessageBox.Show("OnAddInsUpdate");
}
public override void OnStartupComplete(ref Array custom)
{
SWF.MessageBox.Show("OnStartupComplete");
}
public override void OnBeginShutdown(ref Array custom)
{
SWF.MessageBox.Show("OnBeginShutDown");
}
}

public class AddIn : IExcelAddIn
{
private ExcelComAddIn com_addin;

public AddIn()
{
}

public void AutoOpen()
{

try
{
com_addin = new MyCom();
// We want to do this:
// com_addin.DnaLibrary =
ExcelDna.Integration.DnaLibrary.CurrentLibrary;
// But the DnaLibrary property is marked 'internal' to
ExcelDna.Integration.
// v0.29 workaround: set by Reflection
com_addin.GetType().InvokeMember("DnaLibrary",
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.SetProperty,
null, com_addin, new object[]
{DnaLibrary.CurrentLibrary});

ExcelComAddInHelper.LoadComAddIn(com_addin);
}
catch (Exception e)
{
SWF.MessageBox.Show("Error loading COM AddIn: " +
e.ToString());
}

}

public void AutoClose()
{
}
}
]]>
</DnaLibrary>

[Mad1523]

That works great! Thanks for the quick reply!

Here is the code in action for anyone that stumbles onto this thread:

http://groups.google.com/group/exceldna/browse_frm/thread/5b5e5948f46d402a#

[Mahendra Pal Verma Jaiswa]

Dear Govert,

Thanks for your reply. I was searching on the internet. I found a webpage. Somehow, I missed the address. There was explanation on this problem. I am sorry, I am unable to write the reference.

The listing is the following, which works.

Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports System.Math

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class Class1

'This is for automatically register the function in Excel
<ComUnregisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type))
End Sub

'This is for to unregister the function from Excel

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnRegisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type))
End Sub

Private Shared Function GetSubKeyName(ByVal type As Type) As String
Dim s1 As String
s1 = "CLSID\{" & type.GUID.ToString().ToUpper() & "}\Programmable"

Return s1.ToString()
End Function

' this is Excel custom formula "MyTestFormula"
Public Function myTestFormula(ByVal anyNumber As Double) As Double
Dim result As Double = anyNumber * 2
Return result
End Function

Public Function myFun(ByVal x1 As Double, ByVal x2 As Double) As Double
Dim myFuns As New MyFunctions

myFuns.GetX1 = x1
myFuns.GetX2 = x2

Return myFuns.Add

End Function
End Class

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class MyFunctions
Private x1 As Double
Public Property GetX1() As Double
Get
Return x1
End Get
Set(ByVal value As Double)
x1 = value
End Set
End Property

Dim X2 As Double
Public Property GetX2() As Double
Get
Return X2
End Get
Set(ByVal value As Double)
X2 = value
End Set
End Property

Public Function Add() As Double
Return Abs(x1 + X2)
End Function
End Class

Hopefully, this may be useful to others.

There are also some references which are talking about the new technology: Microsoft.Office.Excel.Server.Udf.dll for defining UDF. I have to still look on it.

Thanks for your help.

Mahendra

-----Mensaje original-----
De: exce...@googlegroups.com [mailto:exce...@googlegroups.com] En nombre de Govert van Drimmelen
Enviado el: miércoles, 13 de julio de 2011 02:41 a.m.
Para: Excel-DNA
Asunto: [ExcelDna] Re: ExcelComAddInHelper.LoadComAddIn Help

Hi,

-Govert

public AddIn()
{
}

}

public void AutoClose()
{
}
}
]]>
</DnaLibrary>

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Mahendra,

You have to be a bit careful with the sample you posted. That will
create a different kind of Excel add-in, called an Automation Add-In.
I first described how to make these with .NET here:
http://www.codeproject.com/KB/COM/excelnetauto.aspx.
These Automation Add-Ins do not use the Excel-DNA library at all. It
was after finding many shortcoming with this type of add-in that I
started the Excel-DNA project.

Some of the discussion in this thread can be confusing, since it
describes integrated COM add-ins, which are an advanced feature of
Excel-DNA and not the typical use if you are just exposing some
functions and macros to Excel.

For the problem you reported before, with the .dna file not being
found when using Excel-DNA, the solution is just to check the
filenames of the .xll and .dna files, as posted in the reply to your
question. (One problem might be that your Windows Explorer is hiding
the extensions of the files, so you might have Test.dna.txt displayed
as Test.dna.)

-Govert


On Jul 13, 9:34 pm, "Mahendra Pal Verma Jaiswa" <mahen...@iie.org.mx>
wrote:

[Mahendra Pal Verma Jaiswa]

Hi all,

In my last email I proposed a solution for my problem, the use the .NET class as a variable in creating a UDF in Excel. The solution works well on the computer on which the UDF was created. It creates a library "MSCOREE.DLL" for the UDF. I am looking for it (library) since last two days. I have no idea what is all this. If I could not find any solution for it before this Friday, I will quit programming in Excel. I am not an expert in programming. It is a tool for me to perform some mathematical calculations.

It is really amazing that the Microsoft can do such drastic changes. We all are working to resolve it instead of them (Microsoft). Even they cannot write an appropriate user manual for their products (software). To look something on their webpage is equivalent to the wastage time for users like me (or at least I found that). It was great hassle for me to change from VB6 to .NET. Of course there were many problems with VB6.

I am really sorry to propose the solution before investigating in detail.

Thanks for reading it and your help.

Mahendra

-----Mensaje original-----
De: exce...@googlegroups.com [mailto:exce...@googlegroups.com] En nombre de Mahendra Pal Verma Jaiswa
Enviado el: miércoles, 13 de julio de 2011 02:35 p.m.
Para: exce...@googlegroups.com
Asunto: RE: [ExcelDna] Re: ExcelComAddInHelper.LoadComAddIn Help

[Govert van Drimmelen]

Hi Mahendra,

The learning curve in moving to .NET can be quite steep in the
beginning, but I believe it is worth it.
It sounds like you have some VB6 code that you would like to move to
VB.NET, and make into an Excel add-in exposing the functions to Excel.

Doing this as an "Automation Add-Ins" (which is the latest attempt you
posted about) is one possibility. (The mscoree.dll file is one of
the .NET runtime files, not the file you made. It will installed with
the .NET installation. The message Excel shows about this file is an
Excel bug related to these "Automation Add-Ins".)

I suggest you follow another route - use the Excel-DNA library to glue
your VB.NET library and Excel together.
Excel-DNA is an independent project to allow you to create Excel add-
ins with .NET.

You were using Excel-DNA to make the add-in, when you had the problem
"This excel add-in requires an ‘<addin>.dna’ configuration file which
matched the ‘<addin>.dll’ file.".
At this point you were very close to having a running solution, there
was just a problem with some file names.
I suggest you get back to this point, and check that you have the
Test.xll file and Test.dna file correctly in place.

(Your functions 'myTestFormula' and 'myFun' would go back to being
'Shared' or in a Public Module, and you can remove all the
'RegisterFunction' stuff.)

Once this basic example works, I think progress will be good.
If you still have that error after reviewing the files you copied, and
making sure their true file name extensions are not hidden by Windows
Explorer, please post back.

Best regards,
Govert

On Jul 15, 1:47 am, "Mahendra Pal Verma Jaiswa" <mahen...@iie.org.mx>
wrote:

> For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.