Shared Add-in's just don't work

S

strider

Following the example of http://support.microsoft.com/kb/302901/EN-US/
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.

To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.

After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)

I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).

I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.

THANKYOU in advace for any help!


Heres the mashed up source:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;

using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


namespace PricingLibraryAddIn
{

//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62


#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{


[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");

Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}

public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{

//start processing here asynchronously

return "Executing...";

}

/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}


private CommandBarButton MyButton;

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;

if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}

MessageBox.Show("Connected");

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;

}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{


CommandBars oCommandBars;
CommandBar oStandardBar;

try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}

// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
omissing, omissing);
MyButton.Caption = "My Custom Button";
MyButton.Style = MsoButtonStyle.msoButtonCaption;
}

// The following items are optional, but recommended.
//The Tag property lets you quickly find the control
//and helps MSO keep track of it when more than
//one application window is visible. The property is
required
//by some Office applications and should be provided.
MyButton.Tag = "My Custom Button";

// The OnAction property is optional but recommended.
//It should be set to the ProgID of the add-in, so that if
//the add-in is not loaded when a user presses the button,
//MSO loads the add-in automatically and then raises
//the Click event for the add-in to handle.
MyButton.OnAction = "!<PricingLibraryAddIn.Connect>";

MyButton.Visible = true;
MyButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);


object oName =
applicationObject.GetType().InvokeMember("Name",
BindingFlags.GetProperty, null, applicationObject, null);

// Display a simple message to show which application you
started in.
System.Windows.Forms.MessageBox.Show("This Addin is loaded
by " + oName.ToString(), "MyCOMAddin");
oStandardBar = null;
oCommandBars = null;
}
catch (Exception ex)
{

throw;
}
}

/// <summary>
/// Implements the OnBeginShutdown method of the
IDTExtensibility2 interface.
/// Receives notification that the host application is being
unloaded.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown(ref System.Array custom)
{
object omissing = System.Reflection.Missing.Value;
System.Windows.Forms.MessageBox.Show("MyCOMAddin Add-in is
unloading.");
MyButton.Delete(omissing);
MyButton = null;

}

private void MyButton_Click(CommandBarButton cmdBarbutton, ref
bool cancel)
{
System.Windows.Forms.MessageBox.Show("MyButton was
Clicked", "Pricing Library AddIn");
}


private object applicationObject;
private object addInInstance;


}
}
 
A

Andrew Wiles

Did you ever find a solution to this problem?

I have a similar issue wth exposing Excel functions.


Following the example of http://support.microsoft.com/kb/302901/EN-US/
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.

To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.

After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)

I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).

I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.

THANKYOU in advace for any help!


Heres the mashed up source:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;

using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


namespace PricingLibraryAddIn
{

//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62


#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{


[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");

Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}

public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{

//start processing here asynchronously

return "Executing...";

}

/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}


private CommandBarButton MyButton;

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;

if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}

MessageBox.Show("Connected");

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;

}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{


CommandBars oCommandBars;
CommandBar oStandardBar;

try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}

// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
omissing, omissing);
MyButton.Caption = "My Custom Button";
MyButton.Style = MsoButtonStyle.msoButtonCaption;
}

// The following items are optional, but recommended.
//The Tag property lets you quickly find the control
//and helps MSO keep track of it when more than
//one application window is visible. The property is
required
//by some Office applications and should be provided.
MyButton.Tag = "My Custom Button";

// The OnAction property is optional but recommended.
//It should be set to the ProgID of the add-in, so that if
//the add-in is not loaded when a user presses the button,
//MSO loads the add-in automatically and then raises
//the Click event for the add-in to handle.
MyButton.OnAction = "!<PricingLibraryAddIn.Connect>";

MyButton.Visible = true;
MyButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);


object oName =
applicationObject.GetType().InvokeMember("Name",
BindingFlags.GetProperty, null, applicationObject, null);
 
S

Simon Murphy

Andrew/strider
Are you wanting a COM addin (command type functionality) or an
automation add-in (Worksheet function stuff)?

If its the latter there is an example project here:
http://www.codematic.net/Excel-development/Excel-Visual-studio/visual-studio-excel.htm
about 1/4 of the way down (its C# VS2003)

Worksheet functions need to registered differently to Command stuff.
(And they need Excel 2002 or later) (and they run sloooow btw).

If it is a COM add-in you are after post back, I've a couple installed
no bother in my Excel.

Cheers
Simon
Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training


Andrew said:
Did you ever find a solution to this problem?

I have a similar issue wth exposing Excel functions.


Following the example of http://support.microsoft.com/kb/302901/EN-US/
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.

To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.

After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)

I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).

I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.

THANKYOU in advace for any help!


Heres the mashed up source:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;

using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


namespace PricingLibraryAddIn
{

//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62


#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{


[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");

Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}

public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{

//start processing here asynchronously

return "Executing...";

}

/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}


private CommandBarButton MyButton;

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;

if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}

MessageBox.Show("Connected");

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;

}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{


CommandBars oCommandBars;
CommandBar oStandardBar;

try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}

// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
omissing, omissing);
MyButton.Caption = "My Custom Button";
MyButton.Style = MsoButtonStyle.msoButtonCaption;
}

// The following items are optional, but recommended.
//The Tag property lets you quickly find the control
//and helps MSO keep track of it when more than
//one application window is visible. The property is
required
//by some Office applications and should be provided.
MyButton.Tag = "My Custom Button";

// The OnAction property is optional but recommended.
//It should be set to the ProgID of the add-in, so that if
//the add-in is not loaded when a user presses the button,
//MSO loads the add-in automatically and then raises
//the Click event for the add-in to handle.
MyButton.OnAction = "!<PricingLibraryAddIn.Connect>";

MyButton.Visible = true;
MyButton.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);


object oName =
applicationObject.GetType().InvokeMember("Name",
BindingFlags.GetProperty, null, applicationObject, null);
 
A

Andrew Wiles

Simon

Thanks. Since posting I have succeeded in getting an automation add-in to
work by adding the IDTExtensibility2 interfaces manually so that I can access
the Excel instance. For some reason starting with the Visual Studio shared
addin project (which nominally does the same thing) would not work.

What I would REALLY like to do is to expose the user defined functions from
within our VSTO project in order to keep the project complexity under
control. The core of our solution is a VSTO app and the communication between
the user defined functions and VSTO app content is currently implemented
using remoting (which will also have its own performance impact).

Looking at the content of the article you have linked to it seems we ought
to do some performance review! Fortunately our functions take simple
parameters but it does look like we may need to consider re-working this code
into C++


Simon Murphy said:
Andrew/strider
Are you wanting a COM addin (command type functionality) or an
automation add-in (Worksheet function stuff)?

If its the latter there is an example project here:
http://www.codematic.net/Excel-development/Excel-Visual-studio/visual-studio-excel.htm
about 1/4 of the way down (its C# VS2003)

Worksheet functions need to registered differently to Command stuff.
(And they need Excel 2002 or later) (and they run sloooow btw).

If it is a COM add-in you are after post back, I've a couple installed
no bother in my Excel.

Cheers
Simon
Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training


Andrew said:
Did you ever find a solution to this problem?

I have a similar issue wth exposing Excel functions.


Following the example of http://support.microsoft.com/kb/302901/EN-US/
and putting a couple of days effort into it, i just give up. I can not
get the vanilla Excel shared add-in to work.

To get it to load i followed the directions of this sample
http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx and
sort of mashed the two examples together.

After adding the (Un)RegisterFunction() bits, the addin would load,
create an interactive toolbar, but it wouldn't run the Tax function
(shared or otherwise)

I'm at my wits end, the problem I am trying to overcome is this. I
have a whole library of financial functions written in c# we need to
expose to accountants via Excel. They should be able to wire up the
the inputs and get a result in real time. (either a single value or an
array).

I've seen this done VERY sucessfuly in C++ by exposing an interface
for COM Interop and then having a base workbook that does a DLL import
and has a DECLARE for each function. I just want to do this smoothly
and integrate with all the code we already have.

THANKYOU in advace for any help!


Heres the mashed up source:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using System.Reflection;

using Microsoft.Office.Core;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;


namespace PricingLibraryAddIn
{

//http://msdn2.microsoft.com/en-us/library/ms173189(VS.80).aspx
//http://support.microsoft.com/kb/908002/en-us
//http://groups.google.com/group/
microsoft.public.office.developer.automation/browse_thread/thread/
f5d903069a4d5890/e348a98d45515a62#e348a98d45515a62


#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons
such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you
wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
PricingLibraryAddInSetup project,
// right click the project in the Solution Explorer, then choose
install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("7C84EB93-E185-4327-B6F4-62763E2C8A8A"),
ProgId("PricingLibraryAddIn.Connect"),
ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Connect : Object, Extensibility.IDTExtensibility2
{


[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");

Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("", @"C:\Windows\System32\mscoree.dll");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\
\Programmable");
}

public string ExecuteQuery(System.String strQuery, [Optional]
object optionalServerIp, [Optional] object optionalPort, [Optional]
object optionalPollInterval)
{

//start processing here asynchronously

return "Executing...";

}

/// <summary>
/// Test function
/// </summary>
/// <param name="income"></param>
/// <returns></returns>
[ComVisible(true)]
public static double Tax(double income)
{
if (income > 0 && income <= 7000) { return (.10 *
income); }
if (income > 7000 && income <= 28400) { return 700.00 + (.
15 * (income - 7000)); }
if (income > 28400 && income <= 68800) { return 3910.00 +
(.25 * (income - 28400)); }
if (income > 68800 && income <= 143500) { return 14010.00
+ (.28 * (income - 68800)); }
if (income > 143500 && income <= 311950) { return 34926.00
+ (.33 * (income - 143500)); }
if (income > 311950) { return 90514.50 + (.35 * (income -
311950)); }
return 0;
}


private CommandBarButton MyButton;

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref
System.Array custom)
{
applicationObject = application;
addInInstance = addInInst;

if (connectMode !=
Extensibility.ext_ConnectMode.ext_cm_Startup)
{
OnStartupComplete(ref custom);
}

MessageBox.Show("Connected");

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
if (disconnectMode !=
Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
{
OnBeginShutdown(ref custom);
}
applicationObject = null;

}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate(ref System.Array custom)
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete(ref System.Array custom)
{
try
{


CommandBars oCommandBars;
CommandBar oStandardBar;

try
{
oCommandBars =
(CommandBars)applicationObject.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
// Outlook has the CommandBars collection on the
Explorer object.
object oActiveExplorer;
oActiveExplorer =
applicationObject.GetType().InvokeMember("ActiveExplorer",
BindingFlags.GetProperty, null, applicationObject, null);
oCommandBars =
(CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, oActiveExplorer, null);
}

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Standard"];
}
catch (Exception)
{
// Access names its main toolbar Database.
oStandardBar = oCommandBars["Database"];
}

// In case the button was not deleted, use the exiting
one.
try
{
MyButton = (CommandBarButton)oStandardBar.Controls["My
Custom Button"];
}
catch (Exception)
{
object omissing = System.Reflection.Missing.Value;
MyButton =
(CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing,
 

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