Tom Ogilvy please help : File size getting large

[halimnurikhwan]

Hi Tom,

I have a file that work add a new WB and save it to another directory,
but I have a problem with the size of that file is gained ... why ?
from 92kb to 6mb
Before saving I use :
Public Wb as Workbook

I use Wb in procedure1 and reuse Wb in procedure2,
I assumed that Public Wb is can be used from another procedure
during runtime ... is that right ?

I thought that before automation I've crashed the Excel memory or
temporary file, is it possible ?

Any help will be appreciated ....
Rgds,

Halim.

 

[Tom Ogilvy]

Workbook size is can be affected by many things. Excel maintains a
UsedRange property for each sheet. It describes the smallest rectangular
area that Excel had determined it needs to maintain information about. The
remainder of the worksheet is essentially virtual. It is possible that your
code is causing Excel to consider that a larger rectangle must be used. An
example would be this code

Range("A1").Value = 3

Range("IV65536").Value = 5

a demo from the immediate window with the activesheet being a new blank
sheet:

? activesheet.usedrange.Address, activesheet.usedRange.count
$A$1 1
Range("A1").Value = 1
Range("IV65536").Value = 3
? activesheet.usedrange.Address, activesheet.usedRange.count
$1:$65536 16777216

So you see excel has gone from maintaining information on 1 cell to
maintaining information on 16,777,216 cells. This obviously makes the file
size larger when excel writes information on so many more cells. And this
is just one sheet.

While this example is contrived, it is possible your code is causing
something similar to happen.

Here is a discussion on Debra Dalgleish's site:
http://www.contextures.com/xlfaqApp.html#Unused

 

[halimnurikhwan]

Hi Tom,
I've cleared the contents of used range that no more than a huge range
that
had been used ... but I've done with that problem ... but still don't
know why ?

many thanks for your exlanation ...

I think that's an office Excel Bugs ...
sorry for the lateness posting this ...

Regards,

Halim

Tom Ogilvy menuliskan: