VBA中使用工作表函数：如何加速宏运行？

Hey there! I totally get how frustrating slow VBA macros can be—especially when you're just starting out and trying to get things working. Let's break down why your current code is dragging, and fix it up to run way faster.

Why Your Current Code Is Slow #

The biggest culprits here are:

• Frequent Select/Activate calls: Every time you select a cell, VBA has to communicate with Excel's interface, which adds tons of unnecessary overhead.
• Row-by-row loop: Setting formulas one row at a time is inefficient. Excel is built to handle bulk operations on entire ranges in a single step.

Optimized Code #

Here's a revised version that cuts out the slow parts and leverages Excel's bulk operations:

Sub FastFormulaFill()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim targetRangeJ As Range, targetRangeK As Range, targetRangeL As Range, targetRangeM As Range
    
    ' Turn off performance-hogging features temporarily
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Set ws = ThisWorkbook.Worksheets("Sheet2") ' Make sure this matches your sheet name
    
    ' Find the last row with data in column I (since you check column I for emptiness)
    lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
    
    ' Define the entire ranges for each column you need to fill
    Set targetRangeJ = ws.Range("J2:J" & lastRow)
    Set targetRangeK = ws.Range("K2:K" & lastRow)
    Set targetRangeL = ws.Range("L2:L" & lastRow)
    Set targetRangeM = ws.Range("M2:M" & lastRow)
    
    ' Assign formulas to entire ranges in one go
    targetRangeJ.FormulaR1C1 = "=INDEX(Sheet1!C[-4],MATCH(Sheet2!R[0]C[-6],Sheet1!C[-9],0))"
    targetRangeK.FormulaR1C1 = "=INDEX(sheet1!C[-6],MATCH(sheet2!RC[-7],sheet1!C[-10],0))"
    targetRangeL.FormulaR1C1 = "=INDEX(sheet3!C[-10],MATCH(sheet2!RC[-2],sheet1!C[-11],0))*sheet2!RC[-1]*sheet2!RC[-10]"
    targetRangeM.FormulaR1C1 = "=IF(sheet2!RC[-12]=""BUY"",SUMIFS(sheet4!C[-7],sheet4!C[-12],sheet2!RC[-6],sheet4!C[-11],sheet2!RC[-9])+sheet2!RC[-11],SUMIFS(sheet4!C[-7],sheet4!C[-12],sheet2!RC[-6],sheet4!C[-11],sheet2!RC[-9])-sheet2!RC[-11])"
    
    ' Restore Excel's normal behavior
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Key Improvements Explained #

• No more Select/Activate: We directly reference ranges using worksheet variables, which skips all the interface overhead.
• Bulk formula assignment: Instead of looping through each row, we set the formula for the entire column range in one line. This is exponentially faster.
• Disabled background features: Turning off screen updating, automatic calculation, and events prevents Excel from doing extra work while your macro runs. We restore these settings at the end so Excel behaves normally afterward.
• Efficient last row detection: Using End(xlUp) is a reliable way to find the last row with data, instead of checking each row one by one.

Extra Tips for Even Better Performance #

• If your dataset is extremely large (100k+ rows), you could consider converting the formulas to values after calculating (if you don't need the formulas to stay dynamic). Add this line after assigning formulas:

  ws.Range("J2:M" & lastRow).Value = ws.Range("J2:M" & lastRow).Value
  

• Use With statements if you're referencing the same worksheet multiple times to simplify code and add a tiny performance boost.

内容的提问来源于stack exchange，提问作者Wirfelt1