优化拷贝的VBA性能
优化拷贝的VBA性能
提问于 2016-11-18 15:34:58
我必须通过运行这些带有3个参数I，j，k的代码来生成报告。因为i从1到5运行，j从1到8运行，k从0到12运行。通过这种运行，我大约需要8分钟来完成这个运行。但是我可以跑到几千排。我如何优化这段代码才能运行得更快？
`Option Explicit
Sub CopySensMP()
Sheets("Sens By MP").Activate
Application.ScreenUpdating = False
Dim ts, te As Variant
ts = Time
Dim i, j, k As Integer
For k = 0 To Range("Tbl_Sens_Scenerio").Rows.Count - 1
For i = 1 To Range("Sens_MP_End")
Cells(i + 34 + k * Range("Sens_MP_End"), 15).Value = Range("Tbl_Sens_Scenerio").Cells(k + 1, 3).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 16).Value = Range("Tbl_Sens_Scenerio").Cells(k + 1, 4).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 17).Value = Range("Tbl_Sens_Scenerio").Cells(k + 1, 5).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 18).Value = Range("Tbl_Sens_Scenerio").Cells(k + 1, 6).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 19).Value = Range("Tbl_Sens_Scenerio").Cells(k + 1, 7).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 5) = Range("Tbl_Sens_Scenerio").Cells(k + 1, 2) & " - MP" & Application.WorksheetFunction.Text(i, "000")
For j = 1 To Range("Tbl_Sens_Plan").Count
Cells(i + 34 + k * Range("Sens_MP_End"), 5 + j).Value = Range("Tbl_Sens_MP").Cells(i, j).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 14).Value = Range("Tbl_Sens_Result").Cells(i, 8 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 20).Value = Range("Tbl_sens_result").Cells(i, 2 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 21).Value = Range("Tbl_sens_result").Cells(i, 3 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 22).Value = Range("Tbl_sens_result").Cells(i, 4 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 23).Value = Range("Tbl_sens_result").Cells(i, 5 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 24).Value = Range("Tbl_sens_result").Cells(i, 6 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 27).Value = Range("Tbl_sens_result").Cells(i, 7 + k * 10).Value
Cells(i + 34 + k * Range("Sens_MP_End"), 28).Value = Range("Tbl_sens_result").Cells(i, 1 + k * 10).Value
Next j
Next i
Next k
te = Time
Debug.Print te, ts
Application.ScreenUpdating = True
MsgBox "Done"
End Sub`回答 1
Stack Overflow用户
发布于 2016-11-18 18:37:02
假设您不需要重新计算引用范围中的公式，则可以节省时间的项目符号：
- 切换到手动重新计算，然后在例程结束时切换回自动
- 使用变量而不是对范围的引用(您采用静态数字千位数，并且不激活sheet
- 使用
Long类型而不是Value2类型而不是Integer - Use类型
考虑到上面的内容，这里有一个简单的例程，我相信它还可以进一步改进((i + 34 + k * sens_mp_end)保存为变量，等等)。
Sub CopySensMP()
'Sheets("Sens By MP").Activate
Dim wb As Workbook, ws As Worksheet, sens_mp_end As Long
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sens By MP")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'manual calculation to the rescue
Dim ts, te As Variant
ts = Time
Dim i, j, k As Long 'Integer
sens_mp_end = Range("Sens_MP_End") 'why would you call a range for a static number this many times?
For k = 0 To Range("Tbl_Sens_Scenerio").Rows.Count - 1
For i = 1 To sens_mp_end
ws.Cells(i + 34 + k * sens_mp_end, 15).Value2 = Range("Tbl_Sens_Scenerio").Cells(k + 1, 3).Value2
ws.Cells(i + 34 + k * sens_mp_end, 16).Value2 = Range("Tbl_Sens_Scenerio").Cells(k + 1, 4).Value2
ws.Cells(i + 34 + k * sens_mp_end, 17).Value2 = Range("Tbl_Sens_Scenerio").Cells(k + 1, 5).Value2
ws.Cells(i + 34 + k * sens_mp_end, 18).Value2 = Range("Tbl_Sens_Scenerio").Cells(k + 1, 6).Value2
ws.Cells(i + 34 + k * sens_mp_end, 19).Value2 = Range("Tbl_Sens_Scenerio").Cells(k + 1, 7).Value2
ws.Cells(i + 34 + k * sens_mp_end, 5) = Range("Tbl_Sens_Scenerio").Cells(k + 1, 2) & " - MP" & Application.WorksheetFunction.Text(i, "000")
For j = 1 To Range("Tbl_Sens_Plan").Count
ws.Cells(i + 34 + k * sens_mp_end, 5 + j).Value2 = Range("Tbl_Sens_MP").Cells(i, j).Value2
ws.Cells(i + 34 + k * sens_mp_end, 14).Value2 = Range("Tbl_Sens_Result").Cells(i, 8 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 20).Value2 = Range("Tbl_sens_result").Cells(i, 2 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 21).Value2 = Range("Tbl_sens_result").Cells(i, 3 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 22).Value2 = Range("Tbl_sens_result").Cells(i, 4 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 23).Value2 = Range("Tbl_sens_result").Cells(i, 5 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 24).Value2 = Range("Tbl_sens_result").Cells(i, 6 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 27).Value2 = Range("Tbl_sens_result").Cells(i, 7 + k * 10).Value2
ws.Cells(i + 34 + k * sens_mp_end, 28).Value2 = Range("Tbl_sens_result").Cells(i, 1 + k * 10).Value2
Next j
Next i
Next k
te = Time
Debug.Print te, ts
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "Done"
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40671670
复制相关文章
相似问题
腾讯云开发者
