|
#1
|
||||
|
||||
Quote:
VBA Garbage Collection. @macropod First of all, thanks for the links and just to be clear, I'm not trying to flame anyone here and I won't be making comments like "you didn't look very hard" coz I don't know that for a fact. And for the record, I did a lot of reading before posting. I'm interested in the topic specifically because of the volume and diversity of opinion that I saw. In your first link, they are talking about Access and most of the objects are DAO objects. The example code uses the pattern you advocate but there is no instruction or recommendation for using this pattern. They say that you can free up memory by setting objects to nothing but they do not say that failing to do so, just before a local object goes out if scope, will cause a problem. It was produced by Microsoft but then this guy is also from Microsoft and he's is saying explicitly that it's not necessary and he's no slouch... http://blogs.msdn.com/b/ericlippert/...28/122259.aspx Here's another guy with a solid background who also has a definitive opinion on the issue... http://ramblings.mcpher.com/Home/exc.../scope/garbage If you google VBA garbage collection you will get a lot if hits and I read quite a few if them before my first post on the topic. I found this to be a good representation of the spread of opinion... https://groups.google.com/forum/m/?h...vb/3pw-TGc9PSo The common theme of the two camps is: On the affirmative: I believe I should do it, so I do it, just in case. On the negative: it's not necessary and here is a clear explanation as to why... Your second link is basically supporting what I'm saying: that setting objects to nothing doesn't always do what you expect and that you have to be careful how you construct them and how you tear them down if they have complicated structures. Furthermore, he explicitly says that just setting objects to nothing at the end if a sub is a bad idea. So it's counter to your argument. In your third link there is a list if memory leak sources... Page Setup in Win95 & Win98 & Win ME (GDI problem) - see MSKB Q192869 - NOT fixed by setting objects to Nothing Printing using HP Printers - see MSKB Q165985 and Q218864 - NOT fixed by setting objects to Nothing Querying an open Excel worksheet using ADO - see MSKB Q319998 - NOT fixed by setting objects to Nothing "The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel." Replacing external link formulae - NOT fixed by setting objects to Nothing Some external data retrieval actions, particularly repeatedly reading text files. Excel seems to store only unique strings rather than multiple occurrences of the same string, but does not seem to remove a unique string when it is no longer in use. - NOT fixed by setting objects to Nothing Controls embedded on worksheets - see MSKB Q238570 - applies to excel97 - NOT fixed by setting objects to Nothing User Defined Functions, particularly when they take input parameters from another sheet and return a string. there are some fixes for these problems in Excel 97 SR2 and Excel 2000 SP2 - see MSKB Q265023 - NOT fixed by setting objects to Nothing Not destroying objects in VBA by setting the object variables to nothing in the correct sequence so that you avoid "orphaned" objects. In theory this is not neccessary, but sometimes problems occur when this is not done. Destroy in inside-out container sequence, for example Range then Worksheet then Workbook. This is similar to your second link and points out the need for proper tear-down of non-trivial custom objects. - NOT fixed by simply setting objects to Nothing prior to them going out of scope and not applicable to local scoped objects. So... I'm just sayin' |
#2
|
||||
|
||||
Only via the VBE watch window. If you populate a global collection (declared as public in a standard module) inside a Sub, then after executing it, you will still see the Object structure in the watch window. I guess It's bogus though coz if there is a leak then I guess VBE would remain clueless.
I'm pretty sure I can come up with some kind if recursive routine though to test the deallocation and then just check the memory usage with process explorer. Thinking about it though, for local variables, scoped inside a sub, I would have thought the scripting engine would push a block of frames into the stack on entry. And the stack pointer on entry would be stored. Then, on exit, the stack pointer would be reset to the entry value, thus obliterating all local variables. The second thing I've been thinking about is that it is impossible to reference an object declared locally in a procedure, from outside that procedure. So IF the locals are blown off the stack on exit, and given that VBA uses reference counting to manage life cycle... Well, it seems to support the argument against set to nothing just before exit. |
#3
|
|||
|
|||
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). 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 Code:
Public Sub test() Dim objForm As Object Debug.Print ObjPtr(objForm) Set objForm = UserForm1 Debug.Print ObjPtr(objForm) Set objForm = New UserForm1 Debug.Print ObjPtr(objForm) Set objForm = Nothing Debug.Print ObjPtr(objForm) End Sub 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. |
#4
|
||||
|
||||
Just want you guys to know that other people are reading this thread, not just the contributors. I don't know anything about memory allocation in Windows so I haven't anything useful to add to the discussion, but I'm picking up tidbits.
|
#5
|
||||
|
||||
Slow "comparison/replace" script
Quote:
Quote:
Quote:
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... |
#6
|
|||
|
|||
CoolBlue, I must say you did quite a job
Now, I still will need some time to follow things up in your code, but as far as I see it by now, you basically working on the allocated memory to excel, I'm right? In that case be aware, that windows doesn't reclaim memory once granted at the insance it isn't needed anymore - maybe even not until excel is closed. Having a look at your summary for "DictionarySearch": Code:
17:19:21:0520 Call RangeCompare2: Don't Set to Nothing: WorkingSetSize 180,219,904 ( Reference ) 17:19:21:0700 Start Timer: t = 0.000 sec: WorkingSetSize 180,867,072 ( 647,168 ) 17:19:21:0720 arrCompare Loaded: t = 0.016 sec: WorkingSetSize 181,526,528 ( 1,306,624 ) 17:19:21:0820 objDic Loaded: t = 0.115 sec: WorkingSetSize 182,345,728 ( 2,125,824 ) 17:19:21:0830 arrData Loaded: t = 0.130 sec: WorkingSetSize 182,669,312 ( 2,449,408 ) 17:19:22:0010 Output to Range2 Complete: t = 0.304 sec: WorkingSetSize 182,976,512 ( 2,756,608 ) 17:19:22:0160 after End Sub: WorkingSetSize 196,042,752 ( 15,822,848 ) By the way: What for heavens sake is excel 2013 doing with this huge ammount of memory? I just once gave it a shot showing my version just was in need of about 80 millions ... When you start excel and let it run what the results then like? Just a few thoughts to share with you |
#7
|
||||
|
||||
Slow "comparison/replace" script
Quote:
Wow. That's a very unusual run. When I do it I almost always get less working set than at the start and if it's more it's only a small amount, which I assume is just normal bits and pieces going on in the background. The runs I did clearly showed that the memory had been freed. But, sure... This is only the very beginning for me in understanding memory management and I still have a lot if reading to do before I can confidently interpret these results. If you have any useful links to offer please throw them down here. I would be very grateful of that. My main aim for that post was to throw down a nice tool as a starting point for looking at the memory dynamics, so that we can explore a bit. And I rather got a bit obsessed with that goal... Which was good fun! I didn't really spend much time yet, trying to understand the results, so I'm very interested in your feedback. There are other metrics, apart from working set that can be reported by the API calls so take a look at that see if you see any better measure... Cheers mate. Last edited by CoolBlue; 06-18-2014 at 05:20 PM. |
#8
|
|||
|
|||
Well, that very last run you obviously didn't get less working set. So I take it, that's what excel 2013 occupies normally of memory - 180 millions Bytes???
The reading of the working set might indicate whether memory is freed or not. But it will be more a kind of assumption what's happening then knowing if the particular objects are removed from memory. That's if you run the code several times observing the development of working set: - If there is a steady increase of memory allocation in my opinion it will indicate that memory isn't freed properly (but as to the number of calls been done for each run there will still be the chance that the piling is caused somewhere else) - In case memory allocation from the first run to the last run doesn't increase at all, it will definitely tell that everything is fine (but that won't happen, as you said because of ongoing things in the background - If memory allocation increases a small amount then you are left to guessing Quote:
The intention is to read from the created pointer in memory. Meaning storing ObjPtr in a public variable after setting an object, let it go out of scope and try to access the pointer seeing what's in there: An example with a scalar: Code:
Public lng_VarPtr As Long Public lng_ObjPtr As Long #If Win64 Then Public Const PTR_LENGTH As Long = 8 #Else Public Const PTR_LENGTH As Long = 4 #End If Public Declare PtrSafe Sub Mem_Copy Lib "kernel32" Alias "RtlMoveMemory" ( _ ByRef Destination As Any, _ ByRef Source As Any, _ ByVal Length As Long) ' Platform-independent method to return the full zero-padded ' hexadecimal representation of a pointer value Function HexPtr(ByVal Ptr As LongPtr) As String HexPtr = Hex$(Ptr) HexPtr = String$((PTR_LENGTH * 2) - Len(HexPtr), "0") & HexPtr End Function Public Function Mem_ReadHex(ByVal Ptr As LongPtr, ByVal Length As Long) As String Dim bBuffer() As Byte, strBytes() As String, i As Long, ub As Long, b As Byte ub = Length - 1 ReDim bBuffer(ub) ReDim strBytes(ub) Mem_Copy bBuffer(0), ByVal Ptr, Length For i = 0 To ub b = bBuffer(i) strBytes(i) = IIf(b < 16, "0", "") & Hex$(b) Next Mem_ReadHex = Join(strBytes, "") End Function Sub ExampleForScalar() Dim lngValue As Long lngValue = 100 lng_VarPtr = VarPtr(lngValue) Debug.Print "lngValue : 0x"; HexPtr(lng_VarPtr); _ " : 0x"; Mem_ReadHex(lng_VarPtr, 4) End Sub Sub ValueIsGone() Debug.Print "lngValue : 0x"; HexPtr(lng_VarPtr); _ " : 0x"; Mem_ReadHex(lng_VarPtr, 4) End Sub Now with objects it isn't that easy, and that's where I'm struggling. I trying to recover the object from memory by it's pointer by adding this code: Code:
Function ObjectFromPointer(lPtr As Long) As Object Dim oTemp As Object Mem_Copy oTemp, lPtr, 4 Set ObjectFromPointer = oTemp End Function Sub ExampleForObject() Dim objRange As Object Set objRange = Sheets(1).Range("A1") lng_ObjPtr = ObjPtr(objRange) 'Set objRange = Nothing End Sub Sub ObjectIsGone() Dim objRecover As Object Set objRecover = ObjectFromPointer(lng_ObjPtr) End Sub I'm assuming that the pointer maybe doesn't exist anymore, which would be fine, indicating that the object was taken from memory, but we can have that proof with excel going to hell. Any idea on this? As I said, I'm not doing well with API, I basically got my wisdom from this page: http://bytecomb.com/vba-scalar-varia...ters-in-depth/ |
#9
|
||||
|
||||
Quote:
In order for it to work, you need to figure out the structure of the range object in your example and reserve a buffer with sufficient bytes, for Mem_Copy to dump into. You need to create a 4 byte buffer just like in the scalar routine to receive the bytes you transfer. |
#10
|
||||
|
||||
Ahaha... Hmm ok, that was one if my runs! you just quoted from the the file I posted. I did a few more runs, starting from that state, with the set to nothing switch true and got similar results for a while and then it went back to the behaviour I described earlier where the working set returns to close to the initial value. Looks like I've got some more investigating to do...
Thanks for that code and the link, I'll take a look later... Still much to learn! |
#11
|
|||
|
|||
That's what my version (2010 32bit) requires of memory:
Code:
22:36:21:0473 Call RangeCompare2: Don't Set to Nothing: WorkingSetSize 56.885.248 ( Reference ) 22:36:21:0503 Start Timer: t = 0.000 sec: WorkingSetSize 57.458.688 ( 573.440 ) 22:36:21:0513 arrCompare Loaded: t = 0.008 sec: WorkingSetSize 57.663.488 ( 778.240 ) 22:36:21:0543 objDic Loaded: t = 0.032 sec: WorkingSetSize 58.724.352 ( 1.839.104 ) 22:36:21:0543 arrData Loaded: t = 0.036 sec: WorkingSetSize 59.695.104 ( 2.809.856 ) 22:36:21:0613 Output to Range2 Complete: t = 0.107 sec: WorkingSetSize 60.104.704 ( 3.219.456 ) 22:36:21:0633 after End Sub: WorkingSetSize 58.912.768 ( 2.027.520 ) |
#12
|
||||
|
||||
Quote:
but not cool enough... To be honest, its not really any different from if the throw error switch is false. This is because the error is "handled". The only difference is that it skips the search and the dump to the sheet and a few reports along the way. I wasn't really happy with that part of the model so I fixed it... I've attached a new version that throws an error that is not handled. You can crash out by selecting END in the Error Dialog and then just click anywhere other than the active cell to trigger logging the post-exception memory state. I had to fumble around a bit to get it working coz it needs user intervention to complete the process, but it gives a true windows view of the memory after crashing out of VBA. I hear you about the memory requirements of XL 2013, it is much hungrier that XL 2010. I checked as well... All I can say is you don't get to be that pretty without working at it! Pity about the bugs though... I've attached a pic showing performance graphs from process explorer of the dictionary spreadsheet in XL 2010. I inflated the memory requirements for the Dictionary to highlight whats going on. I did this by using long strings (instead of "") for the Keys. I just kept running the process over and over to see what happens. Its pretty clear that the memory is being released according to process explorer. |
#13
|
|||
|
|||
Quote:
Im still trying to read from the pointers. At least I got it working the way, that the function works when called from the macro creating the object. My mistake was, not destroying OTemp. That I do now with one more line: Code:
Function ObjectFromPointer(lPtr As Long) As Object Dim oTemp As Object Mem_Copy oTemp, lPtr, 4 Set ObjectFromPointer = oTemp Mem_Copy oTemp, 0&, 4 End Function Code:
Sub ExampleForObject2() Dim objRange As Object Set objRange = Sheets(1).Range("A1") lng_ObjPtr = ObjPtr(objRange) If ObjectFromPointer(lng_ObjPtr) Is Nothing Then MsgBox "Object is Nothing" Else MsgBox "Object exists" End If 'Set objRange = Nothing End Sub Code:
Sub ObjectIsGone2() If ObjectFromPointer(lng_ObjPtr) Is Nothing Then MsgBox "Object is Nothing" Else MsgBox "Object exists" End If End Sub Crashing at the point Mem_Copy oTemp, lPtr, 4 in the function. Even with changing oTemp declared as Variant, it can't be done. So what else can be behind the pointer??? There's a chance that the address get's occupied of something else, but what can that be, you can't address to a variant? |
#14
|
||||
|
||||
Yes, i agree, my way is a bit indirect... My next thought was to batch 1,000 runs to get some statistics and see what emerges from the noise ...I feel like I'm trying to find another higgs boson!
But, now that I've got mine working reasonably ok, I'll start studying your code and see what I can learn first. |
#15
|
||||
|
||||
Slow "comparison/replace" script
Try this instead...
Code:
Option Explicit Public lng_VarPtr As Long Public lng_ObjPtr As Long #If Win64 Then Public Const PTR_LENGTH As Long = 8 #Else Public Const PTR_LENGTH As Long = 4 #End If Public Declare PtrSafe Sub Mem_Copy Lib "kernel32" Alias "RtlMoveMemory" ( _ ByRef Destination As Any, _ ByRef Source As Any, _ ByVal Length As Long) ' Platform-independent method to return the full zero-padded ' hexadecimal representation of a pointer value Function HexPtr(ByVal Ptr As LongPtr) As String HexPtr = Hex$(Ptr) HexPtr = String$((PTR_LENGTH * 2) - Len(HexPtr), "0") & HexPtr End Function Public Function Mem_ReadHex(ByVal Ptr As LongPtr, ByVal Length As Long) As String Dim bBuffer() As Byte, strBytes() As String, i As Long, ub As Long, b As Byte ub = Length - 1 ReDim bBuffer(ub) ReDim strBytes(ub) Mem_Copy bBuffer(0), ByVal Ptr, Length For i = 0 To ub b = bBuffer(i) strBytes(i) = IIf(b < 16, "0", "") & Hex$(b) Next Mem_ReadHex = Join(strBytes, "") End Function Sub pionterToSomething(name As String, lng_Ptr As Long, Length As Long) Debug.Print name & " : 0x"; HexPtr(lng_Ptr); _ " : 0x"; Mem_ReadHex(lng_Ptr, Length) End Sub Sub ExampleForScalar() Dim lngValue As Long lngValue = &HAAAAAAAA lng_VarPtr = VarPtr(lngValue) pionterToSomething "lngValue", lng_VarPtr, 4 End Sub Sub ExampleForObject() Dim objRange As Object Set objRange = ActiveCell lng_ObjPtr = ObjPtr(objRange) pionterToSomething "objRange", lng_ObjPtr, 4 'Set objRange = Nothing End Sub Sub ObjectIsGone() Debug.Print ExampleForObject pionterToSomething "objRange", lng_ObjPtr, 4 End Sub Sub ValueIsGone2() Debug.Print ExampleForScalar pionterToSomething "lngValue", lng_VarPtr, 4 End Sub Last edited by CoolBlue; 06-20-2014 at 03:00 PM. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Wierd "script code" in a downloaded .doc file | CNBarnes | Word | 2 | 10-18-2012 02:07 AM |
replace data from variable with "sub and super script" from excel to word by vba | krishnaoptif | Word VBA | 9 | 06-22-2012 05:08 AM |
How to choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
Rules and Alerts: "run a script"? | discountvc | Outlook | 0 | 06-15-2010 07:36 AM |
An "error has occurred in the script on this page" | decann | Outlook | 8 | 09-03-2009 08:54 AM |