使用 VBA 复制和粘贴格式很慢。我怎样才能加快速度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40161564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy and paste formatting is slow using VBA. How can I speed it up?
提问by Johnson Jason
The following code is a function that works. It's just slow and I don't know how to speed it up. It takes an excel row number and the value of it's headerval (string) and finds the same headerval on a different sheet then copies the formatting and applies it to our new sheet. The true false is because the source sheet has 2 different formatting options. It passes in the row to use either 23 or 24. ZROW is a public variable which is set with the ROW to start looking. srccolbyname function gets a col number from the source sheet which has the same headerval
下面的代码是一个有效的函数。它只是很慢，我不知道如何加快速度。它需要一个 excel 行号和它的 headerval（字符串）的值，并在不同的工作表上找到相同的 headerval，然后复制格式并将其应用于我们的新工作表。真假是因为源工作表有 2 个不同的格式选项。它在行中传递以使用 23 或 24。ZROW 是一个公共变量，它与 ROW 一起设置以开始查找。srccolbyname 函数从具有相同 headerval 的源表中获取一个列号
Function formatrow(roww As Long, header As Boolean)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim headerval As String
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("DEALSHEET")
Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Sheets("Sheet1")
If header = True Then: srcrow = 23: Else: srcrow = 24
LastColumn = sht.Cells(ZROW + 1, sht.Columns.Count).End(xlToLeft).Column
For x = 2 To LastColumn
headerval = sht.Cells(ZROW + 1, x).Value
srccol = srccolbyname(headerval)
sht2.Cells(srcrow, srccol).Copy 'THIS IS SLOW
sht.Cells(roww, x).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next x
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
End Function
As requested here is the support function referenced above.
这里要求的是上面提到的支持功能。
Public Function srccolbyname(strng_name As String) As Integer
Call findcol 'find ZROW
Dim x As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Sheet1")
LastColumn = sht.Cells(22, sht.Columns.Count).End(xlToLeft).Column
For x = 2 To LastColumn
chkval = sht.Cells(22, x).Value
If Trim(UCase(chkval)) = Trim(UCase(strng_name)) Then
srccolbyname = x
Exit For
Else
srccolbyname = 2
End If
Next x
End Function
回答by kpg987
There are numerous ways to make your code faster, but you'll find that the copy and paste special in particular is notoriously slow. Provided that the formatting you need to preserve is simply the cell value, background color and font color, you could try to replace
有很多方法可以使您的代码更快，但您会发现特别是复制和粘贴特别慢。假设您需要保留的格式只是单元格值、背景颜色和字体颜色，您可以尝试替换
sht2.Cells(srcrow, srccol).Copy
sht.Cells(roww, x).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With this:
有了这个：
sht2.Cells(srcrow,srcol).Value=sht.Cells(roww,x).Value
sht2.Cells(srcrow,srcol).Interior.ColorIndex=sht.Cells(roww,x).Interior.ColorIndex
sht2.Cells(srcrow,srcol).Font.ColorIndex=sht.Cells(roww,x).Font.ColorIndex
You'll find that people have looked into this issue on Stack Overflow before: fast way to copy formatting in excel
你会发现之前有人在 Stack Overflow 上研究过这个问题：fast way to copy format in excel
If your performance issues persist, I would look into replacing your user-defined function srccolbynameby the range.findmethod (see more about it here: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx). It seems to me to be performing the same role as this inbuilt method. Typically, these inbuilt methods will run faster than a UDF.
如果您的性能问题仍然存在，我会考虑用srccolbyname该range.find方法替换您的用户定义函数（在此处查看更多信息：https: //msdn.microsoft.com/en-us/library/office/ff839746.aspx）。在我看来，它扮演的角色与这个内置方法相同。通常，这些内置方法的运行速度比 UDF 快。
In general, if possible it's better to refer to a range (i.e. a collection of cells) rather than cells individually. By copy-pasting a range rather than the cells one by one, you minimize the traffic (i.e. switching back and forth) between Excel and VBA which typically hampers performance.
一般来说，如果可能的话，最好引用一个范围（即单元格的集合）而不是单独的单元格。通过复制粘贴一个范围而不是一个一个的单元格，您可以最大限度地减少 Excel 和 VBA 之间的通信量（即来回切换），这通常会影响性能。
