如何用Select Case或VLOOKUP优化VBA冗长重复的If-Else分支代码

原代码重复度极高，仅wsName对应的部分参数值有差异，其余逻辑完全一致，以下提供两种可落地的优化方案：

方案一：嵌套Select Case重构 #

利用Select Case的多条件匹配特性，按wsName分组后再处理rate_value分支，合并重复逻辑，大幅减少冗余代码：

' 先初始化变量，避免未赋值的异常
snapdownvol = 0
sweep_value = 0
sweep_value_max = 0

Select Case wsName
    Case "Test-3"
        Select Case rate_value
            Case Is < 50
                snapdownvol = 95
            Case 50
                snapdownvol = 98
                sweep_value = 49.8
                sweep_value_max = 50.2
            Case 100, 200
                snapdownvol = 110
                sweep_value = rate_value - 0.6
                sweep_value_max = rate_value + 0.2
            Case Is > 200
                MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
        End Select
    Case "Test-6", "Test-8"
        Select Case rate_value
            Case Is < 50, 50
                snapdownvol = 98
                If rate_value = 50 Then
                    sweep_value = 49.8
                    sweep_value_max = 50.2
                End If
            Case 100, 200
                snapdownvol = 125
                sweep_value = rate_value - 0.6
                sweep_value_max = rate_value + 0.2
            Case Is > 200
                MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
        End Select
End Select

额外优化点：将rate_value=100和200的分支合并，用计算式替代硬编码；把逻辑完全一致的Test-6和Test-8合并为同一个Case。

方案二：数组模拟VLOOKUP配置表 #

如果后续需要频繁新增wsName或调整参数，用配置表的方式更易维护——把所有参数集中存储，通过匹配查找获取对应值：

' 初始化变量
snapdownvol = 0
sweep_value = 0
sweep_value_max = 0

' 定义配置数组：[wsName, rate阈值, snapdownvol, sweep_value, sweep_value_max]
Dim configArr As Variant
configArr = Array( _
    Array("Test-3", "<50", 95, 0, 0), _
    Array("Test-3", "50", 98, 49.8, 50.2), _
    Array("Test-3", "100", 110, 99.8, 100.2), _
    Array("Test-3", "200", 110, 199.4, 200.4), _
    Array("Test-6", "<50", 98, 0, 0), _
    Array("Test-6", "50", 98, 49.8, 50.2), _
    Array("Test-6", "100", 125, 99.8, 100.2), _
    Array("Test-6", "200", 125, 199.4, 200.4), _
    Array("Test-8", "<50", 98, 0, 0), _
    Array("Test-8", "50", 98, 49.8, 50.2), _
    Array("Test-8", "100", 125, 99.8, 100.2), _
    Array("Test-8", "200", 125, 199.4, 200.4) _
)

' 遍历配置数组匹配参数
Dim i As Integer
For i = LBound(configArr) To UBound(configArr)
    If configArr(i)(0) = wsName Then
        Select Case configArr(i)(1)
            Case "<50"
                If rate_value < 50 Then
                    snapdownvol = configArr(i)(2)
                    sweep_value = configArr(i)(3)
                    sweep_value_max = configArr(i)(4)
                    Exit For
                End If
            Case "50", "100", "200"
                If rate_value = Val(configArr(i)(1)) Then
                    snapdownvol = configArr(i)(2)
                    sweep_value = configArr(i)(3)
                    sweep_value_max = configArr(i)(4)
                    Exit For
                End If
        End Select
    End If
Next i

' 单独处理rate_value>200的异常情况
If rate_value > 200 Then
    MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
    sweep_value = 0
    sweep_value_max = 0
End If

这种方式的优势是：所有参数集中管理，新增或修改时只需调整configArr，无需改动逻辑代码；如果参数数量较多，还可以把配置存到工作表中，用WorksheetFunction.VLookup直接读取，维护成本更低。

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