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:[VBA]With Application.CommandBars("Worksheet Menu Bar")
'add a menu then some controls, etc
End With[/VBA]
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:

[VBA]
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
[/VBA]

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

[vba]
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
[/vba]

# 2 Add an item to the Help-submenu

[vba]
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
[/vba]

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...BLN%5D;Q213552

THANKS again!

[Killian]

Thanks for the update
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:
[VBA]
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)


[/VBA]


Replacement code that works


[VBA]


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)
[/VBA]

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

[VBA]
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

[/VBA]

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:
[vba]public dim gLang as long[/vba]

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
[vba]
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
[/vba]

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!