使代码体系结构更好/使用VLookup表的建议

Question

我现在有这样的代码，我希望它检查字符串是否读取某个文本来将所有其他值设置为某种值，但是我已经构造了我的代码以包含很多if elseif语句，这些语句让我的代码效率降低了，速度也变慢了。有人对我该如何改变有什么建议吗？我确实开始了一个表，尝试使用VLookUp，但我无法让它工作。这是我正在使用的数据的图片

​

这是我指的代码的那一部分。

If wsName = "TEST-3" Then
If rate_value < 50 Then
   vol = 95
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    MsgBox "Less than 50."
ElseIf rate_value = 50 Then
   vol = 98
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 49.8
    sweep_value_max = 50.2
ElseIf rate_value = 100 Then
   vol = 110
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 99.8
    sweep_value_max = 100.2
Else: rate_value = 200
   vol = 110
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 199.4
    sweep_value_max = 200.4
End If

ElseIf wsName = "TEST-8" Then
If rate_value < 50 Then
   vol = 98
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    MsgBox "Less than 50."
ElseIf rate_value = 50 Then
   vol = 98
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 49.8
    sweep_value_max = 50.2
ElseIf rate_value = 100 Then
   vol = 125
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 99.8
    sweep_value_max = 100.2
Else: rate_value = 200
   vol = 125
    Call updateSD(sysnum, vol_rowindex, max, vol)
    Call updateSD(sysnum, vol_rowindex_1, max, vol)
    sweep_value = 199.4
    sweep_value_max = 200.4
End If
End If

Call updateSD(sysnum, rate_rowindex, typ, rate_value)
Call updateSD(sysnum, rate_rowindex_1, typ, rate_value)
Call updateSD(sysnum, rate_rowindex_1, min, sweep_value)
Call updateSD(sysnum, rate_rowindex_1, max, sweep_value_max)


Sub updateSD(sysnum As String, rowindex As Double, columnindex As Long, Value As Double) 
    Worksheets(sysnum).Cells(rowindex, columnindex) = Value
    Worksheets(sysnum).Cells(rowindex, columnindex).Interior.Color = vbYellow
End Sub

Answer 1

维护和易读性

• 没有测试！
• --这只是一次重写，目的是让它更易读(可能不像它)，并且更易于维护(您可以很容易地在代码的开头修改值)。
• 不会运行得更快，因为“瓶颈点”是对updateSD过程的多次调用。为了使其更快，您需要将值从范围写入数组(例如Data = rg.Value)，并在将值写回范围(例如rg.Value = Data)之前修改数组中的值。对于格式(突出显示)，可以使用urg.Interior.Color = vbYellow).

将单元格组合成一个范围，然后一次性格式化组合的范围(例如，Union )。

Sub Test()
    
    ' 1.) At the beginning of the procedure.
    
    ' Note that if you add more 'wsNames',
    ' you need to add more arrays to 'Vols'.
    Dim wsNames() As Variant
    wsNames = VBA.Array("TEST-3", "TEST-8")
    Dim RateValues() As Variant: RateValues = VBA.Array(50, 50, 100, 200)
    Dim Vols() As Variant: Vols = VBA.Array( _
        VBA.Array(95, 98, 110, 110), _
        VBA.Array(98, 98, 125, 125))
    Dim SweepValues() As Variant: SweepValues = VBA.Array( _
        VBA.Array(49.8, 99.8, 199.4), _
        VBA.Array(50.2, 100.2, 200.4))
    
    ' 2.) Code before the loop ???
    
    ' 2.a.) ???
        
    ' 2.b.)
    Dim Index As Variant: Index = Application.Match(wsname, wsNames, 0)
    Dim OutOfBounds As Boolean
        
    ' 3.) The loop
    For WhatEver ...
    
    ' 3.a.) Inside the loop:
    
        If IsNumeric(Index) Then
            Index = Index - 1 ' zero-based
            Select Case rate_value
            Case Is < RateValues(0)
                vol = Vols(Index)(0)
                MsgBox "Less than 50."
            Case RateValues(1)
                vol = Vols(Index)(1)
                sweep_value = SweepValues(0)(0)
                sweep_value_max = SweepValues(1)(0)
            Case RateValues(2)
                vol = Vols(Index)(2)
                sweep_value = SweepValues(0)(1)
                sweep_value_max = SweepValues(1)(1)
            Case RateValues(3)
                vol = Vols(Index)(3)
                sweep_value = SweepValues(0)(2)
                sweep_value_max = SweepValues(1)(2)
            Case Else ' rate_value not found?
                OutOfBounds = True
            End Select
        Else ' wsName not found?
            OutOfBounds = True
        End If
        
        If Not OutOfBounds Then
            
            updateSD sysnum, vol_rowindex, Max, vol
            updateSD sysnum, vol_rowindex_1, Max, vol
            
            ' ???
            updateSD sysnum, rate_rowindex, typ, rate_value
            updateSD sysnum, rate_rowindex_1, typ, rate_value
            updateSD sysnum, rate_rowindex_1, Min, sweep_value
            updateSD sysnum, rate_rowindex_1, Max, sweep_value_max
        
        End If

    Next WhatEver

End Sub