Quote:
Originally Posted by whatsup
Hm, no, that's not the right tool to make visible what's stored/remained in memory.
You have to distiguish between "validity" of a variable and it's "presence" in memory. These are different things: After passing e.g. "End Sub" the local variable isn't valid anymore and you can't access it - that's true -. But this doesn't mean necessarily that it isn't still alive within memory.
The watch window rather points out the validity than its presence in memory (though I'm not 100% sure about this).
|
Yes, I agree and I already mentioned that when I said that using the VBE watch window was probably bogus.
Quote:
Originally Posted by whatsup
To get a glimpse on memory you rather need something like ObjPtr() - which of course itself won't work after leaving the sub for local variables. For example have a look at this:
- Add a module and put the code.
- Add a userform which will be "Userform1"
- Then step through the code by F8 and watch the immediate window
|
See my comments in blue below explaining my understanding of whats going on.
Quote:
Originally Posted by whatsup
Code:
Public Sub test()
Dim objForm As Object
' ///////////////////////////////////////////////////////////////////////////
' objForm is not yet initialised, it points to Nothing
Debug.Print ObjPtr(objForm)
' ///////////////////////////////////////////////////////////////////////////
' Creates an instance of an Object of type UserForm1 and then
' point objForm to that instance. The object is created implicitly by
' auto-instantiation. The object has one reference: it is referenced by objForm.
Set objForm = UserForm1
Debug.Print ObjPtr(objForm)
' ///////////////////////////////////////////////////////////////////////////
' Explicitly instantiate a new object of type UserForm1 and then
' point objForm to that instance. Because referencing is one to one or
' many to one, but not one to many, objForm is no longer referencing the
' first object so it's reference count is decreased by one; the first object
' now has zero references and will be de-allocated.
' The new object has one reference and it is objForm.
Set objForm = New UserForm1
Debug.Print ObjPtr(objForm)
' ///////////////////////////////////////////////////////////////////////////
' ObjForm is pointed at Nothing, the second object's reference count is
' decreased by one; it now has zero references and will be de-allocated.
Set objForm = Nothing
Debug.Print ObjPtr(objForm)
' ///////////////////////////////////////////////////////////////////////////
' on exit, all local variables are destroyed and any objects referenced by
' them have their reference count decremented.
' Any object referenced only by any of these variables will now have zero
' reference count and will be de-allocated.
End Sub
You will clearly see, that with "New UserForm1" a new entry is created in memory, though it's the same variable. Now, if you don't set it at the end to Nothing, memory keeps occupied, while setting it to nothing the entry in Memory = 0 which signifies it's removed from memory.
Now, I can't proof that this isn't the case if you don't destroy the reference manually but leave it to vba, because I haven't got any idea to read in memory what's in it. |
My curiosity drove me to build a test system for this which I have attached below. It uses API calls to report memory used by excel and logs before and after. There are switches on the sheets to direct the various activities. Because its and API report, it is Windows' view of what memory is alocated to Excel so I think that is probably ok and will show any leaks.
There is one for forms and one for the dictionary search. (It was too big as one file...)
The behaviour when set to nothing is used is no different from when its not.
Have a play with it and see what you think...
06-18-2014, 08:25 AM
Slow "comparison/replace" script
Similar Threads
replace data from variable with "sub and super script" from excel to word by vba
Windows 7 64bit 
Office 2013
Threaded Mode