Excel / VBA file more than doubles for no apparent reason when it is saved

Navigation
3

I'm working with Excel and using some of the VBA programming features via modules (without using forms), and a problem has occurred, the file currently has 8MB and I continually save with another name, at a minimum, indicating new release / release every major change; it happens that from one rescue to another the file becomes another size, however, totally disproportionate.

This file went from 8MB to 16MB after working days.

There was no inclusion of data (volume) or insertion of images, at most were punctual changes in programming, in conditional formatting or with direct border formatting in cells. I remember at least in one case having generated a macro to see how to handle cell border changes via VBA , but that's all.

When I notice that the file has increased disproportionately in size, my procedure is to copy the current schedule from the changed VBA (text) module, to the same module as the previous smaller file, overlapping . In addition to working as expected, saving the size continues as the original ( 8MB ).

The point is that this can occur without any notice of the change in the size of the file, and when it is noticed, it is too late to go back after numerous changes; so in these cases there should be some "junk" saved, which would be highly recommended to eliminate.

Does anyone know this problem and can you help me?

    
asked by anonymous 05.04.2017 / 15:08

2 answers

2

I once had a problem similar to Excel. I had a simple spreadsheet, no macros, no forms, no images. The only thing she had was information in the cells and the formatting. Her initial size was 5MB, but after changing some information (including 5 new lines and excluding more than 200), the file went over 25MB .

After much breaking the head I decided to copy the information to a new worksheet. As expected the size declined and stood at just over 3MB .

As I was intrigued, I kept searching and trying to organize the "original" worksheet. After some time I decided to take the formatting of the cells without use and, to my surprise, just to remove the edges of the blank cells the file was just over 3MB , exactly like the other file.

This can be your case as well. Try to check the cell formatting, this might solve your problem.

In the latter case I suggest that you export the modules and import them into a new file, at least to see how the new file will behave and how big it will be after saving.

    
05.04.2017 / 15:47
1

It's hard to know exactly what's going on with your file. I've had similar situations and there are many variables that have to be taken into account to try to reduce the size of the file. I will try to suggest the most common causes for exaggerated document sizes and some tips on how to reduce.

  • Formatting: A very common problem that occurs when we apply formatting to many cells (text format, fill, borders, alignment ...), but especially in entire columns (or rows) and empty cells . This will usually resolve as follows: Go to the last used cell in your spreadsheet ( Ctrl + End can help) and delete all the columns to the right (up to the XFD column) and all rows down line 1048576). Use formatting only on cells actually used. Repeat the action for all of the sheets in your workbook.
  • Large amount of data: common problem, but hard to circumvent. If you work with large databases in Excel, ie dozens of columns and tens of thousands of lines, you will inevitably come across very large files. Try to wipe the formatting of these tables and databases as much as possible, and if possible, fragment a large table into small tables that can be correlated with PROCV or other formulas as you need them.
  • Macro generating overhead: Considering point 1 and 2, make sure your Macro is not generating formatting overhead and data. If it does somehow manipulate formatting, make sure it is not applying formatting to useless cells. Or, if somehow your macro is not running tables with thousands and thousands of lines.
  • XLSB format: One last tip I give is when working with files in the MB house and your routine allows such adaptation, that you save your spreadsheet in XLSB format instead of XLS, XLSX or XLSM. In large files, this format can be up to 80% smaller than traditional Excel file formats.

    • Hope it somehow helps. It's a good turn-around on the internet that you'll find more ways to save space.

        
    05.04.2017 / 15:53
    Create a string resource or a class of constants? findBy Doctrine