DEV Community: Sharp Cells

A Novel Method to Monitor Excel's Edit Mode

Sharp Cells — Tue, 11 Apr 2023 06:27:16 +0000

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!

Experimentation with Optimized Closures

Sharp Cells — Fri, 31 Mar 2023 00:19:44 +0000

Experimentation with Optimized Closures

At Sharp Cells, we love functional programming but we also love writing high-performance code! One feature we love - partial application, represents a potential source of performance frustration.

Partial Application?

For those unaware, partial application is a technique where you can pass some of the arguments to a function and receive a new function that contains the provided arguments and expects only the remaining arguments.

let add a b = a + b

// Call normally
add 1 2 = 3

// Partial application creates a new function that adds 1
let addOne = add 1

// Use the partially applied function
addOne 2 = 3

This technique is made easy from F# using a language feature known as currying and is common in many functional programming languages. But, partial application is not unique to functional programming languages and most programming languages can represent the same thing with some additional ceremony:

Using JavaScript:

function add(x, y) {
  return x + y;
}

// Partially apply the add function to create a new function that adds 1 to any number
const addOne = add.bind(null, 1);

// Call the new function with one argument
console.log(addOne(2)); // Output: 3

In JavaScript, we can use the bind method to create a new function with one of the arguments applied.

Using C#:

Func<int, int, int> add = (x, y) => x + y;

// Partially apply the add function to create a new function that adds 1 to any number
Func<int, int> addOne = x => add(1, y);

// Call the new function with one argument
Console.WriteLine(addOne(2)); // Output: 3

In C#, we can use lambda expressions to create a new Func delegate that captures the first argument.

So What's the Problem?

The source of the potential performance issues is better highlighted when looking at the different languages' implementations. To partially apply a function we must create a new function that encapsulates the first argument. Normal functions can't hold any state so we must create a closure - a special type of object that represents the state and the function to be called.

If, each time we use partial application we create a closure - a new object, when we no longer need that closure it must be removed from the heap, creating more work for the garbage collector and slowing down our program.

The F# Compiler Authors are Smart

Really smart. We made several attempts to demonstrate the effect of creating closures but each time the performance was equivalent to the fully applied version of the function. That's great but what's going on?

A Brief Detour into Inlining

When your F# gets compiled, it gets transformed in many ways to try to run your code in the fastest way possible. One of the ways code can be optimized is inlining - the compiler takes the definition of the function and replaces it with the body of the function. We can see the effect of inlining and other optimizations on F# by using SharpLab to compile F# and then decompile it to a low-level form of C#:

let add a b = a + b

let accumulate (sum: byref<_>) f = 
    sum <- f sum

type StackClosures() =

    member this.Inlined() = 
        let mutable sum = 0
        for i in 0 .. 10000 do
            sum <- add sum i
        sum

    member this.AlsoInlined() = 
        let mutable sum = 0
        for i in 0 .. 10000 do
            accumulate &sum (add i)
        sum

Optimized then decompiled to C#:

public class StackClosures
{
    public int Inlined()
    {
        int num = 0;
        int num2 = 0;
        while (num2 < 10001)
        {
            num += num2;
            num2++;
        }
        return num;
    }

    public int AlsoInlined()
    {
        int num = 0;
        int num2 = 0;
        while (num2 < 10001)
        {
            num = num2 + num;
            num2++;
        }
        return num;
    }
}

As you can see, the add and accumulate functions get completely eliminated by the compiler. Even with the tricky byref parameter. Similarly, the for loop is replaced with an equivalent while loop. Part of the reason F# can make these optimizations is immutability. If a value is immutable then it is always safe to replace it with its implementation. This is great for avoiding unnecessary closure allocation.

Defeating the Optimizer

Generally, this should be avoided, but for the sake of demonstration, we need to find a way to defeat the optimizer.

type StackClosures() =

    member this.NormalClosure() = 
        let mutable sum = 0
        let mutable x = 0
        for i in 0 .. 10000 do
            x <- i
            accumulate &sum (add x)
        sum

Adding an unnecessary variable mutable x is sufficient to prevent the optimizer from doing its thing. Now when we look at the decompilation we can see how the closure is made:

public class StackClosures
{
    public int NormalClosure()
    {
        int num = 0;
        int num2 = 0;
        int num3 = 0;
        while (num3 < 10001)
        {
            num2 = num3;
            int a = num2;
            FSharpFunc<int, int> fSharpFunc = new NormalClosure@13(a);
            num = fSharpFunc.Invoke(num);
            num3++;
        }
        return num;
    }
}

internal sealed class NormalClosure@13 : FSharpFunc<int, int>
{
    public int a;

    internal NormalClosure@13(int a)
    {
        this.a = a;
    }

    public override int Invoke(int b)
    {
        return a + b;
    }
}

There's the class we were expecting. Now each time we go through the loop a new instance of NormalClosure@13 is created, invoked, and discarded. Yuck!

A Better Way?

Objects aren't the only way to represent state in the world of .NET. We can also use structs. These are value types that contain state like an object but can be allocated on the stack instead of the heap allowing them to be created and destroyed without involving the garbage collector.

We can take inspiration from the implementation of NormalClosure@13 to create a struct version of a closure:

[<Struct>]
type StackFunc<'A, 'B, 'C> =
    {
        mutable A: 'A
        F: 'A -> 'B -> 'C
    }
    member inline this.Invoke(b) = this.F this.A b

Neat! But how do we know if it's faster?

BenchmarkDotNet

BenchmarkDotNet is the gold standard for benchmarking all flavors of .NET code. With this tool, we test our functions and compare their performance in a rigorous and reproducible way.

Turning our code into benchmark code is quite simple, we just some attributes to our class and away we go:

module TestFuncs = 

    let add a b = a + b

    let accumulate (sum: byref<_>) f = 
        sum <- f sum

    let accumulateFunc (sum: byref<_>) (f: StackFunc<_,_,_>) = 
        sum <- f.Invoke(sum)

    let expected =
        function
        | 1 -> 1
        | 10 -> 55
        | 100 -> 5050
        | 10000 -> 50005000
        | x -> failwith $"Undefined for {x}"

open TestFuncs

[<MemoryDiagnoser>]
type StackClosures() =

    [<Params(1, 10, 100, 10000)>]
    member val N = 1 with get, set

    [<Benchmark>]
    member this.Inlined() = 
        let mutable sum = 0
        for i in 0 .. this.N do
            sum <- add sum i
        if (sum <> expected this.N) then failwith "Inlined summed incorrectly"

    [<Benchmark>]
    member this.AlsoInlined() = 
        let mutable sum = 0
        for i in 0 .. this.N do
            accumulate &sum (add i)
        if (sum <> expected this.N) then failwith "AlsoInlined summed incorrectly"

    [<Benchmark>]
    member this.NormalClosure() = 
        let mutable sum = 0
        let mutable x = 0
        for i in 0 .. this.N do
            x <- i
            accumulate &sum (add x)
        if (sum <> expected this.N) then failwith "NormalClosure summed incorrectly"

    [<Benchmark>]
    member this.StackFunc() = 
        let mutable sum = 0
        let mutable addOnStack = 
            {
                StackFunc.A = 0
                F = add
            }
        for i in 0 .. this.N do
            addOnStack.A <- i
            accumulateFunc &sum addOnStack
        if (sum <> expected this.N) then failwith "StackFunc summed incorrectly"

BenchmarkRunner.Run<StackClosures>()
|> ignore

Results

Method	N	Mean	Error	StdDev	Gen0	Allocated
Inlined	1	1.568 ns	0.0536 ns	0.0550 ns	-	-
AlsoInlined	1	1.935 ns	0.0611 ns	0.0541 ns	-	-
NormalClosure	1	5.956 ns	0.1424 ns	0.2417 ns	0.0029	48 B
StackFunc	1	9.369 ns	0.1177 ns	0.1101 ns	-	-
Inlined	10	4.108 ns	0.0995 ns	0.0930 ns	-	-
AlsoInlined	10	11.470 ns	0.2381 ns	0.2228 ns	-	-
NormalClosure	10	26.572 ns	0.5540 ns	1.2730 ns	0.0158	264 B
StackFunc	10	35.152 ns	0.7291 ns	0.7161 ns	-	-
Inlined	100	28.490 ns	0.3842 ns	0.3594 ns	-	-
AlsoInlined	100	29.988 ns	0.4265 ns	0.3989 ns	-	-
NormalClosure	100	233.629 ns	3.7461 ns	3.5041 ns	0.1447	2424 B
StackFunc	100	315.870 ns	4.5391 ns	4.2459 ns	-	-
Inlined	10000	2,224.525 ns	30.0005 ns	28.0625 ns	-	-
AlsoInlined	10000	2,216.351 ns	26.0386 ns	24.3565 ns	-	-
NormalClosure	10000	21,989.143 ns	273.0534 ns	242.0547 ns	14.3433	240024 B
StackFunc	10000	30,848.998 ns	589.8022 ns	551.7014 ns	-	-

Looking at the results we can see that we have successfully avoided allocation of the closure but the code is... slower. This is unexpected and extremely unfortunate but what's going on?

Let's take another look at the decompilation. Specifically the Invoke method.

public C Invoke(B b)
{
    return FSharpFunc<A, B>.InvokeFast(F@, A@, b);
}

That's not a normal function invocation and it's bound to be causing some additional overhead. But all is not lost! There's more than one way to represent a function in our struct.

Alternative Struct Representations

The two other possible ways we can represent a function are the Func delegate and the OptimizedClosures.FSharpFunc. Let's create two more structs and rerun the benchmarks.

[<Struct>]
type StackOptFunc<'A, 'B, 'C> =
    {
        mutable A: 'A
        F: OptimizedClosures.FSharpFunc<'A, 'B, 'C>
    }
    member inline this.Invoke(b) = this.F.Invoke(this.A, b)

[<Struct>]
type StackDelegate<'A, 'B, 'C> =
    {
        mutable A: 'A
        F: Func<'A, 'B, 'C>
    }
    member inline this.Invoke(b) = this.F.Invoke(this.A, b)

module TestFuncs = 
    let accumulateOptFunc (sum: byref<_>) (f: StackOptFunc<_,_,_>) = 
        sum <- f.Invoke(sum)

    let accumulateDelegate (sum: byref<_>) (f: StackDelegate<_,_,_>) = 
        sum <- f.Invoke(sum)

[<MemoryDiagnoser>]
type StackClosures() =

    [<Benchmark>]
    member this.StackOptFunc() = 
        let mutable sum = 0
        let mutable addOnStack = 
            {
                StackOptFunc.A = 0
                F = OptimizedClosures.FSharpFunc<_,_,_>.Adapt(add)
            }
        for i in 0 .. this.N do
            addOnStack.A <- i
            accumulateOptFunc &sum addOnStack
        if (sum <> expected this.N) then failwith "StackOptFunc summed incorrectly"

    [<Benchmark>]
    member this.StackDelegate() = 
        let mutable sum = 0
        let mutable addOnStack = 
            {
                StackDelegate.A = 0
                F = add
            }
        for i in 0 .. this.N do
            addOnStack.A <- i
            accumulateDelegate &sum addOnStack
        if (sum <> expected this.N) then failwith "StackDelegate summed incorrectly"

And the results look great! The OptimizedClosures.FSharpFunc version gets ahead of the Func version and both are substantially faster (almost 50%) than the NormalClosure case for large N. The Func delegate does allocate some memory but as it is wrapping the original function it only needs to be allocated once. Both are, however, around, 5x slower than the fully inlined version. NormalClosure and StackOptFunc are roughly the same speed for N = 1.

Method	N	Mean	Error	StdDev	Gen0	Allocated
Inlined	1	1.568 ns	0.0536 ns	0.0550 ns	-	-
NormalClosure	1	5.956 ns	0.1424 ns	0.2417 ns	0.0029	48 B
StackOptFunc	1	5.771 ns	0.1231 ns	0.1152 ns	-	-
StackDelegate	1	11.009 ns	0.2426 ns	0.4120 ns	0.0038	64 B
Inlined	10	4.108 ns	0.0995 ns	0.0930 ns	-	-
NormalClosure	10	26.572 ns	0.5540 ns	1.2730 ns	0.0158	264 B
StackOptFunc	10	16.039 ns	0.3389 ns	0.2830 ns	-	-
StackDelegate	10	25.177 ns	0.5126 ns	0.5484 ns	0.0038	64 B
Inlined	100	28.490 ns	0.3842 ns	0.3594 ns	-	-
NormalClosure	100	233.629 ns	3.7461 ns	3.5041 ns	0.1447	2424 B
StackOptFunc	100	115.131 ns	1.3663 ns	1.2781 ns	-	-
StackDelegate	100	184.305 ns	3.0298 ns	2.8340 ns	0.0038	64 B
Inlined	10000	2,224.525 ns	30.0005 ns	28.0625 ns	-	-
NormalClosure	10000	21,989.143 ns	273.0534 ns	242.0547 ns	14.3433	240024 B
StackOptFunc	10000	13,130.112 ns	206.8013 ns	193.4421 ns	-	-
StackDelegate	10000	15,543.613 ns	169.1367 ns	158.2106 ns	-	64 B

Conclusions

We have successfully demonstrated that it is possible to significantly improve the performance of closures for partial application by replacing the standard representation with a struct version. So are we going to replace all closures in our Sharp Cells add-in with StackOptFunc? Not at this stage.

Without a detailed analysis of the compilation output, it's not clear whether the existing closures are being inlined (and therefore faster) or allocated (and slower) than the struct version. We hope that an optimization like this will find its way into the compiler so that everyone can enjoy faster closures when they cannot be fully inlined.