Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decompile Excel VBA code
Hello all,
I have been working in a large workbook 70+ worksheets. I was writing code to duplicate a form(worksheet) and failed to set some objects to nothing as I was testing the code. As a result the file bloated up to 17 MB from 4 MB. There is a little known capability in MS Access to decompile a database. Create a shortcut and use the decompile switch: "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "D:\database.mdb" /decompile Is there something similar in Excel or does anyone have any suggestion to get the file back to it's original size? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decompile Excel VBA code
You can try this code. It Deletes all of the cells below and to the right of
the last populated cells. It may do the trick... Make a copy first before you try running this code. You need to change "???" to the sheet password to unprotect the sheets (assuming they are protected). Public Sub CompactAllSheets() Dim wks As Worksheet Dim blnIsProtected As Boolean Dim wbk As Workbook Set wbk = ActiveWorkbook For Each wks In Worksheets If wks.ProtectContents = True Then blnIsProtected = True wks.Unprotect "???" Else blnIsProtected = False End If Call CompactSheet(wks) If blnIsProtected = True Then wks.Protect "???" Next wks If MsgBox("For the compact to complete the spreadsheet must be saved. " & _ "Do you want to save now?", vbYesNo + vbInformation, "Save?") = vbYes Then wbk.Save End Sub Public Sub CompactSheet(Optional ByVal wks As Worksheet) Dim rng As Range If wks Is Nothing Then Set wks = ActiveSheet Set rng = LastCell(wks) wks.Range(rng.Offset(0, 1), wks.Cells(1, Columns.Count)).EntireColumn.Delete wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete End Sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Stan" wrote: Hello all, I have been working in a large workbook 70+ worksheets. I was writing code to duplicate a form(worksheet) and failed to set some objects to nothing as I was testing the code. As a result the file bloated up to 17 MB from 4 MB. There is a little known capability in MS Access to decompile a database. Create a shortcut and use the decompile switch: "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "D:\database.mdb" /decompile Is there something similar in Excel or does anyone have any suggestion to get the file back to it's original size? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decompile Excel VBA code
Thanks for the code Jim. I tested it, but it's too risky to run on the
workbook. To many empty cells at the bottom of ranges that may populate at a later time. Thanks again! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decompile Excel VBA code
Believe it or not Tom it was the code. The code cleaner took it back
to the original size. Thanks for the help, Stan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
XLL decompile | Excel Programming |