哪个是更快的复制方法?Excel VBA [英] Which is a faster copying method? Excel VBA

问题描述

我将多次重复VBA模块的一部分，并且我想确保自己以最有效的方式进行此操作.最有效意味着最快的加载时间.

I'm going to be repeating part of a VBA module many times and I want to make sure I'm doing this the most efficient way. Most efficient meaning the quickest loading time.

基本上，我是从wb2上的某个范围复制数据并将其粘贴到wb1目标.

Basically, I'm copying data from a range on wb2 and pasting it to a wb1 destination.

第一种方法似乎更简单，因为它更短:

The first method seems easier as it's shorter:

wb2.Sheets(1).Range(Cells(2, TrpCdBLCol), Cells(100, TrpCdBLCol)).Copy wb1.Sheets("BL Import").Cells(2, TrpCdCol)

第二种方法是将两个变量声明为范围，进行设置并复制

The second method I declare two variables as ranges, set them, and the copy

Dim CopyRange As Range, Dest As Range
    Set CopyRange = wb2.Sheets(1).Range(Cells(2, TrpCdBLCol), Cells(100, TrpCdBLCol))
    Set Dest = wb1.Sheets("BL Import").Cells(2, TrpCdCol)
    CopyRange.Copy Dest

有什么区别吗?还是有一种方法比另一种更好?谢谢！

Is there any difference or is one way better than the other? Thanks!

推荐答案

您可以从range1到range2获得相同的内容，而无需复制并粘贴:

You can get identical contents from range1 to range2 without a copy and paste by:

1. 直接在Range1的Range2中设置值(此方法复制值，可用于计算公式，但不能用于格式)
2. 使用变量数组(添加一个步骤)

在我的测试中，第一个选项的运行时间是第二个的2/3-这可能是由于附加步骤所致.

On my testing the first option ran in 2/3 the time of the second - which would be due to the additional step.

更新:有效的技术

• 使用 Long 而不是 Integer 时，此处是有效编码的一站式参考

• On using Long rather than Integer, Why Use Integer Instead of Long?
• On using Value2 rather than Value, see post from Charles Williams here
• If you are manipulating data and writing it back to a sheet use a variant array, not a range object. If you are working with strings then hereis a one stop reference on efficient coding

选项1

Sub Recut1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim TrpCdBLCol As Long

TrpCdBLCol = 1
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)

vArr = ws1.Range(ws1.Cells(2, TrpCdBLCol), ws1.Cells(100, TrpCdBLCol))
ws2.Range(ws2.Cells(2, TrpCdBLCol),ws2.Cells(100, TrpCdBLCol)).Value2 = vArr

End Sub

选项2

Sub Recut2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim TrpCdBLCol As Long
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
TrpCdBLCol = 1

ws2.Range(ws2.Cells(2, TrpCdBLCol), ws2.Cells(100, TrpCdBLCol)).Value2 = ws1.Range(ws1.Cells(2, TrpCdBLCol), ws1.Cells(100, TrpCdBLCol))

End Sub

这篇关于哪个是更快的复制方法?Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！