Unknown Function ".Index" in German Excel

[markibarki]

In my German Excel version 2016 (16.0.4432.1000, 32-Bit) I get an "Unknown function"-error when I open "Simple Toolkit_DEV.xlam". It should be in menu_lib.bas:47 :
helpMenuIndex = mainMenuBar.Controls(insertBefore).Index

My fix to that is in line 48/49 where I change
Set customMenu = mainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=helpMenuIndex)
to
Set customMenu = mainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=mainMenuBar.Controls.Count)

Now, my Excel shows a new ribbon tab "Add-Ins" with the "Simple Tools (dev)" drop down menu as single element of this tab.

[jimm-domingo]

Thanks @markibarki for reporting this issue. It looks like the error occurs because of the default value for the optional installBefore parameter to the AddCustomMenu procedure in menu_lib.bas (line 42):

Sub AddCustomMenu(menuName As String, definition() As String, _
                  Optional insertBefore As String = "Help")

I suspect the "Help" menu in your German Excel has a different name (caption). So when the code on line 47 tries to find that menu by this default English name, it doesn't find the menu. Therefore, the sub-expression:

 mainMenuBar.Controls(insertBefore)

returns Nothing, which of course, does not have ".Index" function.

To confirm this, could you please restore menu_lib.bas to its original state, and then make this modification to line 36 in menu.bas:

AddCustomMenu myMenuName, definition, "(HELP-MENU-NAME)"

Replace "(HELP-MENU-NAME)" with the name of the Help menu as it appears in your Excel.

[markibarki]

I have tested your suggestion, but it doesn't work. I got the same error message. I also tried to add a custom menu "Help" (and in German "Hilfe"), but also then I got the same error.

When I run the following code to print the menu names, I got English names also in my German Excel:

Dim vsoCommandBar As CommandBar
For Each vsoCommandBar In Application.CommandBars
    Debug.Print vsoCommandBar.Name
Next

Output:

Worksheet Menu Bar
Chart Menu Bar
WordArt
Picture
Drawing Canvas
[...]
Formula Bar
[...]

There is no item "Help" in the resulting list. But also if I try to use "Formula Bar" it doesn't work. It looks like the function ".Index" is generally not available.

[jimm-domingo]

Sorry @markibarki for the delay in responding. That's interesting that the command bars have English names, but in some way, that does make sense, because then code accessing them would work regardless of language used in the Excel UI (German vs English vs ...).

The "Help" menu is actually a member of the first command bar (the one named "Worksheet Menu Bar"). What output do you get when you enter this code snippet?

Dim menu_bar As CommandBar
Dim menu As CommandBarControl

Set menu_bar = Application.CommandBars("Worksheet Menu Bar")
For Each menu in menu_bar.Controls
    Debug.Print menu.Id, menu.Caption
Next

We might see "Hilfe" in the output.

[markibarki]

The output of your code is as follows:

 30002        &Datei
 30003        &Bearbeiten
 30004        &Ansicht
 30005        &Einfügen
 30006        Forma&t
 30007        E&xtras
 30011        Date&n
 1            Tea&m
 30009        &Fenster
 30010        &?

If I change your code to AddCustomMenu myMenuName, definition, "&?", then the error is gone. There is, similar to my initial fix, a new ribbon menu "Add-Ins" with the menu content in it.

[jimm-domingo]

Thanks @markibarki for the additional information. I suspected that the Id of the "Help" menu (whose caption in your Excel is "&?") was 30010. So as noted above in the referenced commit, I modified the default behavior in the AddCustomMenu function to search for the Help menu by its control id, instead of by its caption.

This change works fine with my English Excel, but it still needs to be tested to see if it works with another language. If you could download the latest pre-release (v1.3.0-rc.1), and check if it works without modification for your German Excel, I'd appreciate that.

[jimm-domingo]

Closing this ticket because I just did the official release of v1.3.0. If this problem re-appears, we can always re-open this ticket then.