REBUILD your workbook

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

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

Hi Erik

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

Click to expand...

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 ?

Click to expand...

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 ....

Click to expand...

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

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

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

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

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>

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

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

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