A Novel Method to Monitor Excel's Edit Mode

A poorly understood feature of Excel is its various edit modes when working with cells. Most users are implicitly aware of these features but few would have noticed the text changing in the lower left corner of the Excel window as the modes are cycled through.

These modes describe the current behavior when you take various actions in a cell:

• Ready: Excel is not editing a cell.
• Enter: Excel is editing a cell and pressing the arrow keys will change focus to another cell or switch to Point mode.
• Edit: Excel is editing a cell and pressing arrow keys will move the caret within the cell text.
• Point: Excel is editing a cell but the current selection is pointing to another cell.

This is all very interesting arcane knowledge but what relevance does it have to Sharp Cells? Well, the current edit mode restricts what actions can be performed by Excel at the current time. Specifically, calls to command macros via Application.Run will fail with an exception.

This is no good for Sharp Cells as we use command macros extensively for managing UDF registration among other things. Excel's COM API does include the Application.Ready property but this does not appear to refer to the same readiness from Excel as Ready can be true while in edit mode and Run will still fail. Most native Excel ribbon buttons are disabled while in edit mode so we would like to copy this behavior for ourselves.

A search of the internet suggests various hacky workarounds like checking the enabled status of command bars or intercepting messages from the Win32 API which may not be stable across Excel versions.

The solution presented below is novel, robust, and uses only official Excel APIs though they are essentially undocumented. The only hints to this technique are in the XLCALL.H file from the xll SDK. The key lines are reproduced below for posterity:

long pascal LPenHelper(int wCode, VOID *lpv);

#define xlSpecial    0x4000
/* GetFooInfo are valid only for calls to LPenHelper */
#define xlGetFmlaInfo    (14 | xlSpecial)

/* edit modes */
#define xlModeReady 0    // not in edit mode
#define xlModeEnter 1    // enter mode
#define xlModeEdit  2    // edit mode
#define xlModePoint 4    // point mode

typedef struct _fmlainfo
{
    int wPointMode; // current edit mode.  0 => rest of struct undefined
    int cch;        // count of characters in formula
    char *lpch;     // pointer to formula characters.  READ ONLY!!!
    int ichFirst;   // char offset to start of selection
    int ichLast;    // char offset to end of selection (may be > cch)
    int ichCaret;   // char offset to blinking caret
} FMLAINFO;

This looks very promising but how do we use it? LPenHelper is not a documented function in the Excel SDK. With a little searching, LPenHelper function can be found in XLCALL32.DLL by using the dumpbin utility:

dumpbin /exports "C:\Program Files\Microsoft Office\root\Office16\XLCALL32.DLL"

> Microsoft (R) COFF/PE Dumper Version 14.33.31630.0
> Copyright (C) Microsoft Corporation.  All rights reserved.
> 
> 
> Dump of file C:\Program Files\Microsoft Office\root\Office16\XLCALL32.DLL
> 
> File Type: DLL
> 
>   Section contains the following exports for XLCall32.dll
> 
>     00000000 characteristics
>     6331183F time date stamp Mon Sep 26 13:10:55 2022
>         0.00 version
>            1 ordinal base
>            4 number of functions
>            4 number of names
> 
>     ordinal hint RVA      name
>           2    0 00001080 Excel4
>           3    1 000011B0 Excel4v
>           4    2 00001220 LPenHelper
>           1    3 00001070 XLCallVer

This can also be confirmed by running the same command on XLCALL32.LIB included with the xll SDK. This same function exists going all the way back to the XLCALL32.LIB included with the Excel 1997 SDK so we can safely say it isn't going anywhere. Now we have our way in, lets start writing some code!

Defining the Native Interface

Note: While the rest of the code in this post is F#, Sharp Cells' language of choice, the techniques described should be applicable to any language capable of working with native dlls, including VBA.

.NET's Platform Invoke features are extensive and many automatic conversions are offered which can make interop easier in some circumstances however it has been this author's experience that performing "dumb" interop and manual type conversions is the most reliable method when working with Excel.

// long pascal LPenHelper(int wCode, VOID *lpv);
[<DllImport("XLCALL32.DLL", CallingConvention = CallingConvention.StdCall)>]
extern int private LPenHelper(int wCode, nativeint lpv)

The above code tells F# how to access the native function. It is curious that the C signature includes both long and int types which are both 32-bits in today's systems. Perhaps this relates to compatibility with Excel's 16-bit heritage, much like the pascal calling convention, but this is purely speculation.

type xlEditMode = 
| xlModeReady = 0 // not in edit mode
| xlModeEnter = 1 // enter mode
| xlModeEdit  = 2 // edit mode
| xlModePoint = 4 // point mode

[<Struct; StructLayout(LayoutKind.Sequential)>]
type private FmlaInfo =
    val wPointMode: xlEditMode // current edit mode.  0 => rest of struct undefined
    val cch: int               // count of characters in formula
    val lpch: nativeint        // pointer to formula characters.  READ ONLY!!!
    val ichFirst: int          // char offset to start of selection
    val ichLast: int           // char offset to end of selection (may be > cch)
    val ichCaret: int          // char offset to blinking caret

Once again we use nativeint to describe pointers as we will do the type conversions ourselves if it is required. LayoutKind.Sequential is essential for ensuring that the fields of our struct appear in the same order they are defined in the typedef.

Getting the Formula Info

With our structs defined we are now ready to call the function:

let xlGetFmlaInfo() = 
    let code = LanguagePrimitives.EnumToValue xlAuxFunc.xlGetFmlaInfo
    let mutable fmlaInfo = FmlaInfo()
    let ptr = NativePtr.toNativeInt &&fmlaInfo
    let _ = LPenHelper(code, ptr)
    fmlaInfo

We need to pass a pointer to LPenHelper for it to return the information. To do this we allocate an empty FmlaInfo with a mutable binding and obtain its address using the && operator. The nativeptr<FmlaInfo> is converted to a nativeint using the NativePtr.toNativeInt function.

We discard the returned integer from LPenHelper. Empirically, this function was found to only ever return 0.

Making a Safe API

We can now get the formula info from Excel but it would be very irresponsible for us to return a bare "READ ONLY!!!" pointer to our consumers so let's clean it up a little.

In the trivial case we only care about the edit mode and our xlEditMode enum is a safe value to return so that will be sufficient. However, if we want the entire formula information we will need to design some safe types.

type FormulaInfo =
    {
        /// The formula entered into the cell
        Formula: string
        /// Offset to start of selection
        First: int
        /// Offset to end of selection (may be > Formula.Length)
        Last: int
        /// Offset to blinking caret
        Caret: int
    }

type EditInfo =
| Ready
| Enter of FormulaInfo
| Edit of FormulaInfo
| Point of FormulaInfo

Taking particular note of the comment on wPointMode we have defined a discriminated union, EditInfo, that ensures no undefined data appears in our types.

#define xlModeReady 0
int wPointMode; // current edit mode.  0 => rest of struct undefined

Additionally, we can convert the lpch pointer into a Formula: string using our friend ReadOnlySpan.

module private XLCall32 = 
    let ofFmlaInfo (x:FmlaInfo) =
        let formula =
            if x.cch > 0 then
                let vptr = NativePtr.toVoidPtr (NativePtr.ofNativeInt<char> x.lpch)
                let chars = ReadOnlySpan<char>(vptr, x.cch)
                String(chars)
            else ""
        {
            Formula = formula
            First = x.ichFirst
            Last = x.ichLast
            Caret = x.ichCaret
        }

type XLCall32 private () =
    static member EditInfo =
        let info = XLCall32.xlGetFmlaInfo()
        match info.wPointMode with
        | xlEditMode.xlModeReady -> EditInfo.Ready
        | xlEditMode.xlModeEnter -> EditInfo.Enter (XLCall32.ofFmlaInfo info)
        | xlEditMode.xlModeEdit -> EditInfo.Edit (XLCall32.ofFmlaInfo info)
        | xlEditMode.xlModePoint -> EditInfo.Point (XLCall32.ofFmlaInfo info)
        | x -> invalidOp $"Unexpected value for xlEditMode: {x}"

    static member EditMode = 
        let info = XLCall32.xlGetFmlaInfo()
        info.wPointMode

The use of static properties for this API may be controversial but it was felt that the semantics of LPenHelper were close enough to behaving like properties that it would make sense from the caller's perspective.

Updating the Ribbon

Now we can determine Excel's edit mode, how do we use it to disable buttons on our ribbon? The original problem was that Excel doesn't provide any useful events for knowing the edit mode status and this hasn't changed so instead we will use polling to monitor the value and create our own event.

type EditModeMonitor(msDelay:int) = 
    let cts = new CancellationTokenSource()

    let mutable editMode = XLCall32.EditMode
    let changed = Event<xlEditMode>()
    let setEditMode x = 
        if editMode <> x then
            editMode <- x
            changed.Trigger(editMode)

    let rec monitor() = 
        async {
            do! Async.Sleep(msDelay)
            setEditMode XLCall32.EditMode
            return! monitor()
        }

    do Async.Start(monitor(), cts.Token)

    member __.Current = editMode
    member __.Changed = changed.Publish

    interface IDisposable with
        member __.Dispose(): unit = 
            cts.Cancel()
            cts.Dispose()

EditModeMonitor is a simple class that we create in the OnConnection and Dispose in the OnDisconnection calls to the Sharp Cells ribbon component.

The monitor function runs a loop on a background thread until the class is disposed and we publish a Changed Event to notify the our ribbon state of changes. Configuring a polling rate of 10ms consumes <<1% CPU resources and provides almost immediate updates to our ribbon.

Now, we just subscribe to the event, update the enabled properties, and Invalidate the ribbon as required.

That's it? Unfortunately we're not quite done. A particularly vexatious user could start editing a cell and immediately click on a ribbon button, before our event has a chance to propagate. To protect against this we simply add a second check around the relevant onAction event handlers.

let ensureReady action = 
    match XLCall32.EditMode with
    | xlEditMode.xlModeReady -> action()
    | _ -> error "Invalid action when Excel is not Ready."

That's it. A robust and novel way to monitor and respond to Excel's edit mode changes. Happy Excelling!