Not actually a problem...

Hello friends.
I want to start a little discussion here about some issues which really intrigued me recently.
Some of you gurus keep going on about how you should Set myObjectVariable = Nothing before leaving a Sub or Function. I never did that and still live. So I made some tests with the following code fragment.
In the Sub CreateFSOAndDontCleanUp() you can choose any one of 4 methods to create a FileSystemObject, either locally or to a global variable in the form, and avoid setting the variable back to nothing.

Code:

---

Private FSO As New FileSystemObject

Private FSP As FileSystemObject

Private Sub CreateFSOAndDontCleanUp()
  '1
  'Dim f As New FileSystemObject
 
  '2
  Dim f As FileSystemObject
  Set f = CreateObject("scripting.filesystemobject")
 
  '3
  'Set FSO = New FileSystemObject
 
  '4
  'Set FSP = New FileSystemObject
 
End Sub

Private Sub btnCleanUpTest_Click()
  Static running As Boolean
  Dim i%
  running = Not running
  'txtLoopCount.Text = 0
  btnCleanUpTest.Caption = IIf(running, "Stop", "Don't Clean Up Test")
  While running
    For i = 1 To 1000
        CreateFSOAndDontCleanUp
    Next
    txtLoopCount.Text = Val(txtLoopCount.Text) + 10000
    DoEvents
  Wend
End Sub

---

VB seems to be a very friendly language, indeed. It seems to clean up any locally created objects when leaving a sub or function. After creating millions of filesystemobjects there is still the full amount of memory available. Also when an object variable is assigned a new object, the old one seems to be destroyed automatically. So I don't see any point in setting an objVar back to Nothing.
To get an idea what was supposed to happen, I created an Array of 100000 FileSystemObjects. They consume about 3MByte of memory.
Altough I terminated the exe with the frowned upon "End" statement, all memory is freed properly. This another rumour, perhaps?
Please correct me if I'm missing something here.

(One observation I made herewith is, the CreateObject() method takes much longer to create an FSO than the New statement.)

The second thing I ran into came up when I went and posted code where I created a global FileSystemObject like

Code:

---

Public FSO as New FileSystemObject 'in a module
'or
Private FSO as New FileSystemObject 'in a Form

---

I was advised, not to do so, because for some reason or other it would be slower than an FSO created locally.
I checked this, too.
Taking the precise time to list about 1000 files from a folder with a for each fil in fold.files the global New FSO showed exactly the same time behaviour than a locally created FSO.
I have no idea how I could compare a "Dim AS New" FSO with a "Set FSO = New..." FSO from it's memory structure , otherwise I would have checked for differences.

Please, don't rip my head orf. :) Any comments or clarifications would be highly appreciated.

Might work, but it's bad practice. It would NOT work with Office Apps, and other types of objects.

Code:

---

Option Explicit
' These are both examples of Late Binding

Public Sub RunAccessMacro(strDB As String, strMacro As String)
'================================================================
'for late binding declare it As Object and use CreateObject() function
  Dim AccessDB As Object
  Set AccessDB = CreateObject("Access.Application")

        With AccessDB
                .OpenCurrentDatabase strDB
                .DoCmd.RunMacro strMacro, 1
                '.Visible = True        'you decide
                .CloseCurrentDatabase
        End With
        Set AccessDB = Nothing

End Sub

Public Sub ExcelMacro()
  Dim excl As Object
  Dim wrbk As Object

        Set excl = CreateObject("Excel.Application")
        excl.DisplayAlerts = False
        Set wrbk = excl.Workbooks.Open("myfile", , True, , "mypassword")
        excl.Run "MacroName", "arg1", "arg2"
End Sub

---

If you use END, Excel will still be running!

Quote:

---

Originally Posted by WoF

Hello friends.
I want to start a little discussion here about some issues which really intrigued me recently.
Some of you gurus keep going on about how you should Set myObjectVariable = Nothing before leaving a Sub or Function. I never did that and still live. So I made some tests with the following code fragment.
.

---

As much as I have figured out objects which are created by classes designed of your own in VB are not critical. when writing

Code:

---

Set myObj = new MyObjClass

---

then myObj is a pointer pointing to the memory which was allocated for your Object. When the object looses its last reference it is going to garbage automatically so its bad style but works if you aren't setting pointers to nothing at the end of methods. Tested since years in lots of programs, no problem Alos no problem in new languages like C# where it is written down in MS book that this isn't anymore necessary to be done as loosing the last reference an object will be destroyed ( disposed ) automatically. Totally other with resources like e.g. recordsets. In the beginning of my programming experience I often forgot to close streams, recordsets or databaseconnections. I got in heavy troubles with that in a way that suddenly there was 'no more connection possible to the database' errors and such problems more.

So a) never forget to use 'close' in DAO, ADO or DE objects. There also be sure to set the objects to nothing after that.
b) You really can get in troubles when not closing (or if needed quitting ) and setting to nothing excel, word or access objects as then you will create conditions where excel is still running, but not on the screen, but you can still see it in the 'control-alt- delete Window' ( system - dont know english name ) And in this caseit could be possible that you are unable to open an other excel object by the program again and you get an error, that the task is still running and cannot be used in the moment, you will ahve to wait until it is out of use, ( but this never happens :D ) until you are using ctl.alt .delete and killing the running excel thread manually,

So if you have it in your programming style, you have maybe some unnecessary lines of code but no troubles ;)

IMHO the basic behind all this is if you have connection to other applicatiions like word or excel then you get in troubles as they are connections to other threads behind the scene. So when not closieng and setting to nothing the other thread is'n t really closed and disconnected from yyour application and new connections fail. This can be seen as in that case the excel thread or word thread.. is still running. ( looking in the ctl-alt-del window )

See you

Jonny

The two primary cases with VB (not .Net) where "=nothing" are imporant are

1) When the declaration of the variable is at an outter scope. For example a member variable that is onely used for initialization, set it to nothing or the referenced object will live as long as your object

2) When there is significant processing later in the same scope. Example
function F() as integer

Code:

---

Dim o as SomeClass
o = ....
// using o...
o = nothing
// larger amounts of code that does not depend on o
end function

---

like anyone had already said, you can't dispose (set <object> = nothing) an object declared implicitly (ie., dim gRecordset As New ADO.Recordset). in the following example, you can't dispose the declared object anywhere in the code (not until the application terminates of course :D)

Public gRecordSet As New ADO.Recordset

Right, thank you guys for your input about Set myObj = Nothing

I'd grant you that some of the arguments read here convince me to watch out when using recordsets and database objects which might stay "open".
Also it seems logically, that running applications (word/excel) will stay open. So you sure had to close them explicitly.

Also CPUWizards statement seems likely, when significant processing is still to follow in the same sub. Also granted.

But I wouldn't consider it "bad style" if I don't do a completely useless Set myLocalFSO = Nothing, followed by destroying a couple of other objects used in a sub/function, whereas leaving the procedure will do all that for me automatically. I'd rather consider it "leaving away redundant statements and making use of lovely VB's kindness to produce more readable code". ;)

Ok.

And what about the ominous FSO as New FileSystemObject?
Any experience had, there?
As far as I tried out, there seems to be absolutely no difference if you go
Dim FSO as New FileSystemObject
and
Dim FSO as FileSystemObject
Set FSO = New FileSystemObject

Please take time with any answer... on top of all I seem to have cought a summer-flu and might be off for a couple of days anyway...
Thank you all, so far. :)

Code:

---

Dim FSO as Object
Set FSO = New FileSystemObject

---

Consistency. Set them, close, and destroy. Also, if you properly unload your forms, all objects in that form are destroyed when it is. With the above exceptions.

Quote:

---

Originally Posted by WoF

Dim FSO as New FileSystemObject
and
Dim FSO as FileSystemObject
Set FSO = New FileSystemObject

---

IMHO there is absolutley no difference both is creating a pointer and allocating memory on the heap to an object. Why should there be any difference ? Is there really one. ?

What about for Office? Don't lock yourself into a version (altho Intellisense is nice). Declare objects, then bind to local version of Office App. Then the version won't matter*.

Quote:

---

Originally Posted by WoF

Ok.

And what about the ominous FSO as New FileSystemObject?
Any experience had, there?
As far as I tried out, there seems to be absolutely no difference if you go
Dim FSO as New FileSystemObject
and
Dim FSO as FileSystemObject
Set FSO = New FileSystemObject

Please take time with any answer... on top of all I seem to have cought a summer-flu and might be off for a couple of days anyway...
Thank you all, so far. :)

---

Dim FSO as New FileSystemObject

the object in this kind of declaration will be created/instantiated upon first use of the variable FSO in the code. so there might be a checking (or somekind of overhead) embedded by the vb compiler, such that, the program will use to ensure that the FSO variable has been instantiated everytime you use it in the code... that might be the difference.

Quote:

---

Originally Posted by dglienna

Might work, but it's bad practice. It would NOT work with Office Apps, and other types of objects.

Code:

---

Option Explicit
' These are both examples of Late Binding

Public Sub RunAccessMacro(strDB As String, strMacro As String)
'================================================================
'for late binding declare it As Object and use CreateObject() function
  Dim AccessDB As Object
  Set AccessDB = CreateObject("Access.Application")

        With AccessDB
                .OpenCurrentDatabase strDB
                .DoCmd.RunMacro strMacro, 1
                '.Visible = True        'you decide
                .CloseCurrentDatabase
        End With
        Set AccessDB = Nothing

End Sub

Public Sub ExcelMacro()
  Dim excl As Object
  Dim wrbk As Object

        Set excl = CreateObject("Excel.Application")
        excl.DisplayAlerts = False
        Set wrbk = excl.Workbooks.Open("myfile", , True, , "mypassword")
        excl.Run "MacroName", "arg1", "arg2"
End Sub

---

If you use END, Excel will still be running!

---

statement END doesn't call UNLOAD event.

I was looking for somathing seemed :

Code:

---

'form 1
Private Sub Command1_Click()
 x(0) = "A"
 x(1) = "B"
 x(2) = "C"
End Sub

Private Sub Command2_Click()
 Form2.Show
End Sub

Private Sub Command3_Click()
 Unload Me
End Sub

Private Sub Form_Load()
 List1.AddItem x(0) & ""
 List1.AddItem x(1) & ""
 List1.AddItem x(2) & ""
End Sub
Private Sub Form_Unload(Cancel As Integer)
 'erase x
End Sub

---

Code:

---

'Form 2
Private Sub Command1_Click()
Form1.Show
End Sub

---

step by step :
1.- Open form1
2.- click command1,command2(be load form2) an after command3(Unload form1).
3.-On Form2 click command1 (be load form1 again)

now , List1 is showing values of array x
when I unload form1 it doesn't mean what variables are unloaded too.
I still have 'erase X' in unload event if I want to clean array.

Quote:

---

Originally Posted by Thread1

Dim FSO as New FileSystemObject

the object in this kind of declaration will be created/instantiated upon first use of the variable FSO in the code. so there might be a checking (or somekind of overhead) embedded by the vb compiler, such that, the program will use to ensure that the FSO variable has been instantiated everytime you use it in the code... that might be the difference.

---

If this was truly the case, I'd have expected differences in performance, but there is absolutely no such difference to be detected. I checked that in extensive long loops, using an FSO to access files and folders, checking the time with a high precision "QueryPerformanceCounter" call. Exactly the same.

Also, as I said already, I can see, where working with Word/Excel objects which pop up the entire application and might bkeep it open...

But to the last observation of hensa:
I noticed this, too, last recently and was also a little perplexed. I would have expected the variables of a form to loose all their contents when the form got unloaded, but it seems not to be the case, indeed.

It seems that a form, once loaded, is kept in a "cache" or something that it is present already when it has to be loaded again. It is as if it was only hidden, but gave a call to the Form_Unload() .
This gave me a headache recently when I didn't know how a form's variable would remember a certain value after been unloaded and then been shown again. Is that behaviour documented, does anybody know?

Quote:

---

Originally Posted by WoF

....But to the last observation of hensa:
----when I didn't know how a form's variable would remember a certain value after been unloaded and then been shown again. Is that behaviour documented, does anybody know?

---

I have had troubles with this in programs where the same windows are opened and closed again. This cuased me to very exactly looking for setting all my variables to 0 or other defined standard settings in Form_load(), especially booleans which I'm using for defining different status of the form. And also to set all my objects to nothing in the Form_unload()

That in the end keeps me from such troubles in between.
BTW in between I have some other intersting troubles with an access mdb which was 1997 style( and coded in VBA with lots of code, )changed to Access 2000 and never had problems with closing the mdb now changed to Access 2003 This mdb now is not closing access so when I close the mdb access still runs in the background and I only can get out of troubls when I'm closing MSAccess in the systems threads window directly ( ctl, alt del window ) Running the same mdb in access 2000 I 'm closing the VBA code and also access closes. I think I have the same troubles with objects not set to nothing maybe there. so I think it doesn't what it should do in some specific cases. Maybe this has to do with the transformation of the mdb from 2000 to 2003

I had no problems with Office XP db's. Haven't tried Office 07 yet

Quote:

---

Originally Posted by WoF

But to the last observation of hensa:
I noticed this, too, last recently and was also a little perplexed. I would have expected the variables of a form to loose all their contents when the form got unloaded, but it seems not to be the case, indeed.

It seems that a form, once loaded, is kept in a "cache" or something that it is present already when it has to be loaded again. It is as if it was only hidden, but gave a call to the Form_Unload() .
This gave me a headache recently when I didn't know how a form's variable would remember a certain value after been unloaded and then been shown again. Is that behaviour documented, does anybody know?

---

Mentioning this ties everything together very nicely.

When you unload a named form (ie not instantiated with Set F = New Form1) a reference still exists to the object and as such the memory is not released. When you next load the object those variables that have not fallen out of scope will have their previous values preserved (ie. module-level variables).

You can overcome this by setting the form reference to nothing: e.g

Code:

---

' Inside Form1

' Assuming Form1 was loaded as:
' Load Form1
'not
'Set F = New Form1
'Load F

Private Sub Form_Unload(Cancel As Integer)
    Set Form1 = Nothing
End Sub

---

or you could also do this outside the form after unloading, or before you reload it.

The reason you don't get any error when you next load the form (following the Set Form1 = Nothing) is the the form is essentially declared as Dim Form1 As New Form1 (if you get what I mean).

Every time an object reference declared is used, VB checks to see if the object exists and if not, creates it. I saw comments above saying there's no difference in speed, but that's got to be the way it does it.

Code:

---

Dim col As New Collection

Private Sub Command1_Click()
    Debug.Print ObjPtr(col)
    Debug.Print ObjPtr(col)
    Set col = Nothing
    Debug.Print ObjPtr(col)
End Sub

---

anyway, that's my two pennyworth, sorry if I've gone over anything already mentioned. That's the way I understand it - correct me if I'm wrong

Quote:

---

Originally Posted by dglienna

I had no problems with Office XP db's. Haven't tried Office 07 yet

---

I'm nearly sure this isn't really a problem of office 2003 but it maybe is more exact about still living objects in running the code and this way maybe doesn't dispose objects which aren't disposed by the programmer and this way access doesn't close, while access 2000 and earlier may have disposed all the objects which havn't been set to nothing at programs end automatically.
But thats only my assumption.

To Bushmobiles comments about Forms:
So there seems to be an automatic global variable "Form1" which is a reference to the form Form1 which is not freed when Form1 is unloaded... strange but seems to be true by evidence.

Also strange, the collection example. The Set col = Nothing does not destroy the object as could be seen by printing. But you could assign a new collection to it: Set col = New Collection. It will replace the first one and then seems to stay persistent like the one before.

If there is really some additional check routines added by the compiler, they must be in the assignment procedure, because every other performance of the object is not inflicted by it's type of creation.

So at least I can make sure, there's no objection against using a globally declared Public FSO as New FileSystemObject when I want to peruse it throughout the program and have no intention whatsoever to destroy it until quitting the app. Right?

I suppose it would be open thruout. I always prefer to set and destroy in each section, but I add the DIM in there as well.

Global may be quicker if you open it dozens of times. Not sure.

I have had problems with all versions of Office, until RD (@vbf) explained how things worked. Since then, no problems. (Except with PP :( )

Quote:

---

Originally Posted by WoF

Also strange, the collection example. The Set col = Nothing does not destroy the object as could be seen by printing. But you could assign a new collection to it: Set col = New Collection. It will replace the first one and then seems to stay persistent like the one before.

---

the Set col = Nothing does release the memory, but when we use the reference again (passing it to ObjPtr) it gets re-instantiated - hence the memory address is different.

i.e. compare it to

Code:

---

Dim col As Collection

Private Sub Command1_Click()
    Set col = New Collection
    Debug.Print ObjPtr(col)
    Debug.Print ObjPtr(col)
    Set col = Nothing
    Debug.Print ObjPtr(col) '0
End Sub

---

Quote:

---

Originally Posted by dglienna

...I have had problems with all versions of Office, until RD (@vbf) explained

---

Who or what is RD please ? something to read ?

DING!

Robdog888 is a super moderator there, and a member here (who hasn't been around lately). He mods the Office Forums.

He's helped me with a few things. ;)

Quote:

---

Originally Posted by dglienna

DING!

Robdog888 is a super moderator there, and a member here (who hasn't been around lately). He mods the Office Forums.

He's helped me with a few things. ;)

---

thx:wave:

@Bushmobile:
I didn't notice a change of object address when it becomes reinstantiated. I tried this one:

Code:

---

dim col as new collection
Private Sub Command2_Click()
  'Dim col As Collection
  Set col = New Collection
  Debug.Print "Set new"; ObjPtr(col)
  Set col = Nothing
  Debug.Print "Nothing"; ObjPtr(col)
  Debug.Print "--"
End Sub

---

When working with the local collection, the set to nothing will work. (But I get always the same address.) That's the debug.print after clicking twice:

Set new 1991728
Nothing 0
--
Set new 1991728
Nothing 0
--


When I comment out the local col declaration and use the gloablly dimmed New Collection, the readout is:

Set new 1996336
Nothing 1996336
--
Set new 1991728
Nothing 1991728
--
The referring by ObjPtr after the Set .. = Nothing reinstantiates obviously to the same address as before...


But, however, now lot's of things are much clearer to me and I learned a lot about instantiating behaviour of VB.

I thank you all for your input. I think I'll keep up with not setting objects to nothing and using a global FSO until I really need it differently ;) , and - for sure - when I start using VBA objects of like word-docs or excel-sheets and whenever I'll have to use database objects...