Category Archives: Excel

Capture Excel Workbook Close, Worksheet Delete, Worksheet Rename Events

I created a C# class to handle some of missing Excel application level events such as : Workbook close, worksheet rename, worksheet delete, range name added, range name deleted, application activated, application deactivated.

Excel does offer a workbook closing event, but the user can cancel it. The workbook closed event fires when the workbook actually closes.

Usage is fairly straightforward :

ExcelApplicationEvents _appEvents = 
new ExcelApplicationEvents(_app, 
ApplicationEvents.WorkbookClosed);

_appEvents.WorkbookClosed += 
_appEvents_WorkbookClosed;

Preserve Ribbon Object References

It’s a common problem in VBA to lose reference to module level variables. For instance, untrapped errors will set object variables to nothing.

When dealing with ribbons in VBA this can be a serious problem : loss of a reference to the ribbon object means all further use of the object is impossible (Invalidate, etc.)

To work around this problem we have to store a reference to the ribbon object and retrieve it later if the reference is ever lost.
We can accomplish this using the CopyMemory, SetProp, GetProp , and RemoveProp APIs.

I store the ribbon in memory and save the memory pointer handle into Excel’s window property list.
The first event that fires when a workbook with a ribbon opens is the onload event of custom UI.

Private Sub RibbonOnLoad(Ribbon As IRibbonUI)
    #If VBA7 Then
        Dim lPtr As LongPtr
    #Else
        Dim lPtr As Long
    #End If
    
    If PointerExists Then Set RibbonObj = Nothing
    Set m_oRibbon = Ribbon
    lPtr = ObjPtr(Ribbon)
    If lPtr <> 0 Then
        If Not SetProp(Application.hWnd, m_sRibbonPtrName, lPtr) = 1 Then _
        Err.Raise vbObjectError + 1, , "Error setting window property"
    End If
End Sub

Here I check if there is already an instance of the ribbon saved to memory, and remove the old reference saved against the window property list.

Public Property Set RibbonObj(ByVal NewValue As Object)
    Dim lR As Long
    
    If NewValue Is Nothing Then
        lR = RemoveProp(Application.hWnd, m_sRibbonPtrName)
    End If
    Set m_oRibbon = NewValue
End Property

The set property takes care of this. Setting the ribbon property to nothing removes the pointer from the property list.

Public Property Get RibbonObj() As Object
    Dim obj As Object
    #If VBA7 And Win64 Then
        Dim lPtr As LongPtr
    #Else
        Dim lPtr As Long
    #End If
        
    If Not m_oRibbon Is Nothing Then
        Set RibbonObj = m_oRibbon
    Else
        lPtr = GetProp(Application.hWnd, m_sRibbonPtrName)
        If lPtr = 0 Then
            Err.Raise vbObjectError + 1, , "Error retrieving window property"
        Else
            CopyMemory obj, lPtr, 4
            Set m_oRibbon = obj
            Set RibbonObj = m_oRibbon
            CopyMemory obj, 0&, 4
        End If
    End If
End Property

Later when a reference to the ribbon object is needed, a still valid module reference will be returned. Otherwise the pointer reference is retrieved and used to instantiate the ribbon object from memory.

Preserve Ribbon Object References
Preserve Ribbon Object References

Download workbook with source here.

Excel Trust Center Setting Modification

For testing, I needed a quick way to switch Excel Trust Center settings between restrictive and permissive. Using Process Monitor and these settings, I was able to observe the registry keys and settings Excel’s Trust Center uses to store values.

Using these key/values, I created a VBScript file to automate switching between restrictive and more permissive Trust Center settings.
The script needs to be executed when Excel is not running.  The new settings will be applied the next time Excel is run.

Excel Trust Center Settings
Excel Trust Center Setting Process Monitor
Trust Center Script Dialog
Trust Center Script Dialog

Selecting Yes will set the following Excel Trust Center settings :

Trusted Locations :
Turn off Allow Trusted Locations on my network

Trusted Documents :
Turn off Allow documents on a network to be trusted

Add-ins :
Turns on Require Application Add-ins to be signed by Trusted Publisher

ActiveX Settings :
Turns on Prompt me before enabling Unsafe for Initialization (UFI) controls with additional restrictions and Safe for Initialization (SFI) controls with minimal restrictions

Turns on Safe mode

Macro Settings :
Turns on Disable all macros with notification
Turns off Trust access to the VBA project object model

Protected View :
Turns on Enable Protected View for files originating from the internet
Turns on Enable Protected View for files located in potentially unsafe locations
Turns on Enable Data Execution Prevention mode

External Content :
Turns on Disable all Data Connections
Turns on Disable automatic update of Workbook Links

Selecting No will set the following Excel Trust Center settings :

Trusted Locations :
Turns on Allow Trusted Locations on my network

Trusted Documents :
Turns on Allow documents on a network to be trusted

Add-ins :
Turns off Require Application Add-ins to be signed by Trusted Publisher

ActiveX Settings :
Turns on Prompt me before enabling all controls with minimal restrictions

Macro Settings :
Turns on Enable all macros
Turns on Trust access to the VBA project object model

Protected View :
Turns off Enable Protected View for files originating from the internet
Turns off Enable Protected View for files located in potentially unsafe locations
Turns off Enable Data Execution Prevention mode

External Content :
Turns on Enable all Data Connections
Turns on Enable automatic update for all Workbook Links