Excel VBA Dealing with 64 values for one argument

Question

I have a VBA script I'm trying to run that reads incoming emails to an account and then marks the corresponding cell in a spreadsheet. My test run had 9 jobs it was looking for in a Nested IF Statement.

Like this:

  If InStr(itm.subject, "Test Backup") > 0 Then 
    J = 2
    ElseIf InStr(itm.subject, "TESTdchq") > 0 Then 
        J = 3
    ElseIf InStr(itm.subject, "TESTdynamics") > 0 Then 
        J = 4
    ElseIf InStr(itm.subject, "TEST-VSS-HQ") > 0 Then 
        J = 5
    ElseIf InStr(itm.subject, "TESTWSUS01") > 0 Then 
        J = 6
    ElseIf InStr(itm.subject, "TEST-Camera") > 0 Then 
        J = 7
    ElseIf InStr(itm.subject, "TEST-Vcenter") > 0 Then 
        J = 8
    ElseIf InStr(itm.subject, "TEST-View Connection") > 0 Then 
        J = 9
    ElseIf InStr(itm.subject, "TESTktsrv1") > 0 Then 
        J = 10
  End If

However one of my practical applications has 64 jobs. I need a more efficient way to assign the value to J based on the keyword in the subject of the email. Id assume I could do something with an array and then call the array and compare to the subject.

Here is the whole test script if that helps.

Sub ReconcileTest(itm As Outlook.MailItem)

  Dim xlApp As Excel.Application
  Dim ExcelWkBk As Excel.Workbook
  Dim FileName As String
  Dim PathName As String
  Dim J As Integer
  'J = will be used to declare the proper Job row

  PathName = "C:\Users\Owner\Dropbox\Backups\"
  FileName = "TESTReconcileSheet.xlsx"
  'Declare J
  If InStr(itm.subject, "Test Backup") > 0 Then 
    J = 2
    ElseIf InStr(itm.subject, "TESTdchq") > 0 Then 
        J = 3
    ElseIf InStr(itm.subject, "TESTdynamics") > 0 Then 
        J = 4
    ElseIf InStr(itm.subject, "TEST-VSS-HQ") > 0 Then 
        J = 5
    ElseIf InStr(itm.subject, "TESTWSUS01") > 0 Then 
        J = 6
    ElseIf InStr(itm.subject, "TEST-Camera") > 0 Then 
        J = 7
    ElseIf InStr(itm.subject, "TEST-Vcenter") > 0 Then 
        J = 8
    ElseIf InStr(itm.subject, "TEST-View Connection") > 0 Then 
        J = 9
    ElseIf InStr(itm.subject, "TESTktsrv1") > 0 Then 
        J = 10
  End If

  Set xlApp = Application.CreateObject("Excel.Application")
  With xlApp
    .Visible = True         ' Visible is used for debugging
    Set ExcelWkBk = xlApp.Workbooks.Open(PathName & FileName)
    With ExcelWkBk

      'VBA code to update workbook here
      Dim todaysDate As Date
      Dim D As Integer    
      Dim subject As String
      'D = will be used to declare the proper Date column

      todaysDate = Day(Now)
      D = todaysDate
      'Marksheet
      If InStr(itm.subject, "[Success]") > 0 Then
      .Sheets("sheet1").Cells(J, (D + 2)).Value = "S"
      .Sheets("Sheet1").Cells(J, (D + 2)).Interior.ColorIndex = 43
      ElseIf InStr(itm.subject, "[Failed]") > 0 Then
      .Sheets("sheet1").Cells(J, (D + 2)).Value = "F"
      .Sheets("Sheet1").Cells(J, (D + 2)).Interior.ColorIndex = 3
      ElseIf InStr(itm.subject, "[Warning]") > 0 Then
      .Sheets("sheet1").Cells(J, (D + 2)).Value = "W"
      .Sheets("Sheet1").Cells(J, (D + 2)).Interior.ColorIndex = 27
      End If

      .Save
      .Close
    End With
    .Quit
  End With
End Sub

1 Step better would be using [Select Case](http://msdn.microsoft.com/en-us/library/cy37t14y.aspx) so that your only evaluating `itm.subject` once. 2 steps better might be an array but I think your best bet is to use a [dictionary](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) for this. — Automate This, Nov 04 '14 at 14:49

score 3 · Accepted Answer · answered Nov 04 '14 at 15:01

3

I'd recommend using a dictionary for such a large number of variables. You can create a global dictionary if necessary but the following example is done locally:

Dim dict As New Scripting.Dictionary
Set dict = CreateObject("Scripting.Dictionary")

dict.Add "Test Backup", 2
dict.Add "TESTdchq", 3
dict.Add "TESTdynamics", 4
dict.Add "TEST-VSS-HQ", 5
dict.Add "TESTWSUS01", 6
dict.Add "TEST-Camera", 7
dict.Add "TEST-Vcenter", 8
dict.Add "TEST-View Connection", 9
dict.Add "TESTktsrv1", 10

Dim J As Integer
J = dict(itm.Subject)

MsgBox "J = " & J

Result:

enter image description here

answered Nov 04 '14 at 15:01

Automate This

30,726
11
60
82

Thanks! for the response, I'll try that now. – DrewtilDeath Nov 04 '14 at 15:07
Thank you for this solution, I think it gets me very close to where I need to be. However the script is currently not pulling The J variable out of the subject properly. To clarify the email subjects are in the following format "Test Backup [Success] completed at 2:30 11/4/2014." I'm not sure if Dictionary is by default looking for a keyword, or if I need to use Instr() for that? Thanks for your help so far. – DrewtilDeath Nov 04 '14 at 16:18
That's going to be a little more difficult with a dictionary. Is the string always in the same format in terms of the key is always followed by a space and `[`? If yes, try this: `J = dict(Trim(Split(itm.subject, "[")(0)))` – Automate This Nov 04 '14 at 16:30
Yes the subject format for every job is: %jobname% [%status%] %timestamp% %date% – DrewtilDeath Nov 04 '14 at 17:05
Man, I wish I had two more rep so I could up vote your answers! that did the trick. – DrewtilDeath Nov 04 '14 at 17:26
Given that the subject is variable but has structure, what about storing Regex objects in the dictionary and matching against the subjects? – Blackhawk Nov 04 '14 at 20:04
Blackhawk, I'm not very familiar with REgex objects in VBA. any resource you'd recommend I could read up on them with? – DrewtilDeath Nov 05 '14 at 18:04
Here is a helpful [link about Excel VBA Regex](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835) that I put together that gives several common examples. – Automate This Nov 05 '14 at 18:49

Excel VBA Dealing with 64 values for one argument

1 Answers1