JoelBlogs - Joel Jeffery's Microsoft 365 Blog

Microsoft 365, SharePoint, Teams and Office 365 Architecture, Development, Administration and Training

  • Home
    • Sitemap
  • Articles
    • #SPThingADay
    • SharePoint Online
      • SharePoint Online – Drag and Drop and Large File Uploads
    • SharePoint 2016
    • SharePoint 2013
      • Content Database Changes to the AllUserData Table
    • SharePoint 2010
      • Administration
        • Disable CRL Checking
        • Excel 2010 & PowerPivot
        • Limits & Thresholds
        • PeoplePicker AD Errors
        • Recycle Bin Behaviour
        • Renaming a Server
        • Service Pack 1
        • Unattended Installs
        • Uninstall All SharePoint 2010 Solutions via PowerShell
        • User Alert Management
        • Virtualised SharePoint
        • Visio Stencils for Administrators
      • Development
        • Audience Membership Workflow Activity
        • Base Types, Lists & Content Types
        • BCS & Offline Sync
        • Debugger Skipping Lines
        • Development Laptop Spec
        • Enabling JavaScript IntelliSense
        • Event Receivers & Deployment Jobs
        • FavIcons & SPUrl
        • Google Maps Sandbox Web Part
        • Group By Content Type for List Views
        • Locale Stapler / Master or Default Locale
        • Removing Default Editor Parts
        • Sandbox Embedding Resources
        • Solution Sandbox Introduction
        • SPPersistedObject
        • Restoring Deleted SPSites in SP1
        • SPWebConfigModification 1
        • SPWebConfigModification 2
        • STSADM copyappbincontent vs. Install-SPApplicationContent
        • Workflows for Beginners
        • Workflow InitiationData Seralizer
    • SharePoint 2007
      • Alternate Access Mappings
      • Excel Services
      • Excel Services UDFs & Excel Client 2007
      • Experiences from the Field
      • InfoPath & Forms Server
      • Kerberos & SSRS
      • Records Management
      • Web Application Service
      • WSS vs MOSS
  • Training
    • SharePoint Admin Links
  • Downloads
    • Summary Slides for PowerPoint
    • CodePlex Projects
      • Audience Membership Workflow Activity
      • Google Maps Sandbox Web Part
      • Group By Content Type in List Views
      • Locale Stapler / Master or Default Locale
      • SharePoint Outlook Connector
  • Hire Me!
    • MCP Transcript
    • Résumé/CV

WSS 3.0 and MOSS 2007 tips #5 – Popular Misconceptions – Excel Services UDFs and Excel Client 2007

March 18, 2009 by Joel Jeffery

Back in my first blog posting on WSS 3.0 and MOSS 2007, I discussed Excel Services and User Defined Functions. Since then I have had a number of requests for a follow up post. A common misconception from students in my SharePoint developer courses is this:

Popular Misconception #1: “I can’t test my UDFs in Excel! When I write a UDF class and method, my spreadsheet works on the server, but I get #NAME? showing up when I try to build my spreadsheet in Excel on my desktop.”

This is kind of true. A UDF is just a .NET assembly that has bee appropriately attributed to mark the Class and Methods as discoverable and usable by Excel Services. You deploy this to Excel Services by installing it in the GAC on the server and registering it in the Shared Service Provider (SSP) for Excel Services.

Excel Client 2007, on the other hand, does not know what to do with these .NET assemblies. You can try adding these .DLLs as Excel Add-Ins or COM Add-Ins and Excel will complain that your assembly is not a valid add-in.

Click to zoom! Not a valid Excel add-in!

So how do we work around this? Some say you can wrap your .NET assembly in a .XLL library written in C++. Some say there are third party wrappers that will do this for you at a premium.

You don’t need to go that far.

Let’s have a look at a simple (trivially so) UDF for Excel Services.

   1: namespace JoelsUDF
   2: {
   3: // Mark this class as an Excel Services UDF
   4:     [UdfClass]
   5: public class JoelsUDFClass
   6:     {
   7: #region UDF bits
   8: /// <summary>
   9: /// My very complex UDF method... 
  10: /// </summary>
  11: /// <param name="value">Some number.</param>
  12: /// <returns>Passed in value inc
remented by one.</returns>
  13: // Mark this method as an Excel Services UDF method
  14:         [UdfMethod]
  15: public double AddOne(double value)
  16:         {
  17: return value + 1;
  18:         }
  19: #endregion
  20:     }
  21: }

Very, very simply, this marks the class as a UdfClass, with a single UdfMethod that takes a double and returns the value incremented by one. This can be signed and deployed into Excel Services using the method described in my previous post on Excel Services and UDFs.

If I now want to deploy this same assembly into Excel Client 2007 as an add-in, I could add a few lines of COM registration code. Once again, don’t forget to sign your assembly!

   1: namespace JoelsUDF
   2: {
   3: // Mark this class as visible in COM
   4:     [ProgId(JoelsUDFClass.ProgID)]
   5:     [Guid(JoelsUDFClass.ClassID)]
   6:     [ClassInterface(ClassInterfaceType.AutoDual)]
   7:     [ComVisible(true)]
   8:
   9: // Mark this class as an Excel Services UDF
  10:     [UdfClass]
  11: public class JoelsUDFClass
  12:     {
  13: #region COM bits
  14: // COM ClassID (GUID) and ProgID (Type Name)
  15: const string ClassID = "81FCB6CB-E4F1-4728-A6A7-8BBA85D0B5EA";
  16: const string ProgID = "JoelsUDF.JoelsUDFClass";
  17:
  18: // Registers COM class in the registry
  19:         [ComRegisterFunction]
  20: public static void RegistrationMethod(Type type)
  21:         {
  22: if (typeof(JoelsUDFClass) == type)
  23:             {
  24:                 RegistryKey regKey = Registry.ClassesRoot.CreateSubKey(
  25: "CLSID\\{" + ClassID + "}\\Programmable");
  26:                 regKey.SetValue("", System.Environment.GetFolderPath(
  27:                     Environment.SpecialFolder.System) + @"\mscoree.dll");
  28:                 regKey.Close();
  29:             }
  30:         }
  31:
  32: // Unregisters COM class from the registry
  33:         [ComUnregisterFunction]
  34: public static void UnregistrationMethod(Type type)
  35:         {
  36: if (typeof(JoelsUDFClass) == type)
  37:                 Registry.ClassesRoot.DeleteSubKey("CLSID\\{" +
  38:                     ClassID + "}\\Programmable");
  39:         }
  40: #endregion
  41: #region UDF bits
  42: /// <summary>
  43: /// My very complex UDF method... 
  44: /// </summary>
  45: /// <param name="value">Some number.</param>
  46: /// <returns>Passed in value incremented by one.</returns>
  47: // Mark this method as an Excel Services UDF method
  48:         [UdfMethod]
  49: public double AddOne(double value)
  50:         {
  51: return value + 1;
  52:         }
  53: #endregion
  54:     }
  55: }

I’d then need to register the assembly with RegAsm.exe (from the %SystemRoot%\Microsoft.NET\Framework\v2.xxxx folder).

RegAsm.exe /codebase UDFExample.dll

Click to zoom! Running RegAsm from the command prompt.

Finally I’d add it to Excel as a Excel Automation add-in. Under Excel Client 2007, this is available in File –> Excel Options –> Add-Ins, then select Manage: Excel Add-ins.

Click to zoom! Managing Excel Add-ins in Excel 2007.

Next, select Automation and find your registered Add-in class.

Click to zoom! Automation Add-Ins in Excel 2007.

This *can* cause Excel to complain with the following error:

Click to zoom! Cannot find add-in 'mscoree.dll'. Delete from list?

It’s important that you say “no” to this.

Then you can start using your UDFs in Excel as if they were built in functions.

Click to zoom! Using my Excel Services UDF in Excel Client 2007.

If you’d like the Visual Studio 2008 solution for this example, you can download it here:

Filed Under: SharePoint Tagged With: Excel, Excel Add-ins, Excel Services, MOSS 2007, Office Server, SharePoint, SharePoint Architecture, UDF, User Defined Functions

Joel is a full-stack cloud architect who codes. He is a Microsoft Certified SharePoint Online, SharePoint Server and Azure specialist and Microsoft Certified Trainer.
He has over 20 years' experience with SharePoint and the Microsoft .NET Framework.
He's also co-founder of Microsoft Gold Partner JFDI Consulting Ltd. Read More…

Recent Posts

  • Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections
  • SharePoint PowerShell Tip #1 – Select-Object and FieldValues
  • Popular Misconceptions – Microsoft Teams relationship with SharePoint
  • Course: Microsoft 365 Certified Teamwork Administrator
  • Audience Targeted Searches in Modern SharePoint Online
MCT 2020-2021
Microsoft Teamwork Administrator Associate
Joel's Acclaim Profile
Joel's Microsoft Profile

Tags

Administration Architecture Certification Cloud Development freetraining Information Architecture intranets MCP Microsoft Microsoft Architecture Microsoft Azure microsoftsharepoint migration Mobile Development MOSS Office 365 office365 Office 365 Permissions PowerShell SaaS SharePoint SharePoint 2010 SharePoint 2010 Training SharePoint 2013 SharePoint Administration SharePoint Administrator SharePoint Architecture SharePoint Developer SharePoint Development sharepointia SharePoint Online sharepointonline SharePoint Search SharePoint Training SharePoint Videos Silverlight SOA SPThingADay TechEd 2007 Training Videos Windows Phone 7 WSS

Copyright © 2022 Joel Jeffery, SharePoint Architect