+ Reply to Thread
Results 1 to 5 of 5

Reducing Excel Macro-Enabled File Size - Hack discovered

  1. #1
    Forum Contributor
    Join Date
    10-10-2018
    Location
    Calgary, Canada
    MS-Off Ver
    2016
    Posts
    214

    Reducing Excel Macro-Enabled File Size - Hack discovered

    I discovered this hack to reduce excel file size. I have made many code changes (I am over 60k lines of code), and every time I make a change, even if data is not added, and in fact sheets are removed or userforms removed, the excel file keeps creeping up in size. As long as there are some VBA changes, the file size will increase.

    However, there is a hack that will reduce it to the "real" size. Simply open the workbook, open up a userform without making changes and then exit. Excel will prompt you to save, and when you do so, the file size decreases.
    Last edited by 6StringJazzer; 01-10-2020 at 12:54 PM. Reason: Modified title to indicate macro file

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    Re: Reducing Excel File Size - Hack discovered

    Thanks for sharing this tip. However, I have never experienced the problem you are describing. 60K lines of code is a lot of code, maybe it's only large volumes of code where this shows up.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,696

    Re: Reducing Excel File Size - Hack discovered

    Moved from Excel Programming / VBA / Macros.

  4. #4
    Forum Contributor
    Join Date
    10-10-2018
    Location
    Calgary, Canada
    MS-Off Ver
    2016
    Posts
    214

    Re: Reducing Excel Macro-Enabled File Size - Hack discovered

    also - this is working when Content is disabled. I'm not sure if this works when Content is enabled.

    So what I do, is make a copy of the workbook (so that Excel prompts for edit enable and content enable), open in edit enabled but without content enabled. Then if I want to test the workbook, make another copy and enable the content on this new copy.

    To answer previous question, with 60k+ lines of code, each time I edit VBA and save, the file size increases by about 30-50k. Sometimes it will mysteriously jump by 500kB-1MB, just from code changes

  5. #5
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,343

    Re: Reducing Excel Macro-Enabled File Size - Hack discovered

    I think I am about the same number or rows maybe just a little less.
    I found just exporting all the VBA and reimporting it seemed to make it smaller (which I do via code).
    Your process didn't seem to help although likely my file isn't currently bloated as its reasonably close to the low end of what I think it can be currently.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reducing File Size
    By zmster2033 in forum Excel General
    Replies: 7
    Last Post: 01-06-2017, 01:30 AM
  2. reducing excel file size to increase its profeciency
    By Attique in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2016, 09:35 AM
  3. Reducing Excel File Size
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2012, 12:22 AM
  4. [SOLVED] Hints or tips on reducing Excel 2007 file size
    By AdamC1 in forum Excel General
    Replies: 1
    Last Post: 07-23-2012, 03:07 PM
  5. Reducing file size
    By danidanii in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-14-2011, 08:35 AM
  6. Reducing size of an excel file
    By dbarelli in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2006, 11:55 AM
  7. Replies: 2
    Last Post: 03-29-2006, 12:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1