Issue with managed UDF

All Replies

• Wednesday, February 28, 2007 12:47 AMShahar Prish - MSFT

   
  

  0

  Sign In to Vote
  You also need to tell the client that your UDF is volatile. To do this, you need to implement the COM Addin extensibiluty interface on the UDF class. Once you do that, one of the arguments passed in to the Connect method will be the Excel Client Application object. You then need to use that object to call the Volatile() method. Once called, Excel will treat your UDF as volatile and will make sure it's always refreshed.
  • Reply
  • Quote

   
• Monday, March 05, 2007 9:08 AMPriya G

   
  

  0

  Sign In to Vote

  Hi

  Thanks for the response.

  I tried out the solution mentioned but my problem still persists. If the UDF calculations depend on the value of any cell in the worksheet, even though this cell is not referenced directly or indirectly in the function's argument list,marking the function as volatile ensures that the function is recalculated whenever any cell changes.

  My UDF simply displays a string on the function call.Marking the functions as volatile or calling the object volatile method does not ensure that the excel sheet is refreshed or updated with the updated UDF/dll as soon as the excel workbook loads.

  Is there an alternate solution to resolve this issue?

   

   

  • Reply
  • Quote

   
• Monday, March 05, 2007 5:03 PMShahar Prish - MSFT

   
  

  0

  Sign In to Vote

  Can you walk me through how you made the UDF volatile on the client? Just to make sure it was the correct solution?

  • Reply
  • Quote

   
• Tuesday, March 06, 2007 9:55 AMPriya G

   
  

  0

  Sign In to Vote

  Hi

  given below is the code snippet for what i have been trying.This ClientUDFSample.dll is deployed to the UDF Assembly trusted location in the SharePoint and enabled as well.It has also been added as an automation add-in to the same excel workbook which has the method call  =ShowMe().

   

  using System;

  using System.Collections.Generic;

  using System.Text;

  using Microsoft.Office.Excel.Server.Udf;

  using Microsoft.Win32;

  using System.Runtime.InteropServices;

  using Excel = Microsoft.Office.Interop.Excel;

  using System.Security.Principal;

  using System.IO;

  using Extensibility;

  namespace ClientUDFSample

  {

      [UdfClass]

      [Guid(XlUnlimitedUDFs.ClsId)] // Client Compat.

      [ProgId(XlUnlimitedUDFs.ProgId)] // Client Compat.

      [ClassInterface(ClassInterfaceType.AutoDual)] // Client Compat.

      [ComVisible(true)] // Client Compat.

      public class XlUnlimitedUDFs:Object, Extensibility.IDTExtensibility2

      {

          public XlUnlimitedUDFs() { }

         // BEGIN CLIENT COMPAT. SECTION //

         const string ClsId = "FE6C8D4D-3600-499a-9FA5-F8E252328CDD";

         const string ProgId = "ClientUDFSample.XlUnlimitedUDFs";

         private Excel.Application xl = null;

         private object addInInstance;

         public void OnConnection(object application,

         Extensibility.ext_ConnectMode connectMode,

         object addInInst, ref System.Array custom)

        {

               xl = (Excel.Application)application;

               addInInstance = addInInst;

         }

  public void OnDisconnection(

  Extensibility.ext_DisconnectMode disconnectMode,

  ref System.Array custom){}

  public void OnAddInsUpdate(ref Array custom){}

  public void OnStartupComplete(ref Array custom){}

  public void OnBeginShutdown(ref Array custom){}

  [ComRegisterFunction]

  public static void RegisterFunction(Type type)

  {

  if (typeof(XlUnlimitedUDFs) != type)

  {

  return;

  }

  RegistryKey key = Registry.ClassesRoot.CreateSubKey( @"CLSID\{" + ClsId + @"}\Programmable");

  key.Close();

  }

  [ComUnregisterFunction]

  public static void UnregisterFunction(Type type)

  {

  if (typeof(XlUnlimitedUDFs) != type)

  {

  return;

  }

  Registry.ClassesRoot.DeleteSubKey(@"CLSID\{" + ClsId + @"}\Programmable");

  } // END CLIENT COMPAT. SECTION // // BEGIN UDF SECTION // // END UDF SECTION//

  [UdfMethod(IsVolatile=true)]

  public string ShowMe()

  {

  return "UDF Testing";

  object missing = Type.Missing;

  xl.Volatile(missing);

  }

  }}

   

  As i mentioned in my previous post,once i have changed the return string of ShowMe() and rebuild the dll,the change is not reflected in the excel workbook when opened.

  • Reply
  • Quote

   
• Tuesday, March 06, 2007 6:44 PMShahar Prish - MSFT

   
  

  0

  Sign In to Vote

  Okay. That seems perfect.

  The Excel file needs to be saved at least once after being calculated with the new UDF (the volatile one).

  To do that, Ctrl-Shift-Alt-F9 and then save the file.

  To avoid rebuilding each time, you can create a function that returns DateTime.Now.ToString() - that way you can see if it updated w/o rebuilding the DLL.

  • Reply
  • Quote

   
• Monday, March 19, 2007 9:28 PMLuis Bitencourt-Emilio - MSFT

   
  

  0

  Sign In to Vote

  Did Shahar's reply work out for you?  If so could you mark it as the answer? Otherwise just let us know so we can try to troubleshoot some more.

  Thanks!

  • Reply
  • Quote