A Novel Method to Monitor Excel's Edit Mode
Tags: programming, excel, fsharp, dotnet
Published: 2023-04-11 06:27:16

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.

![Excel's Edit Modes](https://www.sharpcells.com/images/edit-mode.85e57c45.gif)

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`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._application.run?view=excel-pia) 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`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._application.ready?view=excel-pia) 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](https://www.codeproject.com/Articles/20267/Determining-if-Excel-is-in-Edit-mode) or [intercepting messages from the Win32 API](https://www.codeproject.com/Articles/35398/Determining-if-Excel-is-in-Edit-mode-with-Win32-In) 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:

```c
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](https://learn.microsoft.com/en-us/cpp/build/reference/dumpbin-reference?view=msvc-170) utility:

```pwsh
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](https://learn.microsoft.com/en-us/dotnet/standard/native-interop/pinvoke) 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.

```fsharp
// 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`](https://stackoverflow.com/a/51810805/14134059) calling convention, but this is purely speculation.

```fsharp
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`](https://learn.microsoft.com/en-us/dotnet/fsharp/language-reference/basic-types) to describe pointers as we will do the type conversions ourselves if it is required. [`LayoutKind.Sequential`](https://learn.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.layoutkind) 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:

```fsharp
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 [`&&`](https://fsharp.github.io/fsharp-core-docs/reference/fsharp-core-languageprimitives-intrinsicoperators.html#(~&&)) operator. The `nativeptr<FmlaInfo>` is converted to a `nativeint` using the [`NativePtr.toNativeInt`](https://fsharp.github.io/fsharp-core-docs/reference/fsharp-nativeinterop-nativeptrmodule.html) 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.

```fsharp
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](https://learn.microsoft.com/en-us/dotnet/fsharp/language-reference/discriminated-unions), `EditInfo`, that ensures no undefined data appears in our types.

```c
#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`](https://docs.microsoft.com/en-us/dotnet/api/system.readonlyspan-1).

```fsharp
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.

```fsharp
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`](https://learn.microsoft.com/en-us/dotnet/api/extensibility.idtextensibility2.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`](https://learn.microsoft.com/en-us/dotnet/fsharp/language-reference/members/events) 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](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui2/e0565d0e-289c-481f-9cfe-578665e307e0), and [`Invalidate`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.core.iribbonui.invalidate?view=office-pia) 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.

```fsharp
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!