Excel 2016 issue: Memory leak when reading shape properties. RRS feed

  • General discussion

  • Hello,

    Since long time, reading some shape properties increase excel memory consumption.

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

    Option Explicit

    Public Sub testMem()
    Dim strOA As String
      While True
        strOA = Sheets(1).Shapes(1).OnAction
    End Sub

    Run it and look in task manager how memory consumption grows...

    It's same for

    strOA = Sheets(1).Shapes(1).DrawingObject.Caption

    strOA = Sheets(1).Shapes(1).DrawingObject.Text

    Wednesday, January 4, 2017 2:06 PM

All replies

  • Hi Denis,

    I could not reproduce your issue with your code.
    But it could not end.

    I'm not sure what do you want, and have made a sample.
    It displays the name of shapes and their count.

    Private Sub btnReadShape_Click()
        Dim shp As Shape
        Dim shpCnt As Integer
        shpCnt = 0
        ' ---
        For Each shp In ActiveSheet.Shapes
            ActiveSheet.Cells(shpCnt + 1, 1).Value = shp.Name
            shpCnt = shpCnt + 1
        ' ---
        MsgBox "shpCnt = " & shpCnt
    End Sub
    I'd like to know what you want.
    Sorry, if you get angry. I don't wan to do it.

    Thursday, January 5, 2017 1:06 AM
  • Hello,

    This code is to prove the memory leak not else.

    So it's why it's never end.

    As wrote in first post, run sample and look at your task manager the memory growing...

    Sorry for the long time, I don't use this account frequently...

    Wednesday, December 27, 2017 10:51 AM
  • Hi Denis,

    I would like to try again to reproduce your issue.
    Could you share your file via cloud storage such as OneDrive, Dropbox, etc?
    Please remember to modify/delete your vital/private data before sharing.



    Wednesday, December 27, 2017 11:17 AM
  • Hello,

    You only need to copy my code in your workbook...

    Run it F5 (it doesn't stop).

    Open a task explorer.

    Then look at memory usage growing... while macro is running.

    Tuesday, January 2, 2018 2:19 PM
  • I ran your endless loop and looked at task manager. Additional  memory usage was trivial but CPU was high (as expected). Then I tried simply this -

    While True
        strOA = "abc"

    Memory usage was the same (trivial) and CPU the same (a lot). IOW I didn't notice any significant difference in use of system resources between the two routines.

    Tuesday, January 2, 2018 4:05 PM
  • How many time did you ran it?

    Here what I've got:

    At start: 71040K
    1 minute: 84256K
    2 minutes: 99032K
    5 minutes: 148840K

    Now with strOA = "abc"

    At start: 61232K
    1 minute: 56056K
    2 minutes: 56020K
    5 minutes: 55996K

    Do you see the difference?

    Wednesday, January 3, 2018 12:18 PM
  • Yesterday I only left the loop running for about 20 seconds by which time it had been running long enough to see no significant changes. I repeated again in both 2007 and 2016x64 and left running for several minutes. Again CPU rose in both but memory remained the virtually unchanged throughout (both readings only slightly fluctuating).

    Did you try without DoEvents, it could allow response to events in other unknown processes in your system. Not sure why you you included it but In real life never use it like that.

    Wednesday, January 3, 2018 6:24 PM
  • I use DoEvents to be able to stop it.

    I'm on 2016 32b and I test it too in a virtual machine with 32b too (all running under win7 64b).

    Maybe this is due to 32b version?

    Wednesday, January 3, 2018 7:30 PM
  • I don't have 2016x32, I could try 32bit 2010 and 2013 but not today. 2007 of course is always 32bit.

    Maybe your Dovents is allowing something else to use memory, you should be able to interrupt with ctrl-break. If you really need doevents include a counter and call it say every 500 loops. (or say gets called roughly every 0.3 to 0.5 sec)

    What's the overall objective? Most routines involving shapes would complete in under a minute, your own quoted 1 minute result doesn't seem to show much additional memory use.

    Wednesday, January 3, 2018 10:52 PM
  • Hi,

    Please provide the latest code that does not have endless loop. I will try it in Excel 2016, 2013, and 2010 (all 32-bit) on Windows 10, 8.1, and 7 (all 64-bit).

    If you need endless loop, please explain its reason.



    Wednesday, January 3, 2018 11:06 PM
  • It's for something running 24/7.

    Without DoEvents, I've got the same but can't stop it...
    I'm installing 2016 in 64b to see if it is from that.

    PS: The endless loop is to show memory leak. Without, you cannot see it as it consume less than 126k.
    Wednesday, January 3, 2018 11:35 PM
  • I've installed 64b version and it's the same...
    I don't understand why this works at yours as I install it in a fresh Virtual Machine.

    Microsoft Office 365 Business
    Version 1711 (build 8730.2127)

    Thursday, January 4, 2018 12:49 AM
  • Have you tried in a non VM?

    When I tested I only had one shape on the sheet. If(?) you have multiple shapes maybe the difference is related to reading from the collection, that's a long shot but say if you think there might be any other differences in our respective workbooks.

    You should be able to break the loop with Ctrl-Break without DoEvents (or maybe Esc depending on your system), or even clicking the Reset button (generally not recommended).

    Thursday, January 4, 2018 7:30 AM
  • My host is my working station, so I cannot install 64b due to other software (SAP, ...).

    At each time, now, I restart from new workbook, with only one shape as yours.

    I can make a video to show you?

    Thursday, January 4, 2018 10:56 AM
  • I know it's frustrating when others can't repro' what you see, no need for the video, I believe you! I doubt the memory issue is related to a difference in use of 32/64 Office, but something else in our respective systems or test scenarios.

    Ashidacchi and anyone else prepared to test - add a shape (eg rectangle) to a sheet and run the endless loop shown in Denis' OP. If memory does increase try again with DoEvents commented, Ctrl-Break or hit the reset button to break or End

    Thursday, January 4, 2018 12:49 PM
  • Hi Denis,

    I also believe you. But our results are different. They might be caused from our environment (version, build and architecture of Windows, Excel). I think the most is the file we are using.
    Please share your file via cloud storage such as Dropbox, OneDrive, and so on.



    • Edited by Ashidacchi Thursday, January 4, 2018 10:09 PM
    Thursday, January 4, 2018 10:09 PM
  • Hello,

    I'm not sure this will work as I make a new file each time, but I uploaded my file here:

    You have 7 days to download it.

    PS: Why I cannot send link?
    Body text cannot contain images or links until we are able to verify your account.

    Friday, January 5, 2018 11:07 AM
  • Hi Denis,

    Thank you for your sharing the file.  I could download and open it.
    I would like to ask you to explain how to reproduce the issue step by step.

    The 1st time when I opened the file, it required me to [Enable Editing] -- something flushing -- [Enable Contents].
    But after close it (no confirmation to save), I never meet [Enable Editing] - [Enable Contents], and memory-usage does not seem to be changed.
    Thank you.



    • Edited by Ashidacchi Friday, January 5, 2018 12:06 PM
    Friday, January 5, 2018 11:27 AM
  • You only need to open file, activate contains.
    Open VBA Editor (Alt+F11)
    Open ThisWorkbook
    And Start testMem (F5)

    Open Task manager (Ctrl-Shift-Escape) Process Tab
    Look at EXCEL.EXE line in Memory column.

    Friday, January 5, 2018 12:00 PM
  • Hi Denis (Good night, in Japan),

    You wrote "Start testMem (F5)"... but [F5] only shows [Go to] dialog. I suppose it's because of the difference of Locale between us. Mine is Japan/Japanese, but you would be different (I suppose).

    What is the function/feature of [F5]?



    Friday, January 5, 2018 12:14 PM
  • GN,

    F5 is for the VBA editor.

    On Excel you can run it with Atl+F8 and choose "ThisWorkbook.testMem" then Run.

    Friday, January 5, 2018 12:34 PM
  • GN Denis,

    No, [F5] is not for the VBA Editor.
    VBA Editor is opened by [Alt]+[F11] (this is the same between us).

    [Alt]+[F8] shows...

    After clicking [Run]...

        (This says "parameter is invalid")

    Sorry, it is time to drink/go to bed... I would like to try it tomorrow. I hope you will understand the difference between Local or Language.  My environment (Windows and Excel) is ready for Japanese/English.

    Good night...     


    Friday, January 5, 2018 12:48 PM
  • Hi Denis,

    I could reproduce the issue under this environment:
       (virtual machine) Windows 7 Home 64-bit, Excel 2010 32-bit
       (virtual machine) Windows 8.1 Pro 64-bit, Excel 2013 32-bit
       (virtual machine) Windows 10 Home 64-bit, Excel 2013 32-bit
       (virtual machine) Windows 10 Pro 64-bit, Excel 2016 32-bit

       (physical) Windows 7 Ultimate 64-bit, Excel 2013 32-bit
       (physical) Windows 10 Pro 64-bit, Excel 2016 32-bit
       (physical) Windows 10 Pro 64-bit, Excel 2016 32-bit

    cf. in all virtual machines, assigned memory is 4 GB - 6 GB
         in all physical machines, loaded memory is 8 GB, 16 GB, 24 GB

    I have no idea why your code uses more memory while running.
    So, I recommend to give a feedback to Excel User Voice.



    Saturday, January 6, 2018 3:46 AM
  • Hello,

    Yes, I'm not alone...

    I'm thinking that these methods allocate memory and don't freed it.

    Can you test for these too?
    strOA = Sheets(1).Shapes(1).DrawingObject.Caption
    strOA = Sheets(1).Shapes(1).DrawingObject.Text

    I go to Excel USer Voice.

    Thanks a lot

    Saturday, January 6, 2018 9:34 AM
  • A new idea posted:

    Saturday, January 6, 2018 10:01 AM
  • Needs some Votes...
    Saturday, January 6, 2018 10:40 PM