C# Excel add-in exception when formula bar is selected and accessing Range.Value

Question

I am just starting with Excel 2010 Add-in with VSTO and is quickly stumped by an exception generated by this code:

public void DoIt()
{
      Excel.Range selectedRange = Application.Selection as Excel.Range;
      if (selectedRange == null)
      {
           System.Windows.Forms.MessageBox.Show("Nothing selected");
      }
      else if(selectedRange.Cells.Count > 0)
      {
           selectedRange[1, 1].Value = "=2+3"; // exception on this line.
          selectedRange[selectedRange.Rows.Count, selectedRange.Columns.Count].Value = "Birthday";
      }
}

The exception can be reproduced by first clicking on a single cell in the worksheet, then click the formula bar and then run the above function (I call it via a Ribbon button).

Could anyone advise what is happening and how to handle this exception? Thanks you.

I don't know.. that's what I am asking... "Exception from HRESULT: 0x800A03EC" — Jake, Feb 02 '12 at 02:00
possible: http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range — Mitch Wheat, Feb 02 '12 at 02:03
Thanks, but the marked answer doesn't help. I think more importantly I also want to understand the problem, not just circumventing blindly, so as to avoid it completely. — Jake, Feb 02 '12 at 02:08

score 2 · Accepted Answer · answered Feb 02 '12 at 02:12

The exception is because you can't run code while in formula-editing mode. You also can't do quite a few other things. For example, click into the formula bar and click Ctrl-N, which normally opens a new workbook. Nothing happens.

I think you need to just Catch the exception and exit the Sub, which will mimic normal Excel operation.

Ideally you'd want to disable the button, just like most Excel ribbon buttons are when in formula-editing mode. I don't know if that's possible though.

C# Excel add-in exception when formula bar is selected and accessing Range.Value

1 Answers1