0

I'm a beginner learning VBA currently, and confused by the inner workings of Set. This started with me having great confusion as to why Set was not needed for certain assignments. For example, the following block of code runs fine:

Block 1 Code:

TempPercentile = Application.WorksheetFunction.PercentRank(Range("L2", Range("L2").End(xlDown)), ActiveCell.Value)

However, this code throws a compile error with VBA complaining an object is needed:

Block 2 Code:

 Set TempPercentile = Application.WorksheetFunction.PercentRank(Range("L2", Range("L2").End(xlDown)), ActiveCell.Value)

This occurs even when I declare TempPercentile earlier in my code: Dim TempPercentile As Double. My debugger highlights PercentRank as the source of the error, but I actually believe it is because I am attempting to use Set to create a new double.

I understand from this StackOverflow post that Dim is used to declare variables and objects, and Set is used to set the value of an object-reference. Is the reason why the second block of code doesn't work because Set is only used to set the value of objects, and TempPercentile is a Double? The mentioned SO post states that there is no use for Set unless as an object reference. Is this because Set calls some sort of Factory constructor method (which obviously is not needed for a primitive)?

Second, is Set simply creating a memory reference to the object, or is it actually duplicating the copy in memory?

Does Set therefore function like a constructor in Java?

This article states that

the Set keyword is used to create new objects, creating a new Range, for example.

If this is true, doesn't this create lots of issues with performance? Every time you want to declare and set an object, you have to create a new object. I'm asking because if I have very large Objects, like entire worksheets that I am trying to use Set on, I do not want to overload memory.

Yu Chen
  • 6,540
  • 6
  • 51
  • 86
  • 1
    `Set` is only used to assign a reference to an object to a variable. `Let` (the use of which is optional, and very rarely used) is used to assign a value to a variable. So you would `Set` things like a `Range`, or a `Worksheet` (so perhaps `Dim rng As Range : Set rng = Range("A1:A5")`, but you don't use `Set` for values (so perhaps `Dim col As Long : col = Range("A1").Column`). And `Set` doesn't create a new object, it is setting the variable on the left-hand side of the `=` to hold a **reference** to the existing object on the right-hand side. – YowE3K Sep 19 '17 at 00:09
  • @YowE3K thanks for that explanation. So if `Set` is not creating a new object, is this article (http://www.homeandlearn.org/the_set_keyword.html) wrong? – Yu Chen Sep 19 '17 at 00:11
  • In (eg) Set `xlApp = Application` xlApp doesn't get a copy of the Excel Application object, just a reference. See Hans' answer here https://stackoverflow.com/questions/9481140/exposing-property-as-variant-in-net-for-interop/9924325#9924325 for an explanation of why VBA has `Set` in addition to (the typically omitted) `Let` – Tim Williams Sep 19 '17 at 00:29
  • 1
    technically, the _homeandlearn.org/the_set_keyword.html_ is wrong .... in the example on the webpage, `NewRange` is a reference to the `Range("A2:A6")` object .... if you modify cell `A2` then `NewRange` will reflect that change – jsotola Sep 19 '17 at 00:46
  • I think that link is designed for beginners, and it is easier for beginners to think about the variable being a new object rather than just a reference to an object. So pedantically the webpage is probably incorrect, but if you pretend it is correct (without worrying about memory usage) you won't be too far off the mark. – YowE3K Sep 19 '17 at 00:47
  • @jsotola And, of course, vice-versa - i.e. a change to `NewRange` will change the values of cells A2:A6. – YowE3K Sep 19 '17 at 00:49
  • @YowE3K, that is true. i should have mentioned that, if `NewRange` was a new object then the change to `A2` would not be reflected in `NewRange` – jsotola Sep 19 '17 at 00:58

0 Answers0