REBUILD your workbook

[erik.van.geit]

Hi, guys and gals,

It is my habit to REBUILD the projects which are finished.

PURPOSE:
decrease filesize
increase speed
avoid some strange behaviour of all kind

WHAT are we talking about ?
During the development of a workbook+project you are creating, deleting, moving all kinds of items: formats, cells, controls, shapes, modules, variables, .... Some garbage is staying in "the background" of your file. It's like Excel remembers things which are useless for you: f.i. variables which doesn't exist anymore. It's like there are knots in the formulareferences.

QUESTION
One of my weak points is finding my way on websites. Perhaps there does already exist some tips on rebuilding workbooks. Can you provide links? Also your comments are welcome: if I'm wrong (exaggerations or concessions) feel free to share your experiences.

HOW TO REBUILD WORKBOOK
1. create NEW workbook
during the process:
a. save often & use timestamped filenames
b. avoid deleting & cutting cells, shapes, controls, ... use Ctrl-Z
c. prefer to go back a version and rebuild instead of delete or cut

2. sheets
rebuild from scratch
copy only range which is really used
a. drastic: old to new sheet: only pastevalues allowed, enter formats etcetera manually (or using some code) (copy WITHIN new sheet allowed of course !)
b. quicker: paste used range

3. forms
rebuild from scratch
you can copy controls, Ctrl-Select is OK, but don't use shift-select (to avoid copying "invisible" controls: I've seen often more controls on a form than intended)

4. modules
don't copy modules
a. copy text of all modules to textfiles
b. save and close the textfiles
c. delete modules
d. create new modules and paste text from textfiles
(it can be good to rebuild your sheets from scratch also)

thank you for reading!
I hope this thread will be useful
Erik

 

[DominicB]

Hi Erik

Sound advice indeed to get rid of gremlins, hiccups and speed things up. Just for the record however, there is a semi-automated way to accomplish this.

Open your file and goto File > Save As Web Page, select Entire Workbook and click OK. This Excel function is pretty dire in terms of HTML files created - these files tend to be far bigger than they strictly need to be, but that doesn't matter for now. As you would expect the HTML is saved as a who;e series of files. Once saved, close Excel and reopen and choose File > Open. Open the HTML file and the workbook will appear. Now save it back as a .xls file.

This has the same effect as copying sheets across (used ranges only) and exporting / reimporting all userforms and modules. You would typically expect a 40-50% reduction in file size. Delete the HTML files created (and keep a backup of the original - just in case)

Of course, if you just wanted to clean up code / userforms (because we're all aware of just how much junk accumulates in there) you could always use Rob Bovey's VBA Code Cleaner (v4.4) :

http://www.appspro.com/Utilities/CodeCleaner.htm

Thanks for your interesting post, Erik.

DominicB

 

[erik.van.geit]

Dominic,

Thank you for this contribution.
THe HTML-trick didn't work on one of my projects, which has some "heavy creation history". When opening the htm-file Excel got blocked. Is this technique documented somewhere ? To use with care, I think ....

Thank you for the codecleaner-link. For those who don't like to search a lot: a menu-item "Clean Project" is added at the bottom of the menu TOOLS. :wink:

best regards,
Erik

 

[DominicB]

Hi Erik

THe HTML-trick didn't work on one of my projects, which has some "heavy creation history"

I’ve only experience this myself once on a seriously tangled workbook with hundreds of external links and pretty big which was about 8 Mb in size. I don’t actually think size has anything to do with it – it appears to be workbook complexity that hampers the operation. That aside the biggest workbook I’ve condensed using this method (without problems) was a 10 Mb file done using a 1 Gb Dell Optiplex DX270.

Is this technique documented somewhere ?

Not that I know of, it's more of a side effect than anything else, but it's not my technique - I heard about it a couple of years ago and forgot all about it until I was trying to slim down a large workbook about 18 months ago.

To use with care, I think ....

Absolutely. Any of the suggstions in this thread should be carried out on backed up files (or even better, copies of backed up files...)

DominicB

 

[Marc Enzi]

How to track down the offending sheet?

Is there a way to look at the size of each sheet in the workbook?

I have a workbook that I've slimmed to 7 mb, but somewhere in three sheets there is about 6 mb that should not be more than 1 mb total, and I am trying to track down the excess weight to get this thing through email gateways without having to zip it.

Thanks,

Marc

 

[jmcqup]

Thanks for this thread and helpful advice.

In my rebuilding, I tried (I think successfully)

1. Using a formula to text UDF (formtext) so that I can make a parallel table of the formulas (I use =if(isblank(a1),NA(),formtext(a1)) then remove all the #N/A. The result can be copy/pasted as values and a Find/Replace of = -> = turns the 'values' back to formulas. I had one sheet with 27,000 complex, highly variable formulas!

2. Opening two instances of Excel, I believe it is no longer possible to cut/paste values, only 'excel objects'. I found this helpful to move VBA code, as I could just copy/paste text without the intervening step.

hope these ideas are useful.

-james

 

[erik.van.geit]

What a coincidence: just logging in and finding this thread on top...

1. Can you elaborate a little more? For me it is not clear.
2. Seems like you are dragging codemodules from one to another project: this is NOT the correct way to rebuild. You really need to perform an extra step using a textfile, to get rid of "invisible garbage" within the code.

best regards,
Erik

 

[jmcqup]

Hi Erik.

FormText is a procedure that pulls the formula (see below) from a cell. So with the formula above (done either to the right or on a different sheet), if the cell is blank it returns #N/A, if a value, the value, and if a formula, the text of the formula. After making a grid of all the used cells in the original worksheet, I find all #n/a select and delete them. Then I copy/paste values of the remaining values or formulas to the rebuilt worksheet. A formula would appear on the Rebuild sheet as value (e.g. '= 1 + 2) On the rebuilt worksheet, I find/replace all Find: = Replace: =. This doesn't change anything other than to put the formulas in as formulas rather than as values.

Obviously if the formulas are consistent across the sheet/columns, manually created and copying into the required places is faster and probably better. But with a couple of sheets I am rebuilding, the formulas are not consistent and extremely numerous, so I used the above method.

2. While I hoped that if Excel is in separate instances it cannot bring across the code garbage. This is very clear when trying to copy paste between workbooks running in separate instances (you could only bring the 'excel object' or unitext). But probably it is safer to go to a intermediate text file.

Thanks again

-james

<Code>
Function FormText(CellRef As Range, Optional RefIndicator As Integer) As String
' RefIndicator: 0 do not show, 1 show reference, 2 show absolute reference
Dim n As Integer, f As Integer

If IsNull(RefIndicator) = True Then
RefIndicator = 0
End If

FormText = CellRef.Formula

If RefIndicator > 0 Then
FormText = "[" & CellRef.Address & "] " & FormText
End If

n = 1
f = InStr(1, FormText, "]")

Do While n < f
If RefIndicator = 1 And Mid(FormText, n, 1) = "$" Then
FormText = Trim(Left(FormText, n - 1) & Mid(FormText, n + 1, 200))
End If
If Mid(FormText, n, 1) = "," Then
FormText = Trim(Left(FormText, n) & " " & Mid(FormText, n + 1, 200))
End If
n = n + 1
Loop

End Function
<\Code>

 

[Aleciafxc]

Was trying to fix a catastrophic error in my Database, and tried the save as web page idea and it wiped out all of the Visual Basic Code. Did i miss something. Of course I am trying all the ideas on a backup copy.

My database has a very strange happening. After the error occurred the sheet code is separated from the sheet. For example now i have sheet1 and sheet11(ENP), sheet1 has the code it is supposed to have and sheet11(ENP) has no code but it the actual sheet in use. So when I go to ENP no code works. I could copy the code from the sheet1 to sheet11(ENP), but i would have both sheets with code. I don't know if I am explaining myself well. It is very strange. Have any of you ever seen this happen before. I have tried searching the forum, but don't know how to put it, ie. "sheet splits from code more to follow on Action 12 News at 11, now back to you Sam."

Any ideas would be very appreciated.

Alecia

 

[Colin Legg]

For reference, discussion on Aleciafxc's question is continued here:

http://www.mrexcel.com/forum/showthread.php?t=459206