在没有模块级变量的情况下使功能区控制无效 [英] Invalidate Ribbon Control Without Module-Level Variables

问题描述

我已经开发了一个包含自定义功能区的Excel加载项.在某些情况下，我希望能够使功能区上的控件无效(启用/禁用)，但是我能找到的每个示例都在第一次加载功能区时使用模块级或全局变量来存储功能区对象.这似乎是个好方法，但是，如在此处列出所示，在某些情况下变量可以为空.

I've developed an Excel add-in that includes a custom ribbon. I'd like to be able to invalidate (enable/disable) a control on the ribbon in certain situations, but every example I can find uses a module-level or global variable to store the ribbon object when the ribbon is first loaded. This seems like a good way to do it, but, as listed here, there are instances when variables can empty.

所以我想知道，是否有另一种方法可以实现在Excel功能区中启用/禁用控件的结果，而无需使用变量来存储功能区对象，甚至根本不使用invalidate方法?

So I'm wondering, is there a different way to achieve the result of enabling/disabling a control in an Excel ribbon without using a variable to store the ribbon object or without even using the invalidate method at all?

推荐答案

阅读您的描述后，我认为您已经开发了一个纯Excel VBA加载项(而不是Excel VSTO加载项).因此，恐怕没有其他方法可以实现您的目标.幸运的是，有一种解决方法可以在重置后将对象引用恢复为功能区对象.

After reading your description I assume that you has developed a pure Excel VBA Add-In (instead of e.g. an Excel VSTO Add-In). Therefore, I'm afraid that there is no other way to achieve your goal. Fortunately, there is a workaround to restore the object reference to the ribbon object after a reset.

解决方法: 在"Ribbon_Load"事件处理程序中，您可以将对象引用设置为Excel功能区对象，还应该保存功能区对象的"ObjPtr()"值(例如，在工作表单元格中).例如这样的

Workaround: Within the "Ribbon_Load" event handler, where you would set the object reference to the Excel ribbon object, you should also save the "ObjPtr()" value of the ribbon object (e.g. within a worksheet cell). For example like this:

Public gobjRibbon As Office.IRibbonUI

' Callback for customUI.onLoad
Sub Ribbon_Load(ribbon As Office.IRibbonUI)

    Set gobjRibbon = ribbon

    SampleWorksheet.Cells(1,1).Value = ObjPtr(ribbon)
End Sub

这样做，您以后可以恢复对功能区对象的引用(如有必要).您可以通过以下示例调用"RefreshRibbon"过程(该过程也会使整个功能区无效)来实现此目的:

In doing so, you can later restore the reference to the ribbon object (if necessary). You can achieve this by calling the "RefreshRibbon" procedure (which also invalidates the whole ribbon) from the following example:

#If VBA7 Then
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
        ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)
#Else
    Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
        ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)
#End If

#If VBA7 Then
Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If

    Dim objRibbon As Object

    Call CopyMemory(objRibbon, lRibbonPointer, LenB(lRibbonPointer))

    Set GetRibbon = objRibbon
    Set objRibbon = Nothing
End Function

Public Sub RefreshRibbon()

    If gobjRibbon Is Nothing Then
        Set gobjRibbon = GetRibbon(SampleWorksheet.Cells(1,1).Value)
    ' Else: Do nothing!
    End If

    On Error Resume Next
    gobjRibbon.Invalidate
    On Error GoTo 0
End Sub

我建议在Excel会话结束时清除辅助单元格，因为否则Excel有时会崩溃.

I recommend to clear the auxiliary cell at the end of an Excel session, because otherwise Excel suprisingly crashes sometimes.

替代: 重新开发您的VBA外接程序作为VSTO外接程序，以避免丢失丢失的对象引用.

Alternative: Re-develop your VBA Add-In as a VSTO Add-In to avoid having trouble with lost object references.

这篇关于在没有模块级变量的情况下使功能区控制无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！