Solved: Distribute xla in other language versions of Excel

[tpoynton]

Greetings,

I have an xla that people in Germany and Portugal (that I know of so far) can not install. They receive an error, and the add in does not show up in the VBA editor.

On the German version of Excel 2003, the user received the following error when trying to install using "browse" from the add-ins dialog box:

"-2147024809 (80070057)" (one or more arguments are invalid)

I then asked him to save the file in the office add-ins folder so that it would appear in the add-ins dialog box automatically, and he received a "run-time error 5"

I have searched all over to try and find a solution with no luck. If anyone has any ideas on how to deal with this, I would be very appreciative! the add in can be downloaded from www.ezanalyze.com (it's free).

[Killian]

It's a bit difficult to diagnose without seeing the code, but I'll take a stab at it and suggest that it's most likely to be a language issue.

Let's assume this AddIn adds menu items. The popular way to do this is to refer to the commandbar you want to add the menu to by it's name, so for the main menu bar, you would start with:With Application.CommandBars("Worksheet Menu Bar")
'add a menu then some controls, etc
End With
The thing with the Germans and Portuguese is, they got a different word for everything... and while it's possible to order a beer by a process of gesturing and pointing for a couple of minutes, referring to Excel objects requires a little more precision. So unless the German/Portuguese for "Worksheet Menu Bar" is, in fact, "Worksheet Menu Bar", there'll be a problem.
This would explain the Error 5.

The solution is to refer to the native commandbars and controls using their ID property
e.g for the Worksheet Menu Bar, it's 265

The invalid argument is more difficult to get to without debugging, but following the same logic, it may be that an empty string (where some menu text should be) is being passed ? Would need to see code to trace that one.

Hope that helps

[XL-Dennis]

Hi,

I agree with Killian and would also like to add that You should avoid using "True" and "False" and instead use 0 and 1.

Kind regards,
Dennis

[tpoynton]

Thanks for the reply...since I dont have access to the other language versions, i can not determine what the problem is...here is the code i use:


Private Sub Workbook_AddinInstall()
AddMenu
End Sub

Private Sub Workbook_AddinUninstall()
KillMenu

Dim xlaName As String
Dim i As Integer

With ThisWorkbook
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name

If xlaName = "ezanalyze.xla" Or xlaName = "ezanalyze-dev.xla" Then
AddIns(i).Installed = False
End If

Next i
'AddIns("ezanalyze").Installed = False
End With
End Sub
Public Sub Workbook_installAddIns()

End Sub

Sub AddMenu()

Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton
Dim iHelpIndex As Integer
iHelpIndex = Application.CommandBars("Worksheet Menu Bar").Controls("Help").Index
KillMenu

Set ctrlMain = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False)

With ctrlMain
.Caption = "&EZAnalyze-Beta"

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Describe..."
.OnAction = "ThisWorkbook.Describe"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Disaggregate..."
.OnAction = "ThisWorkbook.Disaggregate"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Graph..."
.OnAction = "ThisWorkbook.Graph"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&New Variable..."
.OnAction = "ThisWorkbook.NewVariable"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Advanced..."
.OnAction = "ThisWorkbook.Advanced"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Delete Xtra Sheets"
.OnAction = "ThisWorkbook.DeleteXtraSheets"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Help"
.OnAction = "ThisWorkbook.Help"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&About"
.OnAction = "ThisWorkbook.HelpAbout"
End With

End With
End Sub

Sub KillMenu()
Dim cmdbar As CommandBar
On Error Resume Next
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
cmdbar.Controls("&EZAnalyze-Beta").Delete
On Error GoTo 0
End Sub


This is based on code that can be found all over the web, so I was a little surprised that I wasnt able to find any information on different language versions...

Thanks for the help!

[tpoynton]

Sorry - didnt see your post Dennis. Also, now that you have my code, how do I refer to the ID property instead of my current method? THANKS

[XL-Dennis]

OK, this is a two steps procedure and as I always welcome the learning approach I give You the following:

# 1 Identify the ID etc for 1st level of commandbars and controls


Option Explicit
Sub Get_ID()
Dim cbControl As CommandBarControl
Dim cbBar As CommandBar
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
With ActiveWorkbook
.Sheets("Controls on 1st level").Delete
.Sheets.Add
End With
Application.DisplayAlerts = True
ActiveSheet.Name = "Controls on 1st level"
Application.ScreenUpdating = False
Cells(1, 1).Value = "Menu"
Cells(1, 2).Value = "Control"
Cells(1, 3).Value = "ButtonPicture"
Cells(1, 4).Value = "ID"
Cells(1, 1).Resize(1, 4).Font.Bold = True
i = 2
For Each cbBar In CommandBars
Application.StatusBar = _
"Working with " & cbBar.Name
Cells(i, 1).Value = cbBar.Name
i = i + 1
For Each cbControl In cbBar.Controls
Cells(i, 2).Value = cbControl.Caption
cbControl.CopyFace
If Err.Number = 0 Then
ActiveSheet.Paste Cells(i, 3)
Cells(i, 3).Value = cbControl.FaceId
End If
Cells(i, 4).Value = cbControl.ID
Err.Clear
i = i + 1
Next cbControl
Next cbBar
Range("A:C").EntireColumn.AutoFit
With Application
.ScreenUpdating = True
.StatusBar = False
End With
End Sub


# 2 Add an item to the Help-submenu


Option Explicit
Sub Create_Menu()
Dim cbWorksheet As CommandBar
Dim cbHelp As CommandBarControl
Dim cbExample As CommandBarControl
Dim stExample As String

Set cbWorksheet = Application.CommandBars(1)
Set cbHelp = cbWorksheet.FindControl(ID:=30010)
stExample = "VBAX Example"

On Error Resume Next
cbWorksheet.FindControl(ID:=30010).Controls(stExample).Delete
On Error GoTo 0

Set cbExample = _
cbHelp.Controls.Add(Type:=msoControlButton, Before:=4)

With cbExample
.BeginGroup = True
.Caption = "&Example"
.FaceId = 560
.OnAction = ""
.Tag = "Example"
End With

End Sub

Sub Delete_Menu()
Dim bcExample As CommandBarControl

On Error Resume Next
Set bcExample = Application.CommandBars.FindControl(Tag:="Example")
bcExample.Delete
On Error GoTo 0

End Sub


The above is a bulletproof way when working in a international enviroment.

Kind regards,
Dennis

[tpoynton]

THANK YOU - I am at work at the moment, but I will try to integrate what you have taught me above with my code, test it out and post back here - THANKS!

[tpoynton]

Just wanted to provide an update; I combined some of the things Dennis mentioned with some of the things Killian mentioned, and think I found a solution - I am waiting for the German tester to provide me with the results. If this works, I will repost the code here to document it.

I also came across this useful list of commandbar control ID's at microsoft:

http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q213552

THANKS again!

[Killian]

Thanks for the update :thumb
Hope it all goes to plan
Just as an additional enhancement for mutli-language apps, you can use a worksheet in the AddIn to store Translations for your menu items, forms and dialogs and provide an option to select from the supported languages. (you could probably find a way of auto-detecting the Office language setting - I've never done it but I'm sure it's possible)

[tpoynton]

Indeed there is a way to do autodetect the language settings - Application.LanguageSettings. They also document a method in VBA help for using tables to display things in different languages based on the language setting...I'm not sure if I want to go there just yet, but I might.

[Wolfgang]

hi dennis...

this is exactly what i meant the other day when i said that this forum has helped me " a thousand times"....

looking at your way of coding things shows me that i should be doing something else but "fooling around" with vba or the like..

keep up the good work and have a nice day, still...

best,
wolfgang

[Bob Phillips]

Just as an additional enhancement for mutli-language apps, you can use a worksheet in the AddIn to store Translations for your menu items, forms and dialogs and provide an option to select from the supported languages. (you could probably find a way of auto-detecting the Office language setting - I've never done it but I'm sure it's possible)

The way that we do it is to have .INI files which identify the language, anjd have a series of ids for all the menu items, messages, etc.

This also means that we can open p to the user community to provide us with local langauga versions as they can see what needs to be translated. Saves us trying to get langaugae versions, and it is demand driven.

[tpoynton]

xld - I am very interested in learning more about that - if it is not too much trouble, would you mind sharing how you do that?

I have to admit I am a hack...I have no formal training in computer programming, and slug through things with a combination of dogged persistence and support from experts in forums such as this...

THANKS

[Bob Phillips]

xld - I am very interested in learning more about that - if it is not too much trouble, would you mind sharing how you do that?

I have to admit I am a hack...I have no formal training in computer programming, and slug through things with a combination of dogged persistence and support from experts in forums such as this...

THANKS

I knock up a little demo to show you how we do it. MIght take a couple of days, so bear with me.

Bob

[tpoynton]

Please take your time!

[Killian]

The way that we do it is to have .INI files which identify the language, anjd have a series of ids for all the menu items, messages, etc.

This also means that we can open p to the user community to provide us with local langauga versions as they can see what needs to be translated. Saves us trying to get langaugae versions, and it is demand driven.

The advantage of using the AddIn's worksheet is there's only one self-contained file to distribute... That said, having simple text files for people to add languages on demand is cool and I've used the same method for PowerPoint AddIns - it also allows people to make their own personal "comedy" menus, which worries some people (who usually have the word "manager" in their job title) but I tend to think of it as a feature.

I'll dig some code out - it would be interesting to compare notes...

[tpoynton]

I dont mean to clutter this thread with expressions of gratitude, but THANK YOU!

I do like the idea of having one file to distribute; if there is a way to have a single worksheet with all of the translations that I then incorporate with the addin, I think that would be preferred for my application. I have a gentleman who is helping to translate my documentation into Spanish who also agreed to update the code where needed, but given the # of international downloads I get, I suspect a better long-term solution would be to determine the user's language settings and get the appropriate language for labels, buttons, and output from functions from a worksheet if the translation to their language exists. OR, as was suggested, having a place where people could select their language...given the international flavor of this forum, this might also make a really good KB article!

Still no word on whether or not the new method of dealing with adding the menu worked...

[XL-Dennis]

Hi,

Nice to see so many members engaged with this isssue (and I'm always glad to see You Wolfgang) :)

The advantage to work with large worldwide companies is that they use one singel version of Excel and only one language version of it (that is English)!

For smaller companies the situation can be more difficult and usually we end up with solutions where we either:
a) use one language only (English) or
b) make an end-user configuration setup package.

In the scenarios where I create end-user configuration it's added to an installation package where the installation software allow the users to choose language to be used. Here I usually use files with the fileextension of .dat and it's then used by the COM add-ins.

Based on some really bad experience I nowadays avoid situations where Excel add-ins are depended on external language specific files...

Kind regards,
Dennis

[tpoynton]

GREAT NEWS - the modification below worked on the German version, and I have someone testing it out in Portugal - I am pretty confident the issue is resolved. Turns out a "worksheet menu bar" is not a worksheet menu bar everywhere...

I'll post the specifics of what I modified below, then some code that can be cut and pasted for people in the future who want to add a new menu to the "main" excel menus.

old code that didnt work on German and Portuguese versions:

Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton
Dim iHelpIndex As Integer

iHelpIndex = Application.CommandBars("Worksheet Menu Bar").Controls("Help").Index

KillMenu

Set ctrlMain = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False)





Replacement code that works





Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton
Dim iHelpIndex As Integer

Dim cbHelp As CommandBarControl
Dim cbWorksheet As CommandBar
Set cbWorksheet = Application.CommandBars(1)
Set cbHelp = cbWorksheet.FindControl(ID:=30010)
iHelpIndex = cbHelp.Index

KillMenu

Set ctrlMain = cbWorksheet.Controls.Add _
(Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False)


NOW for code that should work to add a new menu to Excel in front of the Help menu...This should be placed in ThisWorkbook


Option Explicit

Private Sub Workbook_AddinInstall()
AddMenu
End Sub

Private Sub Workbook_AddinUninstall()
KillMenu

Dim xlaName As String
Dim i As Integer

'This loops through all installed addins to see if the addin is installed
With ThisWorkbook
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name

If xlaName = "yourfile.xla" Then
AddIns(i).Installed = False
End If

Next i
End With
End Sub

Sub AddMenu()

Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton
Dim iHelpIndex As Integer
Dim cbHelp As CommandBarControl
Dim cbWorksheet As CommandBar
Set cbWorksheet = Application.CommandBars(1)
Set cbHelp = cbWorksheet.FindControl(ID:=30010)
iHelpIndex = cbHelp.Index

KillMenu

Set ctrlMain = cbWorksheet.Controls.Add _
(Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False)

With ctrlMain
.Caption = "&New Menu"

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Menu Item 1"
.OnAction = "ThisWorkbook.Function1"
End With

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "&Menu Item 2"
.OnAction = "ThisWorkbook.Function2"
End With

'rinse, lather, repeat as necessary to obtain desired # of menu items

End With
End Sub

Sub KillMenu()
Dim cmdBar As CommandBar
On Error Resume Next
Set cmdBar = Application.CommandBars(1)
cmdBar.Controls("&New Menu").Delete
On Error GoTo 0
End Sub

'the following subs run when the user clicks on a menu item; here i am calling dialog boxes

Sub Function1()
UserForm1.Show
End Sub

Sub Function2()
UserForm2.Show
End Sub



I think that's it; while i was documenting this, I realized i forgot to update the code in the killmenu sub...the code above IS updated.

thanks for helping me work this out! I am still interested in exploring different options for dealing with multiple languages, so I will leave this thread as unsolved for now unless encouraged to do otherwise!

[tpoynton]

OK, so I have a solution worked out (following Killian's recommendation) that will enable multiple language versions. the parts of it are pretty simple:

declare a global variable in a module:
public dim gLang as long

create a worksheet (called "lngTrsl8" here) in the addin which has English in column 1, Spanish in Column 2, German in column 3, etc

create a userform that displays available languages which sets gLang; I have it set up so that gLang = the column of the translated text

in a procedure, i then create a new section of code that changes elements on the form (or messages, etc) if gLang is > 1

for example

If gLang > 1 Then
Label1.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(2, gLang).Value
Label2.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(3, gLang).Value
Label4.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(4, gLang).Value
Label5.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(5, gLang).Value
CommandButton1.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(6, gLang).Value
CommandButton_Cancel.Caption = ThisWorkbook.Sheets("lngTrsl8").Cells(8, gLang).Value
End If


This would require quite a bit of work initially with regard to the code in my xla file, but once set could be easily expanded to add multiple languages. All i would need to do is find someone who could do the translations in the Excel file, then I could update and re-release. A benefit of this approach is that it doesnt require the user to keep track of any additional files. A potential drawback is speed...

While language settings can be autodetected (documented in the VBE help), I think I would rather have people choose it themselves.

Anyway, if anyone has any more "elegant" solutions, I would love to hear them!

THANKS!

[tpoynton]

Looks like i am having a conversation with myself...that's OK!

I was testing out the possibility of autodetecting the language...a colleague in Mexico appears to have an English installation! The problem with asking the user to select a langauge is that it has to be done every time excel is launched...I know i could write a registry value or something like that to have it "remember" the setting, but i would like to avoid doing that...

this just gave me a thought; i could write the value to a worksheet within the xla...

ANYWAY, i put together all of the language codes in the attached sheet, and three different ways of obtaining them via VBA. Right now when you open the file, it will tell you the results of the three methods (IDUI, IDInstall, and IDHelp). I found it helpful, so I thought I would share...

[Killian]

Looks like i am having a conversation with myself...
...you're not alone, honest! :grouphug:

Thanks for documenting your progress on the board :friends: the thread's getting a lot of views and I've no doubt it will be a useful reference for the future. (Not least for me, that's a long list of language ID's I know I won't ever have to look up myself) :thumb

[Wolfgang]

hi there...

please let me throw in my 5 euro cent...

some time back in 2001 i had the idea to overcome the limits of excel's autofilter as far as its lookup criteria is concerned and a very friendly guy named george marcus coded the whole thing for me...

i then used original excel-97 on german win-98 and never had any problems with his addin...

now i'm still using it and moved it to excel 2003 on original xp-pro...still no problems...

i gave the addin to a friend who runs xp on the german version of xp-home and guess what...she could load the addin allright, but it did not appear on the tool bar...although the addin code could be read in the vba-environment...

now what....i asked the famous andrew engwirda for help and he came up with the following correction:

' HelpIndex = CommandBars(1).Controls("Help").Index = original code
HelpIndex = Application.CommandBars.FindControl(ID:=30010).Index = his code

and voila, it appeared by magic...

maybe you can use his correction as well if you transport some stuff from the us-version to the german environment...

keep up the good work and have a nice day...

best,
wolfgang

[tpoynton]

OK - want to mark the thread as solved, and provide (yet) another update. Here's what I've come up with to prepare distributing an xla in multiple languages:

1 - ensure commandbar controls are referenced by their ID instead of their String name

2 - if possible, plan at the outset for standardizing userform labels and buttons to reduce the amount of translations

3 - in an xla, you can access a worksheet to modify by setting the "addins" property of ThisWorkbook to false. You can use this worksheet to store the translations. This is an appealing way to approach this problem because it permits you to still only distribute a single xla file, while you can send a separate xls file to multiple people for translation - then it is a simple matter of copying and pasting from the xls file into the xla file.

Initially, my plan was to autodetect the language settings of the host machine using methods described earlier in this thread. However, after testing with a colleague in Mexico, I found that because he purchased his computer in the US, his Excel version was consistently detected as English (it was, in fact, an English version).

Regardless, I then decided that it would probably be best to have users choose a preferred language. To accomplish this, I am saving the value of a global variable to the worksheet; when the addin is loaded, i set the value of the global variable to the value of the cell in the worksheet. This is simple, but probably worth documenting...

To save the worksheet after the user changes the language setting through a userform, I do this:

'get setting from optionbuttons
If OptionButton_Esp.Value = True Then
gLang = 2
ElseIf OptionButton_Ger.Value = True Then
gLang = 3
Else: gLang = 1 '1 is the default, which is English
End If

'This writes the current value of the global variable (gLang) to the
'worksheet in the xla and saves the sheet so that the value is
' "remembered"
ThisWorkbook.Sheets("SheetName").Cells(1, 15).Value = gLang
ThisWorkbook.Sheets("SheetName").Activate
ActiveWorkbook.Save

It is also important to note that the value of the global variable should be set to the column of the worksheet that holds the translated text. That way, when you call the code in a userform (or whatever), the location is already set. Below is an example of having the userform elements translated when the userform is opened:

Public Sub Userform_Activate()
'if language is not English...
If gLang > 1 Then
With ThisWorkbook.Sheets("SheetName")
Label1.Caption = .Cells(2, gLang).Value
Label2.Caption = .Cells(3, gLang).Value
Label3.Caption = .Cells(4, gLang).Value
Label4.Caption = .Cells(5, gLang).Value
CommandButton1.Caption = .Cells(6, gLang).Value
CommandButton2.Caption = .Cells(7, gLang).Value
End With
end if

SO, the way i have my excel sheet set up is to have English in the first column, Spanish in the second column, etc.

This seems like a pretty simple way to go about doing this...

I appreciate all the help and support I have gotten on this threadhttp://vbaexpress.com/forum/images/smilies/beerchug.gif. I hope other people find this information useful!