Detect If Formula Was Invoked Via F2+{Enter} Or Double-click+{Enter}
Alexander Rickman
Hi all,
I have built a custom RTD server (deriving from ExcelRtdServer) and I am trying to determine a way to preserve the manual (single-cell) recalculation functionality of non-RTD functions in my custom RTD wrapper functions. I am aware that I could create a button on the ribbon that runs a callback to manually refresh data, in fact I have already done so, but my users are very disturbed by the fact that they can’t get live data by manually refreshing a cell themselves.
Some background: In a standard Excel function, one can manually refresh a single cell via keying F2+{Enter} or double-clicking into a cell and then keying {Enter}. In both cases, Excel first enters “Edit Mode”, and then keying {Enter} actually invokes the function. Is there any way to detect this within a function call? And if not from within a function call, could I somehow hook window events (possibly borrowing some logic from ExcelDna.IntelliSense) to detect if Excel is in “Edit Mode”, set a global boolean, and then read that from within a function call?
Here’s how I plan on using that info from within an RTD wrapper function call: If a function was invoked via keying F2+{Enter} or double-clicking into a cell and then keying {Enter}, I can use the function execution info (i.e. a string comprised of the function name + parameters), read from a dictionary keyed on function execution info (with live RTD topics as its values), pull out the associated topic, and then update it via topic.UpdateValue(). This would also refresh any other existing cells with the same topic, which is ideal.
I am at a standstill here, so I’ll take all the suggestions I can get.
Thanks,
Alexander
1585471449
Govert van Drimmelen
Hi Alexander,
Excel sheets in Manual recalculation mode don’t work easily with RTD-based functions.
Maybe you need non-RTD functions that are just marked IsVolatile=true, and then do some caching of the values at the back.
This will let the functions recalculate on every F9 press.
Do you need push-updates in addition to the Manual calculation mode updates?
Another options might be to skip the RTD part for sheets that are doing manual recalculation, thus making those function calls synchronous.
To check this, you need to mark your function as IsMacroType=true, then you can call
var calculationMode = XlCall.Excel(XlCall.xlfGetDocument, 14)
1 = Automatic
2 = Automatic except tables
3 = Manual
Another approach is to switch to the “native async functions” of Excel. If you look for “ExcelAsyncHandle” on the Google Group or GitHub, you should find some examples.
Native async functions work different to RTD-based function, in that the user can’t interact with the workbook while the functions are running.
So the interaction can be quite awkward.
But they do work nicely with Manual calculation mode.
Trying to build a different input and calculation model on top of Excel, detecting editing events etc. seem challenging.
Calling topic.UpdateValue() does not help you at all – your user (or some macro, I suppose) needs to do the recalculation after Excel has responded to the UpdateValue() and fetched the data, which might be some seconds later.
-Govert
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/00ad6f2a-319c-4abc-863d-8fdb2ab3cc13n%40googlegroups.com.
Kedar Kulkarni
Alexander Rickman
Alexander Rickman
Kedar Kulkarni
using System;
using System.Linq;
using System.Reactive.Linq;
namespace DCTools
{
public static class RxExample
{
[ExcelFunction(Name ="Clock.Live") ]
public static object LiveClock(object name)
{
var intervalSeconds = 2;
return RxExcel.Observe("LiveClock", new[] { name, intervalSeconds }, () =>
Observable.Interval(TimeSpan.FromSeconds(intervalSeconds)).Select(_ => GetClock(name)));
}
[ExcelFunction(Name = "Clock.F2")]
public static object F2Clock(object name)
{
return GetClock(name);
}
private static string GetClock(object name)
{
return name.ToString() + " - " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
}
public static class RxExcel
{
public static IExcelObservable ToExcelObservable<T>(this IObservable<T> observable)
{
return new ExcelObservable<T>(observable);
}
public static object Observe<T>(string functionName, object parameters, Func<IObservable<T>> observableSource)
{
return ExcelAsyncUtil.Observe(functionName, parameters, () => observableSource().ToExcelObservable());
}
}
public class ExcelObservable<T> : IExcelObservable
{
readonly IObservable<T> _observable;
public ExcelObservable(IObservable<T> observable)
{
_observable = observable;
}
public IDisposable Subscribe(IExcelObserver observer)
{
return _observable.Subscribe(value => observer.OnNext(value), observer.OnError, observer.OnCompleted);
}
}
}
Alexander Rickman
Toby Jacobs
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/01e001d972be%24eae48da0%24c0ada8e0%24%40gmail.com.
Alexander Rickman
After working on this for a while, I finally came with a solution. Originally, I was attempting to sub class Excel’s editor window using the WinAPI and NativeWindow to listen for specific messages that would indicate that Excel’s edit mode state had changed, but the WndProc override was never getting invoked. I remembered reading about a post on Reddit a couple of months ago where someone described using the undocumented LPenHelper function (found in the the Excel SDK) to monitor the state of Excel’s edit mode by periodically calling it and inspecting inspecting the return value. I also noticed that ExcelDna.IntelliSense uses this function, albeit for a different purpose. All that being said, I dislike polling, but I don’t really see any other way of accomplishing this, so I wrote a monitor class to poll Excel’s edit mode state every 10ms, using a System.Threading.Timer, which runs on a ThreadPool thread. In my application, I create a globally scoped singleton instance of the class in the AutoOpen method and reference it where needed. Below is the how it all works together:
- An RTD server scoped ConcurrentDictionary keyed on function execution info (i.e. a string comprised of the function name + parameters), with live RTD topics as its values is maintained.
- A static method was defined on the RTD server to retrieve the current running instance of it.
- An instance method was added to retrieve a topic from the aforementioned dictionary.
- Within a function call, Excel’s last edit mode state is checked. If the the edit mode is != ready, then the running instance of the RTD server is retrieved (if it exists) and a key consisting of the function’s execution info is created and passed to the dictionary to retrieve the topic. If that topic exists, then the required code to retrieve the live data point is run, topic.UpdateValue is called and then topic.Value is returned to the caller. If the edit mode == ready and/or the topic is not found in the dictionary, then it is retrieved from the RTD server.
So far, testing has proven this to be a reliable way to handle single-cell manual recalculation, and I very happy with it.
Below is the code for my ExcelEditModeMonitor class and a sample of how to use it.
ExcelEditModeMonitor
using System; using System.Runtime.InteropServices; using System.Threading; /// <summary> /// Represents the "cell edit Mode" of Excel by periodically polling /// its state via the XLCALL32.dll function "LPenHelper" and inspecting /// its return value. /// </summary> public sealed class ExcelEditModeMonitor: IDisposable { // Constants, struct, and LPenHelper are all defined in the XLCALL32.h file private const int xlSpecial = 0x4000; private const int xlGetFmlaInfo = (14 | xlSpecial); [StructLayout(LayoutKind.Sequential)] private struct FmlaInfo { public int wPointMode; // current edit mode. 0 => rest of struct undefined public int cch; // count of characters in formula public IntPtr lpch; // pointer to formula characters. READ ONLY!!! public int ichFirst; // char offset to start of selection public int ichLast; // char offset to end of selection (may be > cch) public int ichCaret; // char offset to blinking caret } [DllImport("XLCALL32.DLL")] private static extern int LPenHelper(int wCode, ref FmlaInfo fmlaInfo); private Timer _timer; private long _disposedCount = 0; private const int PollingFrequency = 10; // milliseconds private readonly CancellationTokenSource _internalTokenSource; private readonly CancellationToken _internalToken; private readonly CancellationToken _externalToken; private xlEditMode _lastEditMode = xlEditMode.Ready; private xlEditMode _currentEditMode = xlEditMode.Ready; /// <summary> /// Represents the the current cell editing mode displayed on the left side of /// Excel's status bar. /// </summary> public enum xlEditMode { /// <summary> /// This value is does not correlate with an actual Excel cell edit mode, /// but is used as a return value when an exception occurs while attempting /// to poll the current state. /// </summary> Undefined = -1, /// <summary> /// Represents the default state. /// </summary> Ready = 0, /// <summary> /// Indicates when a cell is selected and typing has begun, or when F2 is keyed /// twice. /// </summary> Enter = 1, /// <summary> /// Indicates in-cell editing, which occurs as a result of double clicking a /// cell, keying F2 to enter data. /// </summary> Edit = 2, /// <summary> /// Indicates in-cell formula selection mode, which occurs as a result of entering /// a formula, and clicking the cells/ranges to include in that formula. /// </summary> Point = 4 } public xlEditMode LastEditMode => _lastEditMode; public xlEditMode CurrentEditMode => _currentEditMode; public event EventHandler<ExcelEditModeMonitorEventArgs> EditModeChange; public ExcelEditModeMonitor(CancellationToken cancellationToken) { _internalTokenSource = new CancellationTokenSource(); _internalToken = _internalTokenSource.Token; _externalToken = cancellationToken; _timer = new Timer(_timer_Elapsed, null, 0, PollingFrequency); } public void Dispose() { // don't dispose more than once if (Interlocked.Read(ref _disposedCount) > 0) return; _internalTokenSource.Cancel(); _timer.Change(Timeout.Infinite, Timeout.Infinite); _timer.Dispose(); EditModeChange = null; Interlocked.Increment(ref _disposedCount); } private void _timer_Elapsed(object state) { if (_externalToken.IsCancellationRequested || _internalToken.IsCancellationRequested) { _timer.Change(Timeout.Infinite, Timeout.Infinite); return; } _lastEditMode = _currentEditMode; _currentEditMode = GetEditMode(); if(_currentEditMode != _lastEditMode) { EditModeChange?.Invoke(this, new ExcelEditModeMonitorEventArgs(_lastEditMode, _currentEditMode)); } } private xlEditMode GetEditMode() { try { FmlaInfo fmlaInfo = new FmlaInfo(); int retVal = LPenHelper(xlGetFmlaInfo, ref fmlaInfo); if (retVal != 0) { return xlEditMode.Undefined; } return (xlEditMode)fmlaInfo.wPointMode; } catch { return xlEditMode.Undefined; } } } public sealed class ExcelEditModeMonitorEventArgs : EventArgs { public ExcelEditModeMonitor.xlEditMode LastEditMode { get; set; } public ExcelEditModeMonitor.xlEditMode CurrentEditMode { get; set; } public ExcelEditModeMonitorEventArgs(ExcelEditModeMonitor.xlEditMode lastEditMode, ExcelEditModeMonitor.xlEditMode currentEditMode) { LastEditMode = lastEditMode; CurrentEditMode = currentEditMode; } }Example Usage
public sealed class ExcelDnaAddIn : IExcelAddIn { CancellationTokenSource _globalCancellationTokenSource; ExcelEditModeMonitor _editModeMonitor; public void AutoOpen() { _globalCancellationTokenSource = new CancellationTokenSource(); _editModeMonitor = new ExcelEditModeMonitor(_globalCancellationTokenSource); _editModeMonitor.EditModeChange += OnEditModeChange; } public void AutoClose() { _editModeMonitor.Dispose(); } private void OnExcelEditModeChange(object sender, ExcelEditModeMonitorEventArgs e) { var last = Enum.GetName(typeof(ExcelEditModeMonitor.xlEditMode), e.LastEditMode); var current = Enum.GetName(typeof(ExcelEditModeMonitor.xlEditMode), e.CurrentEditMode); System.Diagnostics.Debug.WriteLine($"Excel Edit Mode Changed From {last} to {current}"); } }
Govert van Drimmelen
Alexander Rickman
Govert,
Glad to contribute to the community! Many thanks for the heads up!
Based on the suggestions in Issue#87, I made some revisions to ExcelEditModeMonitor. See below:
using System; using System.Runtime.ExceptionServices; using System.Runtime.InteropServices; using System.Threading; using System.Windows.Forms; /// <summary> /// Represents the "cell edit Mode" of Excel by periodically polling /// its state via the XLCALL32.dll function "LPenHelper" and inspecting /// its return value. /// </summary> public sealed class ExcelEditModeMonitor: IDisposable { // Constants, struct, and LPenHelper are all defined in the XLCALL32.h file private const int xlSpecial = 0x4000; private const int xlGetFmlaInfo = (14 | xlSpecial); [StructLayout(LayoutKind.Sequential)] private struct FmlaInfo { public int wPointMode; // current edit mode. 0 => rest of struct undefined public int cch; // count of characters in formula public IntPtr lpch; // pointer to formula characters. READ ONLY!!! public int ichFirst; // char offset to start of selection public int ichLast; // char offset to end of selection (may be > cch) public int ichCaret; // char offset to blinking caret } [DllImport("XLCALL32.DLL")] private static extern int LPenHelper(int wCode, ref FmlaInfo fmlaInfo); private readonly SynchronizationContext _excelMainThreadSyncContext; private System.Threading.Timer _timer; private long _disposedCount = 0; private const int PollingFrequency = 10; // milliseconds private readonly CancellationTokenSource _internalTokenSource; private readonly CancellationToken _internalToken; private readonly CancellationToken _externalToken; private xlEditMode _lastEditMode = xlEditMode.Ready; private xlEditMode _currentEditMode = xlEditMode.Ready; /// <summary> /// Represents the the current cell editing mode displayed on the left side of /// Excel's status bar. /// </summary> public enum xlEditMode { /// <summary> /// This value is does not correlate with an actual Excel cell edit mode, /// but is used as a return value when an exception occurs while attempting /// to poll the current state. /// </summary> Undefined = -1, /// <summary> /// Represents the default state. /// </summary> Ready = 0, /// <summary> /// Indicates when a cell is selected and typing has begun, or when F2 is keyed /// twice. /// </summary> Enter = 1, /// <summary> /// Indicates in-cell editing, which occurs as a result of double clicking a /// cell, keying F2 to enter data. /// </summary> Edit = 2, /// <summary> /// Indicates in-cell formula selection mode, which occurs as a result of entering /// a formula, and clicking the cells/ranges to include in that formula. /// </summary> Point = 4 } public xlEditMode LastEditMode => _lastEditMode; public xlEditMode CurrentEditMode => _currentEditMode; public ExcelEditModeMonitor(CancellationToken cancellationToken) { _excelMainThreadSyncContext = new WindowsFormsSynchronizationContext(); _internalTokenSource = new CancellationTokenSource(); _internalToken = _internalTokenSource.Token; _externalToken = cancellationToken; _timer = new System.Threading.Timer(_timer_Elapsed, null, 0, PollingFrequency); } public void Dispose() { // don't dispose more than once if (Interlocked.Read(ref _disposedCount) > 0) return; _internalTokenSource.Cancel(); _timer.Change(Timeout.Infinite, Timeout.Infinite); // Stop timer _timer.Dispose(); Interlocked.Increment(ref _disposedCount); } private void _timer_Elapsed(object state) { if (_externalToken.IsCancellationRequested || _internalToken.IsCancellationRequested) { _timer.Change(Timeout.Infinite, Timeout.Infinite); // Stop timer return; } // Switches Excel's Main UI thread, and updates state _excelMainThreadSyncContext.Post(_ => { _lastEditMode = _currentEditMode; _currentEditMode = GetEditMode(); }, null); } [HandleProcessCorruptedStateExceptions] private xlEditMode GetEditMode() { try { FmlaInfo fmlaInfo = new FmlaInfo(); int retVal = LPenHelper(xlGetFmlaInfo, ref fmlaInfo); if (retVal != 0) { return xlEditMode.Undefined; } return (xlEditMode)fmlaInfo.wPointMode; } catch { return xlEditMode.Undefined; } } }Do you think this is sufficient?
Alexander Rickman
For got to dispose of _excelMainThreadSyncContext in the Dispose method.
See revision below:
public void Dispose() { // don't dispose more than once if (Interlocked.Read(ref _disposedCount) > 0) return; _internalTokenSource.Cancel(); _timer.Change(Timeout.Infinite, Timeout.Infinite); // Stop timer _timer.Dispose(); _excelMainThreadSyncContext.Dispose(); Interlocked.Increment(ref _disposedCount); }