Excel VBA program file suddenly bloats 20x

[Larry Haydn]

I have an Excel VBA application that is used to:
* scan barcodes on a pallet
* gets a new pallet number from the database (Excel workbook)
* writes details back to the database
* then prints out a pallet label
Reading / writing to the database is by programmatically opening the database workbook.
It was around 260KB in size.

Now that I have converted all read/write using SQL, the file size ballooned to 6MB.

What happened?
How can I compact the file (like Access)?

 

[RoryA]

Without being able to see any code, I suggest you open the large workbook(s), then go through each sheet pressing Ctrl+End and see if any of them jump to a cell well past the end of your actual data.

 

[Larry Haydn]

Found the cause!

I formatted a whole column with borders and unlocked them.
After I removed the formatting for the column, and reformat only 100 cells,
the file size shank back to less than 200K.

Lesson learnt.