Select Case Automation: Taking the Pain out of Creating Select Case for Enumerated Lists

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case function just scroll down until you see the big dots.

In VBA (and most other programming languages) it is common to find collections of items enumerated.  For example MS word uses enumeration for all manner of things including paragraph formatting line spacing rules:
The thing is, although you select words, these are actually numbers...
The image above shows that VBA prefers to spit out a number when you interrogate an enumerate item.  The same applies for colors, shapes, built-in dialogues, and pretty much anything else you can think of.  Searching Enumeration in the VBA Help yields a ~20 page list of collections of enumerated items, about 500 documented collections.

I was in the unfortunate position where I had standard (i.e. built in) ‘enum’ values stored as strings but was unable to use them because MS Word was expecting a number. The following code, for example, does NOT work:

Dim strLSR as String
                      strLSR = "wdLineSpaceMultiple"
                      With ActiveDocument.Styles("myStyle")
                           .ParagraphFormat.LineSpacingRule = strLSR
                      End With

Select all Open in new window

It produces a type mismatch error.  

I guess the error message was trying to tell me that a "string" <> "integer"...  In essence it seem the problem is that strLSR = "wdLineSpaceMultiple" when it should be wdLineSpaceMultiple (i.e. without the quotation marks).

That said, it is a curious fact that the "Quick Info" (Ctrl + i) output in the Immediate Window from this:

strLSR = "wdLineSpaceMultiple"
                      debug.print strLSR

Select all Open in new window

actually resolves to the numerical value "5".  This led me on a wild goose chase for a solution that would automagically convert my string to its numerical value.

Hours went past. My eyes glazed over and I eventually accepted that such a solution does not really exist.

It seems that the only viable solutions involve some sort of predefined translation table - what a pain!  A conversion, e.g. simply stripping the quotation marks away, would make the code dynamic, and not dependent predefined translation tables.  But that just does not seem possible...

When it comes to translation there are a few options including arrays and dictionaries, but for me Select Case was convenient…

If you don't know about Select Case you can think of it as a BIG IF.  Using Select Case makes it possible to assign a data to a string* then scroll down a list of conditions until you find a case statement (i.e. a condition) that matches your string data.  Once found it stops looking.

In the following, the 1st block of code assigns a data (wdLineSpaceSingle) to a string (strLSR).  The string is passed to a function called myEnumLineSpace.

The 2nd block of code indexs through the predefined list of strings (conditions, case statements) and if a match is found will execute the corresponding code.  If a match is NOT found the "Case Else" code is executed.  

Sub codeSnippet()
                      Dim strLSR As String  ' Line Spacing Rule
                      strLSR = "wdLineSpaceSingle"
                      With ActiveDocument.Styles("Normal")
                      .ParagraphFormat.LineSpacingRule = myEnumLineSpace(strLSR)  <-- = result of a function
                      '.ParagraphFormat.LineSpacingRule = strLSR  <-- does not work
                      End With
                      End Sub

Select all Open in new window

Function myEnumLineSpace(myVar As String) As Integer
                      By: Dr Tribos 19/06/2014 (dd/mm/yyyy)
                      
                      ' How to call...
                      ' Dim myVar as String
                      ' <.property> = myEnumLineSpace(myVar)  
                      
                       Select Case myVar
                         Case "wdLineSpace1pt5": myEnumLineSpace = 1
                         Case "wdLineSpaceAtLeast": myEnumLineSpace = 3
                         Case "wdLineSpaceDouble": myEnumLineSpace = 2
                         Case "wdLineSpaceExactly": myEnumLineSpace = 4
                         Case "wdLineSpaceMultiple": myEnumLineSpace = 5
                         Case "wdLineSpaceSingle": myEnumLineSpace = 0
                         Case Else: myEnumLineSpace = 0
                       End Select
                      End Function 'myEnumLineSpace

Select all Open in new window

The name of the string in block 1 is passed to the function - the data in strLST and myVar is the same.  But the function itself returns an integer.  In other words, the function 'myEnumLineSpace' accepts a string and returns an integer.

In the example strLSR will match with "wdLineSpaceSingle" and the value of the function will be set to 0.  At least now I have an integer that I can use to specify the line spacing rule for my paragraph.  But writing out all the conditions for a Select Case statement can be painful (and repetitive); and I didn't much like the idea of manually writing out select case for enumerated items.

The following code goes some way to automating the process. The manual steps are to:

1. Find the list of Enumerated items in Word Help

2. Copy the list

3. Paste the list into MS Word (it will appear as a table)

4. Run EnumToCase (it will write to the Immediate Window)

5. Copy from Immediate Window, paste to code

6. Call the function from the code

Sub EnumToCase()
                      ' PURPOSE:
                      ' Take the pain out of creating Select Case for Enumerated
                      ' items when MS Office is expecting a number instead of
                      ' the string.
                      
                      ' INSTRUCTIONS:
                      ' 1. Find the list of Enumerated items in Word Help
                      ' 2. Copy the list
                      ' 3. Paste the list into MS Word (it will appear as a table)
                      ' 4. Run EnumToCase (it will write to the Immediate Window)
                      ' 5. Copy from Immediate Window, paste to code
                      
                      Dim str As String
                      Dim strDefault As String
                      Dim i As Integer
                      Dim oTbl As Table
                      Set oTbl = ActiveDocument.Tables(1)
                      
                      str = InputBox("Enter a FunctionName, no spaces")
                      strDefault = InputBox("Enter a Default value (for Case Else)")
                      
                      Debug.Print "Function " & str & "(myVar as String) as Long"
                      Debug.Print "' By: " & Application.userName & " " & Date & " (dd/mm/yyyy)"
                      Debug.Print
                      Debug.Print "' How to call..."
                      Debug.Print "' Dim myVar as String"
                      Debug.Print "' <.property> = " & str & "(myVar)"
                      Debug.Print
                      Debug.Print " Select Case myVar"
                      
                      For i = 1 To oTbl.Rows.Count
                          Debug.Print "   Case " & Chr(34) & Replace(oTbl.Rows(i).Cells(1).Range.Text, ChrW(13) & ChrW(7), "") & Chr(34) & ": " & str & " = " & Replace(oTbl.Rows(i).Cells(2).Range.Text, ChrW(13) & ChrW(7), "")
                          If i = 101 Then
                          Stop ' Copy from Immediate
                          End If
                      Next i
                      
                      Debug.Print "   Case Else : " & str & " = " & strDefault
                      Debug.Print " End Select"
                      Debug.Print "End Function '" & str
                      End Sub

Select all Open in new window

It’s worth noting a few points about the code.  There are limitations and opportunities:
On the upside, the function is better than typing out manual lists and can be used to process custom tables to make select case easier. The function can be reused.  

Also, while I’m at it, I’d like to acknowledge GrahamSkan, Chris Bottomly and Rgonzo1971.  With their helpful advice and elegant code these gentlemen were unknowingly the inspiration behind this offering :-)  

I welcome any feedback on how the code can be improved - or better still, if there is a more efficient (faster) / flexible (no predefined tables) way to convert strings to integers for enumerated items please let me know!

Cheers,

Notes:
* I used the example of a string - in reality it could be almost anything