Does VBA not pass objects?

[AdrianH]

Hi, I have tried to write a function that returns the object returned by Cells. I.e. Instead of writing:

Range(Cells(ActiveCell.Column-1, ActiveCell.Row), Cells(ActiveCell.Column-1, ActiveCell.Row)).Select

I'd like to write:

Range(CellsOffset("R[-1]C"), CellsOffset("R[-1]C")).Select

Here is the code that I wrote:

Sub test()
    Call Range(CellsOffset("R[-1]C"), CellsOffset("R[-1]C")).Select
End Sub

Function CellsOffset(offset)
    localError = False
    
    rIndex = InStr(offset, "R")
    If (rIndex < 1) Then
        localError = True
    Else
        cIndex = InStr(offset, "C")
        If cIndex < 1 Then
            localError = True
        Else
            Row = Mid(offset, rIndex + 1, cIndex - rIndex - 1)
            Col = Mid(offset, cIndex + 1)
            
            If Len(Row) = 0 Then
                Row = ActiveCell.Row
            ElseIf Left(Row, 1) = "[" Then
                If Right(Row, 1) = "]" Then
                    Row = ActiveCell.Row + CInt(Mid(Row, 2, Len(Row) - 2))
                Else
                    localError = True
                End If
            Else
                Row = CInt(Row)
            End If
            
            If Len(Col) = 0 Then
                Col = ActiveCell.Column
            ElseIf Left(Col, 1) = "[" Then
                If Right(Col, 1) = "]" Then
                    Col = ActiveCell.Column + CInt(Mid(Col, 2, Len(Col) - 2))
                Else
                    localError = True
                End If
            Else
                Col = CInt(Col)
            End If
            'MsgBox "rIndex = " & rIndex & "  cIndex = " & cIndex & "  row = " & Row & "  col = " & Col
        End If
    End If
    If localError Then
        MsgBox CellOffset & "(""" & offset & """) is an invalid offset."
        CellsOffset = Cells(ActiveCell.Row, ActiveCell.Column)
    Else
        CellsOffset = Cells(Row, Col)
    End If
End Function

What appears to be happening, is that the return is of type Variant/Empty instead of Variant/Object/Range. Is it not possible to pass back the Range object?

NOTE: this is only an example. I'd be using this for other things.

I'm using VBA 6.5.1053 from Excel 2007 on WinXP SP 3

Thanks,


A

[MikeTheBike]

Hi

Yes you can pass object to and from a sub/functions, but if you want a function to return a range object them it should be defined that way.

Function SomeFunction() as Range

That said, what you are trying to do, and why, is not immediatly apparant, to me anyway.

I can't see why this will not do what you want

ActiveCell.Offs et(-1, 0).Select

??

All of this will fail if the active cell is in row 1

MTB

[AdrianH]

Turns out that just by doing this:

sub b()
 myVar = Cells(1,1)
end sub

And tracing through will show that myVar is Empty.

However if I do this:

function fn(myParam)
 myVar = myParam
end function

sub b()
 fn(Cells(1,1))
end sub

The act of passing it works, myParam contains a Range object, but still assigning it does not, myVar still is empty.

Strange


A

[AdrianH]

Ok, figured it out. The Range object was a Block object (is that equivalent to a stack object?). So to assign it, it needs to be Set.

Set myVar = Cells(1,1)

As for why I didn't use ActiveCell.Offs et(1,1), well, I didn't know about it, and trying to find this information is like pulling teeth out of a toothless old lady. :(

Thanks for the heads up. :)


A

[AdrianH]

Oh, I just saw another reason why I don't want to use ActiveCell.Offs et(1,1). What if I want the rows to be offset but the columns to be absolute? ActiveCells.Off set(1,1) will not do that. But the function I made CellsOffset("R1 C[1]") will. In that example, rows are absolute, and columns are relative.

Thanks again,


A

[MikeTheBike]

Oh, I just saw another reason why I don't want to use ActiveCell.Offs et(1,1). What if I want the rows to be offset but the columns to be absolute? ActiveCells.Off set(1,1) will not do that. But the function I made CellsOffset("R1 C[1]") will. In that example, rows are absolute, and columns are relative.

Yes I am familiar wirh R1C1 notation, but I could not see nothing in you code example that actally tried to determine whether you had relative or absolute addressing in the function argument, but you were not adding a formula to the cell just moving to a cell relative to the current cell, so I don't see that in comes into it.

Having said all that this is this somethin like ?

Function NewRange(ByVal RowOffset As Long, ByVal ColOffset As Integer) As Range
    Set NewRange = ActiveCell.Offset(RowOffset, ColOffset)
End Function

Sub Test()
    NewRange(-1, 0).Select
End Sub

MTB

[AdrianH]

Grrr, how do you friggn' quote in this thing? Cut/paste? There should be a button. :( There probably are parameters for [ quote ] too.

Having said all that this is this somethin like ?

Anyway, I'm not sure what your question is asking. Is the function that I wrote similar to the one you did? No, yours is strictly relative. Mine can mix the two. I've actually renamed it to CellsR1C1 because it is not just for offsets which could be confusing.

Cells() function can actually be used to do things like this:

r = Cells(1, 1).End(xlDown).Row

So without moving the cursor selection, I can determine how many rows I have in column 1. If I wanted to determine how many rows in the column to the left of the active cell's I could do:

r = CellsR1C1("R1C[-1]").End(xlDown).Row

Also, it is a slightly shorter and maybe easier to read then:

r = Cells(1, ActiveCell.Column - 1).End(xlDown).Row

But whatev.


A