Serious Error while debugging

Serious Error while debugging

• GadgetGeek

  Hi Prashant,

  Let's make one thing clear, the original message that you were getting in the debugger is not an error. It is a message meant to help you avoid common mistakes, they call it Managed Debugging Assistant (MDA). You can turn these helpful messages off by unchecking the MDA box in VS IDE (Click Debug --> Exceptions). So, you can choose to ignore the message complete and continue doing what you were doing originally.

  The larger issue here is that the were you to populate your 6000 cells at one go, Excel UI will freeze for a long time (lets say 90 seconds). Of course, this is not good from the user experience point of view.

  The solution suggested above is to spin up a new thread to populate the cells.
  Pros: (1) Excel UI doesn't freeze
  Cons: (1) Takes significantly more time because of marshalling between STA threads
  (2) You will need to consider if portions of your addin code need to be thread-safe

  For better perf, set a range of cells in one call
  With any of the above to options, you should not be setting each cell one at a time. Instead pass an array of values to Excel to set the entire row in one call. This will improve your performance whether you spin off a new thread or not.
  http://blogs.msdn.com/eric_carter/archive/2004/05/04/126190.aspx

  Finally, the best option may be to redesign your VSTO application such that you import the data using another mechanism e.g. csv file. This seems to be very fast even with 6000 cells. You can write a .csv file in the Temp folder and import that file into the current sheet. Through Excel UI you would achieve this by clicking Data --> Import External Data --> Filter for .csv files and select your file --> Click OK. You can record a macro while doing this and see the relevant code.

  
  
  

• vitaliyk

  You could try spinning up a new STA thread from you VSTO code, create one function that performs all code for filling up the cells in the worksheet and then execute this function in you new thread.

  I would think that the .NET runtime ensures that you are pumping message in your new STA thread even in long running operations. And execution would return to Word's STA thread objects as soon as the event handler returns, so, that thread won't be blocked.

  Of course you will get some more perf hit this way because of the COM marshalling that will need to happen between the two STA threads. But you have to see which one is a better approach for you.

  Let us know how it goes and if you need a code snippet. Btw. how many cells are you populating and with what kind of data

  Thanks.

  
  
  

• CrowleyM

  I tried populating 5000 rows and 5 colums with integer values using the solutions suggested above and it works.

  It also helps that if you spin off a new thread to do your massive populate, then Excel UI won't freeze for 90 seconds which is quite bad for user experience.

  Code:

  public partial class Sheet1
  {
      System.Threading.Thread t;
     
      private void Sheet1_Startup(object sender, System.EventArgs e)
      {
          // Populate 25000 cells in the worksheet  
          t = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(Sheet1.FillSheet));
          t.SetApartmentState(System.Threading.ApartmentState.STA); //causes "this" to be marshalled
          t.Start(this);
      }
  
      // This function will execute on another thread
      public static void FillSheet(object sh)
      {
          Sheet1 sheet = (Sheet1)sh;
          for (int i = 1; i < 5000; i++)
          {
              sheet.Range["A" + i.ToString(), System.Type.Missing].Value2 = i;
              sheet.Range["B" + i.ToString(), System.Type.Missing].Value2 = i;
              sheet.Range["C" + i.ToString(), System.Type.Missing].Value2 = i;
              sheet.Range["D" + i.ToString(), System.Type.Missing].Value2 = i;
              sheet.Range["E" + i.ToString(), System.Type.Missing].Value2 = i;
              sheet.Range["F" + i.ToString(), System.Type.Missing].Value2 = i;
          }
      }
  }

  Also, instead of populating one cell at a time you can pass 2d arrays to populate larger batches of cells at one go. Since, there is a lot of marshalling going on here, it will make a huge difference if your calls are chunkier. You can find more information online on how to pass 2d arrays and set a whole range of cells instead of one at a time.

  Hope this helps.

  
  
  

• Ms Rain

  Whats the code hard to tell what you were doing just from the error message. Does the message occur every time you run the code could it be that the user interface has been changed like by accident typed a letter in a cell and did not hit enter I know that will cause an odd error
  
  
  
  
  
  

• Borook

  Actually I am not binding my excelsheet with ListObject. Instead I am using for loop to fill data into the corresponding cells of excelsheet.

  And when I run the application, all data get filled into the corresponding cells but its very slow. And as the filling of data get prograsses into the excelsheet through for loop, after some time I get this error.

  How can we resolve this error.

  Thank you

  
  

• Dal

  Hi Apurva,

  Thanks for your helpful response.

  I have tried your above solution and also I am not getting above stated error that I was getting earlier.

  But through this I have one more serious problem:

  Filling data to excelsheet through threading (with your solution) is very very slow i.e. for filling just single row, it takes approx 4-5 second. Now If I have more than 100 rows, you can suppose, how much time it would take.

  Actually I have more than 60 columns to my sheet, which should be generated dynamically & get filled with appropriate data.

  Please provide some suitable solution that could be much helpfull. Do you have any code example by which I can pass 2D array here

  Thank you.

  
  
• Serious Error while debugging