Excel VBA program file suddenly bloats 20x

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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)?

Excel bloat.jpg
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,154
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jul 18, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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.

Excel bloat 2.jpg
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top