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

[AlexesDad]

xlw4.0.0b0, Beta version of xlw4 is now available for download.

http://sourceforge.net/project/showfiles.php?group_id=45222&package_id=37893&release_id=690866

 

[zhouxing]

I've setuped the addin on my computer.But it doesn't work.

Common seen reasons:

1] If you are using VS2005 for Excel 2002, you will need an excel.exe.config (can be download here

2] It is a good practice to explicitly specify ComVisible attribute at both assembly and class levels. (because VS2005 and VS2008 have different default settings for this)

There is a compilable example at the link given above (with all the necessary settings), try that out. If it works, copy/past Andy's BS functions in the MyFunctions.cs then everything should work.

 

[anandbohra]

Dear Andy

I am very new to VS2008 after gaining grip over VBA now I had moved to VS (prefer VB.net)
but thanks your code worked well without a single bug.

My question is how do I distribute this great piece of work to users.

like can we make its XLL or DLL or a kind of setup which automatically register this DLL in excel of users

Pl help & guide as I am totally new to this.:sos:

 

[Andy Nguyen]

Wirelessly posted

Glad you found the piece useful. I really wish I can tell you in more detail about deployment of your applications. I have gone through the same process and didn't come out confident that I know something that will work everywhere.
You should take a look at ClickOne deployment method MS uses. If you find out the latest trick, be sure to let us know.
I'm sure that it must be someway to do this because software firms release their C# apps daily and they must have some secret sauce that us layman unaware of.

 

[AlexesDad]

I am not sure if this really is a difficult issue. There are various ways that.NET software can be deployed and the best method depends on your constraints.

Here are three methods that I have used.

1. Package the software as an .msi package.
Pro: If you have a COM assembly you can have it register automatically at install time.
Con: If you have a COM assembly you will require admin rights and if you are rolling out to lots of people where admin rights are difficult to come by maybe awkward. It also requires you have Visual Studio Pro.

2. Use NSIS, free open source packaging system used my numerous software packages to deploy software.
Pro : As for #1. Lets you write scripts that can carry out tasks at install time, such as register COM assemblies or put them in the GAC
Con : As for #1 but doesn't require VS Pro BUT does require the learning of a scripting language

3. XLL If all you want to is deploy a .NET UDF library for Excel then probably the easiest method.
Pro : Very easy to deploy. Just give it to your user and thats it.
Con : Tightly coupled to Excel. Not the most appropriate method for using the IDispatch interface of Excel.

 

[zhouxing]

Dear Andy

I am very new to VS2008 after gaining grip over VBA now I had moved to VS (prefer VB.net)
but thanks your code worked well without a single bug.

My question is how do I distribute this great piece of work to users.

like can we make its XLL or DLL or a kind of setup which automatically register this DLL in excel of users

Pl help & guide as I am totally new to this.:sos:

If the "users" are your colleagues (i.e. you are working for the same company and thus have similar software installation), just try to copy the .dll to user's machine and ask them go to Tools -> AddIn -> Automation -> Browse.

Alternatively, write a two line script, 1 is to copy the .DLL and 2 is to run regasm. Ask your users to click that script for installation.

 

[zhouxing]

Some more general notes on .NET application deployment. It is actually not so bad that many people fear.

For a regular .NET application, the deployment is x-copy. Of course, you need to make sure the required version of .NET runtime and required dependent libraries exist. This is not anything unusual. For example a MacOS app written for Leopard won't work on Tiger. So is a .NET app written for 3.0 won't work on 2.0. Therefore deploying a regular .NET app is not much different from deploying other types of application.

For Excel UDF add-in, there are two additional complications. One is registration and the other is Excel version dependency.

Registration can be combined in a standard setup project. I personally prefer a simple script or let Excel to auto-register. But there are also PC release guys who actually prefer to have a proper setup.exe for all production applications, including add-ins.

Excel dependency arguably is a common issue of integration (i.e. you are integrating with another product, so by definition you may have version dependency). For example, anybody who has written HTML page before may testify the tediousness of making your web page work with all browsers. Here, instead of writing HTML that works with different browsers, we are writing C# code that works with different version of Excel.

If we exam the source of this dependency in more detail, we may find it comes from static linking to a specific interop dll (via reference). As such, we are assuming the target machine has the same interop dll. If we realize this, the solution is simple - include a copy of your PIA dll (just interop.excel and maybe VBE) along with your dll. It will work even if your user has different version of Excel installed.

In addition, from programming perspective, it is also helpful to avoid hard instantiation if possible. So for example, instead of Excel.Application obj = new MsExcel.Application(), you should try object obj = CreateInstance("Microsoft Excel Object") especially if you only use its standard COM interface.

 

[anandbohra]

Here are three methods that I have used.

3. XLL If all you want to is deploy a .NET UDF library for Excel then probably the easiest method.
Pro : Very easy to deploy. Just give it to your user and thats it.
Con : Tightly coupled to Excel. Not the most appropriate method for using the IDispatch interface of Excel.

Pl PL PL tell me how do I distribute my code as XLL :prayer: :prayer:
which option to user? where to do in VS to generate XLL :sos:

 

[AlexesDad]

There are two software solutions I can suggest for building .NET XLL's which are free.

1. XLW
2. ExcelDNA

Since I only know XLW well, I can only give you details for that. Have a look at the following video clip ( there is no sound and it may require you to fullscreen it):
http://www.youtube.com/watch?v=1yLodcb32sI

The website for XLW is http://xlw.sourceforge.net/ and you need the latest Beta release.

The author of ExcelDNA has also posted information about that software on this thread which you should search for.

 

[Tysken]

Thanks for a good post but when i try to make the udfs i get #value! whenever i pass a refrence to a cell like A1 (ie =blsCall(A1;1;1;1;1;1) ).

Does that happen to any of you? And do anyone know why and how to make his work?

 

[Andy Nguyen]

If you use a numerical value in place of A1 in your blsCall, does it work?

 

[Tysken]

No, it does not. That is what I'm trying to do.

 

[Andy Nguyen]

Then you haven't set it up correctly. Does it show up under our Addin?

 

[Tysken]

I'm not sure what you're after.

But I think that I did all the steps in the tutorial (I've done it a couple of times now with the same result).

But if you are talking about Excel I added the Add-in from Add-ins / Excel-addins and then automation. The add in is there and it seems to work fine until I add a refrence in the formula.

Btw, thanks for helping me!

 

[zhouxing]

If you see #Value (instead of #N/A), it indicates an exception was thrown inside the code (instead of function not found). A recommended way to write UDF in C# is always do sth like the following:

object MyFunction(parameter list)
{
try
{
// your real code
}
catch(Exception err_)
{
return err_.ToString();
}
}

i.e. always declare the return type as object (instead of double, for example) and put a try-catch inside. It may give you some hint of what's going no.

Alternatively you can attach a debugger. Debugging C# UDF is the same and as simple as debugging any other C# library.

 

[Andy Nguyen]

I went through the whole steps again to see if I can reproduce the error. It worked the first time and it works now.
I noticed that your Excel function call is not correct. You used

=blsCall(A1;1;1;1;1;1)

see the red ;
It should be , like this

=blsCall(A1,1,1,1,1,1)

Attached is a screenshot of the function working properly in my Excel 2007

 

[Tysken]

Sorry, that is not the problem. If you use ; or , depends on your regional settings (I think). A number with a decimal on my computer is written with "," for example 1,5 (3/2) while on your computer it is written 1.5

That is why I for example I can't write =MOD(10,4), that gives me a error message(mod needs two parameters) while =MOD(10;4) works fine.

 

[Andy Nguyen]

There are settings in your local environment I'm not aware of so I don't know how much I can help debug. See if you get a simple function to work

Add this code to Step 2

public double SumOfTwoNumbers(double NumberOne, double NumberTwo)
        {
            return NumberOne + NumberTwo;
        }

Build them again and see if you can call =SumOfTwoNumbers(A1;A2)

 

[zhouxing]

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);

 

[drjmpc]

Andy -- you are simply the MAN for posting and explaining this!

Thank you