CODEWITHSUNDEEP
c#vstoexcel-interop
Julien N
Julien N

Reputation: 3920

Arrays seem to be 0-based instead of 1-based when working with Range and Cells

I'm currently evaluating VSTO to replace the VBA we use with Excel 2010, so I'm absolutely new to this Excel interop thing.

While testing, I sometimes encountered issues while using ranges and cells, sometimes the arrays seem to be 1-based and sometimes they seem to be 0-based, and I didn't find any information on that.

Here is a small example :

var ws = ((Excel.Worksheet)Globals.ThisWorkbook.Application.ActiveWorkbook.Sheets[1]);
var range = ws.Range["B2:D6"];
AddName("testname", range);

range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);

//Code 1
((Excel.Range)range.Cells[2, 1]).BorderAround2(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlThick);
((Excel.Range)range.Cells[4, 1]).BorderAround2(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlThick);

//Code 2
range.Range[range.Cells[2, 1], range.Cells[4, 1]].Interior.Color = ColorTranslator.ToOle(Color.Green);

//Code 3
range.Range[range.Cells[1, 0], range.Cells[3, 0]].Interior.Color = ColorTranslator.ToOle(Color.Red);

The code creates a named range and adds a thick border around, then adds a double border to the cells that represent the bounds of the range I want.
It sets the background to green for the range that uses the same cells, and in red for the range I want.
Here is the result :
enter image description here

What I wanted to do is to select the cells of the first column in the range except the first and last (so red is what I want). But it seems that if I select the cells individually (code 1), it doesn't behave the same as if I create a range from the same cells (code 2).
To get the range I want, I have to shift all indexes by one (0-based) (code 3).

Is there a reason for that, did I miss something ? I don't see anything about that in the MSDN.

Upvotes: 1

Views: 1596

Answers (1)

PaulF
PaulF

Reputation: 6773

Check out the Remarks here : https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.range.aspx. With the Range property the cells are relative to the defined range, hence the shift by 1.

Upvotes: 2

Related Questions