none
Problem with Excel VBA corrupting windows Start Menu programs RRS feed

  • Question

  • I have a problem that is driving me crazy.  I have an Excel spreadsheet, total size about 5 MB, running on two Windows 10 Pro computers running Office 2016 that manifests the following problems on both machines.  The spreadsheet has about 8,000 lines of VBA code.

    All of the following operations are performed with VBA code. On one sheet of the spreadsheet, the program pulls a data set from several other sheets, computes performance results, and plots it.  The the user has the option to temporarily change key parameters and execute code that recalculates the data set.  This operation can be performed repeatedly. If the "performance" resulting from the temporary changes results in better performance, the user can execute a macro that saves the new parameters permanently.  When doing these operations, the calculations are performed correctly.  In other words, Excel is performing as expected.

    If these operations are performed repeatedly, however, say 15 or more as a guess, weird things start to happen.  One:  sometimes, at apparently random times, one to up to 20 File Explorer windows open up suddenly -- all of them open to the OneDrive folder.  Two:  Some of the ribbon functions stop working.  For example, on the Home tab if I press the Find and Select item, the drop down appears, but none of the functions can be selected, i.e. moving the mouse to "Find" does not highlight the word "Find", and clicking on "Find" does nothing.  Three:  when printing, print preview does not work.  it just shows a blank area.  The actual printing works fine, however.    Four (are you ready?):  When I close Excel, some Windows 10 functions simply do not work (until I reboot or sometimes they come back after a while), e.g. Settings, Start Menu, MS  apps like Money.    I have a third party start menu installed (Classic Shell), and if I start these functions by using Classic Shell, the first time I open one of these functions (e.g. Settings) or Microsoft apps, the splash screen opens and stays open but nothing happens.  If I close the splash screen and open it again, then it works normally, but only for that app or function.  For each function or app I have to go through the open the splash screen, close it, and open it again.  This happens only for Microsoft apps (not for third party apps and not for any desktop programs).   Rebooting fixes it.

    I don't know what is going on.  I did find that after doing repeated Copy and Paste operations with VBA, Excel would trash the sheets where I was pasting data.  I was able to solve this by clearing the clipboard (with Application.CutCopyMode = False) after each copy/paste operation.  But it did nothing to change the behaviors above.

    Any guidance will be much appreciated.  Thanks.


    StillActive

    Thursday, August 24, 2017 10:23 PM

All replies

  • Hi StillActive,

    Have you tried to run a single operation repeatedly? Would the issue still happen?

    I would suggest you release objects after each execution if the issue happens due to repeated execution.

    Best Regards,

    Terry

    Friday, August 25, 2017 9:05 AM