欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久

全部回復

• 2009年11月4日 15:59

  

  Cindy MeisterMVP, 版主

  

  

  

  

  

   
  

  0

  登錄進(jìn)行投票
  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
  • 
    回復
  • 
    引用

   
• 2009年11月4日 17:07

  

  incre-d

  

  

  

  

  

   
  

  0

  登錄進(jìn)行投票

  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

  • 
    回復
  • 
    引用

   
• 2009年11月5日 12:57

  

  LozT

  

  

  

  

  

   

  
  

  0

  登錄進(jìn)行投票
  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.
  • 
    回復
  • 
    引用

   
• 2009年11月5日 14:38

  

  incre-d

  

  

  

  

  

   
  

  0

  登錄進(jìn)行投票
  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.
  • 
    回復
  • 
    引用

   
• 2009年11月5日 15:24

  

  LozT

  

  

  

  

  

   

  
  

  0

  登錄進(jìn)行投票
  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();    }    }    }    

  • 
    回復
  • 
    引用

   
• 2009年11月6日 10:50

  

  incre-d

  

  

  

  

  

   

  

  
  

  1

  登錄進(jìn)行投票

  
  
  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
  •  
  • 
    回復
  • 
    引用

   
• 2009年11月6日 13:01

  

  LozT

  

  

  

  

  

   
  

  0

  登錄進(jìn)行投票
  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.msdn.com/pstubbs/archive/2004/12/31/344964.aspx which I found very useful) gets round this problem.
  
  Thanks for all your help.
  
  
  • 
    回復
  • 
    引用

   
• 2009年11月9日 8:13

  

  Tim LiMSFT, 版主

  

  

  

  

  

   
  

  0

  登錄進(jìn)行投票
  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.
  • 
    回復
  • 
    引用