Using shortcut keys to call a function in an Office Add-in
Recently I came across a problem where a customer was looking for the ways to call a function in an VSTO office add-in using keyboard shortcuts. Since I could not find good documentation on it, I decided to document my findings.
One can implement these in following ways:
1. Use KeyBindings
2. Hook the main window and trap the key combinations to launch custom functions.
Using KeyBindings
KeyBindings can be used to assign keyboard shortcuts to macros. Then macros can be used to call the function in managed add-in. I found following are few useful links which talk about the VSTO-VBA integration.
VSTO VBA integration: https://msdn.microsoft.com/en-us/magazine/cc163373.aspx
Calling VSTO code from VBA: https://msdn.microsoft.com/en-us/office/cc178910.aspx
KeyBindings Collection: https://msdn.microsoft.com/en-us/library/bb211991(v=office.12).aspx.
Using Keyboard Hook
This can also be achieved using Windows Hooks(https://msdn.microsoft.com/en-us/library/ms997537.aspx)
Following is a sample class which traps Ctrl+1 keys and writes "A" in A4 cell in Excel (I used this class in a VSTO Excel addin project). This class uses local hook to trap Ctrl+1 keys. It can be used to set hook (by calling its SetHook function), release hook (by calling its ReleaseHook function).
using System;
using System.Diagnostics;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
namespace Hooking
{
public class InterceptKeys
{
public delegate int LowLevelKeyboardProc(int nCode, IntPtr wParam, IntPtr lParam);
private static LowLevelKeyboardProc_proc = HookCallback;
private static IntPtr _hookID = IntPtr.Zero;
privates tatic Microsoft.Office.Tools.CustomTaskPane ctpRef = null;
//Declare the mouse hook constant. //For other hook types, you can obtain these values from Winuser.h in the Microsoft SDK.
private const int WH_KEYBOARD = 2;
private const int HC_ACTION = 0;
public static void SetHook()
{
_hookID = SetWindowsHookEx(WH_KEYBOARD, _proc, IntPtr.Zero, (uint)AppDomain.GetCurrentThreadId());
}
public static void ReleaseHook()
{
UnhookWindowsHookEx(_hookID);
}
private static int HookCallback(int nCode, IntPtr wParam, IntPtr lParam)
{
int PreviousStateBit = 31;
bool KeyWasAlreadyPressed = false;
Int64 bitmask = (Int64)Math.Pow(2, (PreviousStateBit - 1));
if (nCode < 0)
{
return (int)CallNextHookEx(_hookID, nCode, wParam, lParam);
}
else
{
if(nCode == HC_ACTION)
{
Keys keyData = (Keys)wParam;
KeyWasAlreadyPressed = ((Int64)lParam & bitmask) > 0;
if (Functions.IsKeyDown(Keys.ControlKey) && keyData == Keys.D1 && KeyWasAlreadyPressed == false)
{
object missing = System.Reflection.Missing.Value;
Excel.
Workbook exBook = Excel_CustomTaskPane_ACC.Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.
Worksheet exSheet = (Excel.Worksheet)exBook.ActiveSheet;
exSheet.get_Range(
"A4", missing).Value2 = "A";
}
}
return (int)CallNextHookEx(_hookID, nCode, wParam, lParam);
}
}
[
DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern IntPtr SetWindowsHookEx(int idHook, LowLevelKeyboardProc lpfn, IntPtr hMod, uint dwThreadId);
[DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool UnhookWindowsHookEx(IntPtrhhk);
[DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern IntPtr CallNextHookEx(IntPtr hhk, int nCode,IntPtr wParam, IntPtrlParam);
}
public classFunctions
{
public static bool IsKeyDown(Keyskeys)
{
return (GetKeyState((int)keys) & 0x8000) == 0x8000;
}
[DllImport("user32.dll")]
static extern short GetKeyState(intnVirtKey);
}
}
Comments
Anonymous
August 12, 2015
AWESOME article!Anonymous
March 31, 2016
Good!But for existing bindings, these are executed after the hook limiting the use.Anonymous
March 31, 2016
Also, the implementation is application wide.So, if Excel is not the focus (say in VBA) , then the code still executes.Anonymous
April 28, 2016
Good article. I have a question: for example, if I set "ctrl + v" in the keyboard hook, I found that it will always put a "v". How do I avoid this "v"? Thank you very much.- Anonymous
April 28, 2016
I found it. Thanks.
- Anonymous
Anonymous
May 12, 2016
Useful..