Tidying up If…End If code (Word97 Sr2 Win95)

Topic

New Reply

I have a macro that builds an e-mail list (from Chris Rae) that I have modified. One section contains the following code:

‘ Add Office Services

If regional(0) = “Hampshire” Then

myMailItem.Recipients.Add “Adams, Julie”

End If

If regional(0) = “Kent” Then

myMailItem.Recipients.Add “Pennels, Debbie”

End If

If regional(0) = “Sussex” Then

myMailItem.Recipients.Add “Robertson, Suzanne”

End If

If regional(0) = “Isle of Wight” Then

myMailItem.Recipients.Add “Kitching, Janette”

End If

This works okay but is there any way to shorten this selection process – by using a loop or counter?

Reply | Quote

Replies

One way:

Dim strAddressee As String

Select Case regional(0)
   Case "Hampshire"
      strAddressee = "Adams, Julie"
   Case "Kent"
      strAddressee = "Pennels, Debbie"
   Case "Sussex"
      strAddressee = "Robertson, Suzanne"
   Case "Isle of Wight"
      strAddressee = "Kitching, Janette"
End Select
myMailItem.Recipients.Add strAddressee

(although admittedly it’s not actually any shorter – just a bit more efficient in that once it makes a ‘hit’, it will exit the Select Case structure, whereas all of the those If statements will always run, even it it gets a ‘hit’ on the first If test…)

Gary

Reply | Quote

If you have a few hundred checks to do then you might want to define arrays such as the following, but I generally prefer the easier maintenance and readability of Select Case

Sub test()
Dim varCounties As Variant
Dim varAddressees As Variant
Dim i As Integer
   
varCounties = Array("Hampshire", "Kent", "Sussex", "Isle of Wight")
varAddressees = Array("Adams, Julie",  _
                      "Pennels, Debbie", _
                      "Robertson, Suzanne", _
                      "Kitching, Janette")
   
For i = 0 To UBound(varCounties)
    If varCounties(i) = regional(0) Then
        myMailItem.Recipients.Add varAddressees(i)
        Exit For
    End If
Next i
   
End Sub

StuartR

Reply | Quote

Thanks very much for the help – printed them off and in my “Useful Tips file”.

David

Reply | Quote