Create User Defined Function that changes the structure of an excel worksheet
-
I am trying to develop an AddIn for Excel using VSTO (in VS2008). One aspect of the AddIn is to define various "User Defined Functions".
These functions essentially map to SQL statements which query a database and should dump the corresponding data in to the current excel spreadsheet (say in a Range where the top left hand corner is the cell below that in which the UDF has been entered).
I am well aware that what I have described above is not possible (although I would be very happy to hear otherwise) - a User Defined Function can only affect the cell in which occupies, and dumping the data within cells outside the given one would be prohibited by this.
I wondered whether there was any trick I could employ to mimic the above behaviour. For example, is there an event which is fired upon the execution of a User Defined Function which I could catch (perhaps with details of the UDF the user has executed)? Then I could hope to fire an appropriate function (defined in the AddIn code) which would be able to manipulate the cells of the spreadsheet.
I have tried catching the various events that seem to be fired after a User Defined Function is calcuated but have had no luck so far. Any help would be greatly appreciated.
答案
-
Hi, in the interest of keeping this brief, I've written a working example.
The VBA needs to be in a workbook that you open once your addin has been loaded, hence the theApp.WorkbookOpen event registration.
This code is not by any means robust, and only serves to help with showing how to use the VSTO. It lies to you to handle all errors correctly.
These techniques are all described in various blogs by authorities. I think Andrew Whitechapel has a blog on exposing com into VBA like this.
Lastly, this is only one technique, it's probably not the best, and it most certainly can be better written, I've just thrown the bits together to give you an example.
Regards
Some VBA
Option Explicit
Public ComObject As Object
Public Sub RegisterAddinCom(theComExposed As Object)
Set ComObject = theComExposed
End Sub
Public Function ReturnTwo(ByRef theDest As Range)
ReturnTwo = ComObject.ReturnTwo(theDest)
End Function
RegisterInterest.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Diagnostics;
namespace ExamplesAndTestingExcelAddin
{
class RegisteredItem
{
public Excel.Range TheRange { get; set; }
public object TheData { get; set; }
}
public class RegisterInterest
{
private static int iter;
private Dictionary<string, RegisteredItem> _RegisteredItems;
private Timer _Timer;
public RegisterInterest()
{
iter = 0;
_RegisteredItems = new Dictionary<string, RegisteredItem>();
_Timer = new Timer();
_Timer.Interval = Convert.ToInt16(TimeSpan.FromSeconds(10).TotalMilliseconds);
_Timer.Start();
_Timer.Tick += new EventHandler(_Timer_Tick);
}
void _Timer_Tick(object sender, EventArgs e)
{
foreach (var RegisteredItems in _RegisteredItems)
{
try
{
RegisteredItems.Value.TheRange.Value2 = RegisteredItems.Value.TheData + " - " + iter.ToString();
}
catch (Exception ex)
{
Debug.Print(ex.ToString());
}
iter++;
Debug.Print(RegisteredItems.Value.TheData + " - " + iter.ToString());
}
}
public void Add(Excel.Range rng,object data)
{
RegisteredItem regItem;
string newAddress = rng.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();
if (!_RegisteredItems.TryGetValue(newAddress, out regItem))
{
_RegisteredItems.Add(newAddress, new RegisteredItem() { TheData = data, TheRange = rng });
}
}
}
}
ComExposed.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel.Extensions;
namespace ExamplesAndTestingExcelAddin
{
[ComVisible(true)]
public class ComExposed
{
private RegisterInterest _RegisteredInterest;
public ComExposed()
{
_RegisteredInterest = new RegisterInterest();
}
public string ReturnTwo(Excel.Range theDest)
{
_RegisteredInterest.Add(theDest,(string)theDest.Value2);
return "Success";
}
}
}
ThisAddin.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel.Extensions;
using Office = Microsoft.Office.Core;
namespace ExamplesAndTestingExcelAddin
{
public partial class ThisAddIn
{
private ComExposed _ComExposed;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
_ComExposed = new ComExposed();
Excel.Application theApp;
theApp = this.Application as Excel.Application;
theApp.WorkbookOpen += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(theApp_WorkbookOpen);
}
void theApp_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
{
Excel.Application theApp;
theApp = this.Application as Excel.Application;
theApp.Run("RegisterAddinCom", _ComExposed, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
}
void theApp_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
- 已標記為答案LozT 2009年11月6日 12:56
-
全部回復
-
Hi Loz UDFs aren't really part of VSTO; there are other venues where you could probably get a more detailed and lively discussion on the topic. You'll find a couple listed in the forum's Please Read First message. If a function should write data to more than one cell, then it must be entered by the user as an "array formula". And the UDF should return an array (of data type Object). For more information on array formulas, search that term in the Excel Help files (for end users). You'll find some examples and discussions here, in the forum. Here's a set of search results that pick up some of those.
Cindy Meister, VSTO/Word MVP
-
A better method is to use Button's to fire off the population of the data to the sheet.
However addins like bloomberg and reuters both do what you are describing.
A trick, would be to include the destination cell of the datarange in your formula, and then on a timed event populate that range with your data after the udf has completed because excel is single threaded, You will then use your vsto to capture the address which you want to populate, and in a ontime event you could populate the range
-
Thanks to you both for your quick replies. Cindy Meister - you are completely right - this is not really a UDF question but more one of how one can trigger a VSTO method from a UTF. Incre-D - I really like your idea about using timed events to do this, however I am a bit confused as to how I would implement it: Firstly, let's assume that I have written my UDF (in c#) that takes in a range, and I have a populated this range using data from my database. How do I then trigger an event (whose arguments contain this range) that will be picked up by VSTO? The code for my UDF and for my Add-In are in completely differerent classes as I can't defined a UDF in a managed VSTO Excel Addin). I have tried to create a GlobalEvent class (which must be static because I want the event to persist over all instances of the AddIn and the UDF classes), adding a delegate in the AddIn class and raising the event in the UDF class. My code is as below: My AddInCode (minus AutoGeneratedBits):
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
GlobalEvent.TimeComplete += new GlobalEvent.GlobalEventDelegate(GlobalTimer_TimeComplete);
}
void GlobalTimer_TimeComplete(string message)
{
MessageBox.Show("You've done it");
}
}
<br/><br/><br/><br/><br/><br/>
The GlobalEventClass:
public static class GlobalEvent
{
public delegate void GlobalEventDelegate(string message);
public static event GlobalEventDelegate TimeComplete;
public static void SetEvent(string message)
{
TimeComplete(message);
}
}
...and Finally the class containing the UDFS:
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Functions
{
public double ReturnTwo()
{
GlobalEvent.SetEvent("me");
return 2;
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
When I debug the code, the delegate seems to register with the event TimeComplete on the AddIn start up. However, when I run the UDF ReturnTwo() the debugger is telling me that TimeComplete is "null". I must be missing something crucial here as I don't understand how after I have set the event delegate (in the AddIn startup) it has been reset to null.
-
OK,
I probably explained it badly. And please remember, this is just an idea. There are probably proper patterns to deal with this.
let's take a UDF example
b2: =ReturnData(b3)
this will return "Fail" or "Success"
As part of ReturnData's method, the range b3 will be registered in something like a List<<DataSet,Excel.Range>> RegisteredRanges.
You will then have an addin which uses an ontime event and calls a method named something like PopulateRegisteredRanges
PopulateRegisteredRanges populates the Data in the DataSet to the Excel.Range. Either the DataSet is refreshed during the call to PopulateRegisteredRanges, or it was populated by some other thread.
Remembering that excel is single threaded, so you use the OnTime to run the method at it's earliest convenience.
I'd worry about getting that working before trying to get a global TimeComplete firing.
-
Thanks for your prompt reply Incr-D. I fear that in doing what you suggest I run in to exactly the same problem as I did with the Event firing: I have added a static member RegisteredRanges to ThisAddIn. In the UDF ReturnTwo() I add an item to this list. Because the cell has changed the SheetChange event is fired and caught by the VSTO. When I debug this, despite adding an item to the list in the UDF, when on the SheetChange Event we have RegisteredRanges.Count = 0! I can only conclude that the code for the UDF and the code for the addin are running on completely different processes (although I don't understand this as Excel is single threaded).
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel.Extensions;
using System.Windows.Forms;
using Data = System.Data;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Diagnostics;
using System.IO;
namespace QuantAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public partial class ThisAddIn
{
public static List<string> RegisteredRanges = new List<string>();
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Application.ActiveWorkbook.SheetChange += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetChangeEventHandler(ActiveWorkbook_SheetChange);
}
void ActiveWorkbook_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Functions
{
public double ReturnTwo()
{
ThisAddIn.RegisteredRanges.Add("string");
return 2;
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
}
-
Hi, in the interest of keeping this brief, I've written a working example.
The VBA needs to be in a workbook that you open once your addin has been loaded, hence the theApp.WorkbookOpen event registration.
This code is not by any means robust, and only serves to help with showing how to use the VSTO. It lies to you to handle all errors correctly.
These techniques are all described in various blogs by authorities. I think Andrew Whitechapel has a blog on exposing com into VBA like this.
Lastly, this is only one technique, it's probably not the best, and it most certainly can be better written, I've just thrown the bits together to give you an example.
Regards
Some VBA
Option Explicit
Public ComObject As Object
Public Sub RegisterAddinCom(theComExposed As Object)
Set ComObject = theComExposed
End Sub
Public Function ReturnTwo(ByRef theDest As Range)
ReturnTwo = ComObject.ReturnTwo(theDest)
End Function
RegisterInterest.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Diagnostics;
namespace ExamplesAndTestingExcelAddin
{
class RegisteredItem
{
public Excel.Range TheRange { get; set; }
public object TheData { get; set; }
}
public class RegisterInterest
{
private static int iter;
private Dictionary<string, RegisteredItem> _RegisteredItems;
private Timer _Timer;
public RegisterInterest()
{
iter = 0;
_RegisteredItems = new Dictionary<string, RegisteredItem>();
_Timer = new Timer();
_Timer.Interval = Convert.ToInt16(TimeSpan.FromSeconds(10).TotalMilliseconds);
_Timer.Start();
_Timer.Tick += new EventHandler(_Timer_Tick);
}
void _Timer_Tick(object sender, EventArgs e)
{
foreach (var RegisteredItems in _RegisteredItems)
{
try
{
RegisteredItems.Value.TheRange.Value2 = RegisteredItems.Value.TheData + " - " + iter.ToString();
}
catch (Exception ex)
{
Debug.Print(ex.ToString());
}
iter++;
Debug.Print(RegisteredItems.Value.TheData + " - " + iter.ToString());
}
}
public void Add(Excel.Range rng,object data)
{
RegisteredItem regItem;
string newAddress = rng.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();
if (!_RegisteredItems.TryGetValue(newAddress, out regItem))
{
_RegisteredItems.Add(newAddress, new RegisteredItem() { TheData = data, TheRange = rng });
}
}
}
}
ComExposed.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel.Extensions;
namespace ExamplesAndTestingExcelAddin
{
[ComVisible(true)]
public class ComExposed
{
private RegisterInterest _RegisteredInterest;
public ComExposed()
{
_RegisteredInterest = new RegisterInterest();
}
public string ReturnTwo(Excel.Range theDest)
{
_RegisteredInterest.Add(theDest,(string)theDest.Value2);
return "Success";
}
}
}
ThisAddin.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel.Extensions;
using Office = Microsoft.Office.Core;
namespace ExamplesAndTestingExcelAddin
{
public partial class ThisAddIn
{
private ComExposed _ComExposed;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
_ComExposed = new ComExposed();
Excel.Application theApp;
theApp = this.Application as Excel.Application;
theApp.WorkbookOpen += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(theApp_WorkbookOpen);
}
void theApp_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook Wb)
{
Excel.Application theApp;
theApp = this.Application as Excel.Application;
theApp.Run("RegisterAddinCom", _ComExposed, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
}
void theApp_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
- 已標記為答案LozT 2009年11月6日 12:56
-
-
Hi Incre-D, Thank you for taking the time to give such a complete solution. The code you have just presented is incredibly similar to the working code I have just managed to make work. My previous code was not working as I did now know which instance of the UDF code was registerd with the Addin, but the VBA code you supply (very similar to that found on http://blogs./pstubbs/archive/2004/12/31/344964.aspx which I found very useful) gets round this problem. Thanks for all your help.
-
Glad to hear it works! Thanks Incre-D.
Tim Li
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
|
