vb.net – 如何在使用Visual Studio 2010创建的Excel加载项中执行.Onkey事件？

VB 前端之家
前端之家收集整理的这篇文章主要介绍了vb.net – 如何在使用Visual Studio 2010创建的Excel加载项中执行.Onkey事件？，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
我正在使用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()
原文链接：https://www.f2er.com/vb/255453.html

猜你在找的VB相关文章

VB Format函数
Format[$] ( expr [ , fmt ] ) format 返回变体型 format$ 强制返回为文本 --------------...
vb6/ASP FORMAT MM/DD/YYYY
VB6或者ASP 格式化时间为 MM/dd/yyyy 格式，竟然没有好的办法， Format 或者FormatDateTi...
VB.net 捕获项目全局异常
在项目中添加如下代码：新建窗口来显示异常信息。 Namespace My ‘全局错误处理，新的...
实现用VB.Net/(C#)开发K/3 BOS 插件的真正可行方法
转了这一篇文章，原来一直想用C#做k3的插件开发，vb没有C#用的爽呀，这篇文章写与2011年，...
vb，wps，excel 分裂
Sub 分列() ‘以空格为分隔符，连续空格只算1个。对所选中的单元格进行处理 Dim m...
VB.NET MYSQL DataGridView 增删改查（INSERT,SELECT,UPDATE,DELETE）
  Imports MySql.Data.MySqlClient Public Class Form1 ‘ GLOBAL DECLARATIONS ...
VB.NET 使用ADODB連接資料庫滙出到EXCEL
‘導入命名空間 Imports ADODB Imports Microsoft.Office.Interop   Private Sub A1() Di...
vb.net 多线程運用 ping
Imports System.IO Imports System.Threading Imports System.Diagnostics Public Class F...
VB等待进程结束
VB运行EXE程序，并等待其运行结束 参考：https://blog.csdn.net/useway/article/details/5...
vb中去掉string数组的一部分
今天碰到一个问题，登陆的时候，如果不需要验证手机号为空，则不去验证手机号 因为登陆的时...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积