I am developing a VSTO plugin for Excel 2013 using the Office Development Tools for Visual Studio 2012 released in November 2012.
I have a reference to a worksheet and I am trying to access the value of the cells within a range. This should be a simple task, but for some reason the cell I am trying to access is actually one row higher than the range. I'm not sure what is causing this or if I am going about this the wrong way. The following simple example explains the behavior I see.
var dataEntrySheet = (Worksheet)Globals.ThisAddIn.Application.Worksheets["Data Entry"];
var rangeAddress = dataEntrySheet.Range["A2"].Address
var cellAddress = dataEntrySheet.Range["A2"].Cells[0].Address
The results of the above are:
rangeAddress = "$A$2"
cellAddress = "$A$1"
Does anyone know why this is happening?
It is because of index in here:
Cells[0]
When accessing rows and columns in excel the indexes start with 1. So 0 actually goes one row up. If you would do:
var cellAddress = dataEntrySheet.Range["A1"].Cells[0].Address
you would actually get an exception for trying to access a non existing row. You can even use negative indexes to access rows that are further up.