Exception HRESULT: 0x800A03EC when inserting an Excel formula

Question

This is the exception I'm getting:

System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Formula(Object value)

My code looks like this:

Range rng = activeWorksheet.get_Range("A1");
rng.Formula = "=SUM(A4*C4;A5*C5;A6*C6;A7*C7)/SUM(A4:A7)";

Whenever I run this code I get the previously mentioned exception. However, when I run this code:

Range rng = activeWorksheet.get_Range("A1");
rng.Formula = "=SUM(A4:A7)/4"

This works perfectly. No exception.

I've checked both formulas, they work perfectly fine in my Excel. I've tried setting:

Application.Calculation = XlCalculation.xlCalculationAutomatic;

This does not help at all, I've been googling this solution and have not found anything useful. Does anyone have a clue what might be wrong?

Are you sure you're using the right sign? `;` instead of `:`? Just a guess. — Robin Dorbell, Jan 19 '16 at 14:25
Yeah, using the correct sign, I must've checked 10 times.. It also works in an actual Excel worksheet, so there isn't a formula issue.. — Peroxy, Jan 19 '16 at 14:27

score 7 · Accepted Answer · answered Jan 19 '16 at 15:00

7

Excel's COM interface speaks American so you need to use the US list separators in the formula strings. replace your semicolons with commas and you should be fine.

Range rng = activeWorksheet.get_Range("A1");
rng.FormulaArray = "=SUM(A4*C4,A5*C5,A6*C6,A7*C7)/SUM(A4:A7)";

answered Jan 19 '16 at 15:00

jkpieterse

2,727
1
9
18

When interfacing with Excel this is a must-read: http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm – jkpieterse Jan 19 '16 at 15:21

cronos2546 · Answer 2 · 2016-01-19T15:02:52.123

0

You need to update your formula to an array formula using the FormulaArray property of the range object, using { } and an R1C1 reference.

https://msdn.microsoft.com/en-us/library/office/ff837104.aspx

"=SUM(R4C1*R4C3;R5C1*R5C3;R6C1*R6C3;R7C1*R7C3)/SUM(R4C1:R7C1)"

edited Jan 19 '16 at 15:02

answered Jan 19 '16 at 14:28

cronos2546

1,088
7
16

Ok, now this does get inserted into the worksheet, however, this does not get calculated by Excel, it's treated as a string? I've tried `rng.FormulaArray = "{=SUM(A4*C4;A5*C5;A6*C6;A7*C7)/SUM(A4:A7)}"; `but it does not calculate anything. – Peroxy Jan 19 '16 at 14:40
Well that was on me, you have to use R1C1 for the FormulaArray property. Updating my answer. – cronos2546 Jan 19 '16 at 14:54
Actually, just try removing the {} and use the formulaarray property – cronos2546 Jan 19 '16 at 14:59
Just tried this: `rng.FormulaArray = "{=SUM(R4C1*R4C3;R5C1*R5C3;R6C1*R6C3;R7C1*R7C3)/SUM(R4C1:R7C1)}";` this does not calculate anything and inserts the actual string into the cell.. I've enabled R1C1 in my excel.. – Peroxy Jan 19 '16 at 15:02
Yea, so no need for R1C1 reference, the documentation on MSDN is wrong, I'm updating on github no – cronos2546 Jan 19 '16 at 15:02

Exception HRESULT: 0x800A03EC when inserting an Excel formula

2 Answers2

Linked