the set statement

chris2727272727

Board Regular
Joined
Jul 10, 2005
Messages
152
Could someone please explain the difference between


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

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
myrange = Range("a2:a10")
Then myrange would be a variant array containing the values in A2:A10.

And with this.
Code:
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Re: Set statemnt

chris2727272727 said:
yeah sort of understand have you got any examples
Examples of what?:)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,022
Messages
5,569,711
Members
412,289
Latest member
Kingchaos64
Top