Day38 - 代码优化(1)

今日学习目标

  • 掌握代码重构技术
  • 提高代码执行效率
  • 改进代码可读性

知识点

### 1. 代码重构原则
- 单一职责：每个过程只做一件事
- DRY原则：避免重复代码
- KISS原则：保持简单直接
### 2. 性能优化技巧
- 减少屏幕更新：Application.ScreenUpdating
- 避免Select/Activate
- 使用数组代替单元格操作
- 合理使用With语句
### 3. 变量和常量管理
- 使用有意义的命名
- 统一变量命名规范
- 使用常量代替硬编码

今日代码

' 优化前：低效的代码
Sub OldProcessData()
    Dim i As Long
    Application.ScreenUpdating = False

    For i = 1 To 10000
        Worksheets("Sheet1").Select
        Cells(i, 1).Select
        Selection.Value = i
    Next i

    Application.ScreenUpdating = True
End Sub

' 优化后：高效的代码
Sub NewProcessData()
    Dim i As Long
    Dim ws As Worksheet
    Dim dataArr() As Variant

    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet1")

    ' 使用数组批量操作
    ReDim dataArr(1 To 10000, 1 To 1)
    For i = 1 To 10000
        dataArr(i, 1) = i
    Next i

    ' 一次性写入
    With ws
        .Range(.Cells(1, 1), .Cells(10000, 1)).Value = dataArr
    End With

    Application.ScreenUpdating = True
    MsgBox "处理完成！"
End Sub

' 提取公共函数
Sub UpdateSalesReport()
    Dim ws As Worksheet
    Set ws = Worksheets("销售")

    With ws
        Call FormatHeader(.Range("A1:F1"))
        Call SetNumberFormat(.Range("D:D"), "#,##0.00")
        Call AutoFitColumns(.Range("A:F"))
    End With
End Sub

Sub FormatHeader(rng As Range)
    With rng
        .Font.Bold = True
        .Interior.Color = RGB(0, 51, 102)
        .Font.Color = RGB(255, 255, 255)
        .HorizontalAlignment = xlCenter
    End With
End Sub

Sub SetNumberFormat(rng As Range, fmt As String)
    rng.NumberFormat = fmt
End Sub

Sub AutoFitColumns(rng As Range)
    rng.Columns.AutoFit
End Sub

练习题

  1. 将重复的数据验证代码提取为公共函数
  2. 使用数组改写单元格循环操作
  3. 添加错误处理和日志记录