VBA - 减少 if 内的 for 循环

减少 VBA 中 if 内的 for 循环是提高代码效率和性能的一种常见方法。在编写 VBA 代码时，经常会遇到需要在 if 语句内部进行循环的情况。然而，在大规模数据处理或复杂算法中，频繁的循环可能会导致代码执行速度变慢，因此需要找到一种更高效的解决方案。

vba
Sub TraditionalMethod()
    Dim studentList As Range
    Dim student As Range
    
    Set studentList = Range("A2:C1000")
    
    For Each student In studentList
        If student.Offset(0, 1).Value >= 90 And student.Offset(0, 2).Value = "Class A" Then
            ' 处理符合条件的学生信息
            Debug.Print student.Value
        End If
    Next student
End Sub

vba
Sub EfficientMethod()
    Dim studentList As Range
    Dim studentData() As Variant
    Dim student As Variant
    Dim filteredData() As Variant
    Dim i As Long
    Dim j As Long
    
    Set studentList = Range("A2:C1000")
    studentData = studentList.Value

vba
    j = 1 ' 记录筛选后的数据行数
    
    For i = LBound(studentData) To UBound(studentData)
        If studentData(i, 2) >= 90 And studentData(i, 3) = "Class A" Then
            filteredData(j, 1) = studentData(i, 1)
            j = j + 1
        End If
    Next i

vba
    ' 处理筛选后的数据
    For i = LBound(filteredData) To UBound(filteredData)
        Debug.Print filteredData(i, 1)
    Next i
End Sub

vba
Function Fibonacci(n As Long) As Long
    If n <= 1 Then
        Fibonacci = n
    Else
        Fibonacci = Fibonacci(n - 1) + Fibonacci(n - 2)
    End If
End Function

vba
Function OptimizedFibonacci(n As Long) As Long
    Dim fibArray() As Long
    Dim i As Long
    
    ReDim fibArray(n)
    
    fibArray(0) = 0
    fibArray(1) = 1
    
    For i = 2 To n
        fibArray(i) = fibArray(i - 1) + fibArray(i - 2)
    Next i
    
    OptimizedFibonacci = fibArray(n)
End Function