<

Go Premium for a chance to win a PS4. Enter to Win

x

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

Published on
13,035 Points
3,535 Views
Last Modified:
Awarded
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
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

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!

Cheers,

Notes:
* I used the example of a string - in reality it could be almost anything
0
Comment
Author:DrTribos
  • 3
  • 2
6 Comments
 
 

Administrative Comment

by:Eric AKA Netminder
DrTribos,

Congratulations; your article has been published. Additionally, it has been awarded EE-Approved status as well.

ericpete
Page Editor
0
 
LVL 46

Expert Comment

by:aikimark
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.
http://msdn.microsoft.com/en-us/library/office/aa211923(v=office.11).aspx

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.
0
 
LVL 15

Author Comment

by:DrTribos
@aikimark,

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,
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 46

Expert Comment

by:aikimark
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 Microsoft.com.  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.
0
 
LVL 15

Author Comment

by:DrTribos
@aikimark,

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...
0
 
LVL 46

Expert Comment

by:aikimark
Here is a link to my (just published) article:
http:/A_14015-Iterating-Office-Enumeration-Constants.html
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Other articles by this author
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month