Search code examples
excel-dna

Speeding up the insertion of formulas via xlcFormula via Excel-DNA

I am using Excel-DNA to insert formulas into about 40k rows * 10 columns, and it is quite slow.

 XlCall.Excel(XlCall.xlcFormula, myFormula, new ExcelReference(row, row, column, column));

I managed to improve it dramatically by temporarily disabling the recalculation of cells on update (XlCall.Excel(XlCall.xlcCalculation, 3);), but ideally I would like to find a way to put an entire column of formulas into excel in a single operation (I am assuming this would improve the speed).

I tried passing an object[,] with my call to xlcFormula:

 XlCall.Excel(XlCall.xlcFormula, excelFormulas, new ExcelReference(1, lastRow, columnNumber, columnNumber));

but it put all the formulas into a single field (separated by semicolons). Is there a way to do what I am trying to do, or am I wasting my time on something that is impossible?

Solution

  • You could try it with screen updating also switched off XlCall.Excel(XlCall.xlcEcho, false).

    What about using the Clipboard? You could copy the formulae (with tabs between the columns) to the clipboard, and paste all at once into the Excel sheet. This would probably be as fast as you could get Excel to process the formula strings.