DotNetRefEdit and System.InvalidOperationException: Cross-thread operation not valid

[Dmitry Volynkin]

Hello There,

First of all, I'm very thankful for such a framework to exist. Good work.

I've been trying to implement the threaded WPF version of this sample:

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

Unfortunately when I hit the "DotNetRefEdit/Separate Thread/WPF " button in the ribbon I get this error:

Exception thrown: 'System.InvalidOperationException' in ExcelDna.Integration
Error: System.InvalidOperationException: Cross-thread operation not valid: Application accessed from a thread other than the thread it was created on.
   at ExcelDna.Integration.ExcelDnaUtil.get_Application()
   at DotNetRefEdit.ExcelSelectionTracker..ctor(Int32 excelThreadId) in C:\DotNetRefEdit-master\Source\DotNetRefEdit\ExcelSelectionTracker.cs:line 27
   at DotNetRefEdit.RefEditWindow..ctor(Int32 excelThreadId) in C:\DotNetRefEdit-master\Source\DotNetRefEdit\RefEditWindow.xaml.cs:line 20
   at DotNetRefEdit.MyRibbon.<OpenWPFInSeparateThread>b__10_1() in C:\DotNetRefEdit-master\Source\DotNetRefEdit\Ribbon.cs:line 164

It seems there's a check added since the above code sample was posted:

https://github.com/Excel-DNA/ExcelDna/pull/270

It seems that the sample does make calls to the main thread using:

ExcelAsyncUtil.QueueAsMacro(() =>... );

And this makes sense to me, however, I'm not entirely sure why I'm getting the errors above when the form is instantiated on this line:

_application = (Application)ExcelDnaUtil.Application;

Which is a bit weird as that is meant to be a readonly variable:

private readonly Application _application;

I only have limited knowledge on how threads work.

I'm working on making the single-threaded version work in my project for now and that runs just fine in the sample. But it does have issues with the window not coming back in focus which is not the best UX.

Can you please provide some direction as to where I should look next to make the threaded version work?

Or if there's a RefEdit control that I can just take and implement then I'd rather do that. All I've seen around are quite old implementations and I wasn't convinced that they work.

Thanks,

Regards,

Dmitry.

[Bart Duijndam]

Hi Dmitry,

 I came across similar problems, as posted here : https://groups.google.com/g/exceldna/c/Zpr1KDsRTUE

 There are some alternative projects to have a look at, if you want to create a Dialog Window.

• For an Add-in example using a WPF form with Excel-DNA on GitHub, see :  Sample of an add-in with a WPF form using Excel-DNA
  This example does stay in the foreground while editing. But it has no RefEdit functionality.
• For RefEdit functionality in WPF forms the Application.InputBox() function is being recommended by several authors.
  For a thorough example see:  https://www.breezetree.com/blog/excel-refedit-in-c-sharp

I am not too familiar with WPF forms and really needed a functioning RefEdit approach asap, so I decided to stick with the old DIALOG.BOX funtion:
_resultObject = XlCall.Excel(XlCall.xlfDialogBox, dialogDef);

In a way, that puts you back in the stone ages of Excel, but at least RefEdit is tried and trusted in this dialog environment.

You'll probably will find it very hard to find documentation on these pre-Visual Basic XLM-commands. You need to use “Excel XLM macro’s” as a search term.

I used the following project on GitHub to further develop my own solution: https://github.com/zwq00000/ExcelDna-XlDialog.
I have made several adjustments & improvements to the code, and now it does the job pretty well. I haven't posted anything on GitHub, so my "branch" isn't publically available (yet).

I hope this helps.

Regards,
Bart

[Govert van Drimmelen]

I've had a look at the DotNetRefEdit project and see the same problem. Access to the Excel Application object and the rest of the COM interface really should happen from the main thread only. In the 'Separate Thread' forms in the example, that is not done and Excel-DNA is checking more carefully these days.

It should be possible to refactor the code so that the bit of COM access that is needed is all done on the main thread, and passed back and forth to the form that way. I also see that the focus of the forms is no longer correct - it seems they are no longer anchored to the Excel window. 

Overall I think this is still a topic that needs more attention, and the suggestions from Bart are valid. Using the XML macro style dialogs is rather limited but at least still work, and allow a nice refedit selection behaviour. Wrapping these in a higher level library is not a big problem, and if you make many simple dialogs or dynamic ones, that's probably the approach I'd recommend. 

But we still need nice solution for making a CTP with a RefEdit selection, so I guess that's a TODO.

I've made a new issue on GitHub to try to remember this: https://github.com/Excel-DNA/ExcelDna/issues/375

-Govert

[Bart Duijndam]

Hi Govert, Dmitry,

I've put some effort into this and have created an XlDialogBox class that encapsulates most of the XLW DIALOG.BOX macro. I's my first attempt to upload a project to GitHub, but it seems to have landed okay. You can find the repository here: https://github.com/MrBeee/XlDialogBox/tree/master

It's still a bit work in progress, but vry useable as is. Please give it a go...

Cheers,

Bart

[Dmitry Volynkin]

Hi, thanks to both of you for the good replies. I'll give Bart's suggestions a go next time I'm working on this (when I have a free weekend) - what Bart said sounds kind of promising.

I've also had some limited success just using the single thread, but the code I wrote is quite spaghetti and is still a work in progress.

Regards,

Dmitry Volynkin-Ewens.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/RQ-xLW1A4NI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/245793f6-338e-4e35-b456-5a4b733bc4c9n%40googlegroups.com.

[Govert van Drimmelen]

Hi Bart,

 

The XlDialogBox project looks really nice – thank you for updating and publishing this.

For many cases the XlDialogBox approach is simple and seems to work well.

I think with a friendly wrapper like this, it’s obviously much easier to use, and the code becomes kind of self-documenting.

 

I don’t think I quite understand Excel’s ‘callback’ style yet, so need to have a closer look at that.

 

I think there is a setting in GitHub (Settings -> Branches) where you can configure the default branch for the repo – changing that to “master” should pop up the right code when one opens the GitHub repo home page at https://github.com/MrBeee/XlDialogBox.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[Bart Duijndam]

Hi Govert, Dmitry,

I'm happy to contribute somehow to the Excel-DNA project. It is a great codebbase to create excel extennsions.

As for the call-backs:

• I don't know what is happening behind the scene in some of the Excel dialog controls.
• E.g. RefEdit, IntEdit etc. all validate the input without the dialog returning from the xlfDiialog command
• However, if you use a trigger, the dialog does return from a control (other than the OK-button)
• This allows for changes in the dialogbox (e.g. disable/enable some buttons) before the dialog is displayed again.
• However, this results in some "flicker" as the dialogbox is first cloes and then re-opened
• Furthermore, the Dialogbox does not pop-up on excatly the same plaace as before, which is a bit sloppy

As for the call-back that I created for use in the ShowDialogBox() function, the idea is:

• Whereas the ShowDialog() function is virtual, and anyone can derive from this, this should not be the preferred method
• With the "trigger option" as well as the modified help button, the do-while-(1) loop has become rather complex.
• So it is better to split off parameter checking in a delegate, which is called from ShowDialog(); 
• bool bOK = dialog.ShowDialog(validate);
  
• In the validation routine, you have access to the following parameters:
• The Index nr of the Control that triggered ShowDialog to return
• The array with the Controls that are currently shown in the dialog
• The overal list of controls that have been added to the Dialog (including those that are  invisible at present.
  The invisible controls won't be part of the array with dialog control parameters )
• With this information, one can make an update to the Dialog-control-list, before giving control back to ShowDialog
• ShowDialog then inspects the changes made, and depending on validate returning true/false shows an updated dialogbox

So that is the idea in a nutshell.

To get more information on the (ancient) XLM Excel 4.0 macro langu, I ordered "Greg Harveys Exce 4.0 for the MAC :

Greg Harvey's Excel 4.0 for the MAC: Harvey, Greg: Amazon.nl (sold out in NL, purchased in US).

So I expect that I'll be making some changes to the code when the book arrives.

Thanks for the tip on using Github, I was rather frustrated when I ended up with two branches somehow ☺

Best regards,

Bart

You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/RQ-xLW1A4NI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/021801d72541%2462eb03b0%2428c10b10%24%40icon.co.za.

[Dmitry Volynkin]

Hi guys,

Bart, thanks for piecing that together, looks like a ton of work and research. I've cloned it, but for some reason couldn't make it run. Got these outputs:

Must be something about my set up. I won't explore it further though as I've made my own approach work and it currently suits my needs for now. I think what you've made looks like a bit more of a general case when compared to mine. What I got isn't great code as everything is entangled with everything else, but it works for me for now.

I've posted it on GitHub, have a look if you want:

https://github.com/dimvoly/DotNetRefEditCollapse/tree/master/ExcelAddIn3

Somehow using threads was not required in the end :S

I think between the various links posted, any new users should have plenty of options to make a RefEdit work for their project.

Regards,

Dmitry.

[Govert van Drimmelen]

Hi Dmitry,

 

You probably have to edit the project’s Post-build command line to point at the right folder.

I think it’s set up when the ExcelDnaDoc package is installed, but not fixed up for a pre-built project that you are opening.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Dmitry Volynkin
Sent: 3 April 2021 23:35
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] Re: DotNetRefEdit and System.InvalidOperationException: Cross-thread operation not valid

 

Hi guys,

 

Bart, thanks for piecing that together, looks like a ton of work and research. I've cloned it, but for some reason couldn't make it run. Got these outputs:

 

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d0d7b6a8-b8e1-4e7e-98b0-86cd5543f647n%40googlegroups.com.

[Bart Duijndam]

Hi Dmitry,

As Govert points out, you are missing the ExcelDnaDoc package in your Visual Studio solution. 

Please go to: Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution

In the Search window, please type "ExcelDnaDoc". This will pull in the missing package.

In your "dna file" you should now also have the following line inside the <DnaLibrary> ... </DnaLibrary> tags:

<Reference Path="ExcelDna.Documentation.dll" Pack="true" xmlns="" />

Now the code should compile fine.

Please note that I am currently refactoring the code to make it more streamlined.

I'll post an update on GitHub when I'm finished making some essential changes.

Cheers,

Bart

[Bart Duijndam]

Hi Govert, Dmitry, 

I updated the code of the ExcelDNA.XlDialogBox, and loaded everything in the master branch.

I've significantly updated the README.md file and added an example spreadsheet, 

where you can test the DIALOG.BOX() macro and play with the dialog layout.

Please give it a go.

Cheers,

Bart