none
How to create simple Excel add-in in VB.NET RRS feed

  • Question

  • I am trying to develop my first Excel Add-in in VS using VB.NET. I've understood that DLL has to contain a function xlAutoOpen to be an XLL. However I don't know where to place this function. I haven't found any other reasonable possibility than to ThisAddIn class:

    Partial Public Class ThisAddIn
        Public Function xlAutoOpen() As Integer
            Return 1
        End Function
    End Class

    However when I try to add this Add-id (the built DLL renamed to XLL) to the Excel, it replies that this xll "is not a valid add-in". Is there something wrong with the function xlAutoOpen, or with anything else?

    Thank you for help, Martin

    By the way, I find it strange that the required members are not included in the VS Excel Add-in template.

    Friday, September 9, 2016 2:40 PM

Answers

  • >>> What should I do then if I would like to use this Add-In in everyday use of Excel?

    According to your description, if you want to deploy an Office solution, you could deploy Office solutions by using ClickOnce or Windows Installer.

    For more information, please refer to Deploying an Office Solution

    In addition you could refer to Architecture of VSTO Add-ins

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Sunday, September 18, 2016 2:04 PM
    • Marked as answer by David_JunFeng Monday, September 19, 2016 5:35 AM
    Friday, September 16, 2016 6:47 AM

All replies

  • Hi Martin Karuzo,

    Based on your sample code, you have created Excel VSTO Add-in, why do you need xlAutoOpen? If you want to create Excel VSTO Add-in, you could refer to below links:

    Walkthrough: Creating Your First VSTO Add-in for Excel

    Getting Started Programming VSTO Add-ins

    >>>I find it strange that the required members are not included in the VS Excel Add-in template.

    Could you provive more information about this issue, for example screenshot, that will help us reproduce and resolve it.

    Thanks for your understanding.
    Monday, September 12, 2016 3:28 AM
  • Hi David

    thank you for taking care. Just to explain, I am a beginner, have just some experiences with VBA macros and VB.NET WinForms applications.

    The Walkthrough above is a good example. If I create such project and run it from the Visual Studio (F5), a new instance of MS Excel starts and within this instance the Add-in does its job, i.e. adds some text to every saved workbook. What should I do then if I would like to use this Add-In in everyday use of Excel?

    I tried to register this Add-In this way:
    File -> Options -> Add-Ins -> Manage [Excel Add-Ins] [Go..] -> [Browse]
    the file browser were searching for *.xll files. The VS Add-In project however generates DLL file. I have understood that

    • I need just to rename the suffix of the library file to xll
    • and that an xll library must satisfy some conditions and that the only obligatory one is the xlAutoOpen function

    I have implemented the function as shown in my original question, however Excel doesn't like my XLL file and display an error message that the file "is not a valid add-in"

    So, what should I do? Martin


       
    Wednesday, September 14, 2016 8:05 AM
  • >>> What should I do then if I would like to use this Add-In in everyday use of Excel?

    According to your description, if you want to deploy an Office solution, you could deploy Office solutions by using ClickOnce or Windows Installer.

    For more information, please refer to Deploying an Office Solution

    In addition you could refer to Architecture of VSTO Add-ins

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Sunday, September 18, 2016 2:04 PM
    • Marked as answer by David_JunFeng Monday, September 19, 2016 5:35 AM
    Friday, September 16, 2016 6:47 AM
  • Hi Martin,

    An alternative to building a VSTO add-in (which is what you get if you follow the built-in templates in Visual Studio), is to make your Excel Add-Ins using VB.NET with the free Excel-DNA library.

    In particular, if you want your add-in to provide user-defined worksheet functions (UDFs) then VSTO is no help to you.

    Excel-DNA is based on the native Excel API and implements the .xll with its AutoOpen() entry point. Your VB.NET add-in can get notified when it is loaded by having a class that implements the IExcelAddIn interface, and its AutoOpen() method, but that part is optional.

    The easiest way to get started with an Excel-DNA add-in is to follow these steps:

    1. Create a new VB.NET "Class Library" project.
    2. Install the "ExcelDna.AddIn" package from the NuGet package manager.
    3. Follow the instructions in the ReadMe.txt file that the package will display, to make a Module with your first VB.NET function in.
    4. Press F5 to build and start running your add-in in Excel.

    Excel-DNA also supports making macros that automate Excel using the COM object model, and Excel features like ribbon and task pane extensions, real-time data sources and even in-sheet IntelliSense for your functions.

    If you need any help, the right support forum for Excel-DNA is the Excel-DNA Google group. You'll find thousands of searchable questions and answers, and help for any snags you run into. Beginner questions are especially welcome.

    Regards,

    Govert

    Excel-DNA - Free and easy .NET for Excel

    Tuesday, September 20, 2016 1:37 PM