VBA for Excel - 设置范围

VBA for Excel - Setting a range

提问by mathstackuser123

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:

我有一个关于设置范围的问题。我正在尝试一些东西，但它不起作用。假设我有范围 r，例如我写道：

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

但是此范围对活动工作表有效，例如，如果我在工作表 1 上并写入 r.value = 1，那么它将更改活动工作表上该单元格的值。现在我想在另一个工作表上更改该特定范围的值。所以我写

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

但是当我写这个的时候，它说 Object 不支持这个属性或方法。这是为什么？我如何为另一个工作表分配一个范围（已经设置）？在这种情况下写

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!

会解决我的问题，但是，有时我有更复杂的范围，例如，如果我将一个单元格分配给 r，在那里我使用了 cell.find(...)。感谢任何帮助，谢谢！

采纳答案by Scott Craner

You could use Address:

您可以使用地址：

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

回答by Mathieu Guindon

When you do:

当你这样做时：

Set r = Range("A1")

What you're reallydoing is this:

你真正在做的是：

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

So declare all your variables, and alwaysspecify Option Explicitat 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:

因此，声明所有变量，并始终Option Explicit在每个模块的顶部指定- 这样 VBA 将拒绝编译使用未声明变量（或拼写错误）的代码。并使用显式类型声明变量：

Dim r As Range

A Rangeobject 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).

一个Range对象知道它的Parent，这是它所属的工作表；这就是为什么，正如您所指出的，这个范围对 active worksheet 有效。即使您激活另一个工作表，它也会保留在该工作表上（无论如何，您 99.999% 的时间都不需要这样做）。

That's why you can't do this:

这就是为什么你不能这样做：

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

Because risn't a member of the Worksheetobject - 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 Worksheetscollection class, which is its Itemproperty:

因为r它不是Worksheet对象的成员——它是一个局部对象变量，指向一个非常具体的工作表上的一个非常具体的地址。现在，当您这样做时Worksheets("Sheet42")，您实际上是在访问Worksheets集合类的默认属性，即它的Item属性：

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

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

并且Worksheets集合的Item属性返回一个Object，这意味着您在此之后添加的任何成员调用都将在运行时后期绑定/解析：

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".

在运行时，VBA 查询对象的接口以查找AnythingYouWantHereWillCompileAnyway，但未找到该成员 - 这就是您收到运行时错误 438 - “对象不支持此属性或方法”的方式。

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 Worksheetinterface/class instead of Object:

您可以通过使用早期绑定调用将类型安全移回编译时（而不是运行时），即使用Worksheet接口/类而不是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 Rangeobject is nothing more than an address - and that assumption is wrong.

当你问我如何为另一个工作表分配一个范围（已经设置）时？，你假设一个Range对象只不过是一个地址——这个假设是错误的。

A Rangeis muchmore than just an address. If it were just an address, then it would be a string literal, not an object.

一个Range是远不仅仅是一个地址了。如果它只是一个地址，那么它将是一个字符串文字，而不是一个对象。

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

如果你想要一个代表地址的变量，你可以用一个String变量来做到这一点：

Dim a As String
a = "A1"

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

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

回答by user3598756

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

使用With-End With语法，并在每个Range()（和Cells()）规范前加一个点

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