How To Decrease Excel File Size And Increase Speed

[mtp9302]

Hi,

I am having an interesting problem with an Excel spreadsheet (I'm running Excel 2002) that contains quite a bit of Visual Basic Code. My file size, until recently, was at 2.73 MB. I added several subroutines which were responsible for doing the following:

- Copies an existing workbook sheet.

- Edits the text on several buttons and text boxes.

- Inserts a formula (using the Range().Formula command) into six different cells and drags the formula down through the next 24 cells.

This can be done twice on each of two different worksheets such that the existing sheet is copied and modifed to make two derivative sheets (four derivative sheets total). The creation of each of the two derivative sheets is initiated by a button. A subroutine of each of the two sheet derivatives is included below:

Sub CreatePanel2()
'
' CreatePanel2 Macro
    If Sheets(4).Name = "Panel (2)" Then                 'Checks To See If Panel (2) Already Exists
        MsgBox "'Panel (2)' already exists. To create a third panels sheet, use the button on 'Panel (2)'", vbInformation
        Exit Sub
    End If
    
    Sheets("Panel").Select
    Sheets("Panel").Copy Before:=Sheets(4)
    Sheets("Prices").Select
    Range("F5").Formula = "='Panel (2)'!AP8"
    Range("H5").Formula = "='Panel (2)'!AQ8"
    Sheets("Panel (2)").Select
    
    ActiveSheet.Shapes("Button 7").Select                'Configure "Create Panel (3)" Button
    Selection.OnAction = "CreatePanel3"
    Selection.Characters.Text = "Create Panel (3)"
   
    ActiveSheet.Shapes("Text Box 23").Select             'Text Box For "Create Panel (3)" Button
    Selection.Characters.Text = _
        "Use this button to create an additional panels sheet. " & Chr(10) & "The new sheet corresponds to the part number Paneltotal3."
    With Selection.Characters(Start:=100, Length:=12).Font
        .FontStyle = "Bold"
    End With
    
    ActiveSheet.Shapes("Button 6").Select                   'Configure "Create RFQ2" Button
    Selection.OnAction = "CreateRFQ2"
    Selection.Characters.Text = "Create RFQ (2)"
    
    ActiveSheet.Shapes("Text Box 24").Select                'Text Box For "Create RFQ2" Button
    Selection.Characters.Text = _
        "Use this button to create a request for quote sheet to fax to an outside panel material vendor. The sheet RFQ (2) will correlate to the above items."
    With Selection.Characters(Start:=107, Length:=7).Font
        .FontStyle = "Bold"
    End With
    
    ActiveSheet.Shapes("Text Box 25").Select                        'Notice Text Box
    Selection.Characters.Text = _
        "If you want to create a third panel sheet, be sure to create it before entering any items above. "
    Selection.ShapeRange.ScaleHeight 0.66, msoFalse, msoScaleFromTopLeft
    
    ActiveWindow.SmallScroll Down:=-39
    Range("B19").Select
    
End Sub

Sub CreateRFQ2()

' CreateRFQ2 Macro

    Dim ws As Worksheet

    For Each ws In Worksheets                         'Check to see if RFQ (2) exists.
        If ws.Name = "RFQ (2)" Then
            MsgBox "RFQ (2) has already been created.", vbInformation
            Exit Sub
        End If
    Next
    
    If Sheets("RFQ").Visible = False Then
        Sheets("RFQ").Visible = True
    End If
    
    Sheets("RFQ").Select
    Sheets("RFQ").Copy Before:=Sheets("RFQ")          'Copy sheet "RFQ'
    
    With ActiveSheet
        .Move After:=Worksheets(Worksheets.Count)
    End With

    Range("C7:H7").Formula = "=RFQmaterial('Panel (2)'!$AV$17,'Panel (2)'!$AS$27)"      'Insert proper
    Range("A12:A36").Formula = "= IF('Panel (2)'!B16>0,'Panel (2)'!A16,"""")"           'formulas into
    Range("B12:B36").Formula = "='Panel (2)'!G16"                                       'sheet.
    Range("C12:C36").Formula = "='Panel (2)'!D16"
    Range("D12:D36").Formula = "='Panel (2)'!E16"
    Range("E12:E36").Formula = "='Panel (2)'!F16"
    Range("F12:F36").Formula = "=IF('Panel (2)'!D16>0,FLOOR(C12/(25.4),1/16),"""")"
    Range("H12:H36").Formula = "=IF('Panel (2)'!F16>0,FLOOR(E12/(25.4),1/16),"""")"
    Range("I12:I36").Formula = "='Panel (2)'!L16"
    
    Range("A12").Select
    
End Sub

Everything works great. My problem is that my file size has increased from 2.74 MB to 8.00 MB. We cannot send out email attachments bigger than 6 MB, so I have to find a way to decrease this. I have VBA Code Cleaner 4.4 downloaded, but this did not help. I have heard of strange increases in file size associated with modifying VB code before, but I could always run the code cleaner and reduce the file size to near (not exactly) original. I also have the VB subroutine 'ExcelDiet', which strips the spreadsheet of unused cells to decrease file size. This, also, did not change the file size. I have tried to research this problem online (that's how I found VBA Code Cleaner and ExcelDiet), but I have not had any significant improvements.

In addition, my spreadsheet is also running extremely slow (literally takes 3-5 seconds to calculate cells after I input data and press Enter).

I am wondering if anyone has ever run into this kind of thing or has knowledge of the behavior of Visual Basic which would help me reduce my file size and increase my speed. I was under the impression that the four added subroutines of the two above varieties should not increase the file size by over 5 MB. Have I coded this inefficiently? Is there any advantage to coding formulas in Excel's spreadsheet vs Visual Basic or vice versa? Any ideas as far as good practices to achieve both of these ends (speed and file size) or common fixes would be very helpful. Any information you can give would be much appreciated. Thanks.

-Matt

 

[Emanresux]

Hi i don't know about the increase in file size but i notice you haven't stopped the screen updating in your subs. i know there're not vary long pieces of code but that might help speed them up.


sub A()

application.ScreenUpdating = False

'' your code here

Application.screenUpdating = True

End Sub

 

[mtp9302]

That's a good idea, thanks for your suggestion Emanresux.