来自 Excel VBA .xlam 的 onLoad 功能区回调失败（不触发/工作）

onLoad ribbon callback fails (not firing/working) from Excel VBA .xlam

提问by Jamie Garroch

I've created a simple Excel add-in to demonstrate the problem whereby the ribbon onLoad event isn't firing when the project is saved and automatically loaded as a .xlam but does work when opened as a .xlsm. I've checked it for Excel 2007 (with the corresponding xmlns), 2010 (x32) and 2013 (x32) and it fails in all cases when loaded as a .xlam

我创建了一个简单的 Excel 加载项来演示当项目保存并自动加载为 .xlam 时功能区 onLoad 事件未触发但在作为 .xlsm 打开时有效的问题。我已经检查了 Excel 2007（带有相应的 xmlns）、2010 (x32) 和 2013 (x32) 并且在加载为 .xlam 时在所有情况下都失败

The sample has a single button and when clicked, should cause a flag to be toggled and then invalidates the ribbon which in turn toggles the label of the button via the GetLabel callback.

该示例有一个按钮，当单击时，应该会导致一个标志被切换，然后使功能区无效，进而通过 GetLabel 回调切换按钮的标签。

It works when opened as an .xlsm file but not when loaded automatically from the user XLSTART folder (no Windows Registry changes have been made) as a .xlam add-in. The problem seems to be that the onLoad event isn't firing from the .xlam version and hence the onLoadRibbon procudure doesn't run and there's no ribbon object to invalidate.

它在作为 .xlsm 文件打开时有效，但在从用户 XLSTART 文件夹（未更改 Windows 注册表）作为 .xlam 加载项自动加载时则无效。问题似乎是 onLoad 事件不是从 .xlam 版本触发的，因此 onLoadRibbon 程序不会运行，并且没有要失效的功能区对象。

<customUI onLoad="onLoadRibbon" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab idQ="TabInsert">
                <group id="GroupTest" label="2010" insertBeforeMso="GroupInsertLinks">
                    <button id="ButtonTest"
                        getLabel="GetLabel"
                        onAction="ButtonClick"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

And here's the corresponding add-in VBA code:

这是相应的加载项 VBA 代码：

Option Explicit

Public Toggle As String
Public myRibbonUI As IRibbonUI

' Ribbon callback : runs when ribbon is loaded
Public Sub onLoadRibbon(ribbon As IRibbonUI)
    ' Get a reference to the ribbon
    Set myRibbonUI = ribbon
    Debug.Print "Ribbon Reference Set"
    MsgBox "Ribbon Reference Set"
End Sub

' Ribbon callback : runs when ribbon button is clicked
Public Sub ButtonClick(control As IRibbonControl)
    ' Invalidate the ribbon so that the label of the button toggles between "true" and "false"
    myRibbonUI.Invalidate
    Debug.Print "Ribbon Invalidated"
End Sub

' Ribbon callback : runs when ribbon is invalidated
Public Sub GetLabel(control As IRibbonControl, ByRef label)
    ' Toggle the label for the button to indicate that the callback has worked
    Toggle = IIf(Toggle = "State 1", "State 2", "State 1")
    label = Toggle
    Debug.Print "Ribbon Button Label Toggled"
End Sub

Why isn't this working in the .xlam?

为什么这在 .xlam 中不起作用？

回答by Blackhawk

I was able to successfully create and run the Add-In per your specifications.

我能够根据您的规范成功创建和运行加载项。

First, I created the *.xlsm using your customUI.xml as above. NOTE: I'm on Excel 2007, so I used the other xlmns as you indicated, specifically http://schemas.microsoft.com/office/2006/01/customui. I added the ribbon-handling code next, then reopened the workbook to verify the ribbon functionality. I immediately received a messagebox "Ribbon Reference Set" (I have macros enabled by default). I verified that the "Insert" tab has your extra button and that it toggles.

首先，我使用上面的 customUI.xml 创建了 *.xlsm。注意：我使用的是 Excel 2007，所以我使用了您指出的其他 xlmns，特别是http://schemas.microsoft.com/office/2006/01/customui. 我接下来添加了功能区处理代码，然后重新打开工作簿以验证功能区功能。我立即收到了一个消息框“功能区参考集”（默认情况下我启用了宏）。我确认“插入”选项卡有你的额外按钮并且它可以切换。

Second, I saved the xlsm as an Excel Add-In *.xlam. I manually opened the xlam file by double clicking and all functionality appeared as in the first test above.

其次，我将 xlsm 保存为 Excel 加载项 *.xlam。我通过双击手动打开了 xlam 文件，所有功能都出现在上面的第一个测试中。

Third, I installed the Add-In through the Excel File Menu---> Options---> Add-Ins---> Manage Excel Add-Ins---> Go. I opened an unrelated Excel workbook and verified that the Add-In loaded the same as the tests before. I uninstalled the Add-In using the same menu.

第三，我通过 Excel 文件Menu---> Options---> Add-Ins---> Manage Excel Add-Ins--->安装了加载项Go。我打开了一个不相关的 Excel 工作簿并验证加载项是否与之前的测试加载相同。我使用相同的菜单卸载了加载项。

Fourth, I navigated to C:\Program Files(x86)\and verified the location of the Excel.exe executable, then found the XLSTARTfolder. I placed the xlam into the folder and opened the unrelated Excel workbook and was able to verify that the Add-In functioned as before.

第四，我导航到C:\Program Files(x86)\并验证了 Excel.exe 可执行文件的位置，然后找到了该XLSTART文件夹。我将 xlam 放入文件夹并打开不相关的 Excel 工作簿，并能够验证加载项是否像以前一样运行。

I know that the above doesn't directly answer your question, but test those cases and let's compare the outcomes to find out where it might be breaking down.

我知道以上内容并没有直接回答您的问题，但是请测试这些案例，然后让我们比较结果以找出可能出现问题的地方。

EDIT:

编辑：

In response to your comment, when I manually copy the *.xlam into the %APPDATA%\Microsoft\Excel\XLSTARTfolder, it works as expected. On my Windows 7 machine, the path evaluates to C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART. As you noted, I verified in the Excel options that this is a default trusted location.

针对您的评论，当我手动将 *.xlam 复制到%APPDATA%\Microsoft\Excel\XLSTART文件夹中时，它按预期工作。在我的 Windows 7 机器上，路径评估为C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART. 正如您所指出的，我在 Excel 选项中验证了这是默认的受信任位置。

回答by cboden

I know this is an old post ... but I had a similar same problem and was searching for hours for a solution.

我知道这是一篇旧帖子......但我遇到了类似的问题，并且正在寻找解决方案数小时。

The root cause in my case was as simple as hard to find: I had two different AddIns - but due to copy and paste I missed to rename the callback function in the second one.
The name of the callback procedure must be unique in the entire Excel instance.

在我的案例中，根本原因很简单，很难找到：我有两个不同的 AddIns - 但由于复制和粘贴，我错过了重命名第二个中的回调函数。回调过程
的名称在整个 Excel 实例中必须是唯一的。

So simply try to rename the callback procedure in the VBA code (and of course also in the XML)!

因此，只需尝试在 VBA 代码中重命名回调过程（当然也在 XML 中）！