Declaring 'New' in dim statement

[vbc22]

I'm just curious, I read books that show code written with the 'New' keyword after the declaration statement, and was wondering what's the difference between doing that and simply inserting the 'New' keyword beside the object on the Dim statement?

New object set after Dim statement:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

New object set on Dim statement:

Dim rst As New ADODB.Recordset

Anyone?

 

[Golom]

This

Dim rst As New ADODB.Recordset
Set rst = Nothing
rst.Open etc.

Will work because the "As New" creates a new instance of the object every time it is referenced.

OTOH

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst = Nothing
rst.Open etc.

will blow up with an "Object Required" error because you must explicitly create a new instance of the object with this syntax.

 

[vbc22]

Yes, I realize that, but it seems to me that programmatically, the standard is to set the recordset object to 'New' on a separate line from the declaration statement.

I'm just wondering what value is there in doing that? Or am I misconstruing it as a standard?

 

[RoyVidar]

The code is faster when it doesn't have to check whether or not the object is instantianted each time it is used/referenced.

In some situations, especially when automating other applications, implicit instantiation/object creation will often cause memory leak, and leaving extra instances of the automated application in memory, but I think this will seldom occur with ADO/ADOX.

It seems like a standard, and is also recommended by Getz et al in my version of adh.

Roy-Vidar

 

[MajP]

Yes you should do two lines. The savings are not worth it. Two things are taking place Declaring the variable and then instantiating it.
Dimensioning creates a place holder in memory only. These do it:
Dim
Public
Private
Global

Instantiating actually creates the object. It is done by
Set myObject = New objClass
Or if the object exists already. Such as an open form, you do not use the new because it is not new it already exists.
Set myForm = forms("formName")
In this way it is very clear when an object is actually created or assigned to a variable
This does both
Dim myObj as New Obj

I would not do it. You create the object before your need it, and it is not clear when it is created. And I have no idea what Golom is saying.

Hope this helps.

 

[MajP]

Roy,
Is this true with compiled code? I do not understand how that is possible.

The code is faster when it doesn't have to check whether or not the object is instantianted each time it is used/referenced

 

[RoyVidar]

Well, some simple, not very academic tests I did some while ago seems to indicate such, Getz et al, adh (2000 version) page 233 also claims that. Do you have other results to share?

Roy-Vidar

 

[MajP]

No results, I was just curious. I can not see how it would make a difference, but I was hoping you had an explanation. Are you saying that implicit or explicit is faster? I have ADH, and think it is a great book. I will see if they have more explanation.

 

[vbc22]

Thank you everyone for your comments.

I did a little digging...well, it was actually for something else and came upon this topic in my book. It's authored by Alison Balters, and the book is Mastering Access 2002 Enterprise Development.

On Page 312 after a code example, she writes:

All of the examples in this chapter first declare a variable using the keyword Dim and then instantiate it using the keyword Set. You can remove the Set statement by specifying the New keyword in the Dim statement. For Example, you could use:

Dim rst as New ADODB.Recordset

Although this works, it is not considered desirable. This is because you have little control over when the object is placed in memory. For example the variable is public, Access places it in memory the moment anything in the module is referenced. Separating the Dim and Set statements allows you to declare the variable wherever you like and place it in memory when you need to. Another bad side effect of declaring a variable this way is that VBA wraps the statement with checks to see if the code already instantiated the object. This further slows down processing.

Well after reading that, I guess I won't be lazy and just push that extra line to instantiate then.

Any comments?

Thank you all.

 

[MajP]

There is my answer. Thanks

Another bad side effect of declaring a variable this way is that VBA wraps the statement with checks to see if the code already instantiated the object. This further slows down processing.
[code]

 

[RoyVidar]

I was kind of curious, too, if you had other experience, as I have a tendency of accepting what those guys say, especially when it matches some simple testing.

Chapter 6: ActiveX Data Objects of their 2000 book is available online, scan down to the "Dimension and Set in One Stetement" section, where it says roughly the same as Balter (and for those who only have Balter's Desktop Edition for the 2002 version, the same quote is on page 585-586).

Roy-Vidar

 

[Zion7]

Is it conclusive Then?
Sorry, I'm not sure if I followed all the Quotes.
It seems, under any circumstances, instantiating the variable after the declaration is faster, then the one line method?

I was in the habit of using both. If I knew unequivocally, that i would need to instantiate the variable, I would use one line.

Function PrimaryKeys(strTable) as String
Dim rec As New ADODB.Recordset
   rec.Open strTable, CurrentProject.Connection...
       If not rec.EOF Then
            PrimaryKeys = rec.GetString(adclipString,,"",", ")
        Else
             PrimaryKeys = ""
        End If

    rec.Close:Set rec = Nothing
End function

or, if contigent upon certain results then...

Sub FindRecord()
Dim rec As ADODB.Recordset
Dim varRecord As Variant

     varRecord = DLookUp("pkID","tblCountry","txtCountry Like "A*")

   If IsNull(varRecord) Then
        Set rec = New ADODB.Recordset
         .....
    Else....

...just curious, Thanks!