In a Microsoft Office Excel 2007 workbook, you use a managed-code user-defined function that is also a Microsoft Excel COM+ add-in. The workbook is published to a Microsoft Office SharePoint Server 2007 Web site. However, you receive different results on the server than on a client computer for the same workbook.

For example, the Excel 2007 client automatically converts a data type from a numeric data type to a string data type. However, this behavior does not occur on the server.

To work around this problem, test your managed-code user-defined function in a workbook that is on a client computer instead of a SharePoint Server 2007 server.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

For more information about how to develop a managed-code user-defined function, visit the following Microsoft Developer Network (MSDN) Web sites:

Steps to reproduce the problem

Create a managed-code DLL to use as an Excel COM+ add-in and as an Excel Services user-defined function

1. On a computer that has SharePoint Server 2007 installed, start Microsoft Visual Studio 2005.
2. On the File menu, click New, and then click Project.
3. Click Visual C#, click Class Library, type DataTypes in the Name box, and then click OK.
4. In Solution Explorer, right-click Class1.cs, and then click Rename.
5. Type RoundTripping.cs, and then press ENTER.
6. In the Microsoft Visual Studio dialog box, click Yes.
7. Replace the code that is in the RoundTripping.cs file with the following code example.

   using System;
   using System.Collections.Generic;
   using System.Text;
   using Microsoft.Office.Excel.Server.Udf;
   using System.Runtime.InteropServices;	// This code is used for client compatibility.
   using Microsoft.Win32;	// This code is used for client compatibility.
   
   namespace DataTypes
   {
       [Guid(RoundTripping.ClsId)]	// This code is used for client compatibility.
       [ProgId(RoundTripping.ProgId)]	// This code is used for client compatibility.
       [ClassInterface(ClassInterfaceType.AutoDual)]	// This code is used for client compatibility.
       [ComVisible(true)]	// This code is used for client compatibility.
       [UdfClass]
       public class RoundTripping
       {
           #region CLIENT COMPATIBILITY VARIABLES
   
           const string ClsId = "D08A3087-F858-4103-8C52-66B2D2890011";	// This code is used for client compatibility.
   		const string ProgId = "DataTypes.RoundTripping";	// This code is used for client compatibility.
   
           #endregion
           #region CLIENT COMPATIBILITY
   
           [ComRegisterFunction]	// This code is used for client compatibility.
           public static void RegistrationMethod(Type type)	// This code is used for client compatibility.
           {
               // Only add data to the registration 
               // if this class is the one that is being registered.
               if (typeof(RoundTripping) != type)
               {
                   return;
               }
               // Add "Programmable" under our key.
               RegistryKey key = Registry.ClassesRoot.CreateSubKey("CLSID\\{" + ClsId + "}\\Programmable");
               key.Close();
           }
   
           [ComUnregisterFunction]	// This code is used for client compatibility.
           public static void UnregisterationMethod(Type type)	// This code is used for client compatibility.
           {
               // Only add data to the registration 
               // if this class is the one that is being registered.
               if (typeof(RoundTripping) != type)
               {
                   return;
               }
               // Add "Programmable" under our key.
               Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + ClsId + "}\\Programmable");
           }
   
           #endregion
   
           [UdfMethod(IsVolatile = true)]
           public string ReturnStringAndString(string ArgIn)
           {
               return (ArgIn + ArgIn);
           }
   
   
           [UdfMethod(IsVolatile = true)]
           public bool ReturnNotBool(bool ArgIn)
           {
               return !ArgIn;
           }
   
           [UdfMethod(IsVolatile = true)]
           public double ReturnPIxDbl(double ArgIn)
           {
               return (Math.PI * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public Single ReturnPIxSgl(Single ArgIn)
           {
               return (Single)((Single)Math.PI * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public Int32 Return2xInt32(Int32 ArgIn)
           {
               return (2 * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public Int16 Return2xInt16(Int16 ArgIn)
           {
               return (Int16)((Int16)2 * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public UInt32 Return2xUInt32(UInt32 ArgIn)
           {
               return (2 * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public UInt16 Return2xUInt16(UInt16 ArgIn)
           {
               return (UInt16)((UInt16)2 * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public byte Return2xbyte(byte ArgIn)
           {
               return (byte)((byte)2 * ArgIn);
           }
   
           [UdfMethod(IsVolatile = true)]
           public sbyte Return2xsbyte(sbyte ArgIn)
           {
               return (sbyte)((sbyte)2 * ArgIn);
           }
   
   		[UdfMethod(IsVolatile = true)]
   		public Single ReturnSingleNaN()
   		{
   			return Single.NaN;
   		}
   
   		[UdfMethod(IsVolatile = true)]
   		public Single ReturnSinglePositiveInfinity()
   		{
   			return Single.PositiveInfinity;
   		}
   
   		[UdfMethod(IsVolatile = true)]
   		public Single ReturnSingleNegativeInfinity()
   		{
   			return Single.NegativeInfinity;
   		}
   
   		[UdfMethod(IsVolatile = true)]
   		public Double ReturnDoubleNaN()
   		{
   			return Double.NaN;
   		}
   
   		[UdfMethod(IsVolatile = true)]
   		public Double ReturnDoublePositiveInfinity()
   		{
   			return Double.PositiveInfinity;
   		}
   
   		[UdfMethod(IsVolatile = true)]
   		public Double ReturnDoubleNegativeInfinity()
   		{
   			return Double.NegativeInfinity;
   		}
   	}
   }
   

8. In Solution Explorer, right-click References, and then click Add Reference.
9. Click the .NET tab, click Excel Services UDF Framework, and then click OK.
10. On the Build menu, click Rebuild Solution.

Deploy the user-defined function on the SharePoint Server 2007 server

1. In the root folder of one of the drives on the SharePoint Server 2007 server, create a folder that is named UDFs.
2. Copy the DataTypes.dll file to the newly created folder.
3. Click Start, point to Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
4. Under Shared Services Administration, click SharedServicesName to view the Shared Services home page for that particular shared services provider.
5. Under Excel Services Settings, click User-defined function assemblies.
6. On the Excel Services User-Defined Functions Web page, click Add User-Defined Function. The Excel Services Add User-Defined Function Assembly Web page opens.
7. In the Assembly box, type DriveLetter:\UDFs\DataTypes.dll.
8. Under Assembly Location, click File path.
9. Under Enable Assembly, click to select the Assembly enabled check box, and then click OK.

Build and deploy an Excel 2007 workbook that uses the user-defined function

1. Start Excel 2007.
2. Select cell A1, and then copy the following text to the workbook:

   BlankCell				
   DoBlankCell	FALSE			
   MakeString	FALSE			
   ParameterValue	4			
   				
   				
   Signature	Parameter	Result		Sum()
   public string ReturnStringAndString(string ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=ReturnStringAndString(IF(DoBlankCell=TRUE,BlankCell, ReturnStringAndStringArg))		=SUM(ReturnStringAndStringArg)
   public bool ReturnNotBool(bool ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=ReturnNotBool(IF(DoBlankCell=TRUE,BlankCell, ReturnNotBoolArg))		=SUM(ReturnStringAndStringArg)
   public double ReturnPIxDbl(double ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=ReturnPIxDbl(IF(DoBlankCell=TRUE,BlankCell, ReturnPIxDblArg))		=SUM(ReturnStringAndStringArg)
   public Single ReturnPIxSgl(Single ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=ReturnPIxSgl(IF(DoBlankCell=TRUE,BlankCell, ReturnPIxSglArg))		=SUM(ReturnStringAndStringArg)
   public Int32 Return2xInt32(Int32 ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xInt32(IF(DoBlankCell=TRUE,BlankCell, Return2xInt32Arg))		=SUM(ReturnStringAndStringArg)
   public Int16 Return2xInt16(Int16 ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xInt16(IF(DoBlankCell=TRUE,BlankCell, Return2xInt16Arg))		=SUM(ReturnStringAndStringArg)
   public UInt32 Return2xUInt32(UInt32 ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xUInt32(IF(DoBlankCell=TRUE,BlankCell, Return2xUInt32Arg))		=SUM(ReturnStringAndStringArg)
   public UInt16 Return2xUInt16(UInt16 ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xUInt16(IF(DoBlankCell=TRUE,BlankCell, Return2xUInt16Arg))		=SUM(ReturnStringAndStringArg)
   public byte Return2xbyte(byte ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xbyte(IF(DoBlankCell=TRUE,BlankCell, Return2xbyteArg))		=SUM(ReturnStringAndStringArg)
   public sbyte Return2xsbyte(sbyte ArgIn)	=IF(MakeString=TRUE, TEXT(ParameterValue,"GENERAL"),ParameterValue)	=Return2xsbyte(IF(DoBlankCell=TRUE,BlankCell, Return2xsbyteArg))		=SUM(ReturnStringAndStringArg)
   public Single ReturnSingleNaN()		=IF(DoBlankCell=TRUE,ReturnSingleNaN(),ReturnSingleNaN())		
   public Single ReturnSinglePositiveInfinity()		=IF(DoBlankCell=TRUE,ReturnSinglePositiveInfinity(),ReturnSinglePositiveInfinity())		
   public Single ReturnSingleNegativeInfinity()		=IF(DoBlankCell=TRUE,ReturnSingleNegativeInfinity(),ReturnSingleNegativeInfinity())		
   public Double ReturnDoubleNaN()		=IF(DoBlankCell=TRUE,ReturnDoubleNaN(),ReturnDoubleNaN())		
   public Double ReturnDoublePositiveInfinity()		=IF(DoBlankCell=TRUE,ReturnDoublePositiveInfinity(),ReturnDoublePositiveInfinity())		
   public Double ReturnDoubleNegativeInfinity()		=IF(DoBlankCell=TRUE,ReturnDoubleNegativeInfinity(),ReturnDoubleNegativeInfinity())		
   

3. Right-click cell A2, and then click Name a Range.
4. In the Name box, type DoBlankCell, type =Sheet1!$B$2 in the Refers to box, and then click OK.
5. Right-click cell A3, and then click Name a Range.
6. In the Name box, type MakeString, type =Sheet1!$B$3 in the Refers to box, and then click OK.
7. Right-click cell A4, and then click Name a Range.
8. In the Name box, type ParameterValue, type =Sheet1!$B$4 in the Refers to box, and then click OK.
9. Click the Microsoft Office Button, click Publish, and then click Excel Services.
10. In the File name box, type http://ServerName/WebSiteName/Share Documents/Book1.xlsx.
11. Click Excel Services Options, and then click the Parameters tab.
12. Click Add, click to select the check boxes for DoBlankCell, click to select the check boxes for MakeString, click to select the check boxes for ParameterValue, and then click OK.
13. In the Excel Services Options dialog box, click OK.
14. In the Save As dialog box, click Save.