with VSTO Add-ins
It is not possible to expose worksheet functions directly from a VSTO add-in.
However you can create a separate DLL that the VSTO add-in can load that can expose worksheet functions.
This separate DLL needs to be a COM DLL that uses COM Interop to expose worksheet functions.
The registration of the worksheet functions is done automatically when the COM component is registered.
This automatic registration takes place because of the ComRegisterFunction and ComUnregisterFunction attributes.
link - csharpramblings.blogspot.com/2011/09/communicating-between-vsto-and-udfs-in.html
link - theofficecontext.com/2013/06/08/update-creating-excel-udfs-in-c/
Creating - VSTO Add-in
Open Visual Studio 2022 as Administrator.
New Project, Expand Office/SharePoint > Office Add-ins, Excel 2013 and 2016 VSTO add-in.
Change the Name to "ExcelVSTOFunctions".
Change the Location to somewhere on your C drive.
Check the .NET Framework version is correct and press Create.
All the necessary References should be added automatically.
Add the following code to the ThisAddIn.cs file.
namespace ExcelVSTOFunctions
{
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
System.Windows.Forms.MessageBox.Show("Startup");
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
System.Windows.Forms.MessageBox.Show("Shutdown");
}
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
}
}
Select (Build > Build > Solution).
At this point the necessary registry entries are automatically added to the registry.
Select (Debug > Start Debugging).
Excel will open and you will see the "Startup" message box appear.
If you display the COM Add-Ins dialog box you will see an entry for "ExcelVSTOFunctions".
Close Excel and you will see the "Shutdown" message box appear.
If you select (Build > Clean Solution) then the registry entries will be automatically removed from the registry.
Creating - COM DLL
(File > Add > New Project, Class Library (.NET Framework)
Change the Name to "ExcelAutomation"
Check the .NET Framework version is correct and press Create.
Add three references to this project, (Project > Add Reference).
(1) Assemblies, Extensions: "Extensibility"
(2) Assemblies, Extensions: "System.Windows.Forms"
(3) COM: "Microsoft Office 16.0 Object Library"
Add the following interface to the MyConnect.cs file.
namespace ExcelCOMAddin
{
[System.Runtime.InteropServices.Guid("5FA4884F-AA70-40C0-A4EC-F5390930B152")]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)]
[System.Runtime.InteropServices.ComVisible(true)]
public interface IMyConnect : Extensibility.IDTExtensibility2
{
new void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom);
new void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom);
new void OnAddInsUpdate(ref System.Array custom);
new void OnStartupComplete(ref System.Array custom);
new void OnBeginShutdown(ref System.Array custom);
}
Add the following class underneath the interface in the MyConnect.cs file.
[System.Runtime.InteropServices.ProgId("ExcelCOMAddin.MyConnect")]
[System.Runtime.InteropServices.ComDefaultInterface(typeof(IMyConnect))]
[System.Runtime.InteropServices.Guid("DE3911A1-C82F-42F1-BEFB-F3802E1B6D34")]
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
[System.Runtime.InteropServices.ComVisible(true)]
public class MyConnect : IMyConnect
{
Microsoft.Office.Interop.Excel.Application _ApplicationObject;
Microsoft.Office.Core.COMAddIn _AddinInstance;
public void OnConnection(
object Application,
Extensibility.ext_ConnectMode ConnectMode,
object AddInInst,
ref System.Array custom)
{
this._ApplicationObject = (Microsoft.Office.Interop.Excel.Application)Application;
if (this._AddinInstance == null)
{
this._AddinInstance = (Microsoft.Office.Core.COMAddIn)AddInInst;
this._AddinInstance.Object = this;
}
System.Windows.Forms.MessageBox.Show("onConnection");
}
public void OnDisconnection(
Extensibility.ext_DisconnectMode RemoveMode,
ref System.Array custom)
{
this._ApplicationObject = null;
this._AddinInstance = null;
}
public void OnAddInsUpdate(ref System.Array custom)
{ }
public void OnStartupComplete(ref System.Array custom)
{ }
public void OnBeginShutdown(ref System.Array custom)
{ }
}
}
Add the following code underneath the OnBeginShutdown method
[System.Runtime.InteropServices.Guid("")]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)]
[System.Runtime.InteropServices.ComVisible(true)]
public interface IBetterFunctions
{
string AddTwoNumbers(double value1, double value2);
}
[System.Runtime.InteropServices.Guid("")]
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
[System.Runtime.InteropServices.ComVisible(true)]
public class BetterFunctions : IBetterFunctions
{
public BetterFunctions()
{ }
public string AddTwoNumbers(double value1, double value2)
{
return System.Convert.ToString(value1 + value2);
}
[System.Runtime.InteropServices.ComRegisterFunction]
public static void Method_RegisterFunctions(System.Type type)
{
// (64 bit Office)
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\" + "Programmable");
// (32 bit Office)
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"\\Wow6432Node\\CLSID\\{" + type.GUID.ToString().ToUpper() +
"}\\" + "Programmable");
}
[System.Runtime.InteropServices.ComUnregisterFunction]
public static void Method_UnregisterFunctions(System.Type type)
{
// (64 bit Office)
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\" + "Programmable");
// (32 bit Office)
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"\\Wow6432Node\\CLSID\\{" + type.GUID.ToString().ToUpper() +
"}\\" + "Programmable");
}
}
}
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext