View Full Version : Why Excel commandbutton macro is too slow?
shayanan
02-14-2021, 11:40 AM
I Use a 1200 line module code for 22 commandbutton. when click each one of button, it fulfill 10 cell value. But it is too slow.
is there a way to optimize code?
this is one of 22 button code:
Sub Iron_Condor()
'
' Iron_Condor Macro
'
'
Range("R38").Select
ActiveCell.FormulaR1C1 = "Iron Condor"
ActiveWindow.SmallScroll Down:=14
Range("D47").Select
ActiveCell.FormulaR1C1 = "100"
Range("E48").Select
ActiveCell.FormulaR1C1 = "'-Select-"
Range("D49").Select
ActiveCell.FormulaR1C1 = "'-Select-"
Range("E50").Select
ActiveCell.FormulaR1C1 = "100"
Range("E51").Select
ActiveCell.FormulaR1C1 = "5"
Range("D52").Select
ActiveCell.FormulaR1C1 = "1"
Range("E48").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Range("D49").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Range("E50").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Range("E51").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Range("D52").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FillRight
Range("E48").Select
ActiveCell.FormulaR1C1 = "'Put"
Range("F48").Select
ActiveCell.FormulaR1C1 = "'Put"
Range("G48").Select
ActiveCell.FormulaR1C1 = "'Call"
Range("H48").Select
ActiveCell.FormulaR1C1 = "'Call"
Range("E49").Select
ActiveCell.FormulaR1C1 = "'Long"
Range("F49").Select
ActiveCell.FormulaR1C1 = "'Short"
Range("G49").Select
ActiveCell.FormulaR1C1 = "'Short"
Range("H49").Select
ActiveCell.FormulaR1C1 = "'Long"
Range("E50").Select
ActiveCell.FormulaR1C1 = "90"
Range("F50").Select
ActiveCell.FormulaR1C1 = "98"
Range("G50").Select
ActiveCell.FormulaR1C1 = "102"
Range("H50").Select
ActiveCell.FormulaR1C1 = "110"
Range("E51").Select
ActiveCell.FormulaR1C1 = "2"
Range("F51").Select
ActiveCell.FormulaR1C1 = "4"
Range("G51").Select
ActiveCell.FormulaR1C1 = "4"
Range("H51").Select
ActiveCell.FormulaR1C1 = "2"
Range("D47").Select
ActiveWindow.SmallScroll Down:=-140
End Sub
See: Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)
Paul_Hossler
02-14-2021, 06:16 PM
The macro reorder can be handy, but it doesn't generate effecient code usually and needs manual tweaking to work well
You don't need to .Select most things to use them, the .SmallScroll doesn't add anything
Example of some pieces
Range("R38").Value = "Iron Condor"
Range("D47").Value = "100"
Range("E48").Value "'-Select-"
Range("D49").Value = "'-Select-"
Range("E50").Value = "100"
Range("E51").Value = "5"
Range("D52").Value = "1"
Range("E48").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("D49").Select
Range(Selection, Selection.End(xlToRight)).FillRight
shayanan
02-14-2021, 10:58 PM
Thanks a lot Paul. Its work Mouch Faster Now.
IMPORTANT! First see the two previous posts from snb and Paul
_1 is your code with the Macro Recorder induced Artifacts removed.
_2 is with the FillRight Redundancies removed.
_3 is Advanced VBA, very short and very fast.
All three should produce identical Results, but you need to check my use of H52 in _3
Option Explicit
Sub Iron_Condor_1()
Range("R38") = "Iron Condor"
Range("D47") = "100"
Range("E48") = "'-Select-"
Range("D49") = "'-Select-"
Range("E50") = "100"
Range("E51") = "5"
Range("D52") = "1"
Range(Range("E48"), Range("E48").End(xlToRight)).FillRight
Range(Range("D49"), Range("D49").End(xlToRight)).FillRight
Range(Range("E50"), Range("E50").End(xlToRight)).FillRight
Range(Range("E51"), Range("E51").End(xlToRight)).FillRight
Range(Range("D52"), Range("D52").End(xlToRight)).FillRight
Range("E48") = "'Put"
Range("F48") = "'Put"
Range("G48") = "'Call"
Range("H48") = "'Call"
Range("E49") = "'Long"
Range("F49") = "'Short"
Range("G49") = "'Short"
Range("H49") = "'Long"
Range("E50") = "90"
Range("F50") = "98"
Range("G50") = "102"
Range("H50") = "110"
Range("E51") = "2"
Range("F51") = "4"
Range("G51") = "4"
Range("H51") = "2"
End Sub
Sub Iron_Condor_2()
Range("R38") = "Iron Condor"
Range("D47") = "100"
Range("D49") = "'-Select-"
Range(Range("D52"), Range("D52").End(xlToRight)).FillRight
Range("E48") = "'Put"
Range("F48") = "'Put"
Range("G48") = "'Call"
Range("H48") = "'Call"
Range("E49") = "'Long"
Range("F49") = "'Short"
Range("G49") = "'Short"
Range("H49") = "'Long"
Range("E50") = "90"
Range("F50") = "98"
Range("G50") = "102"
Range("H50") = "110"
Range("E51") = "2"
Range("F51") = "4"
Range("G51") = "4"
Range("H51") = "2"
End Sub
Sub Iron_Condor_3()
Dim Rw48, Rw49, Rw50, Rw51, Rw52 'all As Variants
Rw48 = Array("Put", "Put", "Call", "Call")
Rw49 = Array("Long", "Short", "Long", "Long")
Rw50 = Array(90, 98, 102, 110)
Rw51 = Array(2, 4, 4, 2)
Rw52 = Array(1, 1, 1, 1, 1)
With Sheets("???") '<----------------- Name the operative Worksheet here.
Range("R38") = "Iron Condor"
Range("D47") = 100
Range("D49:D51") = "'-Select-"
Range("D52").Resize(, 5) = Rw52
Range("E48").Resize(, 4) = Rw48
Range("E49").Resize(, 4) = Rw49
Range("E50").Resize(, 4) = Rw50
Range("E51").Resize(, 4) = Rw49
End With
End Sub
shayanan
02-15-2021, 02:03 AM
IMPORTANT! First see the two previous posts from snb and Paul
_1 is your code with the Macro Recorder induced Artifacts removed.
_2 is with the FillRight Redundancies removed.
_3 is Advanced VBA, very short and very fast.
All three should produce identical Results, but you need to check my use of H52 in _3
Option Explicit
Sub Iron_Condor_1()
Range("R38") = "Iron Condor"
Range("D47") = "100"
Range("E48") = "'-Select-"
Range("D49") = "'-Select-"
Range("E50") = "100"
Range("E51") = "5"
Range("D52") = "1"
Range(Range("E48"), Range("E48").End(xlToRight)).FillRight
Range(Range("D49"), Range("D49").End(xlToRight)).FillRight
Range(Range("E50"), Range("E50").End(xlToRight)).FillRight
Range(Range("E51"), Range("E51").End(xlToRight)).FillRight
Range(Range("D52"), Range("D52").End(xlToRight)).FillRight
Range("E48") = "'Put"
Range("F48") = "'Put"
Range("G48") = "'Call"
Range("H48") = "'Call"
Range("E49") = "'Long"
Range("F49") = "'Short"
Range("G49") = "'Short"
Range("H49") = "'Long"
Range("E50") = "90"
Range("F50") = "98"
Range("G50") = "102"
Range("H50") = "110"
Range("E51") = "2"
Range("F51") = "4"
Range("G51") = "4"
Range("H51") = "2"
End Sub
Sub Iron_Condor_2()
Range("R38") = "Iron Condor"
Range("D47") = "100"
Range("D49") = "'-Select-"
Range(Range("D52"), Range("D52").End(xlToRight)).FillRight
Range("E48") = "'Put"
Range("F48") = "'Put"
Range("G48") = "'Call"
Range("H48") = "'Call"
Range("E49") = "'Long"
Range("F49") = "'Short"
Range("G49") = "'Short"
Range("H49") = "'Long"
Range("E50") = "90"
Range("F50") = "98"
Range("G50") = "102"
Range("H50") = "110"
Range("E51") = "2"
Range("F51") = "4"
Range("G51") = "4"
Range("H51") = "2"
End Sub
Sub Iron_Condor_3()
Dim Rw48, Rw49, Rw50, Rw51, Rw52 'all As Variants
Rw48 = Array("Put", "Put", "Call", "Call")
Rw49 = Array("Long", "Short", "Long", "Long")
Rw50 = Array(90, 98, 102, 110)
Rw51 = Array(2, 4, 4, 2)
Rw52 = Array(1, 1, 1, 1, 1)
With Sheets("???") '<----------------- Name the operative Worksheet here.
Range("R38") = "Iron Condor"
Range("D47") = 100
Range("D49:D51") = "'-Select-"
Range("D52").Resize(, 5) = Rw52
Range("E48").Resize(, 4) = Rw48
Range("E49").Resize(, 4) = Rw49
Range("E50").Resize(, 4) = Rw50
Range("E51").Resize(, 4) = Rw49
End With
End Sub
Is there a way to combine code which include line in common?
for example there are so many line in code below is repeated.
Sub Iron_Condor()
'
' Iron_Condor Macro
'
'
Range("R38").Value = "Iron Condor"
ActiveWindow.SmallScroll Down:=14
Range("D50").Value = "100"
Range("E48").Value = "'-select-"
Range("D49").Value = "'-select-"
Range("E50").Value = "100"
Range("E51").Value = "5"
Range("D52").Value = "1"
Range("E48").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("D49").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E50").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E51").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("D52").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E48").Value = "'Put"
Range("F48").Value = "'Put"
Range("G48").Value = "'Call"
Range("H48").Value = "'Call"
Range("E49").Value = "'Long"
Range("F49").Value = "'Short"
Range("G49").Value = "'Short"
Range("H49").Value = "'Long"
Range("E50").Value = "90"
Range("F50").Value = "98"
Range("G50").Value = "102"
Range("H50").Value = "110"
Range("E51").Value = "2"
Range("F51").Value = "4"
Range("G51").Value = "4"
Range("H51").Value = "2"
Range("D47").Select
ActiveWindow.SmallScroll Down:=-140
End Sub
Sub Jade_Lizard()
'
' Jade_Lizard Macro
'
'
Range("R38").Value = "Jade Lizard"
ActiveWindow.SmallScroll Down:=14
Range("D50").Value = "100"
Range("E48").Value = "'-select-"
Range("D49").Value = "'-select-"
Range("E50").Value = "100"
Range("E51").Value = "5"
Range("D52").Value = "1"
Range("E48").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("D49").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E50").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E51").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("D52").Select
Range(Selection, Selection.End(xlToRight)).FillRight
Range("E48").Value = "'Put"
Range("F48").Value = "'Call"
Range("G48").Value = "'Call"
Range("E49").Value = "'Short"
Range("F49").Value = "'Short"
Range("G49").Value = "'Long"
Range("E50").Value = "90"
Range("G50").Value = "104"
Range("E51").Value = "2"
Range("G51").Value = "3"
Range("D47").Select
ActiveWindow.SmallScroll Down:=-98
End Sub
Is there a way to combine code which include line in common?
for example there are so many line in code below is repeated.
Probably. But first compare the time I took to write the three sample codes in my post to the time you took to understand them.
Note that you have recorded 22 macros but I can only see two.
The only commonality I see in those two Macros is the "Labels" section
Sub Iron_Condor_4()
Dim Rw48, Rw49, Rw50, Rw51, Rw52 'all As Variants
Rw48 = Array("Put", "Put", "Call", "Call")
Rw49 = Array("Long", "Short", "Long", "Long")
Rw50 = Array(90, 98, 102, 110)
Rw51 = Array(2, 4, 4, 2)
Rw52 = Array(1, 1, 1, 1, 1)
With ActiveSheet
AddCommonLabels
Range("R38") = "Iron Condor"
Range("D52").Resize(, 5) = Rw52
Range("E48").Resize(, 4) = Rw48
Range("E49").Resize(, 4) = Rw49
Range("E50").Resize(, 4) = Rw50
Range("E51").Resize(, 4) = Rw49
End With
End Sub
Private Sub AddCommonLabels()
Dim D49D51
D49D51 = Array("-Select-", "-Select-", "-Select-")
With ActiveSheet
Range("D47") = 100
Range("D49").Resize(3, 1) = WorksheetFunction.Transpose(D49D51)
End With
End Sub
I notice that IronCondor uses 4@ 4column Arrays, but JadeLizard uses 2@ 3Column and 2@ 2Column Arrays. It is impossible for anyone only seeing the code to know how wide the Rw52 Array is.It will be up to you to to analyze the 22 macros to determine which commonalities you want to use.
Also note that, except for Range D52, all your "FillToRight"s are completely overwritten by new values later in your Macros
Daxton A.
02-16-2021, 01:53 PM
I like to speed up my macros using:
Sub whatWeHaveHereIsFailureToCommunicate()
dim i as integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Code
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I like to speed up my macros using:
Sub whatWeHaveHereIsFailureToCommunicate()
dim i as integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Code
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
If this speeds up your code it indicates that the interaction (reading/writing) with the workbook is too frequent.
You should revise the VBA-code.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.