试图让我的VBA运行得快一点

Question

我是VBA新手，所以我的代码通常很慢/不够优化。

在我的一个程序中，我在工作表中有单元格，当用户按下按钮时必须填充，范围取决于按钮，但概念是相同的。

所以我做了这个怪物:

Cells((Range("namedrange").Row + 5), 1).Value = ThisWorkbook.Sheets(5).Cells(4, 7).Value
Cells((Range("namedrange").Row + 5), 3).Value = ThisWorkbook.Sheets(5).Cells(4, 8).Value
Cells((Range("namedrange").Row + 5), 5).Value = ThisWorkbook.Sheets(5).Cells(4, 9).Value
Cells((Range("namedrange").Row + 5), 8).Value = ThisWorkbook.Sheets(5).Cells(4, 10).Value
Cells((Range("namedrange").Row + 5) + 1, 1).Value = ThisWorkbook.Sheets(5).Cells(5, 7).Value
Cells((Range("namedrange").Row + 5) + 1, 3).Value = ThisWorkbook.Sheets(5).Cells(5, 8).Value
Cells((Range("namedrange").Row + 5) + 1, 5).Value = ThisWorkbook.Sheets(5).Cells(5, 9).Value
Cells((Range("namedrange").Row + 5) + 1, 8).Value = ThisWorkbook.Sheets(5).Cells(5, 10).Value

，但后来改为:

With Range("namedrange")
.Offset(5).Columns(1).Value = ThisWorkbook.Sheets(3).Cells(4, 7).Value
.Offset(5).Columns(3).Value = ThisWorkbook.Sheets(3).Cells(4, 8).Value
.Offset(5).Columns(5).Value = ThisWorkbook.Sheets(3).Cells(4, 9).Value
.Offset(5).Columns(8).Value = ThisWorkbook.Sheets(3).Cells(4, 10).Value
.Offset(6).Columns(1).Value = ThisWorkbook.Sheets(3).Cells(5, 7).Value
.Offset(6).Columns(3).Value = ThisWorkbook.Sheets(3).Cells(5, 8).Value
.Offset(6).Columns(5).Value = ThisWorkbook.Sheets(3).Cells(5, 9).Value
.Offset(6).Columns(8).Value = ThisWorkbook.Sheets(3).Cells(5, 10).Value
End With

是快一点，但我觉得它仍然是次优化。我想知道是否有办法使它更干净/更优雅。要注意的是，在列中存在不连续，例如，它从第一列开始，然后跳到第三列，然后跳到第五列，最后跳到第八列。

代码工作，但它很慢，我只是想要一种方法，使它更快/更干净。

Answer 1

使用变量

• 关于效率，就是这样:你正在使用最有效的方式将值从一个单元格复制到另一个单元格，也就是通过赋值复制。
• 如果你想让它更灵活，可维护性和可读性(?)，这里有一些想法。此外，您可以将剩余的魔术数字和文本移动到代码开头的常量中，甚至使用常量作为参数。

Sub CopyValues()

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

' Specify the worksheet if you know it.
'Dim dnrg As Range: Set dnrg = wb.Sheets("Sheet1").Range("NamedRange")
' Otherwise, make sure the workbook is active.
If Not wb Is ActiveWorkbook Then wb.Activate
Dim dnrg As Range: Set dnrg = Range("NamedRange")

Dim drg As Range: Set drg = dnrg.Range("A1,C1,E1,H1").Offset(5)
Dim cCount As Long: cCount = drg.Cells.Count

' If you know the tab name, use it instead of the index (3).
Dim sws As Worksheet: Set sws = wb.Sheets(3)
Dim srg As Range: Set srg = sws.Range("G4").Resize(, cCount)

Dim r As Long, c As Long

For r = 0 To 1
For c = 1 To cCount
drg.Offset(r).Cells(c).Value = srg.Offset(r).Cells(c).Value
Next c
Next r
End Sub

Answer 2

从VBA中访问Excel的值是一个缓慢的操作，当您发出多个请求时，这会增加。当你基本上是在重复地检索相同的信息时，有两种方法可以用来减少访问时间。

1. 用计算值替换查找
2. 使用with语句

因此你的代码可以写成

Dim myCol as long 
myCol =Range("namedrange").Row + 5
With ThisWorkook.Sheets(5)
Cells(myCol, 1).Value = .Cells(4, 7).Value
Cells(myCol, 3).Value = .Cells(4, 8).Value
Cells(myCol, 5).Value = .Cells(4, 9).Value
Cells(myCol, 8).Value = .Cells(4, 10).Value
myCol=myCol+1 ' trivial example
Cells(mycol, 1).Value = .Cells(5, 7).Value
Cells(myCol, 3).Value = .Cells(5, 8).Value
Cells(myCol, 5).Value = .Cells(5, 9).Value
Cells(myCol, 8).Value = .Cells(5, 10).Value
End with

也请安装免费的，开源的，神奇的Rubberduck插件为VBA。代码检查将帮助您编写更正确的VBA。

Answer 3

我想知道如果你使用所有会发生什么offset参数，行和列。例子:

With Range("namedrange")
.Offset(5, 0).Value = ThisWorkbook.Sheets(3).Cells(4, 7).Value
.Offset(5, 2).Value = ThisWorkbook.Sheets(3).Cells(4, 8).Value
.Offset(5, 4).Value = ThisWorkbook.Sheets(3).Cells(4, 9).Value
.Offset(5, 7).Value = ThisWorkbook.Sheets(3).Cells(4, 10).Value
.Offset(6, 0).Value = ThisWorkbook.Sheets(3).Cells(5, 7).Value
.Offset(6, 2).Value = ThisWorkbook.Sheets(3).Cells(5, 8).Value
.Offset(6, 4).Value = ThisWorkbook.Sheets(3).Cells(5, 9).Value
.Offset(6, 7).Value = ThisWorkbook.Sheets(3).Cells(5, 10).Value
End With

Answer 4

您可以尝试在执行过程中禁用屏幕更新。

禁用ScreenUpdating

1. 要禁用屏幕更新，在代码的开头写上这行:
   Application.ScreenUpdating = False

使ScreenUpdating

2. 要重新启用ScreenUpdating，在代码末尾写上这行:
   Application.ScreenUpdating = True