CODEWITHSUNDEEP
excelvb.netvsto
Ama
Ama

Reputation: 1565

Does MyRange.Cells(i,j) truly return an Excel.Range?

First things first, I emphasis I am under a VSTO / .Net environment, using Visual Studio 2017. No VBA here.

You have a MyRange as Excel.Range = something object and you want to refer to the top left cell of that range. Two options:

But then, although the documentation indicates .Cells() returns an Excel.Range object, Intellisense will not pick it up as such. For example, MyRange.Cells(1,1).Value2 is not readily available from the Intellisense drop down list. However, if you do manually type in .Value2, it will work no problem.

My question is: Is this some limitation from Visual Studio, or does this have some implication at compilation and subsequently at runtime?

First I started to use .Range("A1"), but that becomes quite unconvenient when dealing with dynamic references, such as an equivalent to .Cells(i,j). So instead, I created my own Extension, which relies on implicit conversion. Is that ok? (second question)

Module RangeExtensions

    ''' <summary>
    ''' Returns a Range object that represents the cell in the specified range.
    ''' </summary>
    <System.Runtime.CompilerServices.Extension()>
    Public Function Cell(ByVal MyRange As Excel.Range, ByVal RowIndex As Long, ByVal ColumnIndex As Long) As Excel.Range
        Return MyRange.Cells(RowIndex, ColumnIndex)
    End Function

    ''' <summary>
    ''' Returns a Range object that represents the cell in the specified worksheet.
    ''' </summary>
    <System.Runtime.CompilerServices.Extension()>
    Public Function Cell(ByVal MySheet As Excel.Worksheet, ByVal RowIndex As Long, ByVal ColumnIndex As Long) As Excel.Range
        Return MySheet.Cells(RowIndex, ColumnIndex)
    End Function

End Module

Upvotes: 0

Views: 98

Answers (1)

TnTinMn
TnTinMn

Reputation: 11801

This is no limitation of Visual Studio, but rather one of the properties on the System.Object type.

rangeReference.Cells is a property on the Range type that returns an Excel.Range object.

rangeReference.Cells(1,1) is a shortcut way of writing rangeReference.Cells.Item(1,1). Item is the default property on the Range object. Unfortunately, Item is defined as a Variant type within Excel and .Net represents the Variant type using the System.Object type. For Intellisense to recognize Item as a Range, it needs to be cast to a Range type.

Example:

Dim rng As Excel.Range = Sheet1.Range("A1:B4")
Dim rngCells As Excel.Range = rng.Cells
Dim specificCell As Object
specificCell = rngCells(1, 1)
' or
specificCell = rngCells.Item(1, 1)
Dim specificCellRange As Excel.Range = CType(specificCell, Excel.Range)

However, if you do manually type in .Value2, it will work no problem.

This implies that you are working with Option Strict Off which allows late-binding; the property is discovered at run-time. Late-binding does impose a performance hit in that Value2 must be discovered and and then retrieved. This is done via extra code that the compiler inserts to support the property retrieval.

Upvotes: 2

Related Questions