File getting Twice as large over Network

[supersprdsht]

I have a spreadsheet with a lot of VBA code that is approximately 2 MB on a shared network drive (that is shared between users in 2 states). In the 2 state, where the users are mapping to the shared drive, the file is having problems saving and is 2 to 3 times as large --- now, 6 - 7 MB! Does anyone have any idea why? It is still 2 MB in the other state. Also, is there any way to place the VBA code somewhere else to make the file smaller? What else can I do to try to minimize the file size. The file is about 12 sheets worth of data and code.

Thanks for the help!

 

[Damon Ostrander]

Hi super,

There are several things in your question that I don't completely understand. First, do you mean that when a user in state 1 modifies the workbook it saves as 2 MB, then a user in state 2 modifies it the workbook expands to 6-7 MB, then a user in state 1 opens, edits and re-saves it contracts back to 2 MB? Also, you mention it is on a shared drive, but not whether the file is saved with workbook sharing on, which permits multiple users to edit it simultaneously.

Second, you mention that it is a spreadsheet (not workbook), but that it contains several spreadsheets' data AND CODE. Does this mean that lots of code is located in worksheet event code modules? If so, I would recommend that you relocate any code that is common to all these modules and also any non-event-related code to a standard macro module where it is not associated with a particular worksheet. This should eliminate some code just by virtue of reducing redundancy.

Third, do the users in both states use exactly the same version of Excel?

Finally, you did not mention whether you are using change tracking, a feature that is often used with shared workbooks. This is something that causes files to expand with time, but should not cause the level of expansion you are experiencing.

Damon

 

[Mudface]

Just to add to Damon's final point- I once had a workbook with three worksheets which had a fair amount of worksheet event code and was shared with change history enabled over a 12 month period. The first version ballooned in size from 0.2Mb to over 3.5Mb in one quarter. At this point I removed sharing to delete the change history and it shrank back down to around 1Mb. For the next quarter I removed all the code, replacing it with conditional formatting fomulae and only allowed a 1 month period for change history recording- it finished that quarter at around 1Mb, too.

It probably isn't feasible for you to replace all your code, but consider unsharing the workbook to remove the change history and then resharing it with a shorter time period. Just make sure you tell people before you remove sharing :wink: .