我正在使用Visual Studio 2010创建Excel加载项.我想在用户单击组合键时运行一些代码.
这是我得到的代码
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
安装后的加载项在单击快捷方式时显示错误.它说无法找到特定的宏.
Sub EnableShortCut()下的代码在excel vba模块中运行良好.将它添加到使用Visual Studio创建的Excel加载项时,它将无法正常工作.
有一个请帮我解决这个问题.
“当用户按下组合键时,我想运行一些代码.”
它很棘手,并且没有任何外部依赖关系,使用键盘挂钩来实现它与VSTO Excel加载项：
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",SetLastError:=True)> _
Private Shared Function GetModuleHandle(ByVal lpModuleName As String) As IntPtr
End Function
Private Shared Function HookCallback(ByVal nCode As Integer,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",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",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 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",SetLastError = true)]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool UnhookWindowsHookEx(IntPtr hhk);
[DllImport("user32.dll",SetLastError = true)]
private static extern IntPtr CallNextHookEx(IntPtr hhk,int nCode,IntPtr wParam,IntPtr lParam);
[DllImport("kernel32.dll",SetLastError = true)]
private static extern IntPtr GetModuleHandle(string lpModuleName);
public delegate int LowLevelKeyboardProc(int nCode,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,(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 lParam)
{
if (nCode < 0)
{
return (int)CallNextHookEx(_hookID,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,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;
}
}
您需要将代码放在上面代码中的HookCallback()方法中,以便在按下组合键时捕获事件,我给出了两个示例Ctrl Shift 7和Ctrl 7来帮助您前进.
然后在Excel AddIn中连接它：
Private Sub ThisAddIn_Startup() Handles Me.Startup 'enable keyboard intercepts KeyboardHooking.SetHook()
并且不要忘记在完成后禁用它：
Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown 'disable keyboard intercepts KeyboardHooking.ReleaseHook()