none
VSTO BackgroundWorker throws Cross-Threaded Exception RRS feed

  • Question

  • It doesn't appear possible to utilize background worker from within VSTO add-in.  I am using Visual Studio 2008 to create an application-level Excel 2003 Add-In.  I create custom Menu's/Button's and event handler's within ThisAddIn class.  Each button calls a different Windows Form as follows:

    public void mainmenuButton2_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
            {
                Cursor.Current = Cursors.WaitCursor;
                Excel.Worksheet chksheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
                if (chksheet != null)
                {                
                    Form2 frm = new Form2(NativeWindow.FromHandle(new IntPtr(Application.Hwnd)));
                    frm.ShowModeless();
                }
    }

    The Windows Forms have a button, progress bar and text fields.  The click event of the button triggers:

    1. a new Excel process
    2. the background worker object to perform whichever operation has been selected
    3. updating of the progress bar.
    • Upon completion of the background worker the Excel process and resources are released and the form is closed. 

    While the application works I have noticed at sparse and random intervals running the add-in's operations repeatedly in a continuous fashion will eventually lead to crashing Excel.  When this happens the newly created Excel process will terminate when excel is forced closed, however, the original process that started Excel will remain.  Examining the memory usage for the two excel processes it doesn't spike or suggest anything abnormal - the new Excel process (the one created by the button click event) will steadily increase its usage until it closes - the existing Excel process will show only minimal usage increase.

    Additionally, I have removed all code from the DoWork method of BackgroundWorker and left an innocuous piece of code:

    int fcount = (int)e.Argument

    When I set a breakpoint on this line of code - and mind you this is the only line of code in the DoWork method - the WorkerThread throws a CrossThreadedException:

    5712    5 1650 07bf5be0   188b228 Enabled  03d12bd8:03d13624 05c995f0     0 MTA (Threadpool Worker) Microsoft.VisualStudio.Debugger.Runtime.CrossThreadMessagingException (03d129a0)

    This gives me serious doubts as to the stability of any add-in which implements BackgroundWorker in a VSTO solution.  Is the BackgroundWorker object simply not compatible with VSTO solutions?  Am I implementing it incorrectly?

    Monday, February 18, 2013 11:54 AM

Answers

  • with this simple code do you still get that cross thread error? As for VS - no, it is not buggy, but it tries to re-evaluate all variables that you may have in those debugging windows - and that mixed with STA, winforms and office may lead to non hunt for non existing bugs since they will only appear under VS with those windows open (as VS does not know that it should marshal or nor re-evaluate some properties). So please close all debugging related windows like locals, etc. and verify that indeed you still get that exception (by using try/catch in dowork function)
    Monday, February 18, 2013 2:38 PM
  • you have to change you code in such way that it calls Marshal.ReleaseCOMObject and excelApp.Quit methods after you are done with external excel app so it disappears from process list. This way your issue will be solved.
    Monday, February 18, 2013 7:30 PM
  • if by responsive UI you mean your custom form with some sort of progress bar or something like that then simply display it in another thread and keep work in main thread - this way user will have smooth feedback that something is happening.
    Tuesday, March 5, 2013 7:54 AM
  • Thread             Action

    VSTA_MAIN     Real Excel Work, updating worksheet, etc

    Thread 1         Create and display form with Progress bar, cancel button, etc., just post here messages from VSTA_Main to update Progress bar, message in label, etc.

    Tuesday, March 5, 2013 11:45 AM
  • Updating the progress bar may require using invocation to prevent cross threading errors. I just ran into something similar in an Outlook addin where I needed to update a progress bar and a form listing the file being uploaded, upload speed and percentage of upload completion. To get the form updating correctly I needed to call delegate methods that checked for InvokeRequired and did the UI updates from an invocation where required.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "DamianD" <=?utf-8?B?RGFtaWFuRA==?=> wrote in message news:43b2ac01-24d2-4f24-bcbc-d19f26903e0b...

    Thread             Action

    VSTA_MAIN     Real Excel Work, updating worksheet, etc

    Thread 1         Create and display form with Progress bar, cancel button, etc., just post here messages from VSTA_Main to update Progress bar, message in label, etc.


    Ken Slovak MVP - Outlook
    Tuesday, March 5, 2013 3:03 PM
  • yes, begininvoke on any winforms control simply posts message to window message loop and this is exactly what i was proposing by 'post here messages from VSTA_Main to update Progress bar'
    Tuesday, March 5, 2013 3:09 PM

All replies

  • Background worker by itself is fine. Show us your full (but simplest) code that throws this exception.
    Monday, February 18, 2013 12:09 PM
  • Forgive me in advance for the amount of code here -- I replaced the actual process bw does simply because the dowork method is where the exception is thrown so any code after that is n/a but here is the Form:

    namespace PMEMFJ
    {
        partial class FormException
        {
            /// <summary>
            /// Required designer variable.
            /// </summary>
            private System.ComponentModel.IContainer components = null;
    
            /// <summary>
            /// Clean up any resources being used.
            /// </summary>
            /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
            protected override void Dispose(bool disposing)
            {
                if (disposing && (components != null))
                {
                    components.Dispose();
                }
                base.Dispose(disposing);
            }
    
            #region Windows Form Designer generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {
                this.backgroundWorker1 = new System.ComponentModel.BackgroundWorker();
                this.refEdit1 = new PMEMFJ.RefEdit();
                this.progressBar1 = new System.Windows.Forms.ProgressBar();
                this.btnStart = new System.Windows.Forms.Button();
                this.btnCancel = new System.Windows.Forms.Button();
                this.SelectExcel = new System.Windows.Forms.OpenFileDialog();
                this.SuspendLayout();
                // 
                // backgroundWorker1
                // 
                this.backgroundWorker1.WorkerReportsProgress = true;
                this.backgroundWorker1.WorkerSupportsCancellation = true;
                this.backgroundWorker1.DoWork += new System.ComponentModel.DoWorkEventHandler(this.backgroundWorker1_DoWork);
                this.backgroundWorker1.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(this.backgroundWorker1_RunWorkerCompleted);
                this.backgroundWorker1.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler(this.backgroundWorker1_ProgressChanged);
                // 
                // refEdit1
                // 
                this.refEdit1.BackColor = System.Drawing.SystemColors.Control;
                this.refEdit1.Location = new System.Drawing.Point(104, 8);
                this.refEdit1.Name = "refEdit1";
                this.refEdit1.Size = new System.Drawing.Size(185, 20);
                this.refEdit1.TabIndex = 0;
                this.refEdit1.MouseClick += new System.Windows.Forms.MouseEventHandler(this.refEdit1_MouseClick);
                this.refEdit1.Enter += new System.EventHandler(this.refEdit1_Enter);
                // 
                // progressBar1
                // 
                this.progressBar1.Location = new System.Drawing.Point(8, 32);
                this.progressBar1.Name = "progressBar1";
                this.progressBar1.Size = new System.Drawing.Size(280, 23);
                this.progressBar1.TabIndex = 1;
                // 
                // btnStart
                // 
                this.btnStart.Location = new System.Drawing.Point(40, 64);
                this.btnStart.Name = "btnStart";
                this.btnStart.Size = new System.Drawing.Size(75, 23);
                this.btnStart.TabIndex = 2;
                this.btnStart.Text = "Start";
                this.btnStart.UseVisualStyleBackColor = true;
                this.btnStart.Click += new System.EventHandler(this.btnStart_Click);
                // 
                // btnCancel
                // 
                this.btnCancel.Location = new System.Drawing.Point(176, 64);
                this.btnCancel.Name = "btnCancel";
                this.btnCancel.Size = new System.Drawing.Size(75, 23);
                this.btnCancel.TabIndex = 3;
                this.btnCancel.Text = "Cancel";
                this.btnCancel.UseVisualStyleBackColor = true;
                this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
                // 
                // SelectExcel
                // 
                this.SelectExcel.Filter = "Excel Files|*.xls;*.xlsx;*.xlt;*.xltx;*.xlsb;*.xlsm;*.xltm";
                this.SelectExcel.Multiselect = true;
                this.SelectExcel.Title = "Exception Check";
                // 
                // FormException
                // 
                this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
                this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
                this.ClientSize = new System.Drawing.Size(292, 97);
                this.Controls.Add(this.btnCancel);
                this.Controls.Add(this.btnStart);
                this.Controls.Add(this.progressBar1);
                this.Controls.Add(this.refEdit1);
                this.Name = "FormException";
                this.Text = "FormException";
                this.Shown += new System.EventHandler(this.FormException_Shown);
                this.FormClosed += new System.Windows.Forms.FormClosedEventHandler(this.FormException_FormClosed);
                this.ResumeLayout(false);
    
            }
    
            #endregion
    
            private System.ComponentModel.BackgroundWorker backgroundWorker1;
            private RefEdit refEdit1;
            private System.Windows.Forms.ProgressBar progressBar1;
            private System.Windows.Forms.Button btnStart;
            private System.Windows.Forms.Button btnCancel;
            private System.Windows.Forms.OpenFileDialog SelectExcel;
        }
    }

    and the code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.IO;
    using System.Threading;
    using System.Text.RegularExpressions;
    using System.Linq;
    using System.Runtime.InteropServices;
    using Microsoft.Win32;
    using System.Reflection;
    
    namespace PMEMFJ
    {
        public partial class FormException : Form
        {
            char[] delficr = new char[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
            string rfcol = "";
            string rfrow = "";
            string rfadrs = "";
            char[] chradrs;
            bool processclosed = false;
            int k = 0;
            int l = 0;
            string path = "";
            string[] fnames;
            string xlfname = "";
            object missing = System.Reflection.Missing.Value;
            Excel.Application exlApp = new Excel.Application();
            Excel.Workbook eawb = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Workbook xlWorkbook;
            
            public FormException()
            {
                InitializeComponent();
            }
            private NativeWindow _nativeWindow;
            public FormException(NativeWindow nw)
            {
                InitializeComponent();
                _nativeWindow = nw;
            }
            public void ShowModeless()
            {
                Show(_nativeWindow);
            }
            private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
            {
                int fcount = (int)e.Argument;
                BackgroundWorker worker = sender as BackgroundWorker;
                do
                {
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        break;
                    }
                    else
                    {
                        worker.ReportProgress((int)((float)l / (float)k * 100));
                    }
                    l++;
                } while (l < fcount);
            }
    
            private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
            {
                this.SetProgBar(e.ProgressPercentage);
            }
            delegate void setprogbarhandler(int p);
            private void SetProgBar(int p)
            {
                if (this.progressBar1.InvokeRequired)
                {
                    setprogbarhandler pbarh = new setprogbarhandler(SetProgBar);
                    this.Invoke(pbarh, new object[] { p });
                }
                else
                {
                    progressBar1.Value = p;
                }
            }
            private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
            {
                if (e.Error != null)
                {
                    MessageBox.Show(e.Error.Message);
                }
                else if (e.Cancelled)
                {
                    Invoke(new MethodInvoker(this.Close));
                }
                else
                {
                    Invoke(new MethodInvoker(this.Close));
                }
            }
            private void ReleaseCOMObject(object oxlObject)
            {
                try
                {
                    Marshal.ReleaseComObject(oxlObject);
                    oxlObject = null;
                }
                catch (Exception)
                {
                    oxlObject = null;
                }
            }
    
            private void btnCancel_Click(object sender, EventArgs e)
            {
                if (!backgroundWorker1.IsBusy)
                {
                    this.Close();
                }
                else if (backgroundWorker1.WorkerSupportsCancellation == true)
                {
                    backgroundWorker1.CancelAsync();
                }
            }
    
            private void FormException_Shown(object sender, EventArgs e)
            {
                refEdit1.Focus();
                Excel.Application xlApp = Globals.ThisAddIn.Application;
                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlApp.ActiveSheet;
                Excel.Range xlRange = (Excel.Range)xlWorksheet.Cells[xlApp.ActiveCell.Row, xlApp.ActiveCell.Column];
                Excel.Range exlRange = (Excel.Range)xlWorksheet.Cells[50000, 256];
                exlRange.Select();
                xlRange.Select();
            }
    
            private void refEdit1_Enter(object sender, EventArgs e)
            {
                refEdit1.Focus();
                Excel.Application xlApp = Globals.ThisAddIn.Application;
                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlApp.ActiveSheet;
                Excel.Range xlRange = (Excel.Range)xlWorksheet.Cells[xlApp.ActiveCell.Row, xlApp.ActiveCell.Column];
                Excel.Range exlRange = (Excel.Range)xlWorksheet.Cells[50000, 256];
                exlRange.Select();
                xlRange.Select();
            }
    
            private void refEdit1_MouseClick(object sender, MouseEventArgs e)
            {
                refEdit1.Focus();
                Excel.Application xlApp = Globals.ThisAddIn.Application;
                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlApp.ActiveSheet;
                Excel.Range xlRange = (Excel.Range)xlWorksheet.Cells[xlApp.ActiveCell.Row, xlApp.ActiveCell.Column];
                Excel.Range exlRange = (Excel.Range)xlWorksheet.Cells[50000, 256];
                exlRange.Select();
                xlRange.Select();
            }
    
            private void btnStart_Click(object sender, EventArgs e)
            {
                rfadrs = refEdit1.Address.ToUpper();
                if (rfadrs == "")
                {
                    this.Hide();
                    if (MessageBox.Show("Please make a valid selection with the Reference Edit Box", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification) == DialogResult.OK)
                    {
                        this.ShowModeless(); ;
                        return;
                    }
                }
                else
                {
                    if (validateuserinput())
                    {
                        this.Hide();
                        if (ExcelSelect())
                        {
                            this.ShowModeless();
                            exlApp.ScreenUpdating = false;
                            exlApp.Visible = false;
                            fnames = SelectExcel.FileNames;
                            xlfname = eawb.FullName;
                            backgroundWorker1.RunWorkerAsync(k);
                        }
                        else
                        {
                            this.Close();
                        }
                    }
                    else
                    {
                        this.ShowModeless(); ;
                        return;
                    }
                }
            }
            private void FormException_FormClosed(object sender, FormClosedEventArgs e)
            {
                if (_nativeWindow != null) _nativeWindow.ReleaseHandle();
                if (!processclosed)
                {
                    exlApp.Quit();
                    ReleaseCOMObject(exlApp);
                    processclosed = false;
                }
                else
                {
                    processclosed = false;
                }
            }
            private bool ExcelSelect()
            {
                DialogResult result = SelectExcel.ShowDialog();
                k = SelectExcel.FileNames.Length;
                return result == DialogResult.OK;
            }
            private bool validateuserinput()
            {
                char[] arr = rfadrs.ToCharArray();
                arr = Array.FindAll<char>(arr, (c => (!char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '-')));
                if (rfadrs.Contains("$"))
                {
                    this.Hide();
                    MessageBox.Show("Please use relative instead of absolute cell references", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                    return false;
                }
                else
                {
                    if (rfadrs.Contains(":"))
                    {
                        if (rfadrs.Split(':').Length > 2)
                        {
                            this.Hide();
                            MessageBox.Show("Invalid range cell reference.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                            return false;
                        }
                        else if ((arr.Length > 0) & (!arr.GetValue(0).Equals(':')))
                        {
                            this.Hide();
                            MessageBox.Show("Please use alpha-numeric characters only for your cell reference.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                            return false;
                        }
                        string[] rfadrs1 = rfadrs.Split(':');
                        for (int w = 0; w < rfadrs1.Length; w++)
                        {
                            chradrs = rfadrs1.GetValue(w).ToString().ToCharArray();
                            if ((Array.IndexOf(delficr, chradrs.GetValue(0)) > -1) & (Array.IndexOf(delficr, chradrs.GetValue(1)) > -1))
                            {
                                rfcol = chradrs.GetValue(0).ToString() + chradrs.GetValue(1).ToString();
                                rfrow = "";
                                for (int i = 2; i < chradrs.Length; i++)
                                {
                                    if (chradrs.GetValue(i).ToString().IndexOfAny(delficr) > -1)
                                    {
                                        this.Hide();
                                        MessageBox.Show("Invalid cell reference format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                                        return false;
                                    }
                                    else
                                    {
                                        rfrow = rfrow + chradrs.GetValue(i).ToString();
                                    }
                                }
    
                            }
                            else
                            {
    
                                rfcol = chradrs.GetValue(0).ToString();
                                rfrow = "";
                                for (int i = 1; i < chradrs.Length; i++)
                                {
                                    if (chradrs.GetValue(i).ToString().IndexOfAny(delficr) > -1)
                                    {
                                        this.Hide();
                                        MessageBox.Show("Invalid cell reference format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                                        return false;
                                    }
                                    else
                                    {
                                        rfrow = rfrow + chradrs.GetValue(i).ToString();
                                    }
                                }
                            }
                        }
                        return true;
                    }
                    else
                    {
                        if (arr.Length > 0)
                        {
                            this.Hide();
                            MessageBox.Show("Please use alpha-numeric characters only for your cell reference.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                            return false;
                        }
                        chradrs = rfadrs.ToCharArray();
                        if ((Array.IndexOf(delficr, chradrs.GetValue(0)) > -1) & (Array.IndexOf(delficr, chradrs.GetValue(1)) > -1))
                        {
                            rfcol = chradrs.GetValue(0).ToString() + chradrs.GetValue(1).ToString();
                            rfrow = "";
                            for (int i = 2; i < chradrs.Length; i++)
                            {
                                if (chradrs.GetValue(i).ToString().IndexOfAny(delficr) > -1)
                                {
                                    this.Hide();
                                    MessageBox.Show("Invalid cell reference format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                                    return false;
                                }
                                else
                                {
                                    rfrow = rfrow + chradrs.GetValue(i).ToString();
                                }
                            }
    
                        }
                        else
                        {
    
                            rfcol = chradrs.GetValue(0).ToString();
                            rfrow = "";
                            for (int i = 1; i < chradrs.Length; i++)
                            {
                                if (chradrs.GetValue(i).ToString().IndexOfAny(delficr) > -1)
                                {
                                    this.Hide();
                                    MessageBox.Show("Invalid cell reference format", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                                    return false;
                                }
                                else
                                {
                                    rfrow = rfrow + chradrs.GetValue(i).ToString();
                                }
                            }
                        }
                        return true;
                    }
                }
            }
        }
    }

    Thank you for your fast response!

    Monday, February 18, 2013 1:16 PM
  • is your code an excel add-in? if yes, why are you starting new excel process? as for backgroundworker i do not see any problematic code, although i admit that due to sheer amount of your code i haven't really looked into it too much. Try to provide simplest code that reproduces the error, also make sure that you do not have any 'watch' windows or 'locals' opened in VS, since error may come from them.

    Monday, February 18, 2013 1:31 PM
  • Yes, code is an excel 2003 add-in.  I am starting a new excel process to separate the operations that happen in other excel documents from the open document (ie. if (fnames[l] != xlfname) do some work with default selections on other excel documents save and close else do some work updating with user selection on this document) -- but this code does not need to be included because it comes later....

    The simplest code that reproduces the error would be this:

    public partial class FormException : Form
        {
            int k = 10;
            int l = 0;
            
            public FormException()
            {
                InitializeComponent();
            }
            private NativeWindow _nativeWindow;
            public FormException(NativeWindow nw)
            {
                InitializeComponent();
                _nativeWindow = nw;
            }
            public void ShowModeless()
            {
                Show(_nativeWindow);
            }
            private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
            {
                int fcount = (int)e.Argument;
                BackgroundWorker worker = sender as BackgroundWorker;
                do
                {
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        break;
                    }
                    else
                    {
                        worker.ReportProgress((int)((float)l / (float)k * 100));
                    }
                    l++;
                } while (l < fcount);
            }
    
            private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
            {
                this.SetProgBar(e.ProgressPercentage);
            }
            delegate void setprogbarhandler(int p);
            private void SetProgBar(int p)
            {
                if (this.progressBar1.InvokeRequired)
                {
                    setprogbarhandler pbarh = new setprogbarhandler(SetProgBar);
                    this.Invoke(pbarh, new object[] { p });
                }
                else
                {
                    progressBar1.Value = p;
                }
            }
            private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
            {
                if (e.Error != null)
                {
                    MessageBox.Show(e.Error.Message);
                }
                else if (e.Cancelled)
                {
                    Invoke(new MethodInvoker(this.Close));
                }
                else
                {
                    Invoke(new MethodInvoker(this.Close));
                }
            }
            private void btnStart_Click(object sender, EventArgs e)
            {
                            backgroundWorker1.RunWorkerAsync(k);
            }
        }
    

    When you say errors may come from VS -- in order to identify why application randomly hangs I am using the debugger .load sos and !threads when I step through the code among other debugging techniques to resolve any problems which may be overlooked when the code was written.  Is there a different technique I should use?  Is VS debugger....uh....buggy?

    Monday, February 18, 2013 2:10 PM
  • with this simple code do you still get that cross thread error? As for VS - no, it is not buggy, but it tries to re-evaluate all variables that you may have in those debugging windows - and that mixed with STA, winforms and office may lead to non hunt for non existing bugs since they will only appear under VS with those windows open (as VS does not know that it should marshal or nor re-evaluate some properties). So please close all debugging related windows like locals, etc. and verify that indeed you still get that exception (by using try/catch in dowork function)
    Monday, February 18, 2013 2:38 PM
  • Yes, with this simple code the Visual Studio debugger still identifies the WorkerThread (thread created by backgroundWorker's DoWork method) having CrossThreadException immediately after RunWorkerAsync calls the DoWork event.

    Is there an issue with the fact that the ProgressChanged and WorkerCompleted events of background worker execute their methods on the STA UI started by the VSTO add-in (VSTA_Main)?  Do I have to create a separate UI thread from which I should initialize the Windows Form?  Am I making any sense at all or am I missing something that's plainly obvious?

    I have all code in try/catch blocks and no there is no exception thrown when the add-in runs; the problem I am trying to resolve is random hangs in the add-in.  When the add-in is run repeatedly (more than it would be run by a regular user) after numerous runs without closing Excel the add-in will begin processing the background worker operation and hang with Excel "Not Responding" and Excel will have to be forcibly stopped by killing the process and restarted.  The initial run upon launching Excel will not have a problem; in fact the first few runs of the add-in will process everything without problems, however, if Excel is not closed and reopened their is the possibility that subsequently running the add-in will result in Excel becoming non-responsive.

    Is there a method I could implement in my code that would assist in identifying this behavior or is my only option to check if Excel is non-responsive and kill the process from within my code?


    • Edited by Reciprocity0917 Monday, February 18, 2013 6:39 PM Clarification
    Monday, February 18, 2013 3:02 PM
  • you have to change you code in such way that it calls Marshal.ReleaseCOMObject and excelApp.Quit methods after you are done with external excel app so it disappears from process list. This way your issue will be solved.
    Monday, February 18, 2013 7:30 PM
  • I have now seen the error of my ways and the Form example provided is a very poor representation of the problem mainly because it in no way demonstrates the problem which abounded in my other forms.  The problem, in case anyone cares, is that I was holding references to COM objects within my backgroundWorker thread.  In fact the very nature of the background work requires various calls to these objects.  Unfortunately when attempting to debug the add-in Visual Studio would throw a ubiquitous "CrossThreadMessaging" error as soon as the background thread was started.  My problem was further compounded by the fact that as long as I started a new process on the background thread I could reference the new COM object exclusively within my background thread without concern for running into cross thread issues.

    It was only the action performed on the ThisAddIn object that resulted in an eventual loss of the underlying RCW when a COM reference was called from a thread other than the VSTA_Main thread.  Considering that my Windows Form is being called from the VSTA_Main thread I am required to marshal all calls to update the ui on VSTA_Main as well as all references to ThisAddIn.  Perhaps someone would be kind enough to instruct me on how I would be able to create a new UI thread from which I could call the Windows Form and in that regard I could maintain a responsive Windows Form while processing any work required on ThisAddIn in the background on VSTA_Main.

    I am relatively new to VSTO and c# programming so my knowledge at this point is very limited and basic.  I'm hoping someone can assist me in tightening up my understanding and perhaps provide a better method for accomplishing this goal.  The reason I would prefer the new ui thread for the Windows Form is because of the cumbersome way in which I resolved the issue as follows:

    • Include a new using Directive:
    using System.Windows.Threading;
    • Declare accessors and set the properties from my addin click event handler:
    public partial class ThisAddIn
        {
    
    	Dispatcher _dispatcher = null;
            Excel.Workbook _activeWorkbook = null;
            Excel.Worksheet _activeWorksheet = null;
            public Dispatcher Dispatcher { get { return _dispatcher; } }
            public Excel.Workbook xlWorkbook1 { get { return _activeWorkbook; } }
            public Excel.Worksheet xlWorksheet1 { get { return _activeWorksheet; } }
    
    	public void mainmenuButton_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
            {
                Cursor.Current = Cursors.WaitCursor;
                _dispatcher = Dispatcher.CurrentDispatcher;
                _activeWorkbook = this.Application.ActiveWorkbook;
                _activeWorksheet = (Excel.Worksheet)this.Application.ActiveSheet;
                if (frm1 == null || frm1.IsDisposed)
                {
                    frm1 = new Form1();
                    frm1.Show(NativeWindow.FromHandle(Process.GetCurrentProcess().MainWindowHandle));
                }
                else
                {
                    frm1.Activate();
                }
            }
        }
    • Implement a "backgroundWorker" class from which I can raise events
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    public class workerProgress
        {
            public delegate void WorkerProgressedHandler(object obj, int e);
            public delegate void WorkerCompletedHandler(object obj, EventArgs e);
            public delegate void WorkerStatusBarUpdateHandler(object obj, string e);
            public event WorkerProgressedHandler WorkerProgressed;
            public event WorkerCompletedHandler WorkerCompleted;
            public event WorkerStatusBarUpdateHandler WorkerStatusBarUpdate;
            public delegate void OnWorkerProgressedHandler(int e);
            public void OnWorkerProgressed(int e)
            {
                if (WorkerProgressed != null)
                {
                    WorkerProgressed(this, e);
                }
            }
            public delegate void OnWorkerCompletedHandler();
            public void OnWorkerCompleted()
            {
                if (WorkerCompleted != null)
                {
                    WorkerCompleted(this, new EventArgs());
                }
            }
            public delegate void OnWorkerStatusBarUpdateHandler(string e);
            public void OnWorkerStatusBarUpdate(string e)
            {
                if (WorkerStatusBarUpdate != null)
                {
                    WorkerStatusBarUpdate(this, e);
                }
            }
        }
    • handle as much processing as possible prior to initialization of my background worker and new thread:

    public partial class Form1 : Form

    {

    public static volatile bool cancelwork = false;
    public static volatile bool workcomp = false;

    public static Thread frm2Thread = null;

    private void InitializeBackgroundWorker()
            {
                wp.WorkerProgressed += new workerProgress.WorkerProgressedHandler(wp_WorkerProgressed);
                wp.WorkerCompleted += new workerProgress.WorkerCompletedHandler(wp_WorkerCompleted);
                wp.WorkerStatusBarUpdate += new workerProgress.WorkerStatusBarUpdateHandler(wp_WorkerStatusBarUpdate);
            }

    private void wp_WorkerStatusBarUpdate(object obj, string e)
            {
                lblStatus.Text = e;
            }

    private void wp_WorkerProgressed(object obj, int e)
            {
                progressBar1.Value = e;
            }
    private void wp_WorkerCompleted(object obj, EventArgs e)
            {
                if (cancelwork)
                {
                    lblStatusBar = "Cancelled!";
                }
                else
                {
                    lblStatusBar = "Done!";
                }
                frm2Thread = null;
                this.Close();
            }

    private void btnCancel_Click(object sender, EventArgs e)
            {
                if (frm2Thread.IsAlive)
                {
                    cancelwork = true;
                }
                else
                {
                    this.Close();
                }

            }

    private void Button1_Click(object sender, System.EventArgs e) { //process work here InitializeBackgroundWorker(); frm2Thread = new Thread(Do_Work); frm2Thread.Name = "VSTA_Form"; //for identification purposes during debugging frm2Thread.SetApartmentState(ApartmentState.STA); frm2Thread.Start(); }

    }

    • Do my background work utilizing dispatcher to marshal the calls to update ui/when referencing COM objects:

    private void Do_Work() { try { if (addreference) {

    //*the call below is what I would prefer to handle by starting the Windows Form on a separate

    //ui thread and avoid having to marshal the call back to VSTA_Main

    var owsbuh = new workerProgress.OnWorkerStatusBarUpdateHandler(wp.OnWorkerStatusBarUpdate); Globals.ThisAddIn.Dispatcher.Invoke(DispatcherPriority.Normal, owsbuh, "Processing....");

    while (l < k) { //the event below is simply in case I need to reference COM Objects related to ThisAddIn process //var owwoah = new workerProgress.OnWorkerWorkOnActiveHandler(wp.OnWorkerWorkOnActive); //Globals.ThisAddIn.Dispatcher.Invoke(DispatcherPriority.Normal, owwoah); //do work on WorkerThread l++; if (cancelwork) { break; } else { int prog = (int)((float)l / (float)k * 100);

    //*As well as here var owp = new workerProgress.OnWorkerProgressedHandler(wp.OnWorkerProgressed); Globals.ThisAddIn.Dispatcher.Invoke(DispatcherPriority.Normal, owp, prog); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { workcomp = l == k;

    //*As well as here

    var owch = new workerProgress.OnWorkerCompletedHandler(wp.OnWorkerCompleted); Globals.ThisAddIn.Dispatcher.BeginInvoke(DispatcherPriority.Normal, owch); } }


    Any assistance is greatly appreciated.



    Friday, March 1, 2013 2:01 PM
  • if you want to have responsive UI while doing work with office COM objects, simply start new thread and show your responsive UI there while keeping work on VST_Main thread where it should be.
    Friday, March 1, 2013 2:52 PM
  • I would like to keep the Windows Form handle associated with the activeWorksheet.  If I simply call for the windows form to appear in a new thread of execution I lose two things -- 

    1. Association with ActiveWorksheet
    2. Functionality of my RefEdit control

    Each form has a custom user control which simulates the RefEdit box in Excel.  Since the Windows Form has a user control which requires a reference to the ThisAddIn worksheet object I run into the problem of marshaling the call back to VSTA_Main thread when the Sheet_SelectionChange event fires.

      public partial class RefEdit : UserControl { 
    
      public string Address {
                get { return _txtAdrs.Text; } //textbox object of refedit control
            }
    
      private void SinkEvents() {
                Globals.ThisAddIn.Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(ExcelEvent_SheetSelectionChange);
                Globals.ThisAddIn.Application.SheetActivate += new Microsoft.Office.Interop.Excel.AppEvents_SheetActivateEventHandler(ExcelEvent_SheetActivate);
            }
    
      void ExcelEvent_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target) {
                string address = "";
                try {
                    Excel.Worksheet sheet = (Excel.Worksheet)Sh;
                    if (_state.UseSheetAddress) {
                        foreach (Object obj in Target.Areas) {
                            if (address.Length > 0)
                                address += ",";
                            Excel.Range rng = (Excel.Range)obj;
                            address += "'" + rng.Worksheet.Name + "'!";
                            address += xlUtil.RangeInvoke<string>(rng, "Address", false, false, Excel.XlReferenceStyle.xlA1);
                        }
                    }
                    else {
                        address += xlUtil.RangeInvoke<string>(Target, "Address", false, false, Excel.XlReferenceStyle.xlA1);
                    }
                    _txtAdrs.Text = address;  //<<--Cross-thread error
                }
                catch (Exception) {
                }
            }
    }

    Friday, March 1, 2013 3:10 PM
  • I'm open to any suggestions that would keep my windows form responsive, associated with ThisAddIn.Application.ActiveSheet and maintain functionality of custom RefEdit control which contains both ui objects and references COM objects and events associated with ThisAddIn class.  If I'm not making sense please indicate what and I will attempt to clarify but please bear with me as this is relatively new territory for me and I am doing my best to communicate the problem as I understand. 
    Friday, March 1, 2013 4:35 PM
  • The lack of a response indicates my question is not being understood to the point that no one even knows what to ask for clarification.  Let me try this again.

    I am using Visual Studio 2008 to create an application-level 2003 Excel addin.  The addin creates a menu item in the menubar with items that represent different actions all of which call a different Windows Form object in such a way that the Forms window handle is the Active worksheet from which the Form was initially called.  The forms all contain a custom User Control whose action responds to the sheetselection change event in the active worksheet to populate a textbox on the form(ie. Excels Reference Edit Box).  When all selections on the form are made the user clicks OK and the requested operation begins processing.  The only option the user has at this point is cancel, however, due to the ui thread (VSTA_Main) required to update the Forms progress bar and being the only place where I can reference the ThisAddIn COM objects the Form becomes unresponsive.  I have sidestepped this issue by starting a new thread, creating my own workerProgress class to raise custom events and Marshaling the call from my workerThread back to the main thread using Dispatcher.  If I dispatch too much work the form becomes unresponsive.  Is there a better way to handle my problem?

    Sunday, March 3, 2013 7:34 PM
  • what do you mean by - if i dispatch to much Work the form becomes unresponsive? you are updating Excel file with results marshalled from background thread? this is unnecessary, wait for Work to finish (or cancel by user) to udpate worksheet with all Work done so far.
    Monday, March 4, 2013 6:32 AM
  • The long running operation needs to be synchronous and not asynchronous as entries into the worksheet depend on the previous entry.  Since even operations that could run asynchronously require references to ThisAddIn objects even they must be marshalled back to the ui thread.  So if the UI thread is supposed to synchronise work on the active worksheet, handle references of COM objects, update the progress bar then there is precious little opportunity to remain responsive to user commands such as cancel. 

    The forms where I can begin a new excel process to farm the work out to are not an issue and run very smoothly.  However when all the work must take place on the active worksheet I am struggling to keep a responsive ui.

    Tuesday, March 5, 2013 4:24 AM
  • if by responsive UI you mean your custom form with some sort of progress bar or something like that then simply display it in another thread and keep work in main thread - this way user will have smooth feedback that something is happening.
    Tuesday, March 5, 2013 7:54 AM
  • So basically I should create another form to use as a progressBar/Cancel form and start it in a different thread?  Then when I need to update progress I would grab the instance of progress form and update progress?  I am a little confused when you say "simply display it in another thread" do you mean a new form called from another thread?  How would I display a control in a thread other than the thead it was created on?  should I create a new instance of the progress bar and have it display where?  I will try to implement your suggestion as best as I understand, if it is possible to provide an illustration through sample code I would appreciate it...thank you for your perserverence :P
    Tuesday, March 5, 2013 10:47 AM
  • Thread             Action

    VSTA_MAIN     Real Excel Work, updating worksheet, etc

    Thread 1         Create and display form with Progress bar, cancel button, etc., just post here messages from VSTA_Main to update Progress bar, message in label, etc.

    Tuesday, March 5, 2013 11:45 AM
  • Updating the progress bar may require using invocation to prevent cross threading errors. I just ran into something similar in an Outlook addin where I needed to update a progress bar and a form listing the file being uploaded, upload speed and percentage of upload completion. To get the form updating correctly I needed to call delegate methods that checked for InvokeRequired and did the UI updates from an invocation where required.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "DamianD" <=?utf-8?B?RGFtaWFuRA==?=> wrote in message news:43b2ac01-24d2-4f24-bcbc-d19f26903e0b...

    Thread             Action

    VSTA_MAIN     Real Excel Work, updating worksheet, etc

    Thread 1         Create and display form with Progress bar, cancel button, etc., just post here messages from VSTA_Main to update Progress bar, message in label, etc.


    Ken Slovak MVP - Outlook
    Tuesday, March 5, 2013 3:03 PM
  • yes, begininvoke on any winforms control simply posts message to window message loop and this is exactly what i was proposing by 'post here messages from VSTA_Main to update Progress bar'
    Tuesday, March 5, 2013 3:09 PM