Excel 2013 VBA加载项安装带Ribbon的加载项后IRibbonUI.Invalidate失败

场景描述 #

这是Excel 2013 VBA场景，我开发了一款加载项，通过全局变量listOfAddIns（Variant类型的数组数组valueArray）管理其他加载项的安装与卸载。该加载项包含两类按钮：

• 批量控制按钮：用于安装/卸载所有可用加载项
• 单独控制切换按钮：用于安装/卸载特定加载项
  所有Ribbon元素通过大量回调函数实现完全自定义。

问题 #

安装无自定义Ribbon标签的加载项时一切正常，但安装带自定义Ribbon标签的加载项后，无法调用ribbon对象的.Invalidate方法及回调函数，报错：

Run-Time error'-2147467259(80004005)': Method 'Invalidate' of object 'IRibbonUI' failed

猜想 #

怀疑不同加载项的IRibbonControl存在冲突，但多次尝试仍无法解决。

相关代码 #

CustomUI XML代码 #

<customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
  <ribbon startFromScratch="false"> 
    <tabs> 
      <tab id = "INST1ID" label="Installer"> 
        <group id="GroupControls" label="Group controls"> 
          <button id="B1ID" label="Install All" size="large" onAction="InstallAll" imageMso="AcceptInvitation" /> 
          <button id="B2ID" label="Uninstall ALL" size="large" onAction="UninstallAll" imageMso="MasterViewClose" /> 
        </group> 
        <group id="IndividualControls" label="Individual controls"> 
          <toggleButton id="TB1ID" imageMso="HappyFace" label="AddIn1" onAction="TBsControl" size="large" tag="1" getEnabled="TBsGetEnabled" getPressed="TBsGetPressed" getVisible="TBsGetVisible" /> 
          <toggleButton id="TB2ID" imageMso="HappyFace" label="AddIn2" onAction="TBsControl" size="large" tag="2" getEnabled="TBsGetEnabled" getPressed="TBsGetPressed" getVisible="TBsGetVisible" /> 
          <toggleButton id="TB3ID" imageMso="HappyFace" label="AddIn3" onAction="TBsControl" size="large" tag="3" getEnabled="TBsGetEnabled" getPressed="TBsGetPressed" getVisible="TBsGetVisible" /> 
        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

OnLoad过程 #

Public Sub OnLoad(ribbon As IRibbonUI)
    Set ribbonObject = ribbon
End Sub

批量控制按钮主过程示例 #

Public Sub InstallAll(control As IRibbonControl)
    Call ResetSomePublicVariables
    Call CreateEntryValuesAndFillToListOfAddIns
    Call CheckAvailabilityAndInstallationOfAddInsAndFillToListOfAddIns
    Call UpdateListOfAddInsToInstallAllAvailableAddIns
    Call InstallOrUninstallAddInsDueToListOfAddIns
    ribbonObject.Invalidate
End Sub

单独控制按钮主过程及扩展过程 #

Public Sub TBsControl(control As IRibbonControl, pressed As Boolean)
    Call ResetSomePublicVariables
    Call CreateEntryValuesAndFillToListOfAddIns
    Call CheckAvailabilityAndInstallationOfAddInsAndFillToListOfAddIns
    Call UpdateListOfAddInsDueToToogleButtonRequest(control.tag, pressed)
    Call InstallOrUninstallAddInsDueToListOfAddIns
    ribbonObject.Invalidate
End Sub

Public Sub UpdateListOfAddInsDueToToogleButtonRequest(tag As Long, pressed As Boolean)
    Dim valueArray() As Variant
    valueArray = listOfAddIns(tag)
    If pressed = True Then
        valueArray(VAColumnIndex_Installed) = 1
    Else
        valueArray(VAColumnIndex_Installed) = 0
    End If
    listOfAddIns(tag) = valueArray
End Sub

回调函数示例 #

Public Sub TBsGetVisible(control As IRibbonControl, ByRef returnedVal)
    For Each Item In listOfAddIns
        If control.tag = Item(VAColumnIndex_Number) Then
            returnedVal = True
            Exit For
        Else
            returnedVal = False
        End If
    Next Item
End Sub

已尝试的解决方法 #

• 未使用Custom UI Editor或类似软件，认为当前阶段无帮助
• 将OnLoad过程移至ThisWorkbook模块，为IRibbonControl参数添加前缀，均无效
• 尝试重命名其他加载项中的IRibbonUI对象，无进展
• 查阅大量技术资料未解决问题

解决方案建议 #

1. 确保Ribbon元素ID全局唯一 #

Office加载项的Ribbon ID重复会直接导致IRibbonUI对象冲突。检查所有加载项的CustomUI代码：

• 给你的加载项所有元素ID加专属前缀，比如把INST1ID改成MyInstaller_INST1ID，B1ID改成MyInstaller_B1ID，避免和其他加载项的ID重复
• 为自定义UI添加专属命名空间，修改CustomUI的xmlns属性：

<customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:my="http://yourdomain.com/myinstaller">

2. 重新捕获IRibbonUI对象 #

安装带Ribbon的加载项后，Excel会重新加载Ribbon布局，原有ribbonObject可能失效。在ThisWorkbook模块添加事件触发重新加载：

Private Sub Workbook_Open()
    ' 重置Ribbon并重新触发OnLoad事件，捕获最新的IRibbonUI对象
    Application.CommandBars("Ribbon").Reset
End Sub

3. 避免全局变量被覆盖 #

把ribbonObject从标准模块的全局变量改为ThisWorkbook模块的私有变量，防止被其他加载项覆盖：

' ThisWorkbook模块中
Private m_ribbon As IRibbonUI

Public Property Get RibbonObject() As IRibbonUI
    Set RibbonObject = m_ribbon
End Property

Public Property Set RibbonObject(ByVal newRibbon As IRibbonUI)
    Set m_ribbon = newRibbon
End Property

' 修改OnLoad过程
Public Sub OnLoad(ribbon As IRibbonUI)
    Set ThisWorkbook.RibbonObject = ribbon
End Sub

' 调用Invalidate时替换为
ThisWorkbook.RibbonObject.Invalidate

4. 延迟调用Invalidate #

安装加载项后立即调用Invalidate可能因Ribbon未加载完成报错，添加短暂延迟：

' 替换原有的ribbonObject.Invalidate
Application.OnTime Now + TimeValue("00:00:01"), "InvalidateRibbon"

' 新增单独的Invalidate方法
Public Sub InvalidateRibbon()
    If Not ThisWorkbook.RibbonObject Is Nothing Then
        ThisWorkbook.RibbonObject.Invalidate
    End If
End Sub

5. 调整加载顺序 #

设置你的管理加载项为“启动时加载”，确保它在其他带Ribbon的加载项之后加载，避免IRibbonUI对象被覆盖。

内容的提问来源于stack exchange，提问作者RosMane