Set or not to Set
 

you use Set when the variable is a reference an object. Worksheets is an
object.

You don't use set when you are assigning a value to a variable.

It is actually

Let vVar = 3
but most people leave the Let statement off.

Set vVar = Object

--
Regards,
Tom Ogilvy

"Mike" wrote in message
...
Hello,

In setting variables, I am mystified as to why certain
variables need to be "set" while other do not.

For example, in the following macro, the variable n is
shown as n = "A" but the variable ws has to be shown as
Set ws = Worksheets("Sheet1"). If I don't use Set, I get
a "Run-time error '91' - Object Variable or With block
variable not set".

The Help file states that variables can be declared as
one of the following data types: Boolean, Byte, Integer,
Long etc. "Worksheet" is not one of the data types - is
this the reason that I have to use Set (????).

The macro (the full macro works):

Option Explicit
Sub DeleteRows()
Dim n As String
Dim ws As Worksheet

n = "A"
Set ws = Worksheets("Sheet1")

Set Rng = ws.Range(n & "1", Range(n & "65536").End(xlUp))

*** rest of macro *****

End Sub

TIA

Mike

Set or not to Set
 

Chip and Tom,

Thank you very much. That explains it nicely.

Regards,

Mike

-----Original Message-----
you use Set when the variable is a reference an object.
Worksheets is an
object.

You don't use set when you are assigning a value to a
variable.

It is actually

Let vVar = 3
but most people leave the Let statement off.

Set vVar = Object

--
Regards,
Tom Ogilvy

"Mike" wrote in message
...
Hello,

In setting variables, I am mystified as to why certain
variables need to be "set" while other do not.

For example, in the following macro, the variable n is
shown as n = "A" but the variable ws has to be shown as
Set ws = Worksheets("Sheet1"). If I don't use Set, I
get
a "Run-time error '91' - Object Variable or With block
variable not set".

The Help file states that variables can be declared as
one of the following data types: Boolean, Byte,
Integer,
Long etc. "Worksheet" is not one of the data types -
is
this the reason that I have to use Set (????).

The macro (the full macro works):

Option Explicit
Sub DeleteRows()
Dim n As String
Dim ws As Worksheet

n = "A"
Set ws = Worksheets("Sheet1")

Set Rng = ws.Range(n & "1", Range(n & "65536").End
(xlUp))

*** rest of macro *****

End Sub

TIA

Mike



.