Results 1 to 3 of 3

How To Decrease Excel File Size And Increase Speed

This is a discussion on How To Decrease Excel File Size And Increase Speed within the Excel Questions forums, part of the Question Forums category; Hi, I am having an interesting problem with an Excel spreadsheet (I'm running Excel 2002) that contains quite a bit ...

  1. #1
    New Member
    Join Date
    Aug 2006
    Location
    Rochester, NY
    Posts
    22

    Default How To Decrease Excel File Size And Increase Speed

    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:

    Code:
    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
    Code:
    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

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Posts
    179

    Default

    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

  3. #3
    New Member
    Join Date
    Aug 2006
    Location
    Rochester, NY
    Posts
    22

    Default

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com