File size grows after running macro

  • I have a macro that cuts columns and inserts them into a new position in a sheet, and does so on two different sheets. The file before is 562KB. Once I run the macro and save, the file size jumps to 749KB. If I then close the file, reopen, and save, the file size goes back to 562KB. I'm looking for ideas as to why this is happening. Thanks!

  • Re: File size change after running macro


    Thanks for the reply. I did suspect this, so I created a KillVar sub that does a 'SET <object> = nothing' for each of the objects named in the cut/paste subs, but it doesn't seem to affect the file size. If the file size changes once the file is closed/reopened/saved, this seems to suggest that something is being stored at the first file save after running the macros. Is there a way to check 'memory' to see if something is still being stored? (not sure I am using the correct lingo, I am somewhat new to VBA). What would be stored in the file itself, and then cleared once the file is reopened?
    My KillVar sub for reference, in case I'm not executing this properly.


  • Re: File size change after running macro


    Normally, one would want to destroy the variables based on their type


    Set Object = Nothing
    Integer = Empty
    String = vbNullString
    Erase Array


    From what you mentioned, I don't pressume to say this would be a solution, but try anyway, just to be sure.

  • Re: File size change after running macro


    Quote from Dave Hawley;509376



    Thanks Dave. I have gone through this page previously, but it doesn't affect this specific case. If I run the macro and save, none of these suggestions affect the file size when I save again. Only when I close, reopen, and save does the file size go back to the original size.

  • Re: File size change after running macro


    Quote from SMChacko;509379

    Normally, one would want to destroy the variables based on their type


    Set Object = Nothing
    Integer = Empty
    String = vbNullString
    Erase Array


    From what you mentioned, I don't pressume to say this would be a solution, but try anyway, just to be sure.


    I have updated the file per your suggestion, but no change in behavior.


  • Re: File size change after running macro


    The size of your file is highly dependent on the usedranges in the sheets (especially when formatted).
    Declare as little variables as possible, most of the variables do not have to be public. and Object variables can be easily avoided using With...End With.
    As MS isn't the most comunicative business most of their programs have to be 'discovered' The logic behind their choices ramains most of the time in darkness. So many 'why' question remain unanswered.
    To minimize uour files: reduce the usedranges of the worksheets as much as possible.

  • Re: File size change after running macro


    Quote from snb;509412

    The size of your file is highly dependent on the usedranges in the sheets (especially when formatted).
    Declare as little variables as possible, most of the variables do not have to be public. and Object variables can be easily avoided using With...End With.
    As MS isn't the most comunicative business most of their programs have to be 'discovered' The logic behind their choices ramains most of the time in darkness. So many 'why' question remain unanswered.
    To minimize uour files: reduce the usedranges of the worksheets as much as possible.



    Thanks snb for replying. For reference, I have 35 macros in this workbook, and zero (0) public variables. Most are procedure-level, some are module level, but none are public (Mostly because I wasn't sure of the proper way to use then and clear them). In the two macros that are unders suspect, I'm using zero public variables. According to this page: http://www.ozgrid.com/VBA/variable-scope-lifetime.htm, all the variables therefore should be getting destroyed when the macros are completed. Am I understanding this? Again, I run the macro (do NOTHING else), save, the file size increases by ~200kb. I close the file, reopen, and save again (i.e. I do NOTHING else), and the file size returns to original, lighter size. Again, the question remains...what is being stored that gets destroyed at file close/reopen? And again, this is related to a macro that cuts entire columns, and inserts them into another location in the same worksheet


    Side question #1: the above mentioned OzGrid page mentions module level variables are destroyed when the workbook closes or the end statement is used. Does "End Sub" satisfy this requirement? (Note that this does not apply to my 2 macros under suspect, but I am curious) If so, it would seem that the KillVar sub I have is pointless in my workbook, since I have no public variables.


    Side question #2: Would using a KillVar sub as I've listed above to kill any public variables be a best practice? Then call this sub at the end of every other sub in the workbook?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!