excel 将单元格赋值更改为区域和数组赋值以提高性能

sd2nnvve  于 2023-02-14  发布在  其他
关注(0)|答案(1)|浏览(125)

我正在审查旧版VBA代码以提高性能。
我想将单元格赋值改为区域和数组赋值。
如何将单元格引用转换为区域?

Sheets("Model").Activate
YearRange = Range("C7:EZ7").value

loopvar = 1
Do While YearRange(1, loopvar) <> "" And Right(Trim(YearRange(1, loopvar)), 1) <> "E"
    strValue = Trim(YearRange(1, loopvar))
    Year = CInt(strValue)
  
    GetSpecificData Year, RptPe, RptAvg
  
    If (RptPe <> 0) Then Cells(508, loopvar + 2).value = RptPe
    If (RptAvg <> 0) Then Cells(509, loopvar + 2).value = RptAvg

    loopvar = loopvar + 1
Loop

单元格赋值大约是15。我放了两个供参考。通常有30年或更长时间,所以运行这段代码通常需要大约20秒或更长时间。
我在另一个地方有一个类似的循环,但不是使用单元格,而是使用范围,我能够创建一个数组,然后将数组赋值给范围,并将时间减少到不到一秒。
GetSpecificData从一年中获取数据,并用这些数据填充变量,因此执行此操作所需的时间可以忽略不计。
瓶颈似乎是数据到单元格的分配。

dvtswwa3

dvtswwa31#

由于信息有限,很难说可能的原因。
但可能的罪魁祸首是XL中的自动重新计算?您的工作表现在在每个Cells(508, loopvar + 2).value = RptPe之后重新计算整个工作表,这可能是执行缓慢的原因。
也许你可以像下面这样用Application.Calculation = xlCalculationManual来修饰While ... Loop,但是要确保一些错误处理,否则如果出现错误或者代码中断,你的应用程序会“卡”在非计算模式中。

Sub Something()
    On Error GoTo ErrorExit
    Application.Calculation = xlCalculationManual        

    Sheets("Model").Activate
    YearRange = Range("C7:EZ7").Value

    loopvar = 1
    Do While YearRange(1, loopvar) <> "" And Right(Trim(YearRange(1, loopvar)), 1) <> "E"
        strValue = Trim(YearRange(1, loopvar))
        Year = CInt(strValue)
    
        GetSpecificData Year, RptPe, RptAvg
        
        If (RptPe <> 0) Then Cells(508, loopvar + 2).Value = RptPe
        If (RptAvg <> 0) Then Cells(509, loopvar + 2).Value = RptAvg
      
        loopvar = loopvar + 1
    Loop
    
ErrorExit:
    Application.Calculation = xlCalculationAutomatic
    
End Sub

相关问题