Declaring an object, best way?

[JBL312]

When declaring an object what is the best method to use, and why.

Example 1:

Dim oConn As ADODB.Connection

Set oConn = New ADODB.Connection


Example 2:

Dim oConn As New ADODB.Connection


Example 3:

Dim oFSO As New Scripting.FileSystemObject
Dim oTS As Scripting.TextStream

Set oTS = oFSO.OpenTextFile(gLogFile, ForAppending, True, TristateFalse)


I find that I use the method from example 3 the most. Is this ok practice or should I adopt another method? I would appreciate an explanation as to why one method is better than another, if one can be given.

 

[bboffin]

There is effectively no difference between the first two examples. The second is just shorthand for the first one.

In the third example the only way you can create the TextStream object is the way you have done it. You cannot create a TextStream using New.

All of these examples use early binding and tie your application to the specific references you have defined.

You can use late binding in which case the object variables must be declared as type Object and you have to use the CreateObject function to create the object instead of New. This is most useful where you wish to use say Word or Excel and you're not sure which version is installed on the user's PC. Note that in this case there should be no references defined for the object.

Late binding is slower than early binding but I doubt if you could detect it on a modern PC.

What you are using is fine and gives the best performance.

 

[EBGreen]

I don't think 1 and 2 are the same. Run this code to see why:

Dim oMyObj As New Collection
    
Set oMyObj = Nothing
If oMyObj Is Nothing Then
    MsgBox "Is Nothing"
Else
    MsgBox "Is Something"
End If

Dim oObj2 As Collection
Set oObj2 = New Collection
Set oObj2 = Nothing
If oObj2 Is Nothing Then
    MsgBox "Is Nothing"
Else
    MsgBox "Is Something"
End If

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[mattKnight]

There is effectively no difference between the first two examples. The second is just shorthand for the first one.

I would beg to differ....

try this... with each of example given above

set oConn = Nothing    'Dispose neatly of object
debug.print oConn.State

and you will see in the first case Error 429 (Object error) and the other being a perfectly legal value...

IMHO, using the Dim as New method willand often does lead to inadvertant object creation, which creates a nightmare scenario for debugging.

There is a theoretical speed advantage as as well, in effect the Dim As New wraps each object access in this logic

Is this reference pointing to an instance of the object
No - then Create an object and point the reference to it
carry on with object access

If this happens in a loop the extra overhead will get multiplied many times....

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[mattKnight]

Tom Thumb...

We were typing at the same time -I was Too slow!

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[EBGreen]

But you did explain it better. I just posted sample code and left the proof up to the reader.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[JBL312]

Thank you all for posting. I will stay clear of using As New for the proposed reasons. Your help has been appreciated.

 

[chiph]

Yup, you want to use type #1 because VB will auto-recreate an object "for you".

It's also a good idea to explicitly set your objects to Nothing in a cleanup section of each method. Sure, they're supposed to get set to Nothing when they fall out of scope, but we've seen that not happen like it should.

Public SomeMethod()

  Dim adoConn As ADODB.Connection
  Dim adoComm As ADODB.Command
  Dim adoRS As ADODB.Recordset

  On Error goto ErrHandler

  Set adoConn = New ADODB.Connection
  Set adoComm = New ADODB.Command

  ' Do real code here

  GoTo Cleanup

ErrHandler:
   ' Log error
   ' Fall through into cleanup
Cleanup:
  Set adoRS = Nothing
  If Not adoComm Is Nothing
    If adoComm.State = adStateOpen
      adoComm.Close
    End If
    Set adoComm = Nothing
  End If
  If Not adoConn Is Nothing
    If adoConn.State = adStateOpen
      adoConn.Close
    End If
    Set adoConn = Nothing
  End If

Chip H.


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