locked
Best practice developing a Excel Add-in RRS feed

  • Question

  • I want to develop an Excel-Add in to get some data from a DB.

    The plan is to make my own "cell functions".. [e.g. =getSomeData() ]
    I want to implement different kind of functions. Some will return just one value and others will return an array. The function should be able to take cells or ranges as parameters!

    The question is: what is the best way to achieve this?

    I read a lot about Office Add-ins and still dont know "which way" to choose.
    There are:
    - shared add-ins
    - VS Tools for Office
    - Automation add-ins
    - COM add-ins
    - XLL add-ins
    - VB.NET / C#...

    There are also some products like "spreadsheetgear" or "managed XLL"
    I cant even imagine how they work.. (so i dont know if this would help me..)

    Has anyone already some experience with developing an Excel-addin?

    Every hint, help, information... will be greatly appreciated!


     
    • Edited by Cindy Meister MVP Tuesday, August 23, 2011 4:03 PM Removed hyperlink advertising
    • Moved by Cindy Meister MVP Tuesday, August 23, 2011 4:06 PM Excel-specific question (From:Visual Studio Tools for Office)
    Tuesday, August 23, 2011 2:05 PM

Answers

  • Hello,

    Top-level things. An XLL add-in and Excel Automation add-ins allow creating Excel user-definded functions (UDFs). The differences between them are listed in the section "What Excel UDF type to choose?" in Excel UDF and RTD tips; the page is part of an Add-in Express manual, so you can just omit anything that you don't understand :) 

    Your getSomeData() can be implemented as an Excel UDF. If you describe your task in more details I will be able to tell you if you also need to implement a COM add-in or an RTD server.

    VS Tools for Office as well as Add-in Express, Excel DNA etc are tools that allow developing all or some of Excel extension types. And hope you understand that VB.NET and C# are just programming languages :)

     


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Friday, September 2, 2011 8:08 AM
    Tuesday, August 23, 2011 2:32 PM

All replies

  • Hello,

    Top-level things. An XLL add-in and Excel Automation add-ins allow creating Excel user-definded functions (UDFs). The differences between them are listed in the section "What Excel UDF type to choose?" in Excel UDF and RTD tips; the page is part of an Add-in Express manual, so you can just omit anything that you don't understand :) 

    Your getSomeData() can be implemented as an Excel UDF. If you describe your task in more details I will be able to tell you if you also need to implement a COM add-in or an RTD server.

    VS Tools for Office as well as Add-in Express, Excel DNA etc are tools that allow developing all or some of Excel extension types. And hope you understand that VB.NET and C# are just programming languages :)

     


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by Calvin_Gao Friday, September 2, 2011 8:08 AM
    Tuesday, August 23, 2011 2:32 PM
  • Hi "Xploit"

    I'm going to move your question to the excel for Developers forum. That's where you'll find the broadest spectrum of Excel specialists on this site. They will have lots of experience with what you want to do.

    It will help them to help you if you specify the version of Excel you're targeting and your preferred programming language.

    For what it's worth, a UDF is what you want. If you search that term you should find some discussions and sample code.

    Please note that the VSTO technology does not support creating UDFs.


    Cindy Meister, VSTO/Word MVP
    Tuesday, August 23, 2011 4:06 PM
  • At the moment there is no single Excel addin technology that is best under all circumstances.

    If you want to use VB.Net or C# to develop UDF addins I would recommend using either Addin Express or Excel DNA as tools that greatly simplify what you have to do.

    There is an overview of some of the available technology choices at

    http://fastexcel.wordpress.com/2011/07/07/excel-udf-technology-choices-snakes-ladders-with-vba-vb6-net-c-com-xll-interop/


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    Tuesday, August 23, 2011 4:30 PM
  • If you can write C++ then http://nxll.codeplex.com might be of use. It also can generate documentation that is natively integrated into Excel help. Pretty handy if you want other people to use your add-in too.
    Friday, August 26, 2011 12:23 PM
  • I recommend building your addin using Excel DNA and connecting to external cloud data sources ala Web API's. For more information on Excel Addin and Web API development, check out my series of tutorials @ Excel Addin Framework Criteria.

    Cheers,

    BT

    • Proposed as answer by Bishr Tabbaa Friday, February 10, 2017 2:37 AM
    Friday, February 10, 2017 2:37 AM