VBA range assignment

[Gates Is Antichrist]

Can I accomplish the following through other means? This is an invalid assignment, and Set fails too:
Function foo()
Dim c As Range
c.Row = 5
End Function

 

[Zack Barresse]

This will fail. You haven't set c as anything, remember, it's an Object. You cannot set the Row property of a Range Object, it will already have said property. You can use the property attributes as variables if you'd like. Although as not assigning c to anything makes it fairly hard to retreive any properties from it.

What is it that you're trying to do anyway?

 

[Gates Is Antichrist]

How about this then? This seems to get it. Never mind!

Set c = Range("A1").Offset(4,0)

 

[Zack Barresse]

That would work as it's a Range Object. Although I still don't understand why you would try to set the row property, and not assign it's (property) value to something (e.g. variable, cell value, etc.).

 

[Gates Is Antichrist]

Thanks. The odd approach is because existing code will be called, which presupposes a particular range has been set. (The actual code is more complex than just "5" also; this was a bare simplification to get the syntax)

 

[Zack Barresse]

I figured.

I might suggest, if this function shall be called from another routine, and you wish to pass a range object to the function, use something like this ...

Function foo(myRange As Range)

And call from your routine like this ...

myVariable = foo(Range("A1"))

Then you do have the Range Object set, and it passes the variable to the function which you can also make variable instead of 'static'.

Hope this helps, if you're using it like this.