Excel/VBA file more than duplicates in size for no apparent reason while being saved

Question

I’m working with the Excel and using some programming resources of the VBA via modules (without using forms), and there has been a problem, the file currently has 8MB and I saved continuously with another name, at least, indicating new version/release to each important change; it happens that from one save to another the file becomes another size, however, totally disproportionate.

This file went from 8MB for more than 40MB, and later, when I returned to the previous version with 8MB he passed to 16MB after days of work.

There was no inclusion of data (volume) or insertion of images, at most there were point changes in the programming, in the conditional formatting or with direct border formatting in the cells. I remember at least in one of the cases having generated a macro to check how to treat the changes of the borders of the cells via VBA, but that was it.

When I repair that the file has increased disproportionately in size, my procedure is to copy the current programming of the modified module VBA (text), for the same module of the previous file with the reduced size, overwriting. In addition to working as expected, saving the size continues as the original (8MB).

The issue is that this can occur without noticing the change in file size, and when noticed, it is too late to go back after numerous modifications; therefore in these cases should get some "trash" saved, which would be highly recommended to delete.

Someone knows this problem and can help me?

Answer 1

I once had a similar problem with Excel. I had a simple spreadsheet, no macros, no forms, no images. The only thing she had was cell information and formatting. Its initial size was 5MB, but after changing some information (includes 5 new lines and deletes more than 200) the file went to more than 25MB.

After a lot of head breaking I decided to copy the information to a new spreadsheet. As expected the size decreased and was in little more than 3MB.

As I was intrigued I continued researching and trying to fix the "original" spreadsheet. After some time I decided to take the formatting of the cells unused and, to my surprise, just to remove the borders of the blank cells the file was in little more than 3MB, exactly the same as the other file.

This may be your case as well. Try to check the formatting of cells, it 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 check how the new file will behave and what size it will after saved.

Answer 2

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

1. Formatting: a very frequent problem that occurs is when we apply formatting in many cells (text format, fill, borders, alignment...), but especially in whole columns (or rows) and empty cells. Usually this is solved as follows: go to the last used cell of your worksheet (Ctrl+End can help) and delete all columns on the right (up to the XFD column) and all rows below (up to line 1048576). Use formatting only on effectively used cells. Repeat the action for all sheets in your workbook.
2. Large amount of data: common problem, but difficult to get around. If you work with large databases in Excel, ie tens of columns and tens of thousands of rows, you will inevitably come across quite large files. Try to minimize the formatting of these tables and databases to the maximum and, if possible, fragment a large table into small tables that can be correlated with PROCV or other formulas as needed.
3. Macro generating overload: Taking into account point 1 and 2, make sure your Macro is not generating formatting and data overload. If it moves in any way with formatting, make sure it is not applying formatting to useless cells. Or, if somehow your macro isn’t running tables with thousands and thousands of rows.
4. Format XLSB: a last tip I give is, when working with files in the MB home and its routine allows such adaptation, that you save your spreadsheet with XLSB format instead of XLS, XLSX or XLSM. In large files, this format can get up to 80% smaller than traditional Excel file formats.

I hope it somehow helps. d~e a good turn on the internet you will find more ways to save space.