How to create UDF in Excel using C# and Visual Studio 2008

[pch]

Tysken, if your locale is not English, and especially your Excel's language is different from your Windows, you need to be careful about all operations that require data type conversion either explicitly or implicitly.

Try Andy's example to see whether it works. If not, try the following:

public object SumOfTwoNumbers(Excel.Range NumberOne)
{
   try
   {
     double d = Double.Parse(NumberOne.Text, System.Globalization.CultureInfo.InvariantCulture); // or some variant depending on your locale
    return d+1;
  }
  catch(Exception err_)
  {
     return err_.ToString();
   }
}

If this works, I will be pretty sure your issue is caused by locale mismatch. The best way is to get a matching Windows and Excel or adjusting your regional settings in Control Panel. Alternatively, search MSDN for suggestions. For example, you may need some code like the following: MyExcelAppInstance = (MsExcel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(MsExcel.Application), MyExcelAppInstance);

Congratulation zhouxing for the diagnosis : I faced the same problem as reported by Tysken, applied the suggestion you made, with a minor change in the code for the conversion of the input which caused an error of syntax :

double d = Convert.ToDouble(NumberOne.Value2);

and received a very long message as error code, with a reference to an old format or invalid library.

The problem seems to arise when an English version of Excel is used with different regional settings. The solutions are described here.

.NET4Office : How VSTO solves the Excel LCID or Locale issue in the June CTP build

Thanks zhouxing.

And obviously thanks to Andy for the tutorial, which is outstanding !!!

 

[Ken Abbott]

I tried the tutorial using VS 2010 Express C# and Excel 2010, and no luck with it.

Do you know something about it?

 

[Daniel Duffy]

What were the problems? Is it a worksheet function you are creating?

 

[giacomitti]

I have done all the steps of tutorial, the project build is fine.

In Excel 2010 I do:

File->Options -> Add-Ins -> Manage: Excel Add-ins [Go...] -> Automation

My function is not listed here, so I try to:
Browse->Directory->BlackScholes.dll

I got:
The file you selected does not contain a new Automation Server, or you do not have sufficient privileges to register the Automation Server.

By the way, I am running as a administrator.

Any idea?

 

[Andy Nguyen]

I'll see if I can get my hands on VS 2010 and Office 2010 to test my code again.

 

[giacomitti]

Thank you! I will wait!
Just to remember I am using Visual Studio Express 2010.

Regards,
Arnaldo.

 

[Daniel Duffy]

I have not done it yet for Excel 2010 but for Excel 2007/2003, Anyway, maybe this helps (if not it is a bug in Excel??)

BTW did you look in the Registry using OLEVIEW and check ProgID? I vaguely remember something similar unless COMVisibke == true.
hth


[ComVisible(true)] // Makes the class visible in COM regardless of the assembly COM visible attribute.
[ProgId("DatasimAddIns.CalculatorV1")] // Explicit ProgID.
[Guid("20A394D6-E63F-422a-8308-4776D5602A66")] // Explicit GUID.
[ClassInterface(ClassInterfaceType.AutoDual)] // Automation add-ins need a dual interface.

publicclassCalculator: ProgrammableBase // The ProgrammableBase class must be COM visible too with the AutoDual option.
{
///<SUMMARY>
/// Add two numbers.
///</SUMMARY>

///
The first number.</PARAM>

///
The second number.</PARAM>
///<RETURNS>The result of the addition.</RETURNS>
publicdouble MyAdd(double v1, double v2)
{
return v1+v2;
}
// etc.
}


AND

using System;
using System.Runtime.InteropServices;
namespace Datasim
{

[ComVisible(true)] // Must be COM visible when derived class uses "AutoDual" option.
[ClassInterface(ClassInterfaceType.None)] // This class doesn't need an interface.
publicclassProgrammableBase
{

[ComRegisterFunction()]
publicstaticvoid RegisterFunction(Type t)
{
// Create the "Programmable" sub key.
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(GetSubKeyName(t, "Programmable"));
}

[ComUnregisterFunction()]
publicstaticvoid UnregisterFunction(Type t)
{
// Delete the "Programmable" sub key.
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(t, "Programmable"));
}

privatestaticstring GetSubKeyName(Type t, string subKeyName)
{
returnString.Format("CLSID\\{{{0}}}\\{1}", t.GUID.ToString().ToUpper(), subKeyName);
}
}
}

 

[giacomitti]

The same problem.

I am using a x64 system, do you think it can be a problem?

 

[Daniel Duffy]

I don't know, I have 32 bit machine..

BTW in project settings (Build) did you check-box for "COM Interop"

What does MSN say?

Does it work on 32 bit for you?

 

[giacomitti]

Yes, I checked "Registered for COM interop".

I just have a x64 machine here, I will test tomorrow with Excel 2007 and a x32 system.
After that I will post the result here.

 

[Daniel Duffy]

Yes, I checked "Registered for COM interop".

I just have a x64 machine here, I will test tomorrow with Excel 2007 and a x32 system.
After that I will post the result here.

Any news on this version?

 

[Mugiwara]

hi, i'm currently student in engineering school and i have to do a project on option pricing,
i followed the tutorials but l've a problem
everything goes well until i test functions, excel didn't recognize them but the addin is installed
when i search functions i can see :
"Unable to process your question. Either Microsoft Excel can not find an equivalent function, or help is not installed"
i tried another way by using xlw, i followed the video on youtube and when i click on extract xlw xll template i can't choose C# but only C++
i'm on window 7, visual C# 2010 express and excel 2007

Please anyone can help me?

 

[Tsotne]

i tried another way by using xlw, i followed the video on youtube and when i click on extract xlw xll template i can't choose C# but only C++

It is logical, you shouldn't be able to have C# in options provided.

 

[Tsotne]

BTW you'd better want to translate the code into C++. You'll achieve much better performance and also much more interaction with excel 2007 rather than with C#.

 

[Tsotne]

It is logical, you shouldn't be able to have C# in options provided.

oops sorry. I think I misunderstood. Do you have a any method collection file to add to add-ins? Or a single method reuse?

 

[AlexesDad]

i tried another way by using xlw, i followed the video on youtube and when i click on extract xlw xll template i can't choose C# but only C++
i'm on window 7, visual C# 2010 express and excel 2007

Please anyone can help me?

Hi,

Which version of xlw are you using ? I have only just commenced work on the next version, XLW 5.
XLW 4 does not have support for Visual Studio 2010. Development snapshots for ver 5 are available here:

http://sourceforge.net/projects/xlw/files/xlw/xlw 5DEV/

Support for C# xlls has only been added yesterday, so version xlw-5DEV-25Feb2011.exe, and you can choose C# in template extraction window. It should allows you to build C# xlls, but this version is still under development so a bit rough around the edges.If you choose to install and use this version please keep an eye out for more recent dev versions. Also there is a little kink, if you want to rebuild a Solution please 'clean' & 'buil' as oppose to 'rebuild'.
Regards
A'sD

 

[Mugiwara]

oops sorry. I think I misunderstood. Do you have a any method collection file to add to add-ins? Or a single method reuse?

First i tried to replicate the example with BlackScholesAddin on first page to understand how to use C# functions with Excel but it doesn't work :'(

 

[Mugiwara]

Hi,

Which version of xlw are you using ? I have only just commenced work on the next version, XLW 5.
XLW 4 does not have support for Visual Studio 2010. Development snapshots for ver 5 are available here:

http://sourceforge.net/projects/xlw/files/xlw/xlw 5DEV/

Support for C# xlls has only been added yesterday, so version xlw-5DEV-25Feb2011.exe, and you can choose C# in template extraction window. It should allows you to build C# xlls, but this version is still under development so a bit rough around the edges.If you choose to install and use this version please keep an eye out for more recent dev versions. Also there is a little kink, if you want to rebuild a Solution please 'clean' & 'buil' as oppose to 'rebuild'.
Regards
A'sD

Thank you for helping me
i downloaded XLW 5 but it's always the same problem, when i launch XLW setup, Visual C#2010 express is detected.
And when i launch XLWTemplateExtractor setup i still can't choose C# but only Visual C++ ( 2010,2008,2005,2003), code::block and gcc/make
i don't understand why C# isn't displayed

 

[AlexesDad]

And when i launch XLWTemplateExtractor setup i still can't choose C# but only Visual C++ ( 2010,2008,2005,2003), code::block and gcc/make
i don't understand why C# isn't displayed

When XLW is installed, to extract a C# xll template you need to go to the menu :

XLW -> xlw-5DEV -> xlwDotNet -> Extract XLW .NET xll template

to extract a C# template.

If you are going to :

XLW -> xlw-5DEV -> xlw -> Extract XLW xll template

then this indeed will only give you C++ options.

 

[Mugiwara]

thank a lot
i understand my error, xlwDotNet wasn't installed because i left it grayed when it was installed

i swichted to xlw 4 and Visual C#2008 because i prefer these version more completed
i followed the video on youtube "howto create a C# XLL addin using XLW&Visual Studio"
everything goes well until i want to build my solution, i have this error:
" MSB3073 : La commande ""C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\..\..\VC\vcpackages\vcbuild" "C:\Users\Julien\Documents\XLL_Project\Addin\DemoXLL.sln" "Debug|Win32"" s'est arrêtée avec le code 9009. "
i was so close to success

, once again your precious help would be welcome