Slow "comparison/replace" script

whatsup · #16 06-17-2014, 08:23 AM

Quote:

..just to see what happened and the object was released, according to VBE anyway...

CoolBlue, how did you do that, to check whether memory is freed or not? I tried to come up with an example, but so far I can't think of a method to access memory to look up if the key is removed or not.

CoolBlue · #17 06-17-2014, 08:40 AM

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.

whatsup · #18 06-17-2014, 10:36 AM

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

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.

BobBridges · #19 06-17-2014, 12:11 PM

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.

CoolBlue · #20 06-18-2014, 08:25 AM

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...

whatsup · #21 06-18-2014, 10:36 AM

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 )

I would interprete it the way, that although at Start has been allocated 180,219,904 Bytes, excel was claiming more memory for the task and ended up with 196,042,752 Bytes allocated. That means it doesn't say exactly whether or not memory was freed during or after runtime. It just says, that at the end excel now is asigned the 196 millions Bytes of memory whether it uses them or not. - Are you following what I try to say?

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

CoolBlue · #22 06-18-2014, 03:34 PM

Quote:

Originally Posted by whatsup

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 )

I would interprete it the way, that although at Start has been allocated 180,219,904 Bytes, excel was claiming more memory for the task and ended up with 196,042,752 Bytes allocated. That means it doesn't say exactly whether or not memory was freed during or after runtime. It just says, that at the end excel now is asigned the 196 millions Bytes of memory whether it uses them or not. - Are you following what I try to say?

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

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.

whatsup · #23 06-18-2014, 05:40 PM

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:

If you have any useful links to offer please throw them down here. I would be very grateful of that.

Seemingly you are quite familiar with API calls and doing great, me I don't, so maybe you make something of this:
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

In this example with ValueIsGone() you track down the pointer obtained from ExampleForScalar() and the reading of Memory says the value is zero.

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

But Mem_Copy in the function ObjectFromPointer() is killing the excel application, no debugging but killing, so be careful with that one.
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/

CoolBlue · #24 06-18-2014, 06:47 PM

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!

whatsup · #25 06-18-2014, 07:52 PM

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 )

But it's freshly started, after several runs it will level on almost 80 million Bytes. The differences between Start and End look mostly the same with the set to nothing switch false, sometimes equals. With the set to nothing switch true I got the impression Start and End more frequently match, but maybe that's just imagination, actually I never counted.

whatsup · #26 06-18-2014, 08:12 PM

Bob, I forgot

Quote:

Originally Posted by BobBridges

...That does make a difference, especially because I tend to leave my laptop on for a week or three at a time and Excel is unlikely to be closed at any time during that period...

I'm quite curious about the results you get in respect of memory allocation when trying the tool CoolBlue provided (DictionarySearch).
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.

whatsup · #27 06-18-2014, 08:38 PM

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 )

The difference between Start and End (323.584) remains out of 40 runs 30 times the same, equals once, and for the rest it differs just slightly +/-
What can we make of this?

CoolBlue, I enjoy playing with your tool

CoolBlue · #28 06-19-2014, 01:55 AM

Quote:

Originally Posted by whatsup

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 )

The difference between Start and End (323.584) remains out of 40 runs 30 times the same, equals once, and for the rest it differs just slightly +/-
What can we make of this?

CoolBlue, I enjoy playing with your tool

Well... it's a cool tool!
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.

whatsup · #29 06-19-2014, 05:29 PM

Quote:

To be honest, its not really any different from if the throw error switch is false. This is because the error is "handled".

Now I do see it too - you're right. Nice idea postboning the error-exit. That seems now allright to me. Nonetheless, somehow it's not the right way to go. It still leaves to much room to guessing whether memory is freed from the particular object or not...

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

That way it can be used in the first macro, proofing that if the pointer contains an object, it can be copied to an object (remember this words):

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

That runs without any error. Now let's go to the check afterwards:

Code:

Sub ObjectIsGone2()
If ObjectFromPointer(lng_ObjPtr) Is Nothing Then
    MsgBox "Object is Nothing"
Else
    MsgBox "Object exists"
End If
 End Sub

Now again that will make excel crash!!
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?

CoolBlue · #30 06-19-2014, 05:47 PM

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