Set Keyword

In terms of VBA syntax, one thing I find annoying is the need to use the Set keyword for assignment of object references. I think I forget it as often as not, particularly when using my own classes. For example:

Dim myrange as Range
Set myrange = Range(“Source_Data”)

Why is this necessary? I’m not aware of other object-based languages that require a similar keyword.

VBA is obviously capable of inspecting the type of the right-hand-side expression, to see whether it is a valid object type (same as the left-hand-side type, or implements it, or nothing). That’s why it raises an error if Set is missing. So why does it need the keyword?

As far as I can tell from the Mediaeval Mystery that is the VBA Reference Manual, there is no valid use of Set with a non-object type on the right-hand-side, so it’s not there to disambiguate any such case from the default (Let) assignment.

If anyone can shed light on this, please comment…


14 Responses to “Set Keyword”

  1. 1 Dick Kusleika May 8, 2009 at 1:02 pm

    I think it’s because objects have default properties. Should the last line of this sub be an object assignment or an assignment of the default Value property?

    Sub TestSet()

    Dim MyRange As Range

    Set MyRange = Range(“A1”)

    MyRange = Range(“B1”)

    End Sub

  2. 2 roymacleanvba May 8, 2009 at 1:43 pm

    Hi Dick,
    Actually, Range.Value isn’t a default property – Range doesn’t have one (whereas TextBox.Value, etc, are defaults). Tricky…

    However, I take your general point. But in this case, MyRange is typed as a Range, so it must be the result of the Range() function that we want here, so no ambiguity.

    I guess the complications are assignments to generalised types (Variant, Object): the left-hand-side type could accommodate either the target object or its default property. And also where the type of the default property is the same as that of the target object (does that ever happen)?

    Given a choice, I’d rather have no default properties and no Set keyword 🙂

  3. 3 Gordon May 8, 2009 at 2:50 pm

    Just be glad we don’t still have to use Set’s deprecated cousin, Let, as well

    Let a = 5


  4. 4 jonpeltier May 9, 2009 at 2:22 am

    VB.Net doesn’t even use Set any more.

  5. 5 dougaj4 May 10, 2009 at 1:34 am

    “Actually, Range.Value isn’t a default property – Range doesn’t have one (whereas TextBox.Value, etc, are defaults). Tricky…”

    Roy – if you declare a variable as a variant then:

    MyVariant1 = Range(“A1:A3”)


    MyVariant2 = Range(“A1:A3”).value

    will both return the same thing, a 3×1 array containing the values in A1:A3


    Set MyVariant3 = Range(“A1:A3”)

    will return a range object

  6. 6 geoffness May 10, 2009 at 8:21 am

    I’m with you on the default properties Roy – they serve no purpose as far as I can tell apart from generating this sort of potential confusion. As far as variants go, my thinking would be, if you want to assign an array, use Range(ref).Value, and if you want to assign a range object, use Range(ref).

    I enjoy working with VBA, but this is one peculiarity of its syntax that sets my teeth on edge.

  7. 7 roymacleanvba May 11, 2009 at 8:17 am

    (Doug wrote)
    “if you declare a variable as a variant then:

    MyVariant1 = Range(”A1:A3″)


    MyVariant2 = Range(”A1:A3″).value

    will both return the same thing, a 3×1 array containing the values in A1:A3”

    So in the first case, the value of the RHS expression depends on the type of the LHS variable? Yuk! And Value is a sort of Default property, but doesn’t show up as such in the Object Browser…

    The second case is okay, although it would be better with a proper Array type (as in most other languages).

    As I said, I could live without default properties.


  8. 8 dougaj4 May 11, 2009 at 10:31 pm

    “The second case is okay, although it would be better with a proper Array type (as in most other languages).”

    Well you can declare the variable as an array of course, but if you do then

    Dim MyArray() as double
    MyArray = Range(”A1:A3″).value

    will generate an error, something like “cannot assign to an array”.

    Goodnesss knows why; you can assign to an array, but only if you call it a variant.

    There may be some logic behind that, but I don’t know what it is.

  9. 9 Mike Woodhouse May 22, 2009 at 10:34 am

    The default property thing is indeed the reason – it’s a legacy thing going back to some ancient version of VB, quite possibly v1.0 although my memory doesn’t seem to have that info any longer.

    Set has gone in VB.Net because default properties have been done away with.

  10. 10 Brock January 5, 2014 at 6:13 am

    Great site. Lots of useful info here. I’m sending it to several pals ans additionally sharing in delicious.

    And certainly, thank you for your effort!

  11. 11 stop smoking tablets April 27, 2014 at 9:15 pm

    Excellent post. I used to be checking continuously this blog and I’m inspired!
    Extremely useful info particularly the ultimate phase :
    ) I care for such info much. I was seeking this particular
    info for a very lengthy time. Thanks and best of luck.

  12. 12 shelve April 9, 2015 at 12:28 am

    It is inside the first room on first floor,
    near the dead body. The shelving would ideally be adjustable so books
    can be stored upright and central to the shelve, again to promote air circulation. The overall intent is that you want to start seeking support that will nourish you and make you feel good.

  1. 1 Daily Dose of Excel » Blog Archive » Dallas Wrap Up Trackback on May 22, 2009 at 1:30 am
  2. 2 payday loans Trackback on November 7, 2014 at 9:32 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

May 2009
« Apr   Jun »

%d bloggers like this: