When one should use Set [e.g for SpecialCells return value]?

Question

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:

enter image description here

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

I see some API functions use byVal - So there is a byVal params passing `Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) End Sub` — 0x90, Jul 27 '13 at 10:27
@0x90 Passing an object `ByVal` in VBA means passing the reference byval, not copying the object and passing the copy byval as it would happen in C++. Passing an object byval makes sure the called function will not be able to replace the passed object with entirely another object, but it's still able to fiddle with it. — GSerg, Jul 27 '13 at 10:33
Ok so as I tested: the default is byRef param passing unless there is a explicit byRef/byVal declaration. — 0x90, Jul 27 '13 at 10:49

score 1 · Answer 2 · answered Jul 26 '13 at 09:38

Object variables are assigned using the Set keyword. Non-object variables (let's ignore variants for 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)

score 1 · Answer 3 · answered Jul 26 '13 at 09:41

enter image description here

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

There are two flavors of assignments in VBA: one for ordinary variables, which use Let, and one for object variables, which use Set.

An ordinary (string, logical, numeric) variable is one that points to the location in memory where the variable is stored.

An object variable (all the things you find in the language reference under Objects) is one that points to a structure in memory (a VTable) that in turn contains pointers to the object's properties and methods.

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

Daniel Dušek · Answer 4 · 2014-12-10T20:24:30.107

Do not know it this matters, but Value is not default property of Range. Default property of Range is _Default.

enter image description here

It is defined like this _Default([in, optional] VARIANT RowIndex, [in, optional] VARIANT ColumnIndex) and it represents the array of values of particular 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

When one should use Set [e.g for SpecialCells return value]?

4 Answers4

Linked

Related