- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我开发了一个 VBA 宏,用于刷新 5 个 SAP AAO 查询,然后通过删除重复项将这些查询数据复制到表中,然后加载到电源查询中。在那里我添加了一些计算列,然后将这些数据加载到电源枢轴中。
我还使用下面提到的 VBA 代码在每个工作表中执行了一些操作,但是目前运行代码需要 4 分钟以上。
在这 4 分钟内,至少有 45 秒用于刷新 AAO 查询和剩余的宏运行时间(包括刷新电源查询)
我仍在学习 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
最佳答案
我通常不会回答这篇文章,但我不断遇到这类问题,所以我将分享我如何处理它,希望这也能帮助 future 的访客。但是,请注意这是我个人的编码风格,可能不是常见的可接受的编码风格。随意选择你认为最好的。
1. 使用Option Explicit
2. 在 VBA 中(不像在 Vb.net 中说的那样)在声明变量时,用正确的类型声明它们,否则它们将被声明为 Variant
.变体比原生类型慢。您可能想查看 Is using variants in vba bad for performance? .这是一个例子
Dim Sht, Sht1, Sht2, Sht3, Sht4, Sht5 As Worksheet
在上面,只有
Sht5
声明为
Worksheet
和
Sht, Sht1, Sht2, Sht3, Sht4
被声明为
Variants
.
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 点。
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
.
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. 尽可能避免重复代码。这将减少您的代码行。考虑部分
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
关于excel - 优化 VBA 代码并提高性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/65284481/
在这段令人惊叹的视频 ( https://www.youtube.com/watch?v=udix3GZouik ) 中,Alex Blom 谈到了 Ember 在移动世界中的“黑客攻击”。 在 22
我们希望通过我们的应用收集使用情况统计信息。因此,我们希望在服务器端的某个地方跟踪用户操作。 就性能而言,哪个选项更合适: 在 App Engine 请求日志中跟踪用户操作。即为每个用户操作写入一个日
在针对对象集合的 LINQ 查询的幕后究竟发生了什么?它只是语法糖还是发生了其他事情使其更有效的查询? 最佳答案 您是指查询表达式,还是查询在幕后的作用? 查询表达式首先扩展为“普通”C#。例如: v
我正在构建一个简单的照片库应用程序,它在列表框中显示图像。 xaml 是:
对于基于 Web 的企业应用程序,使用“静态 Hashmap 存储对象” 和 apache java 缓存系统有何优缺点?哪一个最有利于性能并减少堆内存问题 例如: Map store=Applica
我想知道在性能方面存储类变量的最佳方式是什么。我的意思是,由于 Children() 函数,存储一个 div id 比查找所有其他类名更好。还是把类名写在变量里比较好? 例如这样: var $inne
我已经阅读了所有这些关于 cassandra 有多快的文章,例如单行读取可能需要大约 5 毫秒。 到目前为止,我不太关心我的网站速度,但是随着网站变得越来越大,一些页面开始需要相当多的查询,例如一个页
最近,我在缓存到内存缓存之前的查询一直需要很长时间才能处理!在这个例子中,它花费了 10 秒。在这种情况下,我要做的就是获得 10 个最近的点击。 我感觉它加载了所有 125,592 行然后只返回 1
我找了几篇文章(包括SA中的一些问题),试图找到基本操作的成本。 但是,我尝试制作自己的小程序,以便自己进行测试。在尝试测试加法和减法时,我遇到了一些问题,我用简单的代码向您展示了这一点
这个问题在这里已经有了答案: Will Java app slow down by presence of -Xdebug or only when stepping through code? (
我记得很久以前读过 with() 对 JavaScript 有一些严重的性能影响,因为它可能对范围堆栈进行非确定性更改。我很难找到最近对此的讨论。这仍然是真的吗? 最佳答案 与其说 with 对性能有
我们有一个数据仓库,其中包含非规范化表,行数从 50 万行到 6 多万行不等。我正在开发一个报告解决方案,因此出于性能原因我们正在使用数据库分页。我们的报告有搜索条件,并且我们已经创建了必要的索引,但
我有一条有效的 SQL 语句,但需要很长时间才能处理 我有一个 a_log 表和一个 people 表。我需要在 people 表中找到给定人员的每个 ID 的最后一个事件和关联的用户。 SELECT
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它,visit the help center 。 已关
通常当我建立一个站点时,我将所有的 CSS 放在一个文件中,并且一次性定义与一组元素相关的所有属性。像这样: #myElement { color: #fff; background-
两者之间是否存在任何性能差异: p { margin:0px; padding:0px; } 并省略最后的分号: p { margin:0px; padding:0px } 提前致谢!
我的应用程序 (PHP) 需要执行大量高精度数学运算(甚至可能出现一共100个数字) 通过这个论坛的最后几篇帖子,我发现我必须使用任何高精度库,如 BC Math 或 GMP,因为 float 类型不
我一直在使用 javamail 从 IMAP 服务器(目前是 GMail)检索邮件。 Javamail 非常快速地从服务器检索特定文件夹中的消息列表(仅 id),但是当我实际获取消息(仅包含甚至不包含
我非常渴望开发我的第一个 Ruby 应用程序,因为我的公司终于在内部批准了它的使用。 在我读到的关于 Ruby v1.8 之前的所有内容中,从来没有任何关于性能的正面评价,但我没有发现关于 1.9 版
我是 Redis 的新手,我有一个包含数百万个成员(member) ID、电子邮件和用户名的数据集,并且正在考虑将它们存储在例如列表结构中。我认为 list 和 sorted set 可能最适合我的情
我是一名优秀的程序员,十分优秀!