Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Improving the speed of execution

Posted on 2014-02-06
8
Medium Priority
?
209 Views
Last Modified: 2014-02-07
EE Pros,

I have a WS that has a macro that resets all fields.  This macro, when fired, takes about 10 sec. to execute.  Given I don't write very efficient code, I was wondering if there was anything I could do to have this code execute cleaner/faster? Perhaps I have too many "With" statements..... not sure if there are some "tweaks" I can make here but would like to have someone take a quick look.

Much thanks in advance for the advise!

B.

Sub ClearFields()
Dim I As Integer
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ProtectOFF
    With Worksheets("Customer-Inputs")
.Range("A6") = "Y"
.Range("A13") = "Y"
.Range("A20") = "Y"
.Range("A29") = "Y"
.Range("A38") = "Y"
.Range("A45") = "Y"
.Range("A54") = "Y"
.Range("A63") = "Y"
.Range("A70") = "Y"
.Range("A78") = "Y"
.Range("A87") = "Y"
.Range("A96") = "Y"
.Range("C9") = ""
.Range("C11") = ""
.Range("C16") = ""
.Range("C18") = ""
.Range("C23") = ""
.Range("C25") = ""
.Range("C27") = ""
.Range("C32") = ""
.Range("C34") = ""
.Range("C36") = ""
.Range("C41") = ""
.Range("C43") = ""
.Range("C48") = ""
.Range("C50") = ""
.Range("C52") = ""
.Range("C57") = ""
.Range("C59") = ""
.Range("C61") = ""
.Range("C66") = ""
.Range("C68") = ""
.Range("C73") = ""
.Range("C75") = ""
.Range("C81") = ""
.Range("C83") = ""
.Range("C85") = ""
.Range("C90") = ""
.Range("C92") = ""
.Range("C94") = ""
.Range("C99") = ""
.Range("C101") = ""
.Range("C103") = ""
.Range("C108") = ""
.Range("C109") = ""
.Range("C110") = ""
'.Range("D1") = "Name of Company"
.Range("D9") = "Company Specific Input and Comments from Client"
.Range("D11") = "Company Specific Input and Comments from Client"
.Range("D16") = "Company Specific Input and Comments from Client"
.Range("D18") = "Company Specific Input and Comments from Client"
.Range("D23") = "Company Specific Input and Comments from Client"
.Range("D25") = "Company Specific Input and Comments from Client"
.Range("D27") = "Company Specific Input and Comments from Client"
.Range("D32") = "Company Specific Input and Comments from Client"
.Range("D34") = "Company Specific Input and Comments from Client"
.Range("D36") = "Company Specific Input and Comments from Client"
.Range("D41") = "Company Specific Input and Comments from Client"
.Range("D43") = "Company Specific Input and Comments from Client"
.Range("D48") = "Company Specific Input and Comments from Client"
.Range("D50") = "Company Specific Input and Comments from Client"
.Range("D52") = "Company Specific Input and Comments from Client"
.Range("D57") = "Company Specific Input and Comments from Client"
.Range("D59") = "Company Specific Input and Comments from Client"
.Range("D61") = "Company Specific Input and Comments from Client"
.Range("D66") = "Company Specific Input and Comments from Client"
.Range("D68") = "Company Specific Input and Comments from Client"
.Range("D73") = "Company Specific Input and Comments from Client"
.Range("D75") = "Company Specific Input and Comments from Client"
.Range("D81") = "Company Specific Input and Comments from Client"
.Range("D83") = "Company Specific Input and Comments from Client"
.Range("D85") = "Company Specific Input and Comments from Client"
.Range("D90") = "Company Specific Input and Comments from Client"
.Range("D92") = "Company Specific Input and Comments from Client"
.Range("D94") = "Company Specific Input and Comments from Client"
.Range("D99") = "Company Specific Input and Comments from Client"
.Range("D101") = "Company Specific Input and Comments from Client"
.Range("D103") = "Company Specific Input and Comments from Client"
.Range("D108") = "Company Specific Input and Comments from Client"
.Range("D109") = "Company Specific Input and Comments from Client"
.Range("D110") = "Company Specific Input and Comments from Client"
With Worksheets("Demos")
.Range("D3") = "Reset Model"
    End With
With Worksheets("Customer-Inputs")
.Range("F2") = "US Dollars"
Range("D1").Activate
    End With
With Worksheets("Benefit Range")
.Range("M15") = 2
    End With
With Worksheets("Cashflow")
.Range("b5") = 0.05
.Range("b6") = 0
.Range("b7") = 1
End With
With Worksheets("Price Quote")
.Range("E20:E31") = 0
.Range("E44:E56") = 0
.Range("H20:H31") = 0
.Range("I59") = 0
.Range("G4") = "=today()"
.Range("C8") = "Customer Address"

    End With
With Worksheets("Assumptions - Benefits & Costs")
'ProtectOFF
.Range("C7:C12").Value = .Range("E7:E12").Value
.Range("C15:C20").Value = .Range("E15:E20").Value
.Range("C23:C28").Value = .Range("E23:E28").Value
.Range("C31:C36").Value = .Range("E31:E36").Value
.Range("C39:C44").Value = .Range("E39:E44").Value
.Range("C47:C52").Value = .Range("E47:E52").Value
.Range("C55:C60").Value = .Range("E55:E60").Value
.Range("C63:C68").Value = .Range("E63:E68").Value
.Range("C71:C76").Value = .Range("E71:E76").Value
.Range("C79:C84").Value = .Range("E79:E84").Value
.Range("C87:C92").Value = .Range("E87:E92").Value
.Range("C95:C100").Value = .Range("E95:E100").Value
.Range("C105:C107").Value = .Range("E105:E107").Value
.Range("C110:C113").Value = .Range("E110:E113").Value
.Range("C116:C119").Value = .Range("E116:E119").Value
.Range("C122").Value = .Range("E122").Value
.Range("C127").Value = .Range("E127").Value
'ProtectON




'   To be used when copying formulas instead of values
'    Range("C7").PasteSpecial Paste:=xlPasteValues
'    Application.CutCopyMode = False
    End With
   
With Worksheets("Customer-Inputs")
'ProtectOFF
Sheet7.Visible = xlSheetHidden
Sheet8.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden

'Sheet2.Range = ("D1")

'ProtectON
    End With
With Worksheets("Customer-Inputs")
'Expand_all
'Expand_Summary
'Expand_all
    End With
ActiveSheet.Range("D1").Activate
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
0
Comment
Question by:Bright01
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Accepted Solution

by:
MacroShadow earned 1000 total points
ID: 39840601
There are several enhancements you could make, this is the one that will make the biggest difference.
Instead of:
.Range("A6") = "Y"
.Range("A13") = "Y"
.Range("A20") = "Y"
.Range("A29") = "Y"
.Range("A38") = "Y"
.Range("A45") = "Y"
.Range("A54") = "Y"
.Range("A63") = "Y"
.Range("A70") = "Y"
.Range("A78") = "Y"
.Range("A87") = "Y"
.Range("A96") = "Y"
.Range("C9") = ""
.Range("C11") = ""
.Range("C16") = ""
.Range("C18") = ""
.Range("C23") = ""
.Range("C25") = ""
.Range("C27") = ""
.Range("C32") = ""
.Range("C34") = ""
.Range("C36") = ""
.Range("C41") = ""
.Range("C43") = ""
.Range("C48") = ""
.Range("C50") = ""
.Range("C52") = ""
.Range("C57") = ""
.Range("C59") = ""
.Range("C61") = ""
.Range("C66") = ""
.Range("C68") = ""
.Range("C73") = ""
.Range("C75") = ""
.Range("C81") = ""
.Range("C83") = ""
.Range("C85") = ""
.Range("C90") = ""
.Range("C92") = ""
.Range("C94") = ""
.Range("C99") = ""
.Range("C101") = ""
.Range("C103") = ""
.Range("C108") = ""
.Range("C109") = ""
.Range("C110") = ""
'.Range("D1") = "Name of Company"
.Range("D9") = "Company Specific Input and Comments from Client"
.Range("D11") = "Company Specific Input and Comments from Client"
.Range("D16") = "Company Specific Input and Comments from Client"
.Range("D18") = "Company Specific Input and Comments from Client"
.Range("D23") = "Company Specific Input and Comments from Client"
.Range("D25") = "Company Specific Input and Comments from Client"
.Range("D27") = "Company Specific Input and Comments from Client"
.Range("D32") = "Company Specific Input and Comments from Client"
.Range("D34") = "Company Specific Input and Comments from Client"
.Range("D36") = "Company Specific Input and Comments from Client"
.Range("D41") = "Company Specific Input and Comments from Client"
.Range("D43") = "Company Specific Input and Comments from Client"
.Range("D48") = "Company Specific Input and Comments from Client"
.Range("D50") = "Company Specific Input and Comments from Client"
.Range("D52") = "Company Specific Input and Comments from Client"
.Range("D57") = "Company Specific Input and Comments from Client"
.Range("D59") = "Company Specific Input and Comments from Client"
.Range("D61") = "Company Specific Input and Comments from Client"
.Range("D66") = "Company Specific Input and Comments from Client"
.Range("D68") = "Company Specific Input and Comments from Client"
.Range("D73") = "Company Specific Input and Comments from Client"
.Range("D75") = "Company Specific Input and Comments from Client"
.Range("D81") = "Company Specific Input and Comments from Client"
.Range("D83") = "Company Specific Input and Comments from Client"
.Range("D85") = "Company Specific Input and Comments from Client"
.Range("D90") = "Company Specific Input and Comments from Client"
.Range("D92") = "Company Specific Input and Comments from Client"
.Range("D94") = "Company Specific Input and Comments from Client"
.Range("D99") = "Company Specific Input and Comments from Client"
.Range("D101") = "Company Specific Input and Comments from Client"
.Range("D103") = "Company Specific Input and Comments from Client"
.Range("D108") = "Company Specific Input and Comments from Client"
.Range("D109") = "Company Specific Input and Comments from Client"
.Range("D110") = "Company Specific Input and Comments from Client"

Open in new window

Use:
    Application.Union([A6], [A13], [A20], [A38], [A45], [A54], [A63], [A70], [A78], [A87], [A96]).Value = "Y"
    Application.Union([C9], [C11], [C16], [C18], [C23], [C25], [C27], [C32], [C34], [C36], _
                      [C41], [C43], [C48], [C50], [C52], [C57], [C59], [C61], [C66], [C68], _
                      [C73], [C75], [C81], [C83], [C85], [C90], [C92], [C94], [C99], [C101]).ClearContents
    Application.Union([C103], [C108], [C109], [C110]).ClearContents
    Application.Union([D9], [D11], [D16], [D18], [D23], [D25], [D27], [D32], [D34], [D36], _
                      [D41], [D43], [D48], [D50], [D52], [D57], [D59], [D61], [D66], [D68], _
                      [D73], [D75], [D81], [D83], [D85], [D90], [D92], [D94], [D99], [D101]).Value = "Company Specific Input and Comments from Client"
    Application.Union([D103], [D108], [D109], [D110]).Value = "Company Specific Input and Comments from Client"

Open in new window

0
 

Author Comment

by:Bright01
ID: 39840857
Macroshadow,  It works but still doesn't give much speed in execution.  Other changes that may make a difference?

TY.
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 1000 total points
ID: 39841307
You could turn off calculation for when you don't need it.

Application.Calculation = xlCalculationManual 'turn off calculation

Application.Calculate 'use to force calculation

Application.Calculation =xlCalculationAutomatic 'turn calculation back on

Open in new window


This can sometimes speed up macro with many changes.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 39841363
Steve,

So..... if I use the Application Calculation "off" and "on" at the beginning and end, although, I don't think I have a single "calculation" in the Macro (all of it is replacing and resetting fields), I can get more speed?

B.
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39841479
It doesn't matter if your macro has calculation or not, the Application.Calculation setting controls whether formulas or violate functions should be updated.

Overall the slow speed is do to the fact that you are writing to several different sheets and for that there is no faster way.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39841558
If you put calculation to manual at the start and automatic at the end, it may speed up the macro. It may be worth a shot and is only a small change to code.
0
 

Author Closing Comment

by:Bright01
ID: 39841681
Thanks guys!  I do have one little question.  While this didn't increase the speed by much, I do think the code is more efficient with your recommendations.  However, as I build the program, because the macros I have are auto updating when I input the additions, it's taking a while to build the WS.  Can I turn Macros off, build my app. then turn them back on without a problem?  That way I can at least put the additions in quicker.

Thank you again,

B.
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39841721
That's is the reason behind Steve's suggestion.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question