何时应该使用Set [例如SpecialCells返回值]？ [英] When one should use Set [e.g for SpecialCells return value]?

问题描述

我害怕我误解了VBA的excel文档，我有这条线似乎是一个错误：

 范围a = Selection.SpecialCells（xlCellTypeConstants，23）
  

但这一个很好：

 设置a = Selection.SpecialCells（xlCellTypeConstants，23）
  

文档声明：

返回一个范围对象所有符合指定类型和值的单元格。

但它实际上返回一个 byRef 对象，这就是为什么我必须使用 Set 。

我在这里想念什么？ p>

这里是 Range.SpecialCells Excel中的方法帮助：

解决方案

 范围a = Sele ction.SpecialCells（xlCellTypeConstants，23）
  

这是无效的VBA ，不管数据类型如何。您不要在变量名前面声明变量类型，就像您在C＃中所做的那样，您不会在声明点初始化变量，就像在VB.NET中一样。

您可以执行以下操作：

  Dim a As Range 
设置a = Selection.SpecialCells xlCellTypeConstants，23）
  

这将保存对 a 。

您还可以执行以下操作：

  Dim a As Variant 
a = Selection.SpecialCells（xlCellTypeConstants，23）
  

这将保存在 a 范围内的单元格值的二维数组。

返回一个范围对象，表示与指定的类型和值匹配的所有单元格。

但它实际上返回一个byRef对象，这就是为什么我必须使用Set。

VBA中没有任何的对象。所有对象都是byref，当你想复制一个对象的引用时，你总是使用 Set 。您需要 Set 的原因是默认属性。每个对象可以具有只提供对象名称时请求的默认属性。这会产生歧义，所以当需要操作对象引用的内容时，您需要说 Set ，并省略 Set when你想要一个对象的默认属性。 Range 的默认属性是值。

I am afraid I misunderstand the documentation of VBA for excel, I have this line which seems to be an error:

Range a = Selection.SpecialCells(xlCellTypeConstants, 23)

But this one is just fine:

Set a = Selection.SpecialCells(xlCellTypeConstants, 23)

The documentation claims:

Returns a Range object that represents all the cells that match the specified type and value.

But it actually returns a byRef object and that is why I have to use Set.

What do I miss here?

Here is Range.SpecialCells method help in Excel:

解决方案

Range a = Selection.SpecialCells(xlCellTypeConstants, 23)

This is not valid VBA, regardless of data type. You don't declare variable type in front of variable name, as you would do in C#, and you don't initialize variable at the point of declaration, as you would do in VB.NET.

You can do:

Dim a As Range
Set a = Selection.SpecialCells(xlCellTypeConstants, 23)

This will save a reference to the range into a.

You can also do:

Dim a As Variant
a = Selection.SpecialCells(xlCellTypeConstants, 23)

This will save in a a 2D array of values of cells in the range.

Returns a Range object that represents all the cells that match the specified type and value.

But it actually returns a byRef object and that is why I have to use Set.

There are no byval objects in VBA. All objects are byref, and when you want to copy a reference to an object, you always use Set. The reason why you need Set is default properties. Each object can have a default property that is requested when only object name is provided. This creates ambiguity, so you need to say Set when you need to manipulate the object reference itselt, and omit Set when you want the default property of an object. The default property of Range is Value.

这篇关于何时应该使用Set [例如SpecialCells返回值]？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！