0

There are two ways I know-

_xlWorksheet.Range[_xlWorksheet.Cells[1, 1], _xlWorksheet.Cells[10, 10]].Value2 = myarray.ToArray();

OR

_xlWorksheet.Range["A1", "J10"].Value2 = myarray.ToArray();

OR

Is there any other faster way?

As per my understanding, when I use

_xlWorksheet.Range[_xlWorksheet.Cells[1, 1], _xlWorksheet.Cells[10, 10]]

there will be three calls to interop. But, in case of

_xlWorksheet.Range["A1", "J10"]

there will be only one call.

I am not sure which one works faster.

Ankit Goel
  • 353
  • 1
  • 4
  • 13

1 Answers1

1

As far as I understand your question there is no "FAST" way when choosing .Range[_xlWorksheet.Cells[1, 1], _xlWorksheet.Cells[10, 10]] or Range["A1", "J10"] They are the same.

In Excel, you can refer to a range, say A1:A10, in some of these ways

Debug.Print Sheets("Sheet1").Range("A1:A10").Address
Debug.Print Sheets("Sheet1").Range(Sheets("Sheet1").Range("A1"), Sheets("Sheet1").Range("A10")).Address
Debug.Print Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(10, 1)).Address

Choosing one of the above will NOT determine the performance. What WILL determine the performance is HOW you read/write to them

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • As per my understanding, when I use _xlWorksheet.Range[_xlWorksheet.Cells[1, 1], _xlWorksheet.Cells[10, 10]] there will be three calls to interop. But, in case of _xlWorksheet.Range["A1", "J10"] there will be only one call. – Ankit Goel Sep 09 '15 at 12:20
  • Yes there will be but it doesn't make it slow. – Siddharth Rout Sep 09 '15 at 12:20
  • running my code with multiple options. let's see what comes out :) – Ankit Goel Sep 09 '15 at 12:22
  • When you create a RCW, the RCW for the Range object is created on the GC heap. However what’s worth noting is that the reference is created under the hood on the stack and are then discarded. They do not affect the performance in a "Major" way. – Siddharth Rout Sep 09 '15 at 12:23
  • Also I would opt in for `.Range("A1:A10")` is because it is shorter and cleaner :) In the other two ways, you have to ensure that you qualify the cells inside the brackets as well :D – Siddharth Rout Sep 09 '15 at 12:26
  • yes, but when I use .Range("A1:A10") I have to call a method to convert column number to column alphabet. So this is another overhead. – Ankit Goel Sep 09 '15 at 12:26
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89169/discussion-between-siddharth-rout-and-ankit-goel). – Siddharth Rout Sep 09 '15 at 12:26