时间戳

首页 > 解决方案 > VBA：通过范围与数组更改数据

arrays - VBA：通过范围与数组更改数据

问题描述

我试图让我的宏运行得更快。我的电子表格有 300,000 行，作为宏的一部分，我试图将 AF 和 AG 列的值更改为取决于 A 列值的值。

我使用循环函数做到了这一点，但我听说使用数组会更快。我切换到下面的数组代码，宏运行时间大约延长了 5 秒。有没有更有效的编码方式来节省这一步的时间？

代码 1

Dim cell As Range

For Each cell In Range("A2:A400000")
   If cell.Value = "N149933" _
   Or cell.value = "N103291" _
   Or cell.value = "NS11375" _
   Or cell.value = "N187037" _
   Or cell.value = "N132921" _
   Or cell.value = "NS03359" _
   Or cell.value = "N102301" Then
      cell.Offset(0,32).Value = "Foundation and Endowment (Charities)"
      cell.Offset(0,31).Value = "QP"
ElseIf cell.Value = "NS35481" Then
      cell.Offset(0,32).Value = "Corporate"
ElseIf cell.Value = "NS07301" Then
      cell.Offset(0,32).Value = "Public Fund (Government)"
      cell.Offset(0,31).Value = "QP"
ElseIf cell.value = "NS29802" _
    Or cell.value = "NS29803" _
    Or cell.value = "NS29801" Then
      cell.Offset(0,32).Value = "Insurance"
  Else: cell.Offset(0,32).Value = "High Net Worth Individuals"
  End If
Next Cell

代码 2

Dim arr as Variant
arr = Worksheets("Input").Range("A1").CurrentRegion

Dim i as Long
For i = LBound(arr,1) + 1 To UBound(arr,1)
   If arr(i,1) = "N149933" _
   Or arr(i,1) = "N103291" _
   Or arr(i,1) = "NS11375" _
   Or arr(i,1) = "N187037" _
   Or arr(i,1) = "N132921" _
   Or arr(i,1) = "NS03359" _
   Or arr(i,1) = "N102301" Then
     arr(i,32) = "QP"
     arr(i,33) = "Foundation and Endowment (Charities)"
ElseIf arr(i,1) = "NS35481" Then
     arr(i,33) = "Corporate"
ElseIf arr(i,1) = "NS07301" Then
     arr(i,32) = "QP"
     arr(i,33) = "Public Fund (Government)"
ElseIf arr(i,1) = "NS29802" _
    Or arr(i,1) = "NS29803" _
    Or arr(i,1) = "NS29801" Then
     arr(i,33) = "Insurance"
Else: arr(i,33) = "High Net Worth Individuals"
EndIf
Next i

Worksheets("Input").Range("A1").CurrentRegion.ClearContents

Dim rowCount As Long, columnCount As Long
rowCount = UBound(arr,1)
columnCount = UBound(arr,2)
Worksheets("Input").Range("A1").Resize(rowCount,columnCount).Value = arr

标签： arraysvbaperformanceloops

解决方案

也许不使用 ClearContents 会加快速度。

此外，Select Case 可能会使代码更具可读性，但可能不会对性能产生任何影响。

Dim rng As Range
Dim arr As Variant
Dim I As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set rng = Worksheets("Input").Range("A1").CurrentRegion
    arr = rng.Value

    For I = LBound(arr, 1) + 1 To UBound(arr, 1)
        Select Case arr(I, 1)
            Case "N149933", "N103291", "NS11375", "N187037", "N132921", "NS03359", "N102301"
                arr(I, 32) = "QP"
                arr(I, 33) = "Foundation and Endowment (Charities)"
            Case "NS35481"
                arr(I, 33) = "Corporate"
            Case "NS07301"
                arr(I, 32) = "QP"
                arr(I, 33) = "Public Fund (Government)"
            Case "NS29802", "NS29803", "NS29801"
                arr(I, 33) = "Insurance"
            Case Else
                arr(I, 33) = "High Net Worth Individuals"
        End Select
    Next I

    rng.Value = arr
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

推荐阅读