2

I'm learning VB.Net and trying to transition from VBA. How can I retrieve a range typed object using the row and column index?

In VBA I would use Worksheet.Cells([Row,Column]) and that would return an object of type Range.
However using the Excel Interop the return is typed as Object.
Example:

Dim MyExcel as new Application
Dim MyBook as new Workbook=MyExcel.Workbooks.Open("SomePath\SomeFile.xlsx")
Dim MySheet as new Worksheet=MyBook.Sheets("SomeSheet")
MessageBox.Show(CStr(Sheet2015.Cells(1, 1).Value))

Now, the MSDN for Worksheet.Cells says:

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property.

But the link to the Item property from that page takes you to Worksheet.Item, NOT Range.Item - so maybe it should say

"The Item property is the default property for the Worksheet object..."

The return of the Worksheet.Item property is an object of type Object, which explains why I don't get intellisense :(

So is there another method I can use that returns a Range object from a worksheet by providing the index (Row,Column) of the cell, instead of the address?

I know I can use Worksheet.Range and provide the A1 referenced address, but this to me is far more convoluted than just using the row and column index.

I also know I can cast the Worksheet.Cells[R,C] as type Range, but I'd prefer to find a way to return a range. (See my other question here about working with COM objects as base type Object)

Any suggestions?

Community
  • 1
  • 1
CBRF23
  • 1,340
  • 1
  • 16
  • 44
  • I am not sure there is a way to get a Range directly- I just cast the output of Worksheet.Cells(r,c). Feels dirty, but all COM interop feels dirty to me. If you're interested in more typeful Excel interop you might want to check out http://epplus.codeplex.com/releases/view/42439 . – Jerry Federspiel Jul 10 '15 at 18:03
  • Thanks Jerry, I will have to check that library out. Very cool. I agree with COM seeming dirty. Dirtier than I like at least. Seems like you really need to turn Option Strict off to work with COM objects, or else you end up typecasting everywhere - which I really don't like because you end up hardcoding type in multiple locations. – CBRF23 Jul 10 '15 at 18:09
  • possible duplicate of [Fastest way to get an Excel Range of Rows](http://stackoverflow.com/questions/12363163/fastest-way-to-get-an-excel-range-of-rows) – T.S. Jul 11 '15 at 15:10
  • @T.S. - The link provided shows a solution of how to select an entire row by specifying `row:row` however as far as I know there is no way using `.Range` to specify `Row,Column` as numbers. AFAIK the `Range` member wants to see row specified as an integer (1, 2, 3, etc.) and column as a string (A, B, C, YYY, etc.) – CBRF23 Jul 13 '15 at 20:32

1 Answers1

3

You can specify the range as follows:

sheetName.Range(sheetName.Cells(row1, col1), sheetName.Cells(row2, col2))

Then you can use this range for whatever you want.

Yes, I realise this answer is very late, but I just ran into the same problem...

DrDonut
  • 864
  • 14
  • 26
  • Thanks DrDonut I know your solution is late, and my comment is late, but after looking for a few hours this was the answer that doesn't throw COM (or other) errors in my Excel addin project. – Jacob H Aug 24 '17 at 15:16