0

Scenario

I have a userform, and when a button is pressed, it opens an Excel file as read-only, gets some values, and closes that file. Following are the codes I am using to open and close the file.

To open: Set planbook = Workbooks.Open(planFilePath, ReadOnly:=True)

To close: planbook.Close False

Problem

After closing the file, I see that file in the project window, for each time I press the button.

Modules show after closed Planner file.xlsx

It is using a lot of memory. Due to this, Excel hangs intermittently.

Why are files opened earlier but currently closed still appearing in the project window?

Note, when the button is pressed three times (means open and close a workbook three times), and thus there are three files listed in the picture.

Edit 1
It's not the module that remains, the whole file is visible in the VBA window, although the workbook is closed.

Edit 2
Noticed, if I keep the macro open for some time, the files showing in the project window, disappear one by one.

Edit 3
I found, the issue is due to opening and closing a shared workbook. See the answer for more explanation.

Anu
  • 1,123
  • 2
  • 13
  • 42
  • @JvdV Tried `Set Application.ThisWorkbook = Nothing`. But then encountered error. So gave `Set plannerWorkbook = Nothing`. But still have the same issue – Anu Aug 08 '18 at 07:45
  • Please include an entire "Sub" that demonstrates the problem. Not your entire code, if it's long - just enough of it that the problem is reproducible. It's impossible for us to tell from the information you provide what might be wrong. – Cindy Meister Aug 08 '18 at 13:07
  • @CindyMeister I am sorry that I can't give the entire codes here. It is calling many functions and subs after opened planbook. I am just hoping if someone encountered similar issue whereby workbook appear in project window after it is closed. But unluckily there is none yet – Anu Aug 10 '18 at 12:17
  • @GMalc Then what does `planbook.Close False` do if it is not closing the workbook? From what I know, that is the command to close a workbook without saving. – Anu Aug 10 '18 at 12:18
  • I specifically asked you NOT to include the entire code - only enough to reproduce the problem. Without more context it's not possible to *guess* what might be wrong. – Cindy Meister Aug 10 '18 at 12:22
  • @CindyMeister I don't know which part of the code is causing the problem so that it can reproduce the issue. Like I mentioned earlier there is a lot of data extraction from workbook 'planbook'. I already tried to isolate those codes so that it can reproduce. So far I couldn't reproduce when I extracts parts from it. Will post the codes for sure, if I am successful in extracting problematic part – Anu Aug 10 '18 at 12:29
  • I noticed something related to my problem. The file I am opening and closing is a shared file. Once I removed the sharing option of that workbook, it is closing properly without remaining in the project window. So the problem is related to closing a shared file promptly. Any idea anyone? – Anu Aug 11 '18 at 09:27
  • I still couldn't figure out what is wrong. Now noticed that after sometime, the files showing in project window, automatically disappearing one by one if I keep it open for a while – Anu Aug 18 '18 at 07:16
  • 1
    Your question has been undeleted, @Anu – Rob Sep 20 '18 at 13:04

1 Answers1

1

After many checks, it is found out that issue is not due to any codes. It is purely because of I am opening and closing a shared workbook. This type of issues are common when use shared workbooks. So it is best to avoid usage of shared workbooks.

During these process some items I did to clear memory issue are

  1. Clear out variables and objects immediately after their usage.

    Eg: Set wb = nothing

  2. Open workbooks as read only whenever possible.

    Eg: Set wb = Workbooks.Open(blanketPo, ReadOnly:=True)

  3. Try to use ByRef instead of ByVal whenever possible while passing data. But please take care of the implication also when use these two
  4. After copied large set of data and finished the usage with such data, clear away that from memory by using Application.CutCopyMode = False

  5. Use the following code to empty clip board such as object and stuffs which were copied and keeping in clipboard while running macro. I got this code from another forum

    Dim DataObj As New MSForms.DataObject 'empty the clipboard DataObj.SetText "" DataObj.PutInClipboard

Anyway the moment I choose not to use shared workbook, most of the memory and hang up issues disappeared. So its mostly all about the buggy shared workbook.

Anu
  • 1,123
  • 2
  • 13
  • 42