Excel VBA program file suddenly bloats 20x

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
154
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,775
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
154
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,853
Messages
5,833,985
Members
430,249
Latest member
Muka

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