PDA

View Full Version : Solved: Optimising file size



andy_uk
08-11-2004, 04:25 PM
(Apologies in advance, the standard of help in here is that good I'm gonna have to cheat ...)

... I'm working on a fairly large project, lots of coding involved, if & when I get stuck I'll be sure to drop by. OT VBA-wise, I'd welcome some views on a different aspect of it.

There's a "Contacts" table with maybe 200 records. Each record has an entry in the "Department" field, a string of between 7 - 16 characters. There are 15 departments, so I thought if I set up a unique departments list, I could replace the strings with the relevant number (1st department = "1", 2nd = "2", etc.), so when I need to pull out the info down the line I can just INDEX the list.

It works well enough so far, I just wondered if there was any way of assessing upfront if it's worth doing in terms of saved file size ; some kind of formula perhaps?

TIA,
Andy

Jacob Hilderbrand
08-11-2004, 04:54 PM
If you are going to use VBA to get the names from the index number, then there really would be no noticable difference in speed or size from listing the names directly.

Make sure that your used range stays what it should be. Sometimes Excel will think that all larger portion of the sheet is being used whe in fact it is not. This can add a lot to the file size. If this happens just delete all the rows and columns that are not used for each sheet.

I you compile your VBA Project it will add the the file size, but also increase speed. You can't have both so you have to choose what you really want here.

Another thing, if you are using a lot of images in your workbook, consider storing the images on the hard drive and loading them into the workbook. A simple logo on several worksheets can add to the size really fast. Load the images on open, and remove them on before_save.

roos01
08-13-2004, 12:41 AM
here just a quick thought.
I support DRJ option that it won't make that much of difference in speed using indexes.
Also the file size will make not much a difference when adding VBA to it. (I think even that using macro's will enlarge your file more then using formulas.) I usaly use VBA to make my life easier as it automates actions. I never used it decreasing the filesize.

But still I think it is good to use a seperate department list. the benefits here is as always in normalizing data. if the department name changes you have to change it only at one place. (your department list)

to manage your file size you might take a look at:
XL: How to Minimize Metadata in Microsoft Excel Workbooks
http://support.microsoft.com/default.aspx?scid=kb;en-us;223789

Jeroen

andy_uk
08-13-2004, 01:50 AM
>> If you are going to use VBA to get the names from the index number ...

Possibly my bad for explaining poorly. I might do, or I might just use functions (e.g. VLOOKUP to pull Department into a report). I'll admit I hadn't considered the maintenance side, although in this case it's not a major thing - changing Department name in the list seems pretty much the same as using CTRL+H on the Departments field.

I guess it would have been better just to say, "if I have 200 cells containing average 8 chars, then if replace them with 1 or 2 digits, what's the saving in bytes?"

Thanks for the feedback tho. :)

Rgds,
Andy

Jacob Hilderbrand
08-13-2004, 02:03 AM
{Snip}
I guess it would have been better just to say, "if I have 200 cells containing average 8 chars, then if replace them with 1 or 2 digits, what's the saving in bytes?" {Snip}
Somewhere between nothing and negligible.