If Else Tree Using Case Function or VLOOKUP-CodePudding

I have written this code that at the moment works, however, my if/else statement tree is very long and repetitive with only a few changes being the values the variables are set to for each case. I have read up on using the VBA Select Case statement is good to use when testing multiple conditions, for my case testing what the wsName is and what the rate_value is. Here is what my code currently looks like if someone could provide an outline as to how I could either use the Select Case method or using a VLOOKUP table that would be greatly appreciated. For reference, wsName and rate_value are defined before these lines are called.

If wsName = "Test-3" Then
   If rate_value < 50 Then
       snapdownvol = 95
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 110
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 110
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
ElseIf wsName = "Test-6" Then
   If rate_value < 50 Then
       snapdownvol = 98
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 125
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 125
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
ElseIf wsName = "Test-8" Then
   If rate_value < 50 Then
       snapdownvol = 98
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 125
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 125
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
End If

CodePudding user response：

As there is a lot of repetition, I would go for the Case Select:

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