With New Object 2

[CCLINT]

"Get in...and Get right back Out"

Does anyone have any input/comments, pros/cons, concerning the use of the following With/New methods:

With New SomeObject
'Some code
End With

Or:
With CreateObject("SomeLibrary.SomeObject&quot
'Some code
End With


As in:

'conn is an already opened connection to a DB
With New ADODB.Recordset
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "SELECT * FROM myTABLE WHERE SomeField='SomeCriteria'"
Set .ActiveConnection = conn
.Open , , , , Options:=adCmdText

If Not .EOF Then Debug.Print .Fields(0).Value
End With

'Or simply put:
With New ADODB.Recordset
.Open "SELECT * FROM myTABLE WHERE SomeField='SomeCriteria'", conn, adUseServer, adLockOptimistic, Options:=adCmdText
If Not .EOF Then Debug.Print .Fields(0).Value
End With

Please note: There are no recordset object variables declared, and therefore, after the End With, the object is automatically destroyed.
No Close or Set/Nothing statments.
Common sense is telling me though, that the Close method should still be called prior to the End With.

What do "good programming pratices" say against the use of this With/New variant in VB?

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CajunCenturion]

I definately agree with you in that the Close method should be called. The Close method may flush some pending updates, release DB locks, and other type of housecleaning activities that I would not assume are being handled in the deconstructor. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

Is everyone in agreement that there is nothing "bad" about using the With and New together?

I had posted this as a tip because I think many people do not realize that you can do it this way (with any objects)
Using the ADO object was just an example.

But, I was really looking for a discussion.

One negative thing would be that you cannot pass the recordset to a function, unless you do create a variable.

A positive thing is that it is a quick and easy way to do something.

Anyone else? [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[jeepxo]

makes your code smaller, less typing but will require a little more documentation.

I would class myself as an mid level intermediate programmer but I am the most senior in our company, others use my code for examples. It means I have to document the heck out of everything.

Whenever I use the "with" structure I get tons of questions from jr programmers.
If I hand them the same code broken down line by line they understand it.

That would be my only reservation in using your suggestion. "Each day is like a precious gift, you have to make it count" James Birrell 1994-2002

 

[TheVampire]

"With New" would create an auto-instancing variable. This has advantages and disadvantages. Among these are:

1: Since you are waiting to declare the object exactly when you need it, you may potentially save execution time and memory in your app depending on the code flow.

2: Each time VB references an auto-instancing variable, it always checks to see if it "Is Nothing". This will have an effect on performance, depending on how many times the object is referenced. For example, a For/Next Do/While loop that is run many times inside the "With New / End With" structure.

Robert

 

[strongm]

No, as long as you take the same care to use relevant cleanup code before the End With as you might in, say, a sub that instantiates an object on entry and sets it to nothing on exit (or, when lazy, not bothering with the Nothing since scope rules will do it for you), it seems a sound construct to me.

Frankly:
[tt]
Private Sub DoStuff()
Dim myObject as New FileSystemObject

' Do stuff with myObject
' Do any cleanup necessary

Set myObject = Nothing ' or be lazy, and miss this line out
End Sub
[/tt]
is basically the same as:
[tt]
With New FileSystemObject
' Do stuff with the FSO
' Do any cleanup necessary
End With

 

[mattKnight]

It's a nice construct so long as you think about variable scope / auto instancing etc.

I am more inclined to use in the style of code that strongm shows as example rather than the ADO one.

Until today, I had no idea you could do that!


Nice tip CCLINT

Take care

Matt

 

[DonQuichote]

I use the "with new.." construction very often (I usually call it an anonymous instance). It tends to organize your code in a nice way.
The code within a with-block is often easily extracted into a new sub or function, so it has a tendency to 'guide' you towards refactoring into smaller, more elementary procedures.

Best regards

 

[MatDavies]

I never knew this could be done, just used it for a recordset returned from an ado connection, works a treat.

With cnnADO.Execute("Select * from aTable&quot
if not (.eof and .bof) then
' do something with recordset
end if
.Close
End with

thanks!

Matt

If you can keep your head while those around you are losing theirs, you obviously haven't grasped the seriousness of the situation

 

[strongm]

And taking it further (as I have done in a number of example postings recently), you can also use (assuming you are happy with late binding):

With CreateObject("Object_I_Want_To_Use&quot
'blah blah blah
End With

 

[strongm]

Or the following type of construct...

msgbox CreateObject("scripting.filesystemobject&quot.GetDrive("C:\&quot.FileSystem

 

[chiph]

I guess I'm a little more paranoid -- I always check to see if CreateObject actually gave me something before attempting to do something with it.

I think you could be safe doing this if the method was very short or used only once in a method (if you do it twice in a method, and one of them blows up, how do you tell which one it was in your error log?). This can be ameliorated by using line numbers in your code (with the undocumented Erl function).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[strongm]

Yes. I don't think I'd recommend using the technique illustrated in my last post on a regular basis...

 

[SemperFiDownUnda]

I have to agree with TheVampire. It appears to me that using the
With New ADODB.Recordset
...
End With

Will cause the code to check every statement, that uses the Recordset object, to check if the object is instanciated just as it does with

Dim rs As New ADODB.Recordset

I'm not a 100% on this but it seems correct.

 

[strongm]

No, as far as I am aware the only auto-instantiated objects are those Dimmed as New. Don't be fooled by the fact that the With New construct has the word New in it; so does Set wombat = New object...

 

[TheVampire]

Yes, but before the "Set wombat = New object" you have to have a line to dim is as an object. That's what makes it a non auto-instancing variable.

In the case of "With New" though, there is no seperate line dimming the variable, so it is effectively dimmed in that line just as it would have been if you did "Dim XXX as New", right?

Is there any way to check and see if this is the case or not?

Robert

 

[strongm]

To be honest, I'm not ocnvinced it is all that important.

Generally, the main issues concerning "Dim myobject as New whatever" tend to be:

1) object auto-instantiates, which might give unexpected results on occassion (eg Is Nothing always fails)
2) speed, because there is always a check to see if the object is already instantiated

Issue 1 can, frankly, be ignored, since, between "With New" and "End With" the object remains instantiated; you have no (simple) mechanism for decreasing the object's reference count or accessing the root object directly (only by inference the through the . operator)

Issue 2 is the one that you might think is the killer. However, as we said concerning Issue 1, our object remains instantiated throughout, so the only likely overhead is the (pseudocode) ObjectAlreadyInstantiated check (we never have to reinstantiate, which would be the big performance hit). And that check, in reality (and contrary to a number of articles you might find on the web), has a negligible performance hit.

Here's a timing rig to demonstrate the performace issues. Try it in both the IDE and compiled (on my PC the difference between the fastest and the slowest in the compiled version is between 30-50ms over 100 million iterations)
[tt]
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub Command1_Click()
Dim lp As Long
Dim maxloop As Long
Dim starttime As Long
Dim result As Long

Dim fred As New Collection
Dim jim As Collection
Dim withnew As String
Dim dimnew As String
Dim simpleset As String

Set jim = New Collection


maxloop = 100000000

With New Collection
starttime = GetTickCount
For lp = 1 To maxloop
result = .Count
Next
withnew = GetTickCount - starttime & " - With New"
End With

With fred
starttime = GetTickCount
For lp = 1 To maxloop
result = .Count

Next
dimnew = GetTickCount - starttime & " - Dim New"
End With

With jim
starttime = GetTickCount
For lp = 1 To maxloop
result = .Count
Next
simpleset = GetTickCount - starttime & " - Simple Set"
End With

MsgBox withnew + vbCrLf + dimnew + vbCrLf + simpleset

Set fred = Nothing
Set jim = Nothing
End Sub

 

[TheVampire]

OK, so strongm answered the question, that it really doesn't matter that much in terms of performance. Works for me then.

Robert