excel - 在 VBA 中使用字符串值创建存储桶

Question

我正在尝试从国家代码的字符串值创建一桶相应的区域。我已经使用 for 循环和嵌套的 if 条件编写了代码，如下所示:
数据:

Column A.         Column B
Country code.     Region.

代码:

Sub bucket()
    Dim i As Integer
    
    For i = 2 To 4321
        If Range("A" & i).Value = "IN" Or Range("A" & i).Value = "CN" Then
            Range("B" & i).Value = "ASIA"
            
        ElseIf Range("A" & i).Value = "UK" Or Range("A" & i).Value = "GB" Then
            Range("B" & 1).Value = "EMEA"
        
        ElseIf Range("A" & i).Value = "US" Or Range("A" & i).Value = "CAN" Then
            Range("B" & i).Value = "USAI"
        
        Else
            Range("B" & i).Value = "other"
            
        End If
    Next i

End Sub

我只是想检查一下 vba 中的字符串存储桶是否有任何替代解决方案。

Answer 1

更清洁的替代方案是 Select Case

sub bucket()
  Dim i As Integer
  For i = 2 To 4321
    Select Case Range("A" & i)
      Case "IN","CN"
        Range("B" & i).Value = "ASIA"
      Case "UK", "GB"
        Range("B" & i).Value = "EMEA"
      Case "US", "CA"
        Range("B" & i).Value = "USAI"
      Case Else
        Range("B" & i).Value = "other"
    End Select
  Next i
End sub

或者变得更漂亮:

sub bucket()
  Dim i As Integer

  For i = 2 To 4321
    With Range("B" & i)
      Select Case UCase$(Range("A" & i).Value)
        Case "IN","CN"
          .Value = "ASIA"
        Case "UK", "GB"
          .Value = "EMEA"
        Case "US", "CA"
          .Value = "USAI"
        Case Else
          .Value = "other"
      End Select
    End With
  Next

End sub

这些差异使其更易于阅读，更易于稍后添加更多选项，并且如果稍后要更改列，则更易于修改。