ActiveCell在VBA中Cell和Range()之间的关系 [英] Relationship between Cell and Range() in VBA with ActiveCell

问题描述

我正在学习VBA，并且对于Range()对于VBA的确切理解感到非常困惑.我对OOP和Java/Python等语言有相当扎实的了解，但是VBA在这里给我带来了一些麻烦.

I learning VBA and am really confused as to what exactly Range() takes in for VBA. I have a pretty solid grasp of OOP and languages like Java / Python, but VBA is throwing me a bit for a loop here.

尝试使用时出现错误

Set MyRange = Range(ActiveCell, Range(ActiveCell.End(xlDown)))

我知道正确的解决方法是

I know that the correct solution is

Set MyRange = Range(ActiveCell,ActiveCell.End(xlDown))

但是我试图理解为什么会这样.该错误专门来自Range(ActiveCell.End(xlDown)).我知道.End()从 MSDN文档.

But I'm trying to understand why this is the case. The error is specifically coming from Range(ActiveCell.End(xlDown)). I know that .End() returns a Range object from MSDN documentation.

但是，当我写信

MsgBox (TypeName(ActiveCell))

我被告知ActiveCell是Range对象. Range("A1")可以正常工作(当然)，而Range(ActiveCell)会引发错误，所以我想这可以确认Range()不会接受Range对象.

I am informed that ActiveCell is a Range object. Range("A1") works perfectly fine (of course), while Range(ActiveCell) throws an error, so I guess this confirms that Range() doesn't take in Range objects.

但是，"A1"显然是String.当我在VBA编辑器中键入Range()时，我看到Range()接受Cell作为参数参数.那么在VBA的世界中Cell和Range之间的关系是什么? Range是否从Cell继承?我对为什么Range()接受其他Range对象以及String吗?

However, "A1" is clearly a String. When I type in Range() into my VBA editor, I see that Range() accepts Cell as argument parameters. So what is the relationship between a Cell and a Range in the world of VBA? Does a Range inherit from a Cell? I'm confused as to why Range() accepts other Range objects, and also String?

如果Range()不接受Range对象作为参数，那么以下这段代码为什么起作用?

And if Range() doesn't accept Range objects as parameters, then why does this following piece of code work?

Range("A2", Range("A2").End(xlDown).End(xlToRight))

.End()不是在这里返回Range对象吗?

Isn't .End() returning a Range object here?

推荐答案

范围正在查找表示单元格地址的字符串或两个记录范围范围的范围对象.

Range is looking for a string that represents a cell address or two range objects that note the extent of the range.

ActiveCell本身就是一个范围对象.因此，在Range内使用它时:

ActiveCell is a range object in itself. So when using it inside Range:

Range(ActiveCell.End(xlDown))

范围正在寻找第二个范围对象.

The range is looking for the second range object.

您可以将地址作为字符串返回:

You can return the address as a string:

Range(ActiveCell.End(xlDown).Address)

这将起作用，但将迫使vbe进行大量转换.就像将直流电从交流电转换为直流电为手机充电一样.

Which will work but will force vbe to do a lot of conversions. It is like converting DC power to AC back to DC to charge your phone.

只需使用:

ActiveCell.End(xlDown)

当范围对象起作用时.

Set MyRange = Range(ActiveCell, ActiveCell.End(xlDown))

这是Range("A2", Range("A2").End(xlDown).End(xlToRight))起作用的原因.您将返回两个范围对象. vbe假定"A2"在范围的第一位为Range("A2")

This is the reason Range("A2", Range("A2").End(xlDown).End(xlToRight)) works. you are returning two range objects. vbe assumes the "A2" in the first place of the range to be Range("A2")

Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))

每个内部范围都在使用字符串创建范围对象.

Works as each of the interior ranges are using the string to create the range object.

您的第一个是:

Range("A2", Range(Range("A2")).End(xlDown).End(xlToRight))

如您所见，第二个Range现在正尝试仅使用一个范围对象来查找范围，因此需要两个对象才能成为可行范围.

As you can see the second Range is now trying to find a range with only one range object and it would need two to be a viable range.

这篇关于ActiveCell在VBA中Cell和Range()之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！