Using the "Set" statement

  • In VBA, when is it required to use the "Set" Statement. Is it when you are declaring a range as an object?


    Are there any other times that this is necessary to use Set?

  • One way to think about it is if the "thing" on the left of the = sign is to be an object (anything that you can't put into an Excel cell) of any sort then you need the set, if it is a value (numbers, text, other stuff that you would see in an Excel cell) then you don't. Sometimes its tricky.
    x=range("Whatever") puts all of the values in the range into the variant x.
    set x=range("Whatever") makes x the equivalent of the range.

  • Thanks, Rictchie, that's what I did.


    However, it doesn't specify when it should be used only to use it with an object variable.


    Ah, is this when it should be used -- when assigning an object to a variable (e.g. workbook, worksheet, range, commandbuttons, etc.).

  • Interesting.


    I reading a section about using the new keyword and I now see that VBA uses some key words that are in Java as well.


    Have you found that as well?

  • Yeah, I have found that by taking Java I have been better able to understand Excel better.


    The instructor really stressed the Object Oriented Programming concepts (objects, classes, inheritance, polymorhpism, abstraction, dynamic binding, encapsulation, message passing).


    In Java, you don't a VB editor and you have to create your own methods.


    Now, when I look start looking deeper into Excel, I can see that these same concepts apply.


    "Twas really a great experience to write in Java!


    Is anybody out there a Java programmer as well, besides VBA?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!