Stepping any macro causes my ribbon bar to fail

dovbniak · Feb 13, 2012

I am new to this forum and thanks to all who read this post.

I have created a custom ribbon for an Excel spreadsheet and have the following problem when I break/step any macro in VBA:

If I put in a break point in a macro and then abort the macro when the execution reaches that point my ribbon bar no longer works. As I mouse over I get all sorts of errors stating that variables are out of range etc. I have to restart Excel and then the ribbon bar works fine. Here are the steps I do to create this problem:

1. create a custom ribbon complete with callbacks and macros that are called for each callback
2. open the excel file and access the vba editor
3. open any macro and put in a break point
4. run the macro
5. when the macro gets to the break point, stop the macro.
6. If I return to Excel, my ribbon bar no longer works.

The macros I try to break and then abort have nothing to do with the custom ribbon and aren't even called by the ribbon.

Not a huge problem but a little annoying that every time I try to troubleshoot a macro I have to restart Excel.

BTW:

1. I am running Windows XP SP3
2. I am using Office 2010 but this happens in Office 2007 as well

Ken Puls · Feb 15, 2012

This is, unfortunately, to be expected. Every time there is an error, the RibbonX object goes out of scope, and we can't get it back. The only way to do so is to re-open the workbook.

Rory Archibald did come up with a method to store a pointer to the Ribbon object though, which looks like this:

Code:

Public Declare Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (destination As Any, source As Any, _
    ByVal length As Long)

Public Sub ribbonLoaded(ribbon As IRibbonUI)
   ' Store pointer to IRibbonUI
   Dim lngRibPtr As Long

' Store the custom ribbon UI Id in a static variable.
' This is done once during load of UI. I.e. during workbook open.
    Set guiRibbon = ribbon
    lngRibPtr = ObjPtr(ribbon)
    ' Write pointer to worksheet for safe keeping
    Tabelle2.Range("A1").Value = lngRibPtr
End Sub

Function GetRibbon(lngRibPtr as Long) As Object
   Dim objRibbon As Object

   CopyMemory objRibbon, lngRibPtr, 4

   Set GetRibbon = objRibbon
   Set objRibbon = Nothing
End Function

You'd then use the following code to restablish a link to the ribbon when it dies:

Code:

Public Sub DoButton(ByVal control As IRibbonControl)
' The onAction callback for btn1 and btn2
    
    ' Toggle state
    Toggle12 = Not Toggle12
    
    ' Invalidate the ribbon UI so that the enabled-states get reloaded
    If Not (guiRibbon Is Nothing) Then
        ' Invalidate will force the UI to reload and thereby ask for their enabled-states
        guiRibbon.Invalidate 'Control ("tabCustom") InvalidateControl does not work reliably

    Else
      Set guiRibbon = GetRibbon(CLng(Tabelle2.Range("A1").Value))
      guiRibbon.Invalidate
        ' The static guiRibbon-variable was meanwhile lost
'        MsgBox "Due to a design flaw in the architecture of the MS ribbon UI you have to close " & _
'            "and reopen this workbook." & vbNewLine & vbNewLine & _
'            "Very sorry about that.", vbExclamation + vbOKOnly

      MsgBox "Hopefully this is sorted now?"
        ' Note: In the help we can find
        ' guiRibbon.Refresh
        ' but unfortunately this is not implemented.
        ' It is exactly what we should have instead of that brute force reload mechanism.
    End If
    
End Sub

Rory's original post can be found here

Stepping any macro causes my ribbon bar to fail

dovbniak

New member

Ken Puls

Administrator