#16
|
|||
|
|||
Quote:
|
#17
|
||||
|
||||
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. |
#18
|
|||
|
|||
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. |
#19
|
||||
|
||||
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.
|
#20
|
||||
|
||||
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... |
#21
|
|||
|
|||
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 |
#22
|
||||
|
||||
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. |
#23
|
|||
|
|||
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/ |
#24
|
||||
|
||||
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! |
#25
|
|||
|
|||
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 ) |
#26
|
|||
|
|||
Bob, I forgot
Quote:
Leaving excel for long periods open in my opinion will result in any case that things slow down. I would expect that memory allocation after at least one day equals the limit. But I can't tell by experience, because usually I close excel at least every hour, unless it's doing calculations, but than memory is used to the limit. |
#27
|
|||
|
|||
Interesting:
Features: switch Set To Nothing = False, switch Throw Error = True Code:
23:25:33:0697 Call RangeCompare2: Don't Set to Nothing: WorkingSetSize 60.395.520 ( Reference ) 23:25:33:0707 Start Timer: t = 0.000 sec: WorkingSetSize 60.403.712 ( 8.192 ) 23:25:33:0717 arrCompare Loaded: t = 0.008 sec: WorkingSetSize 61.366.272 ( 970.752 ) 23:25:33:0747 objDic Loaded: WorkingSetSize 61.693.952 ( 1.298.432 ) ERROR: 450: Wrong number of arguments or invalid property assignment 23:25:33:0747 after End Sub: WorkingSetSize 60.719.104 ( 323.584 ) What can we make of this? CoolBlue, I enjoy playing with your tool |
#28
|
||||
|
||||
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. |
#29
|
|||
|
|||
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? |
#30
|
||||
|
||||
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. |
|
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 |