difference between declare with 'new' and 'set' keyword?

[Ivan]

Hi,

Dim rst As New ADODB.Recordset
Dim rst As ADODB.Recordset

What's the different ?

also I have the existing program with the keyword "set"

Set xlsMasterRange = xlsActiveSheet.Range("A1")
is it similar to the pointer saying xlsMasterRange refer to the object
xlsActiveSheet.Range("A1") ?

thanks

 

[Chris Nebinger]

Think of an object as a thing. It is different than a
variable.

In order to use an object, you have to dimension it
(declare that you want to use it), then actually
initialize the object.

Dim rst As ADODB.Recordset

That line says that you want to use an object, but you
haven't initialized it yet. If you:

Dim rst As ADODB.Recordset

rst.Open SQL,CurrentProject.Connection

you will get a runtime error 91, Object variable or With
block variable not set


Okay, now you have to initialize it. You have to use the
Set statement to initialize the object.

Set rst = New ADODB.Recordset


Now you have a blank recordset. This code would look like:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open SQL,CurrentProject.Connection


The first two lines can be combined into:

Dim rst As New ADODB.Recordset

This declares the object. The first time you use the
object, it will be initialized. The problem with this is
it's hard to know when the object is actually initialized.



From the MSDN Library:

Generally, when you use Set to assign an object reference
to a variable, no copy of the object is created for that
variable. Instead, a reference to the object is created.
More than one object variable can refer to the same
object. Because such variables are references to the
object rather than copies of the object, any change in the
object is reflected in all variables that refer to it.
However, when you use the New keyword in the Set
statement, you are actually creating an instance of the
object.



Chris Nebinger

 

[Ivan]

thanks...
it's really helpful and clear

Think of an object as a thing. It is different than a
variable.

In order to use an object, you have to dimension it
(declare that you want to use it), then actually
initialize the object.

Dim rst As ADODB.Recordset

That line says that you want to use an object, but you
haven't initialized it yet. If you:

Dim rst As ADODB.Recordset

rst.Open SQL,CurrentProject.Connection

you will get a runtime error 91, Object variable or With
block variable not set


Okay, now you have to initialize it. You have to use the
Set statement to initialize the object.

Set rst = New ADODB.Recordset


Now you have a blank recordset. This code would look like:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open SQL,CurrentProject.Connection


The first two lines can be combined into:

Dim rst As New ADODB.Recordset

This declares the object. The first time you use the
object, it will be initialized. The problem with this is
it's hard to know when the object is actually initialized.



From the MSDN Library:

Generally, when you use Set to assign an object reference
to a variable, no copy of the object is created for that
variable. Instead, a reference to the object is created.
More than one object variable can refer to the same
object. Because such variables are references to the
object rather than copies of the object, any change in the
object is reflected in all variables that refer to it.
However, when you use the New keyword in the Set
statement, you are actually creating an instance of the
object.



Chris Nebinger