VBA for Excel - Setting a range

9
votes

I have a question about set range. There is something that I am trying and it's not working. Let's say I have range r, for example I wrote:

set r = range("a1")

But this range is valid for the active worksheet, for example if I am on worksheet1 and I write r.value = 1 then it will change the value of that cell on the active worksheet. Now I want to change the value of that specific range on another worksheet. So I write

worksheets("specificworksheet").r.value =1

But when I write this, it says Object doesn't support this propert or method. why is that? How can i assign a range (that was already set) for another worksheet? In this case writing

worksheets("specificworksheet").range("a1").value=1

would solve my problem, however, somethimes I have more complicated range, for example if I assinged a cell to r, where I used cells.find(...). Appreciate any help, thanks!

3
vbaexcel
Kudos for not defaulting to Select and Activate upon realising that unqualified Range calls implicitly refer to the active sheet. – Mathieu Guindon

3 Answers

8
votes

When you do:

Set r = Range("A1")

What you're really doing is this:

Dim r As Variant
Set r = Application.ActiveSheet.Range("A1")

So declare all your variables, and always specify Option Explicit at the top of every module - that way VBA will refuse to compile code that uses undeclared variables (or typos). And declare your variables with an explicit type:

Dim r As Range

A Range object knows about its Parent, which is the worksheet it belongs to; that's why, as you noted, this range is valid for the active worksheet. And it remains on that sheet even if you activate another sheet (which you wouldn't need to do 99.999% of the time anyway).

That's why you can't do this:

Worksheets("Sheet42").r.Value = 1

Because r isn't a member of the Worksheet object - it's a local object variable that points to a very specific address on a very specific worksheet. Now, when you do Worksheets("Sheet42"), you're really accessing the default property of the Worksheets collection class, which is its Item property:

Dim sheet As Worksheet
Set sheet = Worksheets.Item("Sheet42")

And the Worksheets collection's Item property returns an Object, which means any member call you add after that is going to be late-bound / resolved at run-time:

Dim obj As Object
Set obj = Worksheets.Item("Sheet42")
obj.AnythingYouWantHereWillCompileAnyway

At run-time, VBA queries the object's interface to look for AnythingYouWantHereWillCompileAnyway, and doesn't find that member - and that's how you get a run-time error 438 - "object doesn't support this property or method".

You can move that type-safety back to compile-time (instead of run-time) by working with early-bound calls, i.e. work with the Worksheet interface/class instead of Object:

Dim obj As Worksheet
Set obj = Worksheets.Item("Sheet42")
obj.AnythingYouWantHereWillCompileAnyway 'nope. that won't compile anymore.

When you ask how can I assign a range (that was already set) for another worksheet?, you assume a Range object is nothing more than an address - and that assumption is wrong.

A Range is much more than just an address. If it were just an address, then it would be a string literal, not an object.

If you want a variable that represents an address, you can do that with a String variable:

Dim a As String
a = "A1"

Dim r1 As Range
Set r1 = Sheet1.Range(a)

Dim r2 As Range
Set r2 = Sheet2.Range(a)
5
votes

You could use Address:

worksheets("specificworksheet").range(r.Address).value=1
2
votes

use With-End With syntax, and precede every Range() (and Cells()) specifications by a dot

With Worksheets("specificworksheet")
    .Range("a1").Value = 1
    .Range("B3:C5").Value = 2
End With