VBA 코드 최적화 및 성능 향상
Dec 14 2020
5 SAP AAO 쿼리를 새로 고치는 데 사용되는 VBA 매크로를 개발 한 다음 중복을 제거하고 파워 쿼리에로드하여 이러한 쿼리 데이터를 테이블에 안전하게 복사합니다. 거기에 계산 된 열을 추가 한 다음 해당 데이터를 파워 피벗에로드합니다.
또한 아래에 언급 된 VBA 코드를 사용하여 각 시트에서 몇 가지 작업을 수행했지만 현재 코드를 실행하는 데 4 분 이상이 걸립니다.
4 분 동안 AAO 쿼리를 새로 고치는 데 최소 45 초가 걸리고 매크로를 실행하는 데 남은 시간 (파워 쿼리 새로 고침 포함)이 소요됩니다.
나는 여전히 VBA를 배우고 있으므로 매크로 레코더 코드와 코드 select / activate시트를 사용했습니다. 이보다 더 나은 옵션을 모르기 때문에 올바른 방법이 아니라는 것을 알고 있습니다. 이러한 이유로 매크로를 실행하는 데 더 많은 시간이 걸립니다.
select코드를 사용 하여 시트를 선택 하지 않으면 오류 메시지가 표시되고 코드가 실행되지 않습니다.
누군가가이 코드를 최적화하여 매크로 작업을 수행하는 데 걸리는 시간을 줄일 수 있다면 좋을 것입니다.
Sub Refresh_AAO_Query()
Dim MyArray, i
Dim Sht, Sht1, Sht2, Sht3, Sht4, Sht5 As Worksheet
Dim OPs_Model_Data, OPs_Data, Sales_Data, Threats_Data, RE_Turnover, RE_LY As Worksheet
Dim Message As String
Dim LastRow1, LastRow2, LastRow3, LastRow4, LastRow5, LastRow6, LoB_LastRow, LoB_LastRow1, LoB_LastRow2, LoB_LastRow3, LastColumn1, LastColumn2, LastColumn3, LastColumn4, LastColumn5, LastColumn6, LastColumn7 As Long
Dim LoB, LoB1, LoB2, LoB3 As ListObject
Dim StartCell0, StartCell1, StartCell2, StartCell3, StartCell4, StartCell5, StartCell6, StartCell7, StartCell8, StartCell9 As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheet2.Visible = xlSheetVisible
Sheet4.Visible = xlSheetVisible
Sheet5.Visible = xlSheetVisible
Sheet9.Visible = xlSheetVisible
Sheet10.Visible = xlSheetVisible
Sheet11.Visible = xlSheetVisible
Sheet12.Visible = xlSheetVisible
Sheet16.Visible = xlSheetVisible
Enable_Analysis_For_Office
Refresh_AAO_Queries
' Fixing "Andhra Pradesh" state issue
MyArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")
For Each i In MyArray
If Sheets(i).Name <> Sheets("Sales_Data").Name Then
Sheets(i).Activate
Sheets(i).Range("A2:K2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$K$50000").AutoFilter Field:=4, Criteria1:= _
"=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.AutoFilter
Else
Sheets(i).Activate
Range("A2:Q2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$Q$50000").AutoFilter Field:=4, Criteria1:= _
"=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.AutoFilter
End If
Next i
' Finding Last Row and Column for("OPs_Model_Data") worksheet
Set Sht = Sheet12
Set Sht1 = Sheet4
Set StartCell0 = Sheet12.Range("A3")
Set StartCell1 = Sheet4.Range("A2")
Sheet4.Select
LastRow1 = Sht1.Cells(Sht1.Rows.Count, StartCell1.Column).End(xlUp).Row
LastColumn1 = Sht1.Cells(StartCell1.Row, Sht1.Columns.Count).End(xlToLeft).Column
Sht1.Range(StartCell1, Sht1.Cells(LastRow1, LastColumn1)).Select
Selection.EntireRow.Delete
Sheet12.Select
LastRow2 = Sht.Cells(Sht.Rows.Count, StartCell0.Column).End(xlUp).Row
LastColumn2 = Sht.Cells(StartCell0.Row, Sht.Columns.Count).End(xlToLeft).Column
Sht.Range(StartCell0, Sht.Cells(LastRow2, LastColumn2)).Select
Selection.Copy
Worksheets("OPs_Model_Data").Range("A2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheet12.Range("A1").Select
' Refreshing the "Table2" Query
ActiveWorkbook.Connections("Query - Table2").Refresh
' Finding Last Row and Column and updating the data for("RE_LY") worksheet
Sheet11.Select
Set Sht3 = Sheet11
Set LoB1 = Sht3.ListObjects("RE_LY_Table")
Set StartCell4 = Sheet11.Range("A4")
Sht3.ListObjects("RE_LY_Table").DataBodyRange.Delete
LastRow4 = Sht3.Cells(Sht3.Rows.Count, StartCell4.Column).End(xlUp).Row
LastColumn4 = Sht3.Cells(StartCell4.Row, Sht3.Columns.Count).End(xlToLeft).Column
Sht3.Range("A1:K1").Copy
Sht3.Range("A3:K3").PasteSpecial Paste:=xlPasteValues
Sht3.Range("A3:J" & LastRow4).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("RE_LY_Table[Business Function]"), Unique:=True
Set StartCell5 = Sheet11.Range("AA4")
LoB1_LastRow = Sht3.Cells(Sht3.Rows.Count, StartCell5.Column).End(xlUp).Row - 1
LoB1.Resize LoB1.Range.Resize(LoB1_LastRow)
Sht3.Range("AA2:AK2").Copy
Sht3.Range("AA3:AK3").PasteSpecial Paste:=xlPasteValues
Range("AK4").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],RC[-10],C[-35],RC[-9],C[-34],RC[-8],C[-33],RC[-7],C[-32],RC[-6],C[-31],RC[-5],C[-30],RC[-4],C[-29],RC[-3],C[-28],RC[-2],C[-27],RC[-1])"
Range("AK4").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Sht3.Range("AK4").PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "0.0"
Sheet11.Range("A1").Select
' Finding Last Row and Column and updating the data for("RE_Turnover") worksheet
Sheet2.Select
Set Sht4 = Sheet2
Set LoB2 = Sht4.ListObjects("RE_Turnover_Table")
Set StartCell6 = Sheet2.Range("A4")
Sht4.ListObjects("RE_Turnover_Table").DataBodyRange.Delete
LastRow5 = Sht4.Cells(Sht4.Rows.Count, StartCell6.Column).End(xlUp).Row
LastColumn5 = Sht4.Cells(StartCell6.Row, Sht4.Columns.Count).End(xlToLeft).Column + 1
Sht4.Range("A1:K1").Copy
Sht4.Range("A3:K3").PasteSpecial Paste:=xlPasteValues
Sht4.Range("A3:J" & LastRow5).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("RE_Turnover_Table[Business Function]"), Unique:=True
Set StartCell7 = Sheet2.Range("AA4")
LoB2_LastRow = Sht4.Cells(Sht4.Rows.Count, StartCell7.Column).End(xlUp).Row - 1
LoB2.Resize LoB2.Range.Resize(LoB2_LastRow)
Sht4.Range("AA2:AK2").Copy
Sht4.Range("AA3:AK3").PasteSpecial Paste:=xlPasteValues
Range("AK4").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],RC[-10],C[-35],RC[-9],C[-34],RC[-8],C[-33],RC[-7],C[-32],RC[-6],C[-31],RC[-5],C[-30],RC[-4],C[-29],RC[-3],C[-28],RC[-2],C[-27],RC[-1])"
Range("AK4").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Sht4.Range("AK4").PasteSpecial Paste:=xlPasteValues
Selection.NumberFormat = "0.0"
Sheet2.Range("A1").Select
' Finding Last Row and Column and updating the data for("Sales_Data") worksheet
Sheet5.Select
Set Sht2 = Sheet5
Set LoB = Sht2.ListObjects("Sales_OPs_Data_Table")
Set StartCell2 = Sheet5.Range("A3")
Sht2.ListObjects("Sales_OPs_Data_Table").DataBodyRange.Delete
LastRow3 = Sht2.Cells(Sht2.Rows.Count, StartCell2.Column).End(xlUp).Row
LastColumn3 = Sht2.Cells(StartCell2.Row, Sht2.Columns.Count).End(xlToLeft).Column + 2
Sht2.Range("A1:Q1").Copy
Sht2.Range("A2:Q2").PasteSpecial Paste:=xlPasteValues
Sht2.Range("A2:J" & LastRow3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sales_OPs_Data_Table[Business Function]"), Unique:=True
Set StartCell3 = Sheet5.Range("AA3")
LoB_LastRow = Sht2.Cells(Sht2.Rows.Count, StartCell3.Column).End(xlUp).Row
LoB.Resize LoB.Range.Resize(LoB_LastRow)
Sht2.Range("AA1:BA1").Copy
Sht2.Range("AA2:BA2").PasteSpecial Paste:=xlPasteValues
Range("AK3").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],[@[Business Function]],C[-35],[@[Industry Group]],C[-34],[@[Industry SubGroup]],C[-33],[@[Customer Group]],C[-32],[@[Resp. SubRegion 1]],C[-31],[@[Sales SubRegion 1]],C[-30],[@[Sales SubRegion 2]],C[-29],[@[Country (End Use)]],C[-28],[@[Sold-to party]],C[-27],[@AccM])"
Range("AL3").FormulaR1C1 = "=SUMIFS(C[-26],C[-37],[@[Business Function]],C[-36],[@[Industry Group]],C[-35],[@[Industry SubGroup]],C[-34],[@[Customer Group]],C[-33],[@[Resp. SubRegion 1]],C[-32],[@[Sales SubRegion 1]],C[-31],[@[Sales SubRegion 2]],C[-30],[@[Country (End Use)]],C[-29],[@[Sold-to party]],C[-28],[@AccM])"
Range("AM3").FormulaR1C1 = "=SUMIFS(C[-26],C[-38],[@[Business Function]],C[-37],[@[Industry Group]],C[-36],[@[Industry SubGroup]],C[-35],[@[Customer Group]],C[-34],[@[Resp. SubRegion 1]],C[-33],[@[Sales SubRegion 1]],C[-32],[@[Sales SubRegion 2]],C[-31],[@[Country (End Use)]],C[-30],[@[Sold-to party]],C[-29],[@AccM])"
Range("AN3").FormulaR1C1 = "=SUMIFS(C[-26],C[-39],[@[Business Function]],C[-38],[@[Industry Group]],C[-37],[@[Industry SubGroup]],C[-36],[@[Customer Group]],C[-35],[@[Resp. SubRegion 1]],C[-34],[@[Sales SubRegion 1]],C[-33],[@[Sales SubRegion 2]],C[-32],[@[Country (End Use)]],C[-31],[@[Sold-to party]],C[-30],[@AccM])"
Range("AO3").FormulaR1C1 = "=SUMIFS(C[-26],C[-40],[@[Business Function]],C[-39],[@[Industry Group]],C[-38],[@[Industry SubGroup]],C[-37],[@[Customer Group]],C[-36],[@[Resp. SubRegion 1]],C[-35],[@[Sales SubRegion 1]],C[-34],[@[Sales SubRegion 2]],C[-33],[@[Country (End Use)]],C[-32],[@[Sold-to party]],C[-31],[@AccM])"
Range("AP3").FormulaR1C1 = "=SUMIFS(C[-26],C[-41],[@[Business Function]],C[-40],[@[Industry Group]],C[-39],[@[Industry SubGroup]],C[-38],[@[Customer Group]],C[-37],[@[Resp. SubRegion 1]],C[-36],[@[Sales SubRegion 1]],C[-35],[@[Sales SubRegion 2]],C[-34],[@[Country (End Use)]],C[-33],[@[Sold-to party]],C[-32],[@AccM])"
Range("AQ3").FormulaR1C1 = "=SUMIFS(RE_Turnover!C[-32],RE_Turnover!C[-42],Sales_Data!RC[-16],RE_Turnover!C[-41],Sales_Data!RC[-15],RE_Turnover!C[-40],Sales_Data!RC[-14],RE_Turnover!C[-39],Sales_Data!RC[-13],RE_Turnover!C[-38],Sales_Data!RC[-12],RE_Turnover!C[-37],Sales_Data!RC[-11],RE_Turnover!C[-36],Sales_Data!RC[-10],RE_Turnover!C[-35],Sales_Data!RC[-9],RE_Turnover!C[-34],Sales_Data!RC[-8],RE_Turnover!C[-33],Sales_Data!RC[-7])"
Range("AR3").FormulaR1C1 = "=SUMIFS(RE_LY!C[-7],RE_LY!C[-17],Sales_Data!RC[-17],RE_LY!C[-16],Sales_Data!RC[-16],RE_LY!C[-15],Sales_Data!RC[-15],RE_LY!C[-14],Sales_Data!RC[-14],RE_LY!C[-13],Sales_Data!RC[-13],RE_LY!C[-12],Sales_Data!RC[-12],RE_LY!C[-11],Sales_Data!RC[-11],RE_LY!C[-10],Sales_Data!RC[-10],RE_LY!C[-9],Sales_Data!RC[-9],RE_LY!C[-8],Sales_Data!RC[-8])"
Range("AS3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11],Ops_Data_Backend_Calc!C[-36],Sales_Data!RC[-10],Ops_Data_Backend_Calc!C[-35],Sales_Data!RC[-9])"
Range("AT3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11],Ops_Data_Backend_Calc!C[-36],Sales_Data!RC[-10])"
Range("AU3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11])"
Range("AV3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12])"
Range("AW3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13])"
Range("AX3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14])"
Range("AY3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15])"
Range("AZ3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-51],Sales_Data!RC[-25],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16])"
Range("BA3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-52],Sales_Data!RC[-26],Ops_Data_Backend_Calc!C[-51],Sales_Data!RC[-25],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17])"
Range("AK3:BA3").Select
Selection.Copy
Range("AK3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Sht2.Range("AK3").PasteSpecial Paste:=xlPasteValues
LoB_LastRow = Sht2.Cells(Sht2.Rows.Count, StartCell3.Column).End(xlUp).Row
Range("AK3:BA" & LoB_LastRow).Select
Selection.NumberFormat = "0.0"
Sheet5.Range("A1").Select
' Finding Last Row and Column and updating the data for("Threats_Data") worksheet
Sheet9.Select
Set Sht4 = Sheet9
Set LoB3 = Sht4.ListObjects("Threats_Data_Table")
Set StartCell8 = Sheet9.Range("A3")
Sht4.ListObjects("Threats_Data_Table").DataBodyRange.Delete
LastRow6 = Sht4.Cells(Sht4.Rows.Count, StartCell8.Column).End(xlUp).Row
LastColumn6 = Sht4.Cells(StartCell8.Row, Sht4.Columns.Count).End(xlToLeft).Column
Sht4.Range("A1:Y1").Copy
Sht4.Range("A2:Y2").PasteSpecial Paste:=xlPasteValues
Sht4.Range("A2:Y" & LastRow6).Copy Range("AA2")
Set StartCell9 = Sheet11.Range("AA3")
LoB3_LastRow = Sht4.Cells(Sht4.Rows.Count, StartCell9.Column).End(xlUp).Row
' LoB3.Resize LoB3.Range.Resize(LoB3_LastRow)
Sht4.Range("AA1:AY1").Copy
Sht4.Range("AA2:AK2").PasteSpecial Paste:=xlPasteValues
Range("AU3:AY" & LoB3_LastRow).Select
Selection.NumberFormat = "0.0"
Range("A1").Select
' Replacing the "# and ## to "# - Not assigned"
MyArray = Array("OPs_Model_Data", "OPs_Data", "Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")
For Each i In MyArray
Sheets(i).Activate
Sheets(i).Range("A1").Select
Cells.Replace What:="##", Replacement:="# - Not assigned", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="#", Replacement:="# - Not assigned", LookAt:=xlWhole _
, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Next i
Sheet6.Select
Range("A1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'ActiveWorkbook.RefreshAll
' Refreshing other queries
ActiveWorkbook.Connections("Query - Sales_OPs_Data_Table").Refresh
ActiveWorkbook.Connections("Query - Threats_Data_Table").Refresh
Sheet2.Visible = xlSheetHidden
Sheet4.Visible = xlSheetVeryHidden
Sheet5.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
Sheet12.Visible = xlSheetHidden
Sheet10.Visible = xlSheetVeryHidden
Sheet11.Visible = xlSheetHidden
Sheet16.Visible = xlSheetVeryHidden
End Sub
Public Sub Enable_Analysis_For_Office()
Dim addin As COMAddIn
On Error GoTo AAO_Addin_Err
Dim AddInnStatus As Boolean
AddInnStatus = False
''checks if AAO add-in has been installed or not
For Each addin In ThisWorkbook.Application.COMAddIns
If addin.progID = "SapExcelAddIn" Then
addin.Connect = True
AddInnStatus = True
End If
Next
AAO_Addin_Err:
If AddInnStatus <> True Then
MsgBox "Please install AAO Add-in and re-run the tool", vbCritical, "Missing AAO Add-in"
Exit Sub
End If
End Sub
Public Sub Refresh_AAO_Queries()
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_2")
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_3")
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_5")
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_6")
If lResult = False Then
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_2")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_3")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_5")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_6")
' Example: You can also skip the else
Else
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_2")
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_3")
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_5")
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_6")
End If
End Sub
답변
5 SiddharthRout Dec 14 2020 at 15:15
나는 일반적으로이 게시물에 대답하지 않았을 것이지만 이러한 종류의 질문을 계속 접하게되므로 향후 방문자에게도 도움이되기를 바라며 어떻게 처리하는지 공유 할 것입니다. 그러나 이것은 내 개인적인 코딩 스타일이며 일반적으로 허용되는 코딩 스타일이 아닐 수 있습니다. 가장 좋다고 생각하는 것을 자유롭게 선택하십시오.
1. 명시 적 옵션 사용
2. VBA에서 (Vb.net 에서와는 달리) 변수를 선언 할 때 모든 변수를 올바른 유형으로 선언하고 그렇지 않으면 Variant. 변형은 기본 유형보다 느립니다. 당신은 참조 할 수 있습니다 입니다 성능에 대한 VBA를 나쁜에 변종을 사용하고 계십니까? . 다음은 예입니다.
Dim Sht, Sht1, Sht2, Sht3, Sht4, Sht5 As Worksheet
위의 경우 only Sht5는로 선언 Worksheet되고 Sht, Sht1, Sht2, Sht3, Sht4로 선언됩니다 Variants.
3. 이벤트 로 작업 할 때마다 ...
코딩이 끝날 때 되돌릴 수 있도록 기본 설정을 저장합니다.
원래 설정을 재설정 할 수 있도록 오류 처리를 사용하십시오. 이벤트 를 켜 거나 끌 때마다 오류 처리를 사용하여 다시 켜거나 끄십시오. 그렇지 않으면 재설정되지 않습니다.
다음은 예입니다.
Option Explicit
Sub Sample()
Dim scrnUpdating As Boolean
Dim dsplyAlerts As Boolean
On Error GoTo Whoa
With Application
'~~> Get user's current setting
scrnUpdating = .ScreenUpdating
dsplyAlerts = .DisplayAlerts
'~~> Set it to necessary setting
.ScreenUpdating = False
.DisplayAlerts = False
End With
'~~> Rest of your code
LetsContinue:
With Application
'~~> Reset original settings
.ScreenUpdating = scrnUpdating
.DisplayAlerts = dsplyAlerts
End With
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
4. 너무 많은 변수를 불필요하게 생성하지 마십시오. 가능할 때마다 재사용 하십시오 . 아래 7 번을 참조하십시오.
5. Excel VBA에서 선택 사용을 피하는 방법 . 이것은 귀하의 경우에도 적용됩니다. 이 예보기
귀하의 코드
MyArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")
For Each i In MyArray
If Sheets(i).Name <> Sheets("Sales_Data").Name Then
'
'
'
Else
'
'
'
End If
Next i
다음과 같이 쓸 수도 있습니다.
Dim SheetsArray, sht
SheetsArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")
Dim lRow As Long
Dim rng As Range
For Each sht In SheetsArray
With Sheets(sht)
'~~> Remove any filters
.AutoFilterMode = False
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
If .Name <> "Sales_Data" Then
Set rng = .Range("A2:K" & lRow)
Else
Set rng = .Range("A2:Q" & lRow)
End If
With rng
.AutoFilter Field:=4, Criteria1:="=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
End With
With .Columns(4).SpecialCells(xlCellTypeVisible)
.Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
Next sht
마찬가지로 코드
Set Sht = Sheet12
Set Sht1 = Sheet4
Set StartCell0 = Sheet12.Range("A3")
Set StartCell1 = Sheet4.Range("A2")
Sheet4.Select
'
'
'
Worksheets("OPs_Model_Data").Range("A2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheet12.Range("A1").Select
다음과 같이 쓸 수 있습니다.
Dim wsA As Worksheet, wsB As Worksheet, wsDest As Worksheet
Dim LastRow As Long, LastCol As Long
Set wsA = Sheet12: Set wsB = Sheet4
Set wsDest = ThisWorkbook.Worksheets("OPs_Model_Data")
LastRow = wsB.Range("A" & wsB.Rows.Count).End(xlUp).Row
wsB.Rows("2:" & wsBLastRow).Delete
With wsA
'~~> Reuse the lastrow variable
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastColumn = .Cells(3, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(3, 1), .Cells(LastRow, LastColumn)).Copy
DoEvents
wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues
End With
Sheet11및에 대해 간단히 복제하십시오 Sheet2. 사용할 필요가 없습니다 .Select.
6. 6 개월 후에도 코드를 볼 때 코드를 이해할 수 있도록 가능한 한 많이 코드에 주석을 달십시오.
7. 범위에 수식을 입력하려면 한 셀에 수식을 입력 한 다음 복사하여 붙여 넣을 필요가 없습니다. 한 번에 전체 범위에 수식을 입력 할 수 있습니다. 예를 들어,
Range("AK3").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],[@[Business Function]],C[-35],[@[Industry Group]],C[-34],[@[Industry SubGroup]],C[-33],[@[Customer Group]],C[-32],[@[Resp. SubRegion 1]],C[-31],[@[Sales SubRegion 1]],C[-30],[@[Sales SubRegion 2]],C[-29],[@[Country (End Use)]],C[-28],[@[Sold-to party]],C[-27],[@AccM])"
'
'
'
위의 내용을 간단한 예를 들어 설명하겠습니다. 수식을 입력 할 말 =Sum(A3:L3)에서 AK3다음이 같이 쓸 수있다 마지막 행까지 붙여 넣을 수 동일한 공식을하고 싶어
'~~> Reuse the worksheet variable
Set wsA = Sheet5
With wsA
'~~> Resuse Last Row variable
LastRow = .Range("AK" & .Rows.Count).End(xlUp).Row
With .Range("AK3:AK" & LastRow)
'~~> Enter formula in the entire range
.Formula = "=Sum(A3:L3)"
'~~> Convert to values
.Value = .Value
End With
End With
8. 가능한 한 repitivite 코드를 피하십시오. 이렇게하면 코드 줄이 줄어 듭니다. 섹션 고려
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")
'
'
'
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_6")
이것은 또한 다음과 같이 쓸 수 있습니다.
For i = 1 To 6
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_" & i)
DoEvents
Next i
노트 :
- 나는 가능한 한 정교하게 노력했습니다. 다른 것이 있으면이 게시물을 업데이트하겠습니다.
- 위에서 제공 한 코드 스 니펫을 테스트하지 않았습니다. 오류가 발생하거나 오류가 발견되면 언제든지 의견을 말하면 수정하겠습니다.
- 흥미로운 읽기 -To 'Err'is Human