Multiple Select Case choices

[Roderick]

I'm using Word 2016 in Windows 10.

I had earlier prepared four Word templates (letterheads) for a client dealing with four languages. They have now asked me to put them into one template with the user choosing the preferred language they should be in.

Using Select Case and buttons on a userform is not a problem and everything works as it should plus a 'well-done' from the client. Their next question was "Can you do this in 13 different languages for the same template?"

The answer of course is "Yes" but here comes the question of tidyness into the programming of the procedures. The template has at least 30 different messages to the user such as "You're not in a table" or "You haven't selected a graphic" and so on.

Using four languages was easy: I just made four Select Case entries to handle each language with an appropriate messagebox. With 13 different languages this could be a rather long and involved business with a lot of code bloat.

I've struggled to try and come up with an answer but it always leads me back to Select Case entries. Can anyone suggest another method that could solve the challenge without creating 30+ Select Case statements of thirteen entries each?

Or is the answer "live with it, buddy!"?

[Paul_Hossler]

Maybe not the best way, but one way that doesn't require a lot of Selects

There are ways to embed the strings into the CustomXML for a document if you want to get sophisticated

Option Explicit
Public Enum eLang
    langEng = 0
    langGer = 2
    langSpa = 3
'   etc.
    langRus = 12
End Enum


Public Enum eText
    txtYes = 0
    txtNo = 1
    txtMaybe = 2
'   etc.
    txtAllDone = 29
End Enum

Public langInUse As eLang
Public aryText(langEng To langRus) As Variant


Sub Init()
    aryText(langEng) = Split("Yes;No;Maybe", ";")
    aryText(langGer) = Split("Ya;Nein;Vielleicht", ";")
    aryText(langSpa) = Split("Si;Prohibido;Tal vez", ";")

    langInUse = langEng
    MsgBox aryText(langInUse)(txtYes)
    MsgBox aryText(langInUse)(txtNo)
    MsgBox aryText(langInUse)(txtMaybe)


    langInUse = langGer
    MsgBox aryText(langInUse)(txtYes)
    MsgBox aryText(langInUse)(txtNo)
    MsgBox aryText(langInUse)(txtMaybe)


    langInUse = langSpa
    MsgBox aryText(langInUse)(txtYes)
    MsgBox aryText(langInUse)(txtNo)
    MsgBox aryText(langInUse)(txtMaybe)
End Sub

It should be possible to automatically set langInUse. This returns ENU for English-US

Option Explicit

Private Declare PtrSafe Function GetLocaleInfoEx Lib "kernel32" (ByVal lpLocaleName As LongPtr, ByVal LCType As Long, ByVal lpLCData As LongPtr, ByVal cchData As Long) As Long

Const LOCALE_SABBREVLANGNAME As Long = &H3
'Abbreviated name of the language. In most cases, the name is created by taking the
'two-letter language abbreviation from ISO Standard 639 and adding a third letter, as appropriate,
'to indicate the sublanguage. For example, the abbreviated name for the language corresponding
'to the English (United States) locale is ENU.
'ref: https://en.wikipedia.org/wiki/List_of_ISO_639-3_codes

Function LocalLanguage() As String
    Dim sLocaleName As String
    Dim sRetBuffer As String
    Dim nCharsRet As Long
    
    nCharsRet = GetLocaleInfoEx(0, LOCALE_SABBREVLANGNAME, StrPtr(sRetBuffer), 0)
    If nCharsRet > 0 Then
        sRetBuffer = String(nCharsRet, Chr$(0))
        nCharsRet = GetLocaleInfoEx(0, LOCALE_SABBREVLANGNAME, StrPtr(sRetBuffer), nCharsRet)
        LocalLanguage = Left(sRetBuffer, Len(sRetBuffer) - 1)
    End If
End Function

'you might get by with just the first 2 letters to get into the right family
Sub test()
    MsgBox LocalLanguage
End Sub

[Roderick]

Thanks, Paul for your helpful advice.

This has given me a lot of food for thought and trying to follow your logic.

Each template has 31 messages to the user, from 'you're not in a table' to 'your office details have not been set', etc. Some messages can be quite long - three lines of text at times.

However, your suggestion has given me a thought: at present I have an Access database which popupates the userform fields. These are extracted from the Access table and put in the Registry so that they are always available and they are always in the selected language.

Could there be a way of accessing the table and putting the user messages (all 31) on the document as they are required to appear without going through the Registry? I want to leave the existing userform structure as it is. It works OK.

How to distinguish the language used for these messages? Well, I saw your function above and tested it out and it works brilliantly - for me it's ENG.

Now, here comes the problem: knowing the client very well, I cannot guarantee that their locales will be properly selected. Here I came up with a thought: when the user selects the language to use in that template, it adds the country's dictionary to the Normal style and this affects all the other styles in the template.

I thought of referencing the dictionary set for the template and then adding the appropriate user message depending on that dictionary. When they close the document in question it reverts to whatever they have set in their own system.

My problem, is how can I access the table and drop those messages straight onto the document?

Any ideas, please?

[SamT]

It's just as easy to code for 31 languages as 13 with one simple, but long, selectcase.
Personally, I like Object Modules for this kind of issue.
Module "modUKEnglish" Code

Public Function Msg1() As String
Msg1 = "Excuse me, My Man. you're in the Wrong Table"
End Function

'Property Subs Are the more elegant method
Public Property Get Msg1() As String
Msg1 = "Excuse me, My Man. you're in the Wrong Table"
End Property

Select Case Code

Public UniversalMessages As Object 'Global Module Level Variable

Set UniversalMessages = Nothing
Select Case Language
Case UK English: Set UniversalMessages = New modUKEnglish
Case US English: Set UniversalMessages = New modUSEnglish
Case French: Set UniversalMessages = New modFrench
'
'
Case Else: Set UniversalMessages = New modUKEnglish 'Just in case
End Select

Getting a Message Code

If WrongTable Then MsgBox UniversalMessages.Msg1

Each Class Module is identical except the language of the messages. One Select Case statement with one line per language.

If you have to add a message, add it to your favorite language module, then copy it to all the others. Translate as you get to it. The Code will still work.

You can add a Method (Sub) to each Object Module to set all the Styles as needed. Just call it from the module's Class_Initialize sub. Be sure to kill all the previous Styles. {Sub Class_Terminate}

OH WAIT! Does Word use Class Modules? :

[Roderick]

Thanks Sam for the post.

Word does use Class Modules (and souting at myself: "I DO NEED TO LEARN ABOUT THEM MORE!") so I'm collecting some tutorials to see how I could use them properly.

I seem to be a very 'procedural' person creating standard modules. Perhaps now is the time to wake up and thing of using Class Modules.

All this may take me some time to work out, so please excuse any delay in reporting success (I must think positively!)

[SamT]

In the particular case of language selection, Class Modules make sense, because only one needs to be "active" at a time. It's nice that you can put Property Statements in a Class module, but Functions would work in Standard modules. Unfortunately all Standard Modules are "active" while the code is running.

[Paul_Hossler]

Thanks, Paul for your helpful advice.

This has given me a lot of food for thought and trying to follow your logic.

Each template has 31 messages to the user, from 'you're not in a table' to 'your office details have not been set', etc. Some messages can be quite long - three lines of text at times.

However, your suggestion has given me a thought: at present I have an Access database which popupates the userform fields. These are extracted from the Access table and put in the Registry so that they are always available and they are always in the selected language.

Could there be a way of accessing the table and putting the user messages (all 31) on the document as they are required to appear without going through the Registry? I want to leave the existing userform structure as it is. It works OK.

How to distinguish the language used for these messages? Well, I saw your function above and tested it out and it works brilliantly - for me it's ENG.

Now, here comes the problem: knowing the client very well, I cannot guarantee that their locales will be properly selected. Here I came up with a thought: when the user selects the language to use in that template, it adds the country's dictionary to the Normal style and this affects all the other styles in the template.

I thought of referencing the dictionary set for the template and then adding the appropriate user message depending on that dictionary. When they close the document in question it reverts to whatever they have set in their own system.

My problem, is how can I access the table and drop those messages straight onto the document?

Any ideas, please?

1. SamT's suggestion would work -- I opted for the procedural approach since I thought it's easier to understand

2. Using Access would seem to require that the user have Access on the computer, and that you'd have at least two files to maintain -- my 'all in one' only required the Word template

3. I wouldn't use the Registry for storing all 13 x 30 strings -- no good real reason, just doesn't seem 'comfortable' to have so much stored like that (It would work of course)

4. If you can't trust the users (and who can) I would have a 'maintenance' type macro ('Set_Your_Language') that the user could call to pick a language from a list (userform with radio buttons) and store the selection in the registry and use that to index into the aryText in my #2. You could use LocalLanguage to make a first guess


5. Greg Maxey has some good write-ups about 'storing' data in a Word CustomXML section (which is not typically used).
This would probably be the most elegant way to do it, but it is a little complicated, uncommon so if someone else has to take over maintenance it has a 'Where did THAT come from factor'

http://gregmaxey.com/word_tip_pages/...mXMLParts.html

http://gregmaxey.com/word_tip_pages/...pful_help.html

6. Personally, I'd keep it simple (and unelegant) by using the array approach. Using Access or Excel to construct the 30 languages concatenated strings and paste them into a module:

I.e. build 30 strings from Access table with Language in first column, text1, ..., text30 in next, one row per language

aryText(langEng) = Split("Yes;No;Maybe"; etc;etc;etc;etc;etc, ";")
aryText(langGer) = Split("Ya;Nein;Vielleicht"etc;etc;etc;etc;etc, ";")
aryText(langSpa) = Split("Si;Prohibido;Tal vezetc;etc;etc;etc;etc", ";")

and just paste into a module

[Roderick]

Thanks a lot.

Sorry if I haven't been back. I'm working through this very slowly!

[Paul_Hossler]

NP

Many ways to do it -- this is just one

Lots of times I'll use Excel to build a lot of strings that I'll paste into a code module since I don't like typing unnecessarily

[SamT]

Attached is a very brief example of using Language Class modules/

I used a Standard Module as the Selected Language Module since I think it might be easier for you to understand the Flow, but personally, I would use a Class module for it, since a Class module is more elegant, sophisticated, secure, and fool proof.

To create all the Language Class Modules, Complete one. Then Open a new Workbook in the Same Excel instance and Drag the Competed Class module to the new Book in the VBA editor's Project Explorer. Rename the New Book Version to a new Language, but and something so you can tell it has yet to be trnslated, (cls***French, for example.) Then rag it back into the Main Workbook.

Now rename the New Book's version something else (cls***Italian) and drag it back. Repeat till you have all 30+ Language Class modules in the main Book.

At this time all Language Classes have their Constants in the same original language as the first completed one. As you translate them, rename them to remove the Untranslated indicator, (cls***French to clsFrench). No other changes need to be made.

If you choose to use a Class module for the Selected Language, Do the Drag and Drop, rename, Drag and Drop one more time, but this time rename it to "clsProjectMessages"

First, do a CTRL+H on it and replace all instances of "Const" with "Private".Then delete all the "= "SomeString"" in the Declarations section.

Copy all the Property Gets and Paste a copy below or above the copied section of code.

Here's an example of a Property Get Sub taken from the attached

Public Property Get MSG_WrongBookPart1() As String
  MSG_WrongBookPart1 = pWrongBookPart1
End Property

To convert this to a Write Property, (Property Let sub.)
Select all the Newly Pasted Get Subs, then use CTRL+H to replace "Get" with "Let".
Use CTRL+H to replace ")As String" with "As String)".

In all the opening lines of all the Property Let Sub, copy the "pVariable" in the second line (pWrongBookPart1)But without the "p" ("WrongBookPart1") and paste it between the "(" and the "AS" in the first line.

Now you have

Public Property Let MSG_WrongBookPart1(WrongBookPart1 As String)
  MSG_WrongBookPart1 = pWrongBookPart1
End Property

Finally, Swap the sides of the equation in the second line, Dropping the "MSG_", giving you the complete Property Let Sub

Public Property Let MSG_WrongBookPart1(WrongBookPart1 As String)
  pWrongBookPart1 = WrongBookPart1 
End Property

One last thing: Compound messages. In the attached, I used a Public Function in the Standard Module "CurrentMessages."
You can't use Property Get Subs, since they don't take any input parameters. Just us a Public Function

Public Function WrongBookMSG(WorkbookName As String) As String
  WrongBookMSG = pWrongBookPart1 & WorkbookName & pWrongBookPart2
End Function

And remove the two? Message Parts Property Get Subs that only return one part.

Remember, all Property Get Subs are identical except for the three Names involved. Name one is the Name of the Property (the name of the Get and Let Subs.) Name two is the Name of the Input Parameter, Name three is the Name of the Private Variable that stores the Property Value. All three Names are identical except for the presence of a prefix or suffix

Examples

Property Name	Parameter Name	Variable name
WrongBook	WrongBook_MSG	pWrongBook
MsgWrongBook	WrongBook_MSG	pWrongBook
Msg_WrongBook	WrongBook	pWrongBook
WrongBook	WrongBookMessage	pWrongBook

[Paul_Hossler]

Well it just seems easier to me to just use one standard module and initialize the strings into an array (ala #2) since standard modules are more common and less complicated (in general)

Since the language strings seem to originate from Access, you could export into Excel (since I'm not too familiar with Access VBA) and generate the Sub Init() strings and paste them into a module

The Yellow would be from Access, the Green I added for the VBA statements, and the Orange are the strings the 'throw away' macro generated to be pasted as a block into the Init() sub

Capture.JPG

Option Explicit

Sub BuildStrings()
    Dim rData As Range, rRow As Range
    Dim v As Variant
    
    Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
    
    For Each rRow In rData.Rows
        With rRow
            v = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(.Cells(3).Resize(1, 9)))
            .EntireRow.Cells(12).Value = "aryText(lang" & .Cells(1).Value & ") = Split(" & Chr(34) & Join(v, ";") & Chr(34) & ","";"")"
        End With
    Next
       
End Sub

'paste the statements into a module
'aryText(langEng) = Split("Yes;No;Maybe;etc;etc;etc;etc;etc;", ";")
'aryText(langGer) = Split("Ya;Nein;Vielleicht;etc;etc;etc;etc;etc;", ";")
'aryText(langSpa) = Split("Si;Prohibido;Tal vezetc;etc;etc;etc;etc;etc;", ";")