How to avoid cell selection in Excel while processing in a VSTO add-in

Question

I have developed an Excel add-in that populates a sheet with data.

The main loop to populate the data is:

Excel.Worksheet sheet = workbook.Sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);
int newRow = 2;
// Llena la hoja con el maestro
foreach (var producto in maestro)
{
    sheet.Cells[newRow, 1].Formula = producto.SKU.ToFormula();
    sheet.Cells[newRow, 2].Value = producto.Descripcion;
    sheet.Cells[newRow, 3].Value = producto.Linea;
    sheet.Cells[newRow, 4].Value = producto.Familia;
    sheet.Cells[newRow, 5].Value = producto.UltimoCorrelativo;
    sheet.Cells[newRow, 6].Value = producto.FechaCreacion;
    sheet.Cells[newRow, 7].Value = producto.FechaModificacion;

    newRow++;
}

All works perfectly, however, since this process is run in a STA thread in order to not freeze the UI, a user can do other actions while processing. One of those actions is to click on a cell. That action causes the application to crash inside the for loop, in any instruction.

Exception thown is HRESULT: 0x800AC472

How can avoid that?

maybe that you can try to set a wait cursor during the processing time , see https://stackoverflow.com/a/39544078/3205529 — Malick, Jan 04 '18 at 17:04
it is not an option because that does not avoid user to click anywhere. The other option is to avoid user inputs with a VSTO instruction but that does not allow to click on a Cancel button. Finally, I use a try catch block and if user clicks on a cell and the exception is thrown, a message box appears allowing user to retry processing. — jstuardo, Jan 04 '18 at 18:16
Ok, another idea : maybe application.screenupdating set to False, then True. But I'm not sure if it prevents selections. — Malick, Jan 04 '18 at 18:42

score 0 · Answer 1 · answered Jan 05 '18 at 18:39

Excel does not really support multi threading. If you receive error 0x800AC472 this means that Excel was busy working on an operation, while a second operation was requested, e.g. while you were populating the cells, the user clicked on a cell - or vice versa.

The recommended solution to avoid this error, is to retry after a short time. So, if a System.Runtime.InteropServices.COMException is thrown and (uint) e.HResult == 0x800AC472, then do a System.Threading.Thread.Sleep( someMilleseconds ) and try again. Do this in a loop and rethrow the exception after a maximum of n retries.

score 0 · Answer 2 · answered Jan 16 '18 at 03:30

If UI is not frozen, and there is no modal window over Excel, user always can select cells. You cannot prevent it. So, if you are doing long operations on Excel sheet, you absolutely need to set Application.ScreenUpdating = false. And actually you can create a window in another STA thread and put Cancel button there. When you get exceptions, you can set screenupdating = true. But in your case I suggest another way. From your example it looks like you only want to set values for cells, and these cells are in one range:

sheet.Cells[newRow, 1].Formula = producto.SKU.ToFormula();
sheet.Cells[newRow, 2].Value = producto.Descripcion;
sheet.Cells[newRow, 3].Value = producto.Linea;
sheet.Cells[newRow, 4].Value = producto.Familia;
sheet.Cells[newRow, 5].Value = producto.UltimoCorrelativo;
sheet.Cells[newRow, 6].Value = producto.FechaCreacion;
sheet.Cells[newRow, 7].Value = producto.FechaModificacion;

There is much better and faster way to do it. You can set whole range of values from an array. Here is a good example. The same works for formulas. So instead of 7 operations for each producto, you have just 2 for all of them. After such optimization, maybe you don't even need Cansel button.

How to avoid cell selection in Excel while processing in a VSTO add-in

2 Answers2