1.Opinion on Interaction between VBA and Addin // 2. WinForms not working

[Rusty Fox]

Dear Community,

some months ago I discovered the Excel-DNA project, and integrated it into c#-projects intended for use as excel addins. I am a beginner in .NET/C#/VS'19.

1.

Until now I only mastered making a addin with a Ribbon by loading some xml-code from a VBA-Module. Advantage: I can adapt my Ribbon by changing the vba code without to recompile the addin. All Buttons in the ribbon fire the same OnAction in my Addin.xll :

 public void ExecuteCall(IRibbonControl control)

        {

            dynamic xlApp = ExcelDnaUtil.Application;

            xlApp.Run(control.Id);

        } 

In the vba-code I have procedures that are named like the corresponding Coontrol-IDs.

Is this be intended to be used like this or do you see any problems?

2. Now I wanted to begin to implement some standard forms into my Addin.xll based on WinForms.

For example a form with a Listbox, that Lists entrys given by a passed comma-seperated String. I want to pass the string by a vba-Function calling the form. The form should return the selected value as string.

My starting point was to create a WinForm in my C#-Project and testing to start this from vba.

Within my c#-code I could not handle to show the form from a normal call of method including: 

Application.Run(new Form1());

-> it aborts.

I only can handle to get it shown when I put it directly to the Startup of the addin in when it is called from "GetCustomUI"-method.

I also found this links that may are relelated to this topic:

https://github.com/Ron-Ldn/DotNetRefEdit

https://addinx.github.io/addinx/home.html

The first sample is very old I cant get the projects run with my VS'19

The second one works fine but is WPF.

Beside my problem to start a simple form with INput/Output from VBA I also would appreciate some direction of what is the start of the Art to handle Excel-DNA and WinForms. 

In my opinion there are only old examples out there. May someone could give some code samples or guidlines for this?

I appreciate a lot

Best Regards Rusty

[Govert van Drimmelen]

Hi Rusty,

1.

Having a single callback for many ribbon controls in the way you show is not a problem at all.

2.

Regarding the forms - I think the way this code 

    Application.Run(new Form1());

is possibly interpreted as calling the Excel COM object's Application.Run method, and passing in the Form would not make sense.

Even if it is the Windows Forms Application object, that won't work either.

You might get somewhere with 

var frm = new Form1();

frm.ShowDialog();

Another approach is to make a Windows Forms "user control" and host this as a custom task pane.

That's a bit more friendly to the Excel integration. Here's a sample project that is hopefully not to old to try: Samples/CustomTaskPane at master · Excel-DNA/Samples (github.com)

Integrating the form with "input/output from VBA" might complicate things more.

I'm not sure I have easy answers experimenting a bit.

-Govert