是个 .在 .Cells 定义时需要在 .Range 中吗? [英] Is the . in .Range necessary when defined by .Cells?
问题描述
人们普遍认为这不是最佳实践".
dim rng 作为范围with thisworkbook '<~~ 可能设置一个外部工作簿与 .worksheets("sheet1")设置 rng = .range(cells(2, 1), cells(rows.count, 1).end(xlup))以以
定义范围的两个
因此始终建议正确限定您的对象,以便代码可以在任何地方运行
It is pretty much widely accepted that this is not 'best practise'.
dim rng as range
with thisworkbook '<~~ possibly set an external workbook
with .worksheets("sheet1")
set rng = .range(cells(2, 1), cells(rows.count, 1).end(xlup))
end with
end with
The two Range.Cells properties that define the scope of the Range object will default to the ActiveSheet property. If this is not Sheet1 (defined as the .Parent in the With ... End With statement), the assignment will fail with,
Run-tim error '1004': Application-defined or object-defined error
Solution: use .Cells
not Cells
. Case closed.
But...
Is the .
necessary in this Range object definition when both the Range.Cells properties inherit the .Parent worksheet property that is defined in the With ... End With statement?
How can this,
dim rng as range
with thisworkbook '<~~ possibly set an external workbook
with .worksheets("sheet1")
' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
set rng = .range(.cells(2, 1), .cells(rows.count, 1).end(xlup)) '<~~ .range
end with
end with
debug.print rng.address(0, 0, external:=true)
... be different from this,
dim rng as range
with thisworkbook '<~~ possibly set an external workbook
with .worksheets("sheet1")
' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup)) '<~~ range not .range
end with
end with
debug.print rng.address(0, 0, external:=true)
We use .range
when the parameters that define the scope of the range are ambiguous; e.g. .range([A1])
The A1
cell could be from any worksheet and will default to the ActiveSheet property without the .
. But why do we need to reference the parent of a range object when the scope that defines it has properly referenced its parent worksheet?
My opinion is slightly different here.
YES it is required. You can't always control where the user may run the code from.
Please consider these few test cases
SCENARIO
Workbook has 2 worksheets. Sheet1 and Sheet2
TEST 1 (Running from a module)
Both Code give same result
TEST 2 (Running from a Sheet code area of Sheet1)
Both Code give same result
TEST 3 (Running from a Sheet code area of Sheet2)
'~~> This code fails
set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))
You will get Application Defined or Object defined
error
And hence it is always advisable to properly qualify your objects so that the code can run from anywhere
这篇关于是个 .在 .Cells 定义时需要在 .Range 中吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!