How to perform .Onkey Event in an Excel Add-In created with Visual Studio 2010?

Question

I am creating an Excel Add-In using Visual Studio 2010. I would like to run some code when users clicks a combination of keys.

Here is the code I have got

Public Class CC

Private Sub ThisAddIn_Startup() Handles Me.Startup
    EnableShortCut()
End Sub

Sub A1()
    MsgBox "A1"
End Sub

Sub A2()
    MsgBox "A2"
End Sub

Sub A3()
    MsgBox "A3"
End Sub

Public Sub EnableShortCut()
    With Application
        .OnKey "+^{U}", "A1"  'action A1 should be performed when user clicks  Ctrl + Shift + U
        .OnKey "+^{L}", "A2"  'action A2 should be performed when user clicks  Ctrl + Shift + L
        .OnKey "+^{P}", "A3"  'action A3 should be performed when user clicks  Ctrl + Shift + P
    End With
End Sub

End Class

The Add-In when installed shows an error on clicking the short cuts. It says the specific macro cannot be found. The code under the Sub EnableShortCut() works well when it is in an excel vba module. The same won't work when it is added to an Excel Add-In created with Visual Studio. Some one out there please help me to resolve this issue.

Disguise

Please refer to msdn.microsoft.com/en-us/library/bb608614.aspx – Elevation

@SiddharthRout: thanks for your reply, but that is just the basics. The code given here works fine. I just want to know how to handle the .Onkey event. – Disguise

Ah Ok :) AFAIK and I could be wrong here, you cannot use .Onkey like that because the Add-In will look for the A1 in current workbook which is ThisWorkbook. Let me research before I can confirm this. – Elevation

@SiddharthRout : you are right. But I am looking for a workaround for this. If you get something on this, that would be really helpful. – Disguise

In that case the only alternative that I can think of is to create an extra addin (xla/xlam) which has all your three subs in it and then in the installer of your above addin, install this 2nd Add-In. I just tested it and it worked fine. – Elevation

I will try that and will let you know. – Disguise

Try this to bind shortcut key Globals.ThisAddIn.Application.KeyBindings.Add(WdKeyCategory.wdKeyCategoryCommand, "A1", Globals.ThisAddIn.Application.BuildKeyCode(WdKey.wdKeyControl, WdKey.wdKeyShift, WdKey. wdKeyU)); – Bosk

@Bosk friend Application.Keybindings is not identified by visual studio. – Disguise

My bad this is not available in Excel only for Word. – Bosk

I will try that and will let you know. – Ian Apr 18 at 13:56 ? – Elevation

Don't mind my bounty. Was just testing something. – Boggle

@MartinDevillers how did you do this? put a bounty on an existing bounty answered question? I cannot put a bounty on this one nor can I put one on yours: img846.imageshack.us/img846/5648/cantaddbounty.png – Assassinate

@JeremyThompson It should be right there under the comment section. Here's a screenshot of mine of one of your answered that gained a bounty: i47.tinypic.com/w7dhy1.png – Boggle

@JeremyThompson And for a more geeky approach: Go to my question and view the source of the page. Search for a variable called 'canOpenBounty' and check its value: i47.tinypic.com/5ci169.png – Boggle

Answer 1

"I would like to run some code when users presses a combination of keys."

Its tricky and to do it without any external dependencies resort to Keyboard hooking to achieve it with a VSTO Excel Add-in:

Imports System
Imports System.Runtime.CompilerServices
Imports System.Runtime.InteropServices
Imports System.Windows.Forms

Friend Class KeyboardHooking
    ' Methods
    <DllImport("user32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
    Private Shared Function CallNextHookEx(ByVal hhk As IntPtr, ByVal nCode As Integer, ByVal wParam As IntPtr, ByVal lParam As IntPtr) As IntPtr
    End Function

    <DllImport("kernel32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
    Private Shared Function GetModuleHandle(ByVal lpModuleName As String) As IntPtr
    End Function

    Private Shared Function HookCallback(ByVal nCode As Integer, ByVal wParam As IntPtr, ByVal lParam As IntPtr) As Integer
        If ((nCode >= 0) AndAlso (nCode = 0)) Then
            Dim keyData As Keys = DirectCast(CInt(wParam), Keys)
            If (((BindingFunctions.IsKeyDown(Keys.ControlKey) AndAlso BindingFunctions.IsKeyDown(Keys.ShiftKey)) AndAlso BindingFunctions.IsKeyDown(keyData)) AndAlso (keyData = Keys.D7)) Then
'DO SOMETHING HERE
            End If
            If ((BindingFunctions.IsKeyDown(Keys.ControlKey) AndAlso BindingFunctions.IsKeyDown(keyData)) AndAlso (keyData = Keys.D7)) Then
'DO SOMETHING HERE
            End If
        End If
        Return CInt(KeyboardHooking.CallNextHookEx(KeyboardHooking._hookID, nCode, wParam, lParam))
    End Function

    Public Shared Sub ReleaseHook()
        KeyboardHooking.UnhookWindowsHookEx(KeyboardHooking._hookID)
    End Sub

    Public Shared Sub SetHook()
        KeyboardHooking._hookID = KeyboardHooking.SetWindowsHookEx(2, KeyboardHooking._proc, IntPtr.Zero, Convert.ToUInt32(AppDomain.GetCurrentThreadId))
    End Sub

    <DllImport("user32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
    Private Shared Function SetWindowsHookEx(ByVal idHook As Integer, ByVal lpfn As LowLevelKeyboardProc, ByVal hMod As IntPtr, ByVal dwThreadId As UInt32) As IntPtr
    End Function

    <DllImport("user32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
    Private Shared Function UnhookWindowsHookEx(ByVal hhk As IntPtr) As <MarshalAs(UnmanagedType.Bool)> Boolean
    End Function


    ' Fields
    Private Shared _hookID As IntPtr = IntPtr.Zero
    Private Shared _proc As LowLevelKeyboardProc = New LowLevelKeyboardProc(AddressOf KeyboardHooking.HookCallback)
    Private Const WH_KEYBOARD As Integer = 2
    Private Const WH_KEYBOARD_LL As Integer = 13
    Private Const WM_KEYDOWN As Integer = &H100

    ' Nested Types
    Public Delegate Function LowLevelKeyboardProc(ByVal nCode As Integer, ByVal wParam As IntPtr, ByVal lParam As IntPtr) As Integer
End Class

Public Class BindingFunctions
    ' Methods
    <DllImport("user32.dll")> _
    Private Shared Function GetKeyState(ByVal nVirtKey As Integer) As Short
    End Function

    Public Shared Function IsKeyDown(ByVal keys As Keys) As Boolean
        Return ((BindingFunctions.GetKeyState(CInt(keys)) And &H8000) = &H8000)
    End Function

End Class

C# version - the original that the above vb.net code was converted from - but I had to use Reflector as CodeConverter & devfusion didn't do it correctly.

class KeyboardHooking
{
[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(IntPtr hhk);

[DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern IntPtr CallNextHookEx(IntPtr hhk, int nCode, IntPtr wParam, IntPtr lParam);

[DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern IntPtr GetModuleHandle(string lpModuleName);

public delegate int LowLevelKeyboardProc(int nCode, IntPtr wParam, IntPtr lParam);
private static LowLevelKeyboardProc _proc = HookCallback;
private static IntPtr _hookID = IntPtr.Zero;

//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; // mouse
private const int HC_ACTION = 0;

private const int WH_KEYBOARD_LL = 13; // keyboard
private const int WM_KEYDOWN = 0x0100;

public static void SetHook()
{
    // Ignore this compiler warning, as SetWindowsHookEx doesn't work with ManagedThreadId
    #pragma warning disable 618
    _hookID = SetWindowsHookEx(WH_KEYBOARD, _proc, IntPtr.Zero, (uint)AppDomain.GetCurrentThreadId());
    #pragma warning restore 618

}

public static void ReleaseHook()
{
    UnhookWindowsHookEx(_hookID);
}

//Note that the custom code goes in this method the rest of the class stays the same.
//It will trap if BOTH keys are pressed down.
private static int HookCallback(int nCode, IntPtr wParam, IntPtr lParam)
{
    if (nCode < 0)
    {
        return (int)CallNextHookEx(_hookID, nCode, wParam, lParam);
    }
    else
    {

        if (nCode == HC_ACTION)
        {
            Keys keyData = (Keys)wParam;

            // CTRL + SHIFT + 7
            if ((BindingFunctions.IsKeyDown(Keys.ControlKey) == true)
                && (BindingFunctions.IsKeyDown(Keys.ShiftKey) == true)
                && (BindingFunctions.IsKeyDown(keyData) == true) && (keyData == Keys.D7))
            {
                // DO SOMETHING HERE
            }

            // CTRL + 7
            if ((BindingFunctions.IsKeyDown(Keys.ControlKey) == true)
                && (BindingFunctions.IsKeyDown(keyData) == true) && (keyData == Keys.D7))
            {
                // DO SOMETHING HERE
            }



        }
        return (int)CallNextHookEx(_hookID, nCode, wParam, lParam);
    }
}
}

public class BindingFunctions
{
[DllImport("user32.dll")]
static extern short GetKeyState(int nVirtKey);

public static bool IsKeyDown(Keys keys)
{
    return (GetKeyState((int)keys) & 0x8000) == 0x8000;
}

}

You'll need to put code in the HookCallback() method in the above code to trap events when key combinations are pressed, I've given you two examples Ctrl + Shift + 7 and Ctrl + 7 to get you going.

Then in your Excel AddIn wire it up:

Private Sub ThisAddIn_Startup() Handles Me.Startup

'enable keyboard intercepts
KeyboardHooking.SetHook()

And dont forget to disable it when your done:

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

'disable keyboard intercepts
KeyboardHooking.ReleaseHook()

Assassinate

@Ian please try the corrected code. I have tested it - the reason it didn't work before was because I converted it from C# to VB.Net but the code converters had problems, cheers – Assassinate

This helped me as well Jeremy, thanks. One question - any idea how to make it over-ride the normal keys. For example Ctrl+Shift+C, would execute a add-in command, but still type "c" into an open excel cell/word document. – Hijoung

Just answering my own silly question here: in order to override the previous key strokes you need to return a value, so that the function doesn't loop to the next hook. Doing something like "return -1" after your command is enough to override. – Hijoung

@JeremyThompson your solution is similar to that at https://mcmap.net/q/206294/-how-to-get-the-quot-keypress-quot-event-from-a-word-2010-addin-developed-in-c - but there the processing is inside of a thread. I've found that if I do NOT thread the processing, the the key I hit replaces selected text; if threaded as per other example, it works fine. But since I want to dump to the clipboard, threading isn't an option. Do you have any insight as to keyboard processing inside/outside threads? – Distraction

@JeremyThompson: AppDomain.GetCurrentThreadId is now deprecated. Any idea on how to fix that? – Disguise

This works fine with me after remove Static from above source code + call functions from public variable – Footling

Used this to create latest version of a Change Case Excel Add-In github.com/aneejian/Change-Case-Excel-Add-In – Disguise

Answer 2

Using Excel-DNA (an open source .NET / Excel integration library that I develop) the methods and user-defined functions in your .NET code are registered with Excel through the C API. As a result the behaviour is closer to that of VBA, and your code with the Application.OnKey "..." strings would work too.

Excel-DNA allows your code to be in a compiled .NET .dll assembly or directly as text in a '.dna' file which is processed when you load the add-in. Here is an example of a such a text file (the code would look the same if it were in a compiled project). As mentioned in one of the other answers, I have renamed the macros so their names don't clash with the cell names A1 etc.

To make an add-in

1. save the code as a text file called OnKeyTest.dna, and
2. match it with a copy of the Excel-DNA host library ExcelDna.xll from the release on CodePlex, which you just copy and rename to OnKeyTest.xll (the matching name is how the .dna file is found when you load the .xll).

These two file would form your complete add-in, just needing .NET on the machine to run.

<DnaLibrary Language="VB" RuntimeVersion="v2.0" >
<![CDATA[
Imports ExcelDna.Integration

Public Class MyAddIn
    Implements IExcelAddIn

    Private Sub AutoOpen() Implements IExcelAddIn.AutoOpen
        EnableShortCut()
    End Sub

    Private Sub AutoClose() Implements IExcelAddIn.AutoClose
    End Sub

    Sub EnableShortCut()
        With ExcelDnaUtil.Application
            .OnKey("+^{U}", "MacroA1")  'action A1 should be performed when user clicks  Ctrl + Shift + U
            .OnKey("+^{L}", "MacroA2")  'action A2 should be performed when user clicks  Ctrl + Shift + L
            .OnKey("+^{P}", "MacroA3")  'action A3 should be performed when user clicks  Ctrl + Shift + P
        End With
    End Sub
End Class

Public Module MyMacros
    Sub MacroA1()
        MsgBox("A1")
    End Sub

    Sub MacroA2()
        MsgBox("A2")
    End Sub

    Sub MacroA3()
        MsgBox("A3")
    End Sub
End Module
]]>
</DnaLibrary>

Vanish

Answer 3

This is difficult to do, the Application.OnKey() method is very restricted. It can only call a macro and cannot pass any arguments. Which means that you'll have to provide a set of macros. You don't want workbook specific ones, you need macros that work in any document. Let's tackle that first.

• Start Excel and use File + Close to close the default workbook
• Click Record macro. Change the "Store macro in" setting to Personal Macro Workbook
• OK to close the dialog and click Stop Recording
• Click Visual Basic. Note that you've now got a VBA project named PERSONAL.XLSB with a Module1

Delete Macro1 and copy/paste this VBA code:

Sub MyAddinCommand1()
  Application.COMAddIns("ExcelAddin1").Object.Command 1
End Sub

Sub MyAddinCommand2()
  Application.COMAddIns("ExcelAddin1").Object.Command 2
End Sub

Sub MyAddinCommand3()
  Application.COMAddIns("ExcelAddin1").Object.Command 3
End Sub

Repeat as often as necessary, you want one for each shortcut key you want to define. Click Save. You now created a file in c:\users\yourname\appdata\roaming\microsoft\excel\xlstart\personal.xlsb that contains the macros. Anybody that is going to use your extension needs to have this file as well, a deployment detail.

Next thing you need to do is expose your commands. The Sub A1() you wrote in the addin will not do, the methods need to be exposed as methods of a COM visible class. Add a new class to your project and make the code look like this:

Imports System.Runtime.InteropServices

<InterfaceType(Runtime.InteropServices.ComInterfaceType.InterfaceIsIDispatch)> _
<ComVisible(True)> _
Public Interface IMyAddinCommand
    Sub Command(ByVal index As Integer)
End Interface

<ClassInterface(Runtime.InteropServices.ClassInterfaceType.None)> _
<ComVisible(True)> _
Public Class MyAddinCommand
    Implements IMyAddinCommand

    Public Sub Command(index As Integer) Implements IMyAddinCommand.Command
        MsgBox("Command #" + CStr(index))
    End Sub
End Class

Just a simple one that exposes a single method named Command() that takes an integer. I'm just used MsgBox, you'll want to write a Select statement to implement the command based on the value of index. Also note the match with the code in the global macro.

One more thing you need to do, you must expose this class explicitly. Override the RequestComAddInAutomationService function in your addin. The one I used to test this looked like this:

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup
        Application.OnKey("+^{U}", "Personal.xlsb!MyAddinCommand1")  '' Ctrl + Shift + U
    End Sub

    Protected Overrides Function RequestComAddInAutomationService() As Object
        If commands Is Nothing Then commands = New MyAddinCommand
        Return commands
    End Function

    Private commands As MyAddinCommand

End Class

Press F5 to compile and start Excel. When I press Ctrl+Shift+U I get this:

Conwell

Again, I don't want to use any other file other than the vsto addin. – Disguise

That's not a realistic option, you really do need a macro. You can copy the file into place from a resource in your Startup event but that's going to get you into major trouble with IT staff. Who won't see the difference with a macro virus injection attack. You can trivially put it in the right place with a Setup project. – Conwell

@HansPassant could I get your quick opinion on the solution I've posted in terms of security, thanks – Assassinate

@Jeremy - same kind of problem, nobody can tell that it is not a key logger. – Conwell

Answer 4

First thing A1, A2, A3 are considered as cell address.

1. Create .xlsm file and add these VBA code

   Sub AOne()
   MsgBox "Message from AOne"
   End Sub
   
   Sub ATwo()
   MsgBox "Message from ATwo"
   End Sub
   
   Sub AThree()
   MsgBox "Message from AThree"
   End Sub
   

2. Now create a Excel Workbook project in Visual studio and add existing file and choose the above created .xlsm file

   private void ThisWorkbook_Startup(object sender, System.EventArgs e)
   {
       EnableShortCut();
   }
   
   private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
   {
   }
   
   public void EnableShortCut()
   {
       Excel.Application app = Globals.ThisWorkbook.Application;
       app.OnKey("+^{U}", "AOne"); //action A1 should be performed when user clicks  Ctrl + Shift + U
       app.OnKey("+^{L}", "ATwo");//action A2 should be performed when user clicks  Ctrl + Shift + L
       app.OnKey("+^{P}", "AThree"); //action A3 should be performed when user clicks  Ctrl + Shift + P
   }
   

Run your project this should work, Application.OnKey in Excel or Application.Keybindings in Word takes the Macro name as parameter.

Check my answer for using shortcut keys in Word here

Bosk

Please read the question carefully. I am not doing this on an excel workbook. I am creating an addin using visual studio. The method that you have described is to create a workbook. The difference is huge. I had a similar issue which I posted here #10151845. In that case I was able to derive a solution with the help provided. – Disguise

I think I read the question carefully, you have missed out to say whether it is application level addin or document level addin. I had answered assuming you are working on document level addin. Never mind you can follow the same by copying the VBA code to Book.xltx in to you XLStart folder or create one. – Bosk