New vs Set Keywords

[shamas21]

Hi All

Can someone give me an explanation as to what the difference between the two variations of using the New keyword to create a new instance and using the Set. I mean Code 1 would always be better because you dont have to ever use the Set keyword

1.

    Dim wb As New Excel.Workbook

2.

    Dim wb As Excel.Workbook
    Set wb = New Excel.Workbook

Are there benefits and cons to both i.e. memory allocation etc.., all answers are very welcome.

Thanks

 

[MarkK]

The two code examples do exactly the same job. Any performance difference wouldn't be worth mentioning. The first is a little tidier.

But, in some cases, because some classes are complex and may be costly to create--and an instance of Excel is an example of that--you may want to allocate space for a variable, but never use it. In that case, use the second construct, and only use the new keyword if code execution demands it . . .

dim xl as Excel.Application
if weekday(date()) = vbTuesday Then
   set xl = new Excel.Application
end if

You can see in this example that the Excel.Application object will only be needed on Tuesdays, and Excel is costly to create, so we only create it conditionally.

 

[shamas21]

The two code examples do exactly the same job. Any performance difference wouldn't be worth mentioning. The first is a little tidier.

But, in some cases, because some classes are complex and may be costly to create--and an instance of Excel is an example of that--you may want to allocate space for a variable, but never use it. In that case, use the second construct, and only use the new keyword if code execution demands it . . .

dim xl as Excel.Application
if weekday(date()) = vbTuesday Then
   set xl = new Excel.Application
end if

You can see in this example that the Excel.Application object will only be needed on Tuesdays, and Excel is costly to create, so we only create it conditionally.

Thank for the explanation - it makes sense.

However, by using the Dim declaration of xl then im already allocating memory in my computer for xl i.e. 4 Bytes for an Object. But what you are saying is that if I use the New keyword then I will be allocating extra space in memory when i create a new instance of the Excel.Application Class? So are you saying that by writing Dim xl as Object allocates 4 Bytes and later Setting xl = New Excel.Application will allocate further bytes in memory?

 

[MarkK]

Here's the difference:

Private Sub Test12936418674()
    Dim clock As Single
    
    clock = Timer
    Dim tmp As Excel.Application
    Debug.Print "Declare: " & Format(Timer - clock, "0.0000")
    
    clock = Timer
    Set tmp = New Excel.Application
    Debug.Print "Create: " & Format(Timer - clock, "0.0000")
    
    tmp.Quit
    Set tmp = Nothing
    
    Beep
    
End Sub

For classes, declaring the variable is really fast because it's just a pointer to the instance, and the instance doesn't exist yet. Creating the instance takes resources, and sometime a lot. In this example you can see that the time is spent creating the instance, not declaring the variable.

 

[shamas21]

But if I simply add the New Keyword to the Create is yields the same results as if I didnt

Private Sub Test12936418674()

    Dim clock As Single
    
    clock = Timer
    Dim tmp As [B][COLOR="Red"]New[/COLOR][/B] Excel.Application
    Debug.Print "Declare: " & Format(Timer - clock, "0.0000")
    
    clock = Timer
    Set tmp = New Excel.Application
    Debug.Print "Create: " & Format(Timer - clock, "0.0000")
    
    tmp.Quit
    Set tmp = Nothing
    
    Beep
    
End Sub

 

[MarkK]

Yeah, in that case the instance isn't created until you first reference it, and that's totally legit.

    clock = Timer
    Dim tmp As New Excel.Application
    Debug.Print "Declare: " & Format(Timer - clock, "0.0000")
    
    clock = Timer
    Debug.Print TypeName(tmp.Workbooks)
    Debug.Print "Create: " & Format(Timer - clock, "0.0000")

You are free to do this however you like.

 

[ChrisO]

We can watch this in Task Manager by looking at running Processes or the Total Commit Charge:-

Option Compare Database
Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)


Private Sub TestIt()
    Dim tmp As New Excel.Application
    
    Sleep 5000
    
    MsgBox ObjPtr(tmp)  ' This reference creates the instance of Excel.
    
    Sleep 2000
    
    ' tmp will clean up when it goes out of scope.
    
End Sub

We need to be careful with what we read on the internet…
From:-
http://msdn.microsoft.com/en-us/library/office/aa164811(v=office.10).aspx
>>If you create an instance of the object by using New in the Dim statement, the object will be created even if it isn't used.<<

To my way of thinking that is totally ambiguous or just plain wrong.
One could ask; if we do not create an instance of the object by using New in the Dim statement, will the object be created even if it isn’t used?

The answer appears to be that the Object, not the Object pointer (which is what tmp actually is), is not created until it is referenced. Despite what Microsoft (or anyone else for that matter) says, Task Manager indicates the Object does not exist until it is referenced.

----------

http://www.cpearson.com/excel/classes.aspx
Others talk about it adding overhead to test for Nothing. Really, some sense of proportion is required with these things. An Object pointer is Nothing when it has a value of 0 (zero). In a 32 bit system it is a 4 byte address of the Object. It is a numerical value the same size as a Long. How much time does it take to test if a Long is 0? Far less than accurate measurements would allow.

And if we are going to say that the overhead would be incurred because the Object pointer needs testing for Nothing each time it is used then what’s the difference? Even if the Object pointer was once set to some Object doesn’t mean that it still is. Other code may have set the Object pointer back to Nothing and hence the test for Nothing should be done even if the original code was written as per the recommendation.

----------

So I would suggest that we read as much as we can but try to test everything we use and suggest to others.

Chris.

 

[Galaxiom]

I don't know if it is the case for VBA but in Visual Basic my (perhaps misguided) understanding is that a reference counter is incremented and decremented for each object as the code is executed. The system knows when the object is released because the counter gets to zero. This is how the garbage collector knows when to recover the memory.

Hence object variable does not have to be tested for Nothing.

Either way I am sure any test required is trivial compared to the other processes that go on under the hood when objects are referenced or instantiated.

 

[ChrisO]

Yes, it is my understanding as well that reference counting is used in VBA to determine if the memory for the Object should be reclaimed.

However, pointers to the Object may become Nothing (numerically 0) and that is somewhat of a different thing. For example, if there are two pointers to the same Object then one may get set to Nothing. We would then have a valid Object in memory because one pointer is still referencing it. However the other pointer, which was referencing it and was then set to Nothing, might still be used in code.

It would seem logical to me that a test for a pointer being Nothing must be done under all circumstances before that pointer can be used. Hence, any argument that it incurs a performance overhead seems irrational to me because it would need to be done in all cases irrespective of how the pointer is declared.

So what I am saying is that both sites I posted a link to are either ambiguous or wrong or both.
What I would like people to do is test things for themselves and stop simply re-posting things they have read elsewhere.

Chris.