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"
.ParagraphFormat.LineSpacingRule = strLSR
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"
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.
Dim strLSR As String ' Line Spacing Rule
strLSR = "wdLineSpaceSingle"
.ParagraphFormat.LineSpacingRule = myEnumLineSpace(strLSR) <-- = result of a function
'.ParagraphFormat.LineSpacingRule = strLSR <-- does not work
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 Function 'myEnumLineSpace
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
' Take the pain out of creating Select Case for Enumerated
' items when MS Office is expecting a number instead of
' the string.
' 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
Debug.Print " Case Else : " & str & " = " & strDefault
Debug.Print " End Select"
Debug.Print "End Function '" & str
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
. 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