带有单元格参数的 VBA Excel Range()

VBA Excel Range() with Cell argument

提问by user943870

Why does the following not work:

为什么以下不起作用：

Range(Cells(1,1)).Value = 3

Range(Cells(1,1)).Value = 3

Cells(1,1)should essentially be the same thing as using A1right?

Cells(1,1)本质上应该与使用相同A1吗？

(I realize that I could just do Cells(1,1).Value = 3, but I'm just curious as to why it doesn't work.)

（我意识到我可以做Cells(1,1).Value = 3，但我只是好奇为什么它不起作用。）

I read the MSDN entry and it shows that the first argument must be A1style, yet something like this does work:

我阅读了 MSDN 条目，它表明第一个参数必须是A1样式，但这样的操作确实有效：

Range(Cells(1,1), Cells(2,3)).Value = 2

Range(Cells(1,1), Cells(2,3)).Value = 2

Totally confused.

完全糊涂了。

采纳答案by Woody Pride

When you want to use the Cells property to specify the parameters of the range object (if I remember rightly - I've not been using VBA for some time), then you have to effectively supply two arguments.

当您想使用 Cells 属性指定范围对象的参数时（如果我没记错的话 - 我有一段时间没有使用 VBA），那么您必须有效地提供两个参数。

So if you want to reference a range object that has only one cell, then you need to write:

所以如果你想引用一个只有一个单元格的范围对象，那么你需要写：

Range(Cells(1, 1), Cells(1, 1)).value = "Hello World"

回答by chris neilsen

When Rangeis used with a single parameter, the parameter is is interpreted as a range name.

当Range与单个参数一起使用时，该参数被解释为范围名称。

Range(Cells(1,1))

is the same as using

与使用相同

Range(Cells(1,1).Value)

So you will get a result only is the value of Cells(1,1)is a valid range address in A1style

因此，您只会得到一个结果，其值Cells(1,1)是A1样式中的有效范围地址

Only when passed two range parameters are they interpreted as the corners of a range.

只有当传递两个范围参数时，它们才会被解释为范围的角点。

回答by Willby

Instead of referring to a single cell like this:

而不是像这样引用单个单元格：

Range(Cells(1,1), Cells(1,1))

You can write:

你可以写：

 Range(Cells(1,1).Address)

回答by pashute

For a single cell its much easier: Use the default Cells() function:

对于单个单元格，它更容易：使用默认的 Cells() 函数：

Cells(1,1) = "hello world"

or use a Sheet's Cells() function:

或使用 Sheet 的 Cells() 函数：

Dim sht as Worksheet
Set sht = Sheets("myworksheet") ' or: = Sheets(1)
sht.Cells(1,1) = "hello world" 

For a range you'll have to use two params, as explained in the other answers given here. But the advantage is that you can set a whole range of fields to a value. And you can work on a sheet that isn't the 'Active one', behind the scenes. For example:

对于一个范围，您必须使用两个参数，如此处给出的其他答案中所述。但优点是您可以将整个范围的字段设置为一个值。而且您可以在幕后处理非“活动表”的工作表。例如：

Const colRand = 4
Const colDiff = 5

Dim sht as Worksheet, rngHi As Range, rngRand As Range, rngDiff As Range
Set sht = Sheets("myworksheet") ' or: = Sheets(1)

Set rngHi = sht.Range(sht.Cells(1,1), sht.Cells(3,3)
rngHi = "hello world" 

Set rngRand = sht.Range(sht.Cells(1,colRand), sht.Cells(8,colRand) ' column 4, rows 1-8
rngRand = "=RAND()"

Set rngDiff = sht.Range(sht.Cells(2,colDiff), sht.Cells(8,colDiff) ' column 5, rows 2-8
' using FormulaR1C1 in case the sheet isn't set to use that type of formula
Set rngDiff.FormulaR1C1="=RC[-1] - R[-1]C[-1]" ' on previous columnn, diff between this row and previous row

Explanation:

解释：

The Cells function receives either:
a string parameter- in which you specify the A1_And_Colon Style range
or two Cell parameters- the beginning cell of the range and the end cell.

Cells 函数接收：
一个字符串参数- 在其中指定 A1_And_Colon 样式范围
或两个 Cell 参数- 范围的开始单元格和结束单元格。

So to set the range with 'cells' you need to give both cells divided by a comma:

因此，要使用“单元格”设置范围，您需要用逗号分隔两个单元格：

Range(Cells(1,1), Cells(1,1)) = "hello world"
Range(Cells(2,2), Cells(3,4)) = "you cannot square around, but you can round a square"
Sheets(1).Cells(5,5) = "=Round(Sqrt(5))"

回答by Alex M

I'm writing this answer because I'm learning VBA and it took me the better part of three days to figure out what was happening here, and the official documentation does not discuss this topic at all. This QA is good but the information is a bit scattered, from my perspective today.

我写这个答案是因为我正在学习 VBA，我花了三天的大部分时间才弄清楚这里发生了什么，而官方文档根本没有讨论这个话题。这个 QA 很好，但从我今天的角度来看，信息有点分散。

Here's what I know about using the Cells() property inside a Range() object to reference a single-cell range. Which I need to do all the time!

这是我所知道的在 Range() 对象中使用 Cells() 属性来引用单个单元格范围的知识。我需要一直做的！

Given a valid ws object...

给定一个有效的 ws 对象...

You think this will work:

你认为这会奏效：

ws.Range(ws.Cells(i,j))

ws.Range(ws.Cells(i,j))

It doesn't. You'll get Run-time error '1004': Method 'Range' of object'_Worksheet' failed.

它没有。您将收到运行时错误“1004”：对象“_Worksheet”的方法“范围”失败。

The obvious fix,as described by @Woody_Pride is:

如@Woody_Pride 所述，明显的解决方法是：

ws.Range(ws.Cells(i,j), ws.Cells(i,j))

ws.Range(ws.Cells(i,j), ws.Cells(i,j))

Unfortunately, having to do this is absolutely infuriating, and is not actually strictly necessary.

不幸的是，必须这样做绝对令人气愤，实际上并不是绝对必要的。

What you actually need is, as asserted by @Willby, although the explanation as to why this is the case is actually in the answer by @chris_neilsen:

你真正需要的是，正如@Willby 所断言的，尽管@chris_neilsen 的回答中实际上解释了为什么会这样：

ws.Range(ws.Cells(i,j).Address)

ws.Range(ws.Cells(i,j).Address)

This will also work, as suggested by @pashute (who is wrong in most parts of his explanation):

这也将起作用，正如@pashute 所建议的那样（他的解释的大部分内容都错了）：

ws.Cells(i,j)

ws.Cells(i,j)

Thank youto everyone who contributed on this page; I feel like I now, finally, have the entire picture.

感谢所有在此页面上做出贡献的人；我觉得我现在终于拥有了整个画面。

回答by Mark Walsh

I know sometimes you need a range for other properties other than value. What i would do is make a function to help you:

我知道有时您需要一个值范围以外的其他属性。我会做的是做一个功能来帮助你：

Public Function cellRange(ws As Worksheet, rowNum As Integer, colNum As Integer) As Range
    Set cellRange = ws.Range(ws.Cells(rowNum, colNum), ws.Cells(rowNum, colNum))
End Function

This way you can make cleaner code:

通过这种方式，您可以编写更清晰的代码：

Set ws = ActiveWorkbook.Sheets("Sheet1")
cellRange(ws, 1, 3).Interior.Color = cellRange(ws, 1, 8).Interior.Color

回答by BerendVink

When using "cells", it is required to formulate Object.cells , e.g. Application.cells(2,2) or activeWorksheet.cells

使用“单元格”时，需要制定 Object.cells ，例如 Application.cells(2,2) 或 activeWorksheet.cells