vba - 使用 VBA 复制和粘贴格式很慢。我怎样才能加快速度？

Question

下面的代码是一个有效的函数。只是速度很慢，我不知道如何加快速度。它需要一个 Excel 行号及其 headerval(字符串)的值，并在不同的工作表上找到相同的 headerval，然后复制格式并将其应用到我们的新工作表。正确错误是因为源工作表有 2 个不同的格式选项。它传入行以使用 23 或 24。ZROW 是一个公共(public)变量，它与 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

此处要求的是上面引用的支持功能。

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 

Answer 1

有很多方法可以让你的代码更快，但是你会发现复制和粘贴特别慢是出了名的。如果您需要保留的格式只是单元格值、背景颜色和字体颜色，您可以尝试替换

  sht2.Cells(srcrow, srccol).Copy 
  sht.Cells(roww, x).PasteSpecial Paste:=xlPasteFormats,    Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=False
 Application.CutCopyMode = False

这样:

 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

您会发现人们之前已经在 Stack Overflow 上研究过这个问题:fast way to copy formatting in excel

如果您的性能问题仍然存在，我会考虑用 range.find 方法替换您的用户定义函数 srccolbyname (在此处查看更多信息:https://msdn.microsoft.com/en-us/library/office/ff839746.aspx )。在我看来，它的作用与这个内置方法相同。通常，这些内置方法的运行速度比 UDF 更快。

一般来说，如果可能的话，最好引用一个范围(即单元格的集合)而不是单独的单元格。通过复制粘贴一个范围而不是逐个单元格，可以最大限度地减少 Excel 和 VBA 之间的流量(即来回切换)，这通常会影响性能。