1

I would like to create a new Excel function (user Defined Function), for that i did the same steps as in this link: https://excel-dna.net/

  1. I created a class Library Project

  2. I installed the package ExcelDna.Integration

  3. I created a method sayHello

But when i try to call my function from a cell in excel (i put =sayHello("World"), the function didn't appear, it seems it's not added to excel functions. is there some missing steps? how can i make my function appear to be able to use it ?

Improve this question
3
  • seems like a lot of work to do just to add a UDF in excel. Can you not just write it in VBA? or if C# is your preferred language, create a dll and reference it in your excel project Commented Apr 6, 2018 at 8:02
  • 1
    @Zac While it's faster to make a UDF with VBA, if you want to code in C# or modern VB.NET in Visual Studio, and then use .NET libraries etc., you need a little bit of glue like Excel-DNA to bind the .NET runtime to Excel. Referencing a C# .dll into Excel directly is very restrictive and causes various problems. Commented Apr 6, 2018 at 9:45
  • I've never had to use external add-ins so maybe it's my lack of knowledge on the subject. On the back of this, I'll have a look at it and maybe something new for me to learn.. thanks Commented Apr 6, 2018 at 10:02

1 Answer 1

Reset to default
3

You should install the package "ExcelDna.AddIn" to make the add-in (that will set up the add-in including the important .dna file, and also bring in the "ExcelDna.Integration" reference library).

So the steps would be:

  • Create a new C# Class Library project (targeting .NET Framework not .NET Standard)
  • Install the ExcelDna.AddIn package
  • Add some code, e.g.

public static class MyFunctions { public static string SayHello(string name) { return "Hello " + name; } }

  • Press F5 to compile and load the add-in in Excel

(Note that on some Excel installations, the Debug setting for the project get an extra %1 in the executable path - just remove this from the end if you get an error when debugging)

The best support for Excel-DNA is the Google group at https://groups.google.com/forum/#!forum/exceldna

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank u very much, i found this tutorial : youtube.com/watch?v=kkihL6vP_tI, it's amazing and everything is ok, it was a simple example to display a message, now i want that my function extract data from database, i created a method to do this but this time my function doesn't appear,i can't find the problem, my query is ok
@GtariAbir It might be that your function is not static, or the parameter types are not supported. Write to the Google group if you're still stuck.
my function is static but it is not public when i define it as public i get this error "Inconsistent accessibility: return type List<AbsProduct> is less accessible than method 'Class1.GetAbsPrice (DateTime)" how can i declare it as public without getting this error please ?
Oh my class AbsProduct was not declared as public when i defined it as public the problem is solved but when build my solution and try to call my function GetAbsPrice from a cell in excel a window is displayed with this message "Initialization [Error] Method not registered- unsupported signature. abstract or generic:Class1.GetAbsPriceForNav'
Is it possible to return DataTable to Excel using DNA ? because i change the result type of my function from list to DataTable and i always get the same message "Initialization [Error] Method not registered - unsupported signature, abstract or generic: 'Class1.GetAbsPriceForNav' ", when i change to int or string it works well, does the excel dna support types like list / dataTable or not ? if not how can i change to get many rows from database please ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.