Building excel application instead of add-in

[Alex Shovkoplyas]

Hello,

Currently I have an excel app (with template spreadsheets, and rich VBA: custom ribbon, functions, forms) and I would like to migrate those functionality into .net.

I've discovered this library and found that it creates add-ins. My problem is that they are available to all workbooks, but I'm interesting in developing only for one specific workbook.

I've tried Application.RegisterXLL but all workbooks opened after also will get access to my add-in.

Is there any way to limit add-in only to specific workbook?

[Govert van Drimmelen]

Hi Alex,

You are correct that Excel-DNA lets you make Excel add-ins, and these are not linked to a particular workbook in the way that a VBA project has a more limited scope in a workbook.

In particular, the UDF functions that are exported through Excel-DNA are global and available to all workbooks.

These are limitations of the Excel add-in model, and there is some gap between you can do with the built-in VBA environment, vs Excel's support for externaly add-ins.

Suppose you have some workbooks that you can recognize as 'special' in some way, and you want the add-in to behave different when such a workbook is active. At least for the UI parts - a ribbon or a Custom Task Pane in your add-in, you should be able to disable or hide some parts of you UI extension.

For the functions you are in the global function namespace, but your function can also detect and manage some aspects of the calling sheet, and in that way return some error when called from a non-special workbook.

In practice, it might be worth having a think about your add-in again, and seeing whether you really have to present the user with a single fixed workbook through which all interaction happens, or whether a more flexible set of features with functions, ribbon helpers etc. that can be used from any workbook might be a good interface too. That could prevent you from fighting too much against the way Excel supports add-ins most naturally. 

-Govert

[Alex Shovkoplyas]

Thank you for response!

In my case its a real application built over excel UI, and currently I don't imagine how to fit it into add-in model, will continue investigation)

четвер, 25 травня 2023 р. о 23:38:23 UTC+3 Govert van Drimmelen пише:

[PRB]

Hey Alex and Govert

I have a similar situation where I needed the addin to only work in a specific workbook. 

In my case I am making template workbooks(xltm) for specific reports, where the user can enter in criteria using a form or range and the data is returned to the workbook. My first attempt I followed the normal steps, but the addin was available in every workbook. Unfortunately, some of the data/buttons/options cannot be shared with users outside of a department. At this point I almost thought Exceldna would not work for me.

Then I thought about my existing VBA app in which I added my own custom Ribbon using the Custom UI Editor app and I wondered if I could wire up Exceldna

to those the ribbons callbacks in the VBA script in that specific workbook. It worked!

1. Create a normal Exceldna addin in C# and get everything working.

2. Create an xltm file (or any xl file type)  and add a module with ribbon callbacks that match the names of the commands--

Sub btnRefresh(Control As IRibbonControl)

       Application.Run(''RefreshCommand")

End Sub

3. Create a ribbon for the xltm file using the CustomUI Editor app and hook everything up. I also remove all unnecessary extra tabs so the user can only do what I want them to do in the file. (https://bettersolutions.com/vba/ribbon/custom-ui-editor.htm)

4. Comment out the Exceldna RibbonUI xml portion in the C# solution.

5. Make sure to pack all extra dll files and build the solution.

6. Install the packed xll file and run the xltm file...

The addin commands will only be available for the buttons and callbacks in your custom ribbon in that specific workbook.

In my case I rename the dna files specific to the report xltm file so I can install multiple addins for users that handle multiple files... otherwise they only need the specific addin for that specific file.

Notes:

- Have not used this method with UDFs.

- Develop the addin like a normal excel dna addin first and then convert it over, if you dont, you wont have the debugging messages to help you.

In my case I have a master addin that is only available to me. I develop with that, and then create a seperate solution for the specific report addin and copy over what i need. This lets me have the debugging and allows me to continue development without affecting the end user. The addin xll file is stored in the same folder as the report and when the user installs they do not copy to their PC addin folder. This allows me to switch the xll file when I am ready to release a new version to them without reinstalling everything... you can also switch the xltm files if needed as well.

I am sure this is probably a convoluted process, but it works for me.

To date I have not had any issues with this setup and the exceldna file works just fine in a hybrid style.

However, please let me know your thoughts, especially if I have overlooked something critical.

Hopefully my fingers kept up with my brain and this makes sense