How do I copy the functionality of Range.Cells in my own class?

I am trying to emulate the Range.Cells(row, col) property in my own class. My .Cells property does update the specified cell correctly.

The problem, however, is that as I'm typing d.Cells(1, 3) =, after the equals sign intellisense will suggest "Cells(row As Long, col) as Range". I'm not sure if this will cause problems for me down the track.

The Cells property is defined like this:

Property Get Cells(row As Long, col As Variant) As Range

    ' Get the column number for the requested cell
        Dim c As Long
        If IsNumeric(col) Then
            ' ensure it is an int
                c = CInt(col)
        ElseIf VarType(col) = vbString Then
            ' Get column number from the header name
                c = Me.Column(CStr(col))
        Else
            ' Otherwise, variant type is not supported
                Exit Property
        End If

    ' Return the requested Cell if column number is valid
        开发者_运维技巧If c > 0 And c <= pHeaderRange.Columns.Count Then
            Set Cells = pHeaderRange.CurrentRegion.Cells(1 + row, c)
            ' the row is +1 because pHeaderRange.CurrentRegion also returns 
            ' the header row
        End If

End Property

I have also tried this:

Public Property Get Cells(row As Long, col As Variant) As Range
    ' same code as above
End Property
Public Property Set Cells(v As Variant)
    ' some code here
End Property

But I get the message: "Compile Error: Definitions of property procedures for same property are inconsistent, or property procedure has an optional parameter a ParamArray, or an invalid Set final parameter."

I think I get the compile error because of parameters I have included in this line Property Get Cells(row As Long, col As Variant) As Range. But I need these parameters to select the cell.

What is the correct way to define the .Cells property in a user defined class so that it works the same way as Range.Cells?

Full code is:

Option Explicit

Private pHeaderRange As Range

'
' Sets the Range of the header row.
'
' -r   Range   The header row is expected to the in the CurrentRegion of r.
'
Property Let Header(location As Range)

    ' if range is empty, only the top, left cell will be selected
        Dim r As Range
        Set r = location.CurrentRegion

    ' if top row is blank, then remove top row from the range
        If WorksheetFunction.CountA(r.Rows(1)) = 0 Then
            ' dont (and cant) resize unless there are multiple rows in the range
                If r.Rows.Count > 1 Then
                    Set r = r.Resize(r.Rows.Count - 1, r.Columns.Count).Offset(1, 0) ' resizes and repositions range
                Else
                    ' the is no header, only a blank cell
                        Set pHeaderRange = r
                        Exit Property
                End If
        End If

    ' find the starting column of the header row
        Dim startCell As Range
        Dim endCell As Range
        Set startCell = r.Cells(1, 1)

        If IsEmpty(startCell) Then
            ' if startCell is empty, look for header to the right
                Set startCell = r.End(xlToRight)
        ElseIf IsEmpty(startCell.Offset(0, -1)) Then
            ' if cell to the left is empty, we have already found the start of the header
        Else
            ' otherwise move to left to find the start
                Set startCell = startCell.End(xlToLeft)
        End If

        ' find the last column of the header row
            If IsEmpty(startCell.Cells(1, 2)) Then
                ' if cell to the right is empty, header row only contains one cell
                    Set endCell = startCell
            Else
                ' otherwise move to right to find the end
                    Set endCell = startCell.End(xlToRight)
            End If

    ' store the header range
        Set pHeaderRange = Range(startCell, endCell)

    ' debug
        pHeaderRange.Select

End Property


'
'
Public Property Get Cells(row As Long, col As Variant) As Range

    ' Get the column number for the requested cell
        Dim c As Long
        If IsNumeric(col) Then
            ' change to int
                c = CInt(col)
        ElseIf VarType(col) = vbString Then
            ' Get column by header name
                c = Me.Column(CStr(col))
        Else
            ' Otherwise, variant type is not supported
                Exit Property
        End If

    ' Return the requested Cell if column number is valid
        If c > 0 And c <= pHeaderRange.Columns.Count Then
            Set Cells = pHeaderRange.CurrentRegion.Cells(1 + row, c) ' the row is +1 because CurrentRegion also returns the header row
        End If

End Property
Public Property Set Cells(v As Range)
    ' some code here
End Property

'
' Returns the entire column range of the header that matches the index.
'
' -name String  The header name to find
'
Public Property Get Column(name As String) As Long

    ' Find header
        On Error Resume Next ' continue even if name is not found (ie Find returns an error)
        Dim r As Range
        Set r = pHeaderRange.Find(name)

    ' return column number
        Column = r.Column - pHeaderRange.Column + 1

End Property

http://msdn.microsoft.com/en-us/library/gg251357.aspx

The parameters for Property Get, Property Let, and Property Set procedures for the same property must match exactly, except that the Property Let has one extra parameter, whose type must match the return type of the corresponding Property Get...

The problem is that you have arguments in your Get that aren't in your Set. So this would work

Public Property Get Cells(lrow As Long, vcol As Variant) As Range


End Property
Public Property Set Cells(lrow As Long, vcol As Variant, v As Range)


End Property

Except that it makes no sense (which you already know). The reason Cells works in the Excel object model is that it's a read-only property (there's a Get, but no Let or Set). The Cells property returns a Range object, but you can't set Cells. I'm not sure what you're trying to accomplish with your Set statement, but perhaps you don't need it. You don't seem to have any module level variables to store it.

If I understand well your question, maybe you could try:

Public Property Set Cells(row As Long, col As Variant) As Range
    'code to set the class
End Property

See some tips on this thread: Set property of vba class with object reference