When should I use Set [for example, for the return value of SpecialCells]?

Question

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

Range a = Selection.SpecialCells(xlCellTypeConstants, 23) 

But this is very good:

 Set a = Selection.SpecialCells(xlCellTypeConstants, 23) 

Documentation Requirements:

Returns a range of an object that represents all cells matching the specified type and value.

But it actually returns a byRef object, so I have to use Set .

What am I missing here?

Here is the help of the Range.SpecialCells method in Excel:

Answer 1

 Range a = Selection.SpecialCells(xlCellTypeConstants, 23) 

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

You can do:

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

This will keep the range reference in a .

You can also do:

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

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

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

But it actually returns a byRef object, so I have to use Set.

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

Answer 2

Object variables are assigned using the Set keyword. Object-less variables (let ignore options now) do not use the Set keyword

 dim a as int dim b as string dim c as boolean a = 1 b = "hello" c = false dim a as Range dim b as Worksheet dim c as PivotTable set a = ActiveSheet.Range("a1") set b = ActiveSheet set c = ActiveSheet.PivotTables(1) 

Answer 3

Range.SpecialCells Method returns a Range object that represents all cells that match the specified type and value. set keyworkd is used to assign an object reference.

There are two assignment options in VBA: one for regular variables that use Let and one for object variables that use Set.

A regular (string, logical, numeric) variable is one that indicates the place in memory where the variable is stored.

The object variable (all the things that you will find in the language link in the "Objects" section) refers to the structure in memory (VTable), which, in turn, contains pointers to the properties and methods of the object.

Link http://www.excelforum.com/excel-programming-vba-macros/693357-when-to-use-the-keyword-set.html

Answer 4

I don’t know, this is important, but Value not the default property for Range . The default property of Range is _Default .

It is defined as _Default([in, optional] VARIANT RowIndex, [in, optional] VARIANT ColumnIndex) and is an array of values ​​of a certain range.

 Sub test() Dim rng As Range Set rng = ActiveSheet.Range("A1:C3") ' all cells in range rng become value of 1 rng.Value = 1 ' all cells in range rng become now value of 2 rng.[_Default] = 2 ' first cell in range rng become value of 3 rng.[_Default](1, 1) = 3 ' nothing changes rng.Value()(1, 1) = 4 Dim a1, a2 ' however both Value and _Default return same array of variants a1 = rng.Value a2 = rng.[_Default] End Sub