Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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:
Intellisense - for line spacing ruleThe thing is, although you select words, these are actually numbers...
Words resolve to 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

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

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

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

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 "' How to call..."
                      Debug.Print "' Dim myVar as String"
                      Debug.Print "' <.property> = " & str & "(myVar)"
                      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

Open in new window

It’s worth noting a few points about the code.  There are limitations and opportunities:
no error checking (assumes a table exists)
in how big a table can be processed, Immediate window can hold 199 lines (Wd2010)
could write to a text file and save as *.bas so it can be imported
could process several tables at once (could put function name in header row)
the date comment is in US Format which confuses us in Australia (hence the dd/mm/yyyy reminder)
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!


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

Comments (5)

aikimarkGet vaccinated; Social distance; Wear a mask
Top Expert 2014

1. You would be better off populating a dictionary object rather than creating routines with this/these Select Case statements.

2. You should mine the name/value pairs from this web site using the MSXML2.XMLHTTP object.  Once you have retrieved the responsetext, you can parse the result with a DOM object or the regular expression object.  Be aware that the name values have a trailing space and should be trimmed before saving.

NOTE: There are other sources of these values from people who have encountered your problem.

An alternative to #2 is to use the TypeLibInfoFromFile() method from the TypeLibInfo library.  The constants are in the msword.olb file.

If you want to construct code, you can use the VBE (VB.Project) object to create the code directly.  Be aware that there are character limits to routines.  It used to be 32K, but that was a long time ago.



Thank you for your comment. As I said, I'm a beginner (& I'm here to learn).

I did consider using a scripting dictionary (and have several in my code already) however as far as I know a Scripting Dictionary Object would have to be created & (more to the point) populated at run time whereas the Select Case Function is right there, ready to go.  So I expect the Select Case to be overall faster; particularly IF it is only called a few times (and particularly if my favored options are nearer the top).

That said, I would be interested to know from a technical perspective why you say:
1. You would be better off populating a dictionary object rather than creating routines with this/these Select Case statements.
And, when (if ever) you'd advocate using select case.

NOTE: Yes I'm sure there are other sources - I could have googled. My code creates a select case function from a 2 column table, it can contain any information...  

Your comments on: mining websites for information, using TypeLibInfoFromFile() and using VBE (VB.Project) object to create the code directly are really interesting and I will follow-up.

Thanks & Cheers,
aikimarkGet vaccinated; Social distance; Wear a mask
Top Expert 2014

Reading a delimited file is very fast and provides you much more flexibility than hard-coded Select Case statements.

You can use the .INI format or some other standard delimited format, such as CSV or tab-delimited, pipe-delimited, etc.  You would use the intrinsic functions or API calls to read INI files and use FileSystemObject.TextStream object to read an ASCII text file.  You gain a lot of flexibility in this area, since you only need to update the delimited file and not the code that consumes it.

If using a delimited text file (non-INI format), you have two fairly simple and fast methods of parsing the data:
* The VBScript.regexp object (regular expression engine)
*The Split() function to parse the data by lines and then iterating each line data, using the Split() function to parse the name/value pairs.

As you are parsing the data, you populate a dictionary object with the name/value pairs.  The name (string data) is the key to the item and its numeric value is the data associated with that (key) name.  All of this takes place in a couple of seconds, or less.  Once populated, you exploit the power and speed of the dictionary object to hash into its data store with your string value and access the associated numeric value.

I have written the first of several articles on this.  The first article is under editor review now.  The first article uses the tlbinf32.dll.  The second article will probably be the programmatic mining of the enumeration data directly from the OLB files, using VBA code.  The third article will be the mining of the data from  I will definitely show the externalization (persistence) of the data in the third article.

With hash tables, you don't really worry about optimizing the most frequently occurring keys/names at the start of the list (Case clause order).  Back in the late 90s, I gave a user group presentation that looked at the performance of the Select Case statement and the comparison of string values was MUCH slower that the comparison of numeric values.



Thanks for the followup. I'm very much looking forward to reading your articles... I wish someone had told me all that about a year ago (on reflection they might have but I didn't understand).  

I have also used INI files but only to grab single items of information.  However now, where I need the flexibility you mention, I am moving to XML and find it satisfactorially fast.

I still think select case has a place in code, at least as much as an if statement, but certainly agree that depending on the task other approaches may be more appropriate.

In the meanwhile I hope the article serves to help other learners understand what select case is, how it can be used, and provides some insight into programming as they "learn to walk, before they run".  

The thing that occurred to me was that here I am performing a repetitive (programming) task so that I did not have to perform a repetitive (documenting) task... so I had a crack (1st attempt) at automating the programming process...
aikimarkGet vaccinated; Social distance; Wear a mask
Top Expert 2014

Here is a link to my (just published) article:

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.