the set statement

[chris2727272727]

Could someone please explain the difference between


myrange = ("a2:a10") and
set myrange = ("a2:a10")

 

[Norie]

Well the first one will actually create a string "a2:a10" and the second will cause a compile error, Object required.

Now if you had this.

myrange = Range("a2:a10")

Then myrange would be a variant array containing the values in A2:A10.

And with this.

Set myrange = Range("a2:a10")

myrange would be set to an object containing cells A2:A10.

With the second one you would have access to all the properties/methods a range/cell has, not just the value.

 

[chris2727272727]

Set statemnt

yeah sort of understand have you got any examples

 

[Norie]

Re: Set statemnt

yeah sort of understand have you got any examples

Examples of what?

 

[chris2727272727]

examples

examples of when to use the set statement and when to not

 

[Norie]

Well you use the Set statement when you want to create a reference to an object.

You would want to do that if you wanted to say change the properties, eg. background colour, of say a range.

It's actually quite hard to give examples as there are thousands of reasons you might want/need to use Set.

Another example is using it like this.

Set wbOpen = Workbooks.Open(FileName:="C:\MyExcel.xls")

This creates a reference to the newly opened workbook which you can use in later code to work with and manipulate the workbook.

This also saves on having to select/activate which can slow down code and even cause errors.