none
Unreleased Memory on Property get for Shape.OnAction On Excel RRS feed

  • Question

  • Hello,
    I've got a macro reading a shape's OnAction property . It's looping, and I saw that Excel keep a lot of memory without release it.

    Here how to reproduce the bug.
    Create a workbook with one shape.
    Create a vba module with this code:

    
    

    Option Explicit

    Public Sub testMem()
    Dim strOA As String

    While True
      strOA = Sheets(1).Shapes(1).OnAction
      'strOA = Sheets(1).Shapes(1).DrawingObject.Caption
      'strOA = Sheets(1).Shapes(1).DrawingObject.Text
      DoEvents
    Wend
    End Sub

    Run this macro and see Excel memory width in windows task manager.

    This occur only when reading this property not on writing, nor on reading other string property.

    OS Microsoft Windows*7 Enterprise (Like on XP SP3)
    Version 6.1.7601 Service Pack 1 Build 7601
    Microsoft Excel 2010 (14.0.6129.5000) SP1 (32bits)
    Tuesday, April 30, 2013 10:09 AM

All replies

  • Of course it will give problems. The code appears to put it into an eternal loop because you are telling it to loop and have not got any condition set to break out of the loop.

    If you want help with this then you need to tell us what you are trying to achieve.


    Regards, OssieMac

    Wednesday, May 1, 2013 6:26 AM
  • Hello,

    Could you read post before reply?

    Hello,
    I've got a macro reading a shape's OnAction property . It's looping, and I saw that Excel keep a lot of memory without release it.

    Here how to reproduce the bug.

    Run this macro and see Excel memory width in windows task manager.

    This occur only when reading this property not on writing, nor on reading other string property.

    It's not abnormal to have a macro looping. The mistake is that Excel don't use garbage for some function like get OnAction. But let OnAction is good...

    So don't tell me "Of course it will give problems."

    Waiting a good reply.

    Wednesday, May 1, 2013 9:28 AM
  • I read your OP carefully and read it exactly the same way as OssieMac did. I would have given a similar initial appraisal and asked for more information with a view to help identifying where the problem was.

    You said your OnAction code loops, well normally OnAction doesn't loop unless the coder has inadvertently introduced some loop. You went on to describe how to reproduce your loop which I assumed to be similar to your actual code. Whilst you may have intended something different most readers would have interpreted it the way we did.

    Please appreciate (unless obviously MS staff) people here volunteer help. Sometimes we do misread or misunderstand, but then all anyone has to do then is clarify.

    Peter Thornton

    Wednesday, May 1, 2013 10:23 AM
    Moderator
  • Could you read post before reply?

    It's not abnormal to have a macro looping.

    Firstly I did read your post and based on reply by Peter Thornton, I am not the only one who interpreted your post as I did.

    Secondly it IS abnormal to place a macro into an eternal loop and testing an eternal loop to see what it does proves nothing.


    Regards, OssieMac

    Wednesday, May 1, 2013 10:53 AM
  • Sorry for the form of my reply, I'm French so maybe I offend you with my poor English. My code is here only too prove the bug, not really the code I've done.

    In my real code, I have to control that each shape added on workbook have the good OnAction property.

    To explain in large, my workbook do some shape modification while reading an PLC interface. Like: Make a shape green when a bit is on or red when is off. And, if you click on this shape, it's toggle this bit...

    The mistake is that if you copy a lot of shapes from an other workbook, the OnAction keep old workbook name. So if you click on this shape Excel open this old workbook with its old macro...

    So I verify each shape have good macro link to OnAction, while macro running. So, it's take more time to fill memory but after 1 or 2 hours (some times more) I've got a Windows message indicating that I've got no more memory...

    The bug is only that Excel don't free memory for this property get (like .DrawingObject.Caption or .DrawingObject.Text). Once more time, the code is here only to reproduce so everyone can test it.

    I thought to be clear when I said: Here how to reproduce the bug. If not sorry.

    Is one of you test my code and see Excel memory increase?

    Best regards, Denis

    Wednesday, May 1, 2013 3:12 PM
  • It's not clear exactly what you are doing but it seems most likely the loop is related to your linked OnActions. It is impossible for us to know the consequences of that but it seems unlikely to be Excel's fault.

    Before doing anything suggest delete all the OnAction strings you've accidentally copied between workbooks, eg

    Sub RemoveOnActionLinks()
    Dim s As String
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim shp As Shape
         Set wb = ActiveWorkbook
         For Each ws In wb.Worksheets
                 For Each shp In ws.Shapes
                         s = shp.OnAction
                         s = Replace(s, wb.Name & "!", "", , , vbTextCompare)
                         If InStr(s, "!") Then
                                 shp.OnAction = ""
                         End If
                 Next
         Next
    End Sub

    This is only lightly tested so double check it deletes only and onAction strings with links in shapes in the activeworkbook, assuming of course you want all OnActions that link to other workbooks to be deleted.

    Peter Thornton

    Wednesday, May 1, 2013 4:55 PM
    Moderator
  • Thanks,

    I thought that, but my post don't ask how can I do with Excel Bug, but just "Hey, there is a bug to correct!!!"

    Sorry if it's not a M$ usual, I come from open source program ;)

    Wednesday, May 1, 2013 7:41 PM
  • Hello,

    Nobody to report this bug to Microsoft?

    Tuesday, May 14, 2013 3:32 PM
  • What is the bug?

    Peter Thornton

    Tuesday, May 14, 2013 5:43 PM
    Moderator
  • Hello,
    I've got a macro reading a shape's OnAction property . It's looping, and I saw that Excel keep a lot of memory without release it.

    Run this macro and see Excel memory width in windows task manager.

    This occur only when reading this property not on writing, nor on reading other string property.

    Can't be more clear.

    Run this macro and you'll see it, after 1 or 2 hours (depend of memory) Excel crash...

    Wednesday, May 15, 2013 10:54 AM
  • As already pointed out by both OssieMac and myself the macro in your OP doesn't demonstrate any bug at all, only what is entirely predictable. It's a bit like keeping your foot on the pedal and over revving the engine until it blows up in a cloud of smoke and then saying the engine is unreliable.

    Peter Thornton

    Wednesday, May 15, 2013 2:02 PM
    Moderator
  • So why if I read .AlternativeText or other property, memory usage don't grow???

    It's not processor time, but MEMORY. Where it's normal to load and not unload memory just for reading property?

    It's like Excel allocate memory without freeing it. Just for these three property.

    Wednesday, May 15, 2013 9:06 PM
  • I ran your macro and compared reading OnAction and AlternativeText, for me there was no difference. Also even if I simply run

    While True
     DoEvents
    Wend

    ... I get the same results, that is CPU usage (but not memory) quickly grows to 100% until I do ctrl-break and bail out

    Calling DoEvents indefinitely can be very resource intensive but it depends on what else is going on in your system, at the very least it takes time.

    If you particularly need DoEvents in a long loop, eg to check if user has pressed a cancel button, do it like this -

    ' inside the loop
    nextDoEvnts = nextDoEvnts + 1
    If nextDoEvnts >= 10000 then ' adjust to flag say between every 0.1 to 0.5
    seconds
    nextDoEvnts = 0
    DoEvents
    End If

    Peter Thornton

    Thursday, May 16, 2013 8:57 AM
    Moderator
  • Hello,

    What is version of excel have you got, because bug was corrected on Office 2013.

    Me it's on Microsoft Excel 2010 (14.0.6129.5000) SP1 (32bits)

    Don't talk about CPU usage, the bug is only about Memory.

    Thursday, May 16, 2013 10:51 AM
  • I tested again on two different machines with 2003 & 2010/32 respectively. On both the memory almost didn't change, though CPU usage went quickly to 100% on the 2003 machine but only about 60% on the system with 2010. That was with DoEvents but suggest you try without DoEvents.

    Peter Thornton

    Thursday, May 16, 2013 12:18 PM
    Moderator
  • I start the test with Excel has got 21 200K. Wait 1mn then it's up to 41 108K...

    Are you sure to have the line "strOA = Sheets(1).Shapes(1).OnAction"  ?

    Here, someone else having same trouble:

    http://www.mrexcel.com/forum/excel-questions/677701-memory-leak-excel-2010-excel2007-when-writing-textbox-visual-basic-applications.html

    Thursday, May 16, 2013 12:39 PM
  • Yes definitely testing Sheets(1).Shapes(1).OnAction

    I've just tested again in a third machine with WinXP & XL2007. The CPU quickly went to 100% but the memory almost didn't change.

    Did you notice any difference after testing without DoEvents?

    Peter Thornton

    Thursday, May 16, 2013 1:32 PM
    Moderator
  • It's same. Just Excel stop responding...

    Two years ago, I haven't got this bug... Perhaps an update?

    Thursday, May 16, 2013 2:21 PM
  • I'm not aware of any update concerning this. However consider a different approach. Providing you have deleted all those accidentally copied buttons there should be a better way. I have routines that loop several hundred shapes checking properties and never had your problem.

    Peter Thornton

    Thursday, May 16, 2013 3:40 PM
    Moderator
  • Look for an older version 14.0.4760.1000 (32 bits) on WinXP, it's same.

    Thursday, May 16, 2013 5:10 PM
  • Hello, Any news with this?

    Always same, even with new updates...

    Will M$ work on it???

    Saturday, February 21, 2015 9:06 PM
  • What I'm reading is that some people have a memory leak but many don't. That means a hard to identify issue. Firstly update to SP2 and latest cumulative update. Any issues with latest updates are likely to waste far less time than not having the latest and struggling with bugs that are often already fixed.

    I'm reading that your problem is onaction strings that have the old workbook name in them after copying, so try this version of Peter's code:

    Sub RemoveOnActionLinks()
    Dim s As String
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim shp As Shape
        Set wb = ActiveWorkbook
        For Each ws In wb.Worksheets
            For Each shp In ws.Shapes
                s = shp.OnAction
                If InStr(s, "!") > 0 Then
                    shp.OnAction = Mid(s, InStr(s, "!") + 1)
                End If
            Next
        Next
    End Sub
    

    Use DoEvents sparingly, and not in a loop that repeats a lot. you might try adding:

    Set shp=nothing
    Set ws=nothing
    set wb=Nothing

    at the end of the routine. That might help remove the memory problem but I would not think it necessary.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, February 22, 2015 8:32 PM
  • Hello,

    What did you misunderstand???

    Open new workbook.

    Create a new shape (like square) on first sheet.

    Open VBA and copy these lines in sheet code:

    Public Sub TestMem()
      Dim sp As Shape
      Dim str As String
      Set sp = ThisWorkbook.Sheets(1).Shapes(1)
      While True
        str = sp.OnAction
        DoEvents
      Wend
    End Sub
    Open task manager (strl+Shift+Esc).

    Read Excel Memory usage (24792K).

    Run TestMem() for 20 seconds.

    Now Excel use 34552K...

    Or with this other code:

    Public Sub TestMem()
      Dim str As String
      While True
        str = ThisWorkbook.Sheets(1).Shapes(1).OnAction
        DoEvents
      Wend
    End Sub

    From 34740 to 40260.

    Now this one:

    Public Sub TestMem()
      Dim str As String
      While True
        str = ThisWorkbook.Sheets(1).Shapes(1).DrawingObject.Caption
        DoEvents
      Wend
    End Sub

    From 40348 to 44560

    And finish with this one:

    Public Sub TestMem()
      Dim str As String
      While True
        str = ThisWorkbook.Sheets(1).Shapes(1).DrawingObject.Text
        DoEvents
      Wend
    End Sub

    From 44588 to 46696.

    Even When I close workbook, Excel stay at 47496K!!!

    I'm up to date:

    Microsoft(r) Excel (r) 2010 (14.0.7143.5000) SP2 MSO (14.0.7143.500)

    Microsoft Windows 7 Entreprise
    Version    6.1.7601 Service Pack 1 Build 7601

    Do you keep telling me that everything is normal ???

    Monday, February 23, 2015 8:28 PM
  • Denis,

    I don't know exactly what do you want.

    You want to report a bug?

    You need to use a macro that access extensively the OnAction property and can't get your application to work?

    There are serveral documented and undocumented memory leak scenarios at Excel. However, in the moment I use your code that loops millions of times in an object, everything can happen.

    For instance: I bet that if you run this code, sooner or later your Excel will crash:

    dim wb as workbook
    do
    set wb = workbooks.open("c:\test\workbook.xlsx")
    wb.close false
    set wb = nothing
    doevents
    loop

    Even having the best practices, the garbage collector is not perfect. Maybe some methods consume more or less memory than others. We don't really know what happens in the background of these VBA code lines!

    I've not seen up to date an Excel workbook that needed to access 1 billion+ times an OnAction's shape property, so I don't really think we have a bug here, we have a memory anomaly that happens if we push the VBA limits in a very directed way.



    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Monday, February 23, 2015 8:47 PM
  • If you really can't work without this memory leak fixed, raise a support incident with Microsoft. We're all volunteers here and definitely do not have access to code to try tracking this memory leak down.

    My code and other people's suggestions are all aimed at helping you work around the problem. We have no interest in proving a bug that only happens for us if you loop many thousands of times.

    So yes we understand what you are saying. We are saying it is sometimes a problem, but you can live with it, hence our workarounds.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Tuesday, February 24, 2015 1:01 AM
  • Hello,

    Thanks for your answers, but I need it working... This workbook is designed to work lot of days continuously.

    I don't want to pay money to open a case to say at M$ where their mistakes are...

    Could someone here have contact with M$ developer?

    Thursday, February 26, 2015 4:41 PM