如何提高VBA宏代码的速度?

I do not have much experience with writing macros, and therefore need the help of this community for the following issue encountered:

我在编写宏方面没有太多经验，因此在遇到以下问题时需要社区的帮助:

My macro copies a range of values entered in a vertical range in one worksheet and then pastes the values horizontally (transpose) in another worksheet. It would in theory paste the values from the first sheet to first row of the second worksheet which does not have content. Since the first five rows have contents, it thus pastes the values to the sixth row. The problem I have with the running of the macro is that I feel like it is too slow and I would therefore like it to run faster.

我的宏复制一个工作表中垂直范围内输入的值范围，然后在另一个工作表中水平粘贴值(转置)。理论上，它会将第一张表中的值粘贴到第二个工作表的第一行，而后者没有内容。由于前五行有内容，因此它将值粘贴到第六行。我在运行宏时遇到的问题是，我觉得它太慢了，因此我希望它运行得更快。

I have the same macro doing the same thing but that instead pastes the values to another worksheet to the first row, and it runs perfect.

我有相同的宏做同样的事情，但是它将值粘贴到另一个工作表到第一行，它运行的很好。

My best guess is therefore that the second macro is running slow because it has to start pasting on the sixth row and there may be some contents on the first 5 rows that take a lot of time for the macro to go through (there a lot of cell references to other workbooks) to determine where the next row for pasting should be. That is my best guess though and since I hardly know anything about macros, I cannot say for sure what the problem is.

因此我最好的猜测是,第二个宏观运行缓慢,因为它粘贴在第六行开始,可能会有一些内容在第一个5行花费很多时间宏经历(有很多细胞引用到其他作业本)来确定下一行粘贴。这是我最好的猜测，因为我几乎不了解宏，所以我不能确定问题是什么。

I hereby provide you with the code of my macro and sincerely hope that somebody can tell me what is making my macro slow and provide me with a solution as to how to make it run faster. I am thinking that a solution might potentially be that the macro should not consider the first five rows of data and start pasting immediately on row 6 for the first entry. Then on row 7 the next time, and etc. This might be a solution but I do not know how to write the code in a way that it would do that.

我在此向您提供我的宏的代码，并真诚地希望有人能告诉我是什么使我的宏变慢，并为我提供如何使它运行得更快的解决方案。我认为一个解决方案可能是，宏不应该考虑前5行数据，然后在第6行开始粘贴第一个条目。然后下一次在第7行，等等。这可能是一个解决方案，但我不知道如何编写代码。

Thank you for taking time and helping me to find a solution, here is the code:

感谢您花时间帮助我找到解决方案，以下是代码:

Sub Macro1()
Application.ScreenUpdating = False

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myCopy As Range
    Dim myTest As Range

    Dim lRsp As Long

    Set inputWks = wksPartsDataEntry
    Set historyWks = Sheet11

      'cells to copy from Input sheet - some contain formulas
      Set myCopy = inputWks.Range("OrderEntry2")

      With historyWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With

      With inputWks
          Set myTest = myCopy.Offset(0, 2)

          If Application.Count(myTest) > 0 Then
              MsgBox "Please fill in all the cells!"
              Exit Sub
          End If
      End With

      With historyWks
          With .Cells(nextRow, "A")
              .Value = Now
              .NumberFormat = "mm/dd/yyyy hh:mm:ss"
          End With
          .Cells(nextRow, "B").Value = Application.UserName
          oCol = 3
          myCopy.Copy
          .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
          Application.CutCopyMode = False
      End With

      'clear input cells that contain constants
      With inputWks
        On Error Resume Next
           With myCopy.Cells.SpecialCells(xlCellTypeConstants)
                .ClearContents
                Application.GoTo .Cells(1) ', Scroll:=True
           End With
        On Error GoTo 0
      End With

Application.ScreenUpdating = True
End Sub

7 个解决方案

#1

Just reiterating what has already been said:

重申已经说过的话:

Option Explicit

Sub Macro1()

'turn off as much background processes as possible
With Excel.Application
        .ScreenUpdating = False
        .Calculation = Excel.xlCalculationManual
        .EnableEvents = False
End With

    Dim historyWks As Excel.Worksheet
    Dim inputWks As Excel.Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myCopy As Excel.Range
    Dim myTest As Excel.Range

    Dim lRsp As Long

    Set inputWks = wksPartsDataEntry
    Set historyWks = Sheet11

      'cells to copy from Input sheet - some contain formulas
      Set myCopy = inputWks.Range("OrderEntry2")

      With historyWks
          nextRow = .Cells(.Rows.Count, 1).End(Excel.xlUp).Offset(1, 0).Row
      End With

      With inputWks
          Set myTest = myCopy.Offset(0, 2)

          If Excel.Application.Count(myTest) > 0 Then
              MsgBox "Please fill in all the cells!"
              GoTo QuickExit
          End If
      End With

      With historyWks
          With .Cells(nextRow, 1)
              .Value = Now
              .NumberFormat = "mm/dd/yyyy hh:mm:ss"
          End With
          .Cells(nextRow, 2).Value = Excel.Application.UserName
          oCol = 3
          myCopy.Copy
          .Cells(nextRow, 3).PasteSpecial Paste:=Excel.xlPasteValues, Transpose:=True
          Excel.Application.CutCopyMode = False
      End With

      'clear input cells that contain constants
      With inputWks
        On Error Resume Next
           With myCopy.Cells.SpecialCells(Excel.xlCellTypeConstants)
                .ClearContents
                Excel.Application.Goto .Cells(1) ', Scroll:=True
           End With
        On Error GoTo 0
      End With

    Calculate

QuickExit

With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlAutomatic
        .EnableEvents = True
End With

End Sub

I'd step through the macro line-by-line to try to locate which line is slow.

我会一步一步地遍历每一行，试图找出哪一行是慢的。

Another alternative - although not sure if it'll speed things up - is to avoid the clipboard and lose the copy/paste so you'd apply a method like the following to move the data:

另一种选择——虽然不确定是否会加快事情的速度——是避免剪贴板，丢失复制/粘贴，所以您可以应用如下方法来移动数据:

Option Explicit

Sub WithoutPastespecial()

'WORKING EXAMPLE

Dim firstRange As Range
Dim secondRange As Range

Set firstRange = ThisWorkbook.Worksheets("Cut Sheet").Range("S4:S2000")
With ThisWorkbook.Worksheets("Cutsheets")
    Set secondRange = .Range("A" & .Rows.Count).End(Excel.xlUp).Offset(1)
End With

With firstRange
      Set secondRange = secondRange.Resize(.Rows.Count, .Columns.Count)
End With
secondRange.Value = firstRange.Value

End Sub

#2

Best way to improve performance based on my experience is to work on variables in code rather than accessing the spreadsheet every time you want to lookup a value. Save any range you want to work with in a variable(variant) and then iterate through it as if it was the sheet.

根据我的经验，提高性能的最佳方法是在代码中处理变量，而不是每次查找值时都访问电子表格。保存您想要在变量(变量)中使用的任何范围，然后迭代它，就好像它是表一样。

    dim maxRows as double
    dim maxCols as integer.
    dim data as variant
with someSheet
    maxRows = .Cells(rows.count, 1).end(xlUp).row             'Max rows in sheet
    maxCols = .Cells(1, columns.count).end(xlToLeft).column   'max columns in sheet
    data = .Range(.Cells(1,1), .Cells(maxRows, maxCols))      'copy range in a variable
end with

From here you can access the data variable as if it was the spreadsheet like - data(row, column) with MUCH MUCH faster read speed.

从这里可以访问数据变量，就像它是电子表格一样——数据(行、列)，读取速度更快。

#3

Please take a look at this article as well. How to speed up calculation and improve performance...

请也看看这篇文章。如何加快计算速度，提高性能……

By all means, Application.calculation= xlCalculationManual is usually the culprit. But we can notice that volatile Excel sheet functions can mostly kill your application on large scale of data processing and functional aspect.

无论如何,应用程序。计算= xlCalculationManual通常是罪魁祸首。但我们可以注意到，volatile Excel表单函数在数据处理和功能方面可以在很大程度上杀死应用程序。

Also, for your current code following post might not be directly relevant. I find it useful for tips on over-all Excel/VBA performance optimization.

另外，对于您当前的代码，后面的代码可能并不直接相关。我发现它对于全面的Excel/VBA性能优化技巧非常有用。

75 Excel speeding up tips

Excel加速提示

PS: I don't have enough reputation to comment on your post. So added as an answer..

我没有足够的声誉来评论你的文章。作为回答。

#4

As suggested by a few others in the comments, you should definitely change Application.Calculation to xlCalculationManual and rememeber to set it back to xlcalculationAutomatic at the end. Also try setting Application.Screenupdating = False (and turning that back on again too). Also, bear in mind that .Copy is a very inefficient way to copy cell values - if you really just want the values, loop through the range setting .Value to the .Values in the old range. If you need all the formatting, you're probably stuck with .Copy.

正如评论中其他一些人所建议的，您肯定应该修改应用程序。计算到xlCalculationManual并记住，在最后将其设置为xlcalculationAutomatic。也试着设置应用程序。screen update = False(并再次打开它)。另外，请记住，. copy是一种非常低效的复制单元格值的方法——如果您只是想要这些值，那么请循环遍历范围设置. value到旧范围中的. values。如果您需要所有的格式，您可能需要使用. copy。

When you turn off the calc/screen refresh flags, please remember to turn them back on in all circumstances (even when your program exits at a different point, or causes a runtime error). Otherwise all sorts of bad things will happen. :)

当您关闭calc/screen refresh标志时，请记住在所有情况下都要打开它们(即使您的程序在不同的点退出，或者导致运行时错误)。否则所有的坏事都会发生。:)

#5

Just a few suggestions (would have posted as a comment but I guess I don't have the rep):

只是一些建议(可能会作为评论发布，但我想我没有代表):

1. Try refering to cell addresses instead of named ranges (doubt this would be the cause but may be causing some hit to performance)

   尝试引用单元地址而不是命名范围(这可能是原因，但可能会对性能造成一些影响)

2. Do your workbook formulas contain links to other workbooks? Try testing the code on a file with broken links to see if it improves performance.

   您的工作簿公式是否包含到其他工作簿的链接?尝试在一个有坏链接的文件上测试代码，看看它是否提高了性能。

3. If neither of these are the issue, my guess is that if the formulas are overly complex, there is probably some processing overhead being added. Try the code on a file containing only values to see if there is any improved performance.

   如果这两者都不是问题所在，我的猜测是，如果公式过于复杂，可能会增加一些处理开销。尝试只包含值的文件上的代码，看看是否有任何改进的性能。

#6

You can improve the speed by stopping calculation during changing cell value and after that you can enable it. please follow the link. http://webtech-training.blogspot.in/2013/10/how-to-stop-heavy-formula-calculation.html

您可以通过在更改单元格值期间停止计算并在此之后启用计算来提高速度。请按照链接。http://webtech-training.blogspot.in/2013/10/how-to-stop-heavy-formula-calculation.html

#7

.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True Application.CutCopyMode = False

. cells (nextRow, 3). pastal Paste:=xlPasteValues，转置:=True ApplicationCutCopyMode = False

I wouldn't do that. Cut,copy & Paste operations are the costliest operations, in terms of processor utilization, in an operating system.

我不会这样做。就处理器利用率而言，剪切、复制和粘贴操作是操作系统中最昂贵的操作。

Instead, you could just assign the value from one cell / range to an another cell / range, as in

相反，您可以将值从一个单元格/范围分配到另一个单元格/范围，如in

    Cells(1,1) = Cells(1,2)  or Range("A1") = Range("B1")

Hope you got my point..

希望你明白我的意思。