Bright01
asked on
Improving the speed of execution
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-Input s")
.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-Input s")
.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-Input s")
'ProtectOFF
Sheet7.Visible = xlSheetHidden
Sheet8.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
'Sheet2.Range = ("D1")
'ProtectON
End With
With Worksheets("Customer-Input s")
'Expand_all
'Expand_Summary
'Expand_all
End With
ActiveSheet.Range("D1").Ac tivate
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
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
ProtectOFF
With Worksheets("Customer-Input
.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-Input
.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-Input
'ProtectOFF
Sheet7.Visible = xlSheetHidden
Sheet8.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
'Sheet2.Range = ("D1")
'ProtectON
End With
With Worksheets("Customer-Input
'Expand_all
'Expand_Summary
'Expand_all
End With
ActiveSheet.Range("D1").Ac
End With
Application.ScreenUpdating
Application.EnableEvents = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
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.
ASKER
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.
Thank you again,
B.
That's is the reason behind Steve's suggestion.
ASKER
TY.