Excel 76000行填充自定义单元格取色VBA函数失效问题咨询

• 自定义VBA函数的运行开销远高于Excel原生函数，76000行公式逐行调用VBA运行时、访问单元格对象的填充色属性，会占满Excel的UI线程，导致操作无响应。
• 未关闭易失性的自定义函数会在填充过程中触发重复重算，进一步放大性能开销。
• 开启自动重算时，每填充一行公式就会触发一次全局计算，延长操作时间，看起来像操作失效。

方案1：临时调整计算设置完成填充 #

1. 点击顶部菜单栏「公式」→「计算选项」→勾选「手动」
2. 在B1输入公式后，点击名称框输入B1:B76000按回车快速选中整列区域，按下Ctrl+D批量向下填充
3. 填充完成后将计算选项改回「自动」即可
   该方案无需修改代码，适合临时使用场景

方案2：优化自定义函数降低重算开销 #

给原自定义函数添加关闭易失性的声明，减少不必要的重算触发：

Function ColorIndex(CellColor As Range)
    Application.Volatile False
    ColorIndex = CellColor.Interior.ColorIndex
End Function

修改后再执行填充操作，卡顿会明显缓解。

方案3：直接用VBA批量生成结果（推荐） #

不需要输入和填充公式，直接运行VBA一次性写入所有结果，7万多行数据仅需几秒即可完成：

Sub 批量生成颜色对应文本()
    Dim ws As Worksheet
    Dim i As Long
    Dim 最后行 As Long
    
    Set ws = ActiveSheet ' 可替换为你的工作表名称，比如Set ws = Sheets("数据页")
    最后行 = 76000 ' 行数动态的话可以改成：最后行 = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 关闭无关功能提升运行速度
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For i = 1 To 最后行
        Select Case ws.Cells(i, "A").Interior.ColorIndex
            Case 4: ws.Cells(i, "B").Value = "new"
            Case 3: ws.Cells(i, "B").Value = "old"
            Case Else: ws.Cells(i, "B").Value = "" ' 其他颜色对应内容可自行修改
        End Select
    Next i
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

运行上述宏即可直接得到所有结果，不会出现无响应问题。

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