Excel VBA 列出键绑定（OnKey ？）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16862306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA to list key bindings (OnKey ?)
提问by Mark Goldfain
I am working with a large set of Excel vba scripts and some of them are bound to Ctrl-key combinations.
我正在使用大量 Excel vba 脚本，其中一些绑定到 Ctrl 键组合。
I know how to set one at a time in the user interface for Excel: Pull up the list of vba scripts (Alt-F8), select a script, and click Options. Then you can bind/unbind the script to a Ctrl-key combo. However, you can bind more than one script to the same key, and Excel will pick one (probably the first one it finds in some manner) and ignore the other bindings.
我知道如何在 Excel 用户界面中一次设置一个：拉出 vba 脚本列表 (Alt-F8)，选择一个脚本，然后单击选项。然后您可以将脚本绑定/解除绑定到 Ctrl 键组合。但是，您可以将多个脚本绑定到同一个键，Excel 将选择一个（可能是它以某种方式找到的第一个）并忽略其他绑定。
So, I want to assign Ctrl-e to a script, but first I have to find what other script(s) it is currently bound to, out of a list of hundreds of scripts.
所以，我想将 Ctrl-e 分配给一个脚本，但首先我必须从数百个脚本的列表中找到它当前绑定到的其他脚本。
Is there a way to get Excel to list the current key bindings (with a VBA macro, I suppose)? I have seen a solution for Word, that examines the "KeyBindings" collection. This is not working in Excel, though. Does Excel have a different object?
有没有办法让 Excel 列出当前的键绑定（我想是使用 VBA 宏）？我见过 Word 的解决方案，它检查“KeyBindings”集合。但是，这在 Excel 中不起作用。Excel 有不同的对象吗？
采纳答案by Peter Albert
After searching for a while, I could not find any possibility to programmatically get a list of all key bindings.
搜索了一段时间后，我找不到任何以编程方式获取所有键绑定列表的可能性。
However, if you basically want to find out, which procedure runs on a shortcut, but you're not sure and don't want to crawl through your Personal Workbook, Add-ins, etc., you can create a dynamic breakpoint that will always stop on the first line of VBA code executed. To do this, simple use the Add Watchdialog (right click somewhere in the code window) enter the following parameters:
但是，如果您基本上想知道哪个过程在快捷方式上运行，但您不确定并且不想爬行您的个人工作簿、加载项等，您可以创建一个动态断点，它将始终停在执行的 VBA 代码的第一行。为此，只需使用Add Watch对话框（右键单击代码窗口中的某处）输入以下参数：
Then, simply execute the shortcut you're interested in - and the VBE will show you the routine that is bound to it...
然后，只需执行您感兴趣的快捷方式 - VBE 将向您显示与其绑定的例程...
回答by Ron Rosenfeld
You can list the keys assigned to a macro with a VBA macro. It turns out although the shortcut key cannot be accessed "directly", if you export a module, any shortcut key is listed in the exported file. That file can then be parsed to return the procedure name and the associated shortcut key.
您可以使用 VBA 宏列出分配给宏的键。事实证明，虽然无法“直接”访问快捷键，但如果导出模块，则导出文件中会列出任何快捷键。然后可以解析该文件以返回过程名称和关联的快捷键。
The line in the exported file that has this information looks like:
导出文件中包含此信息的行如下所示：
Attribute MacroShortCutKeys.VB_ProcData.VB_Invoke_Func = "a\n14"
属性MacroShortCutKeys.VB_ProcData.VB_Invoke_Func = " a\n14"
The bolded information above is the name of a macro, and the associated shortcut key. I don't know the significance of the \n14 at the end (and I am not sure if it will be consistent as I have not extensively tested this.
上面加粗的信息是宏的名称和关联的快捷键。我不知道最后 \n14 的重要性（我不确定它是否会保持一致，因为我没有对此进行广泛的测试。
Running the macro below requires that you set the option to Trust access to the VBA Project object model in the Trust Center settings; and also that you set references in VBA as listed in the code.
运行下面的宏需要您在信任中心设置中将选项设置为信任对 VBA 项目对象模型的访问；并且您还按照代码中列出的方式在 VBA 中设置引用。
I have just output the results to the Immediate window, but you could do also easily put it on a worksheet.
我刚刚将结果输出到立即窗口，但您也可以轻松地将其放在工作表上。
The exported file is stored in a folder C:\Temp, and the file is deleted when we are done with it. If C:\Temp does not exist, you will have to create it. (That could have been done in the macro, but I got lazy).
导出的文件存储在文件夹 C:\Temp 中，完成后该文件将被删除。如果 C:\Temp 不存在，则必须创建它。（这本来可以在宏中完成，但我很懒惰）。
If a macro does not have a shortcut key, it will not be listed.
如果宏没有快捷键，则不会列出。
EDITThe routine only lists those shortcuts that were assigned using the Macro Dialog box on the Excel worksheet. It will NOT list shortcut keys that were assigned using the Application.OnKey method. Not sure yet how to get those.
编辑该例程仅列出使用 Excel 工作表上的宏对话框分配的那些快捷方式。它不会列出使用 Application.OnKey 方法分配的快捷键。还不确定如何获得这些。
Option Explicit
'MUST set to Trust Access to the VBA Project Object Model
' in Excel Options
'Set reference to:
'Microsoft Visual Basic for Applications Extensibility
'Microsoft Scripting Runtime
'Microsoft VBScript Regular Expressions 5.5
Sub MacroShortCutKeys()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As CodeModule
Dim LineNum As Long
Dim ProcKind As VBIDE.vbext_ProcKind
Dim sProcName As String, sShortCutKey As String
Const FN As String = "C:\Temp\Temp.txt"
Dim S As String
Dim FSO As FileSystemObject
Dim TS As TextStream
Dim RE As RegExp, MC As MatchCollection, M As Match
Set RE = New RegExp
With RE
.Global = True
.IgnoreCase = True
.Pattern = "Attribute\s+(\w+)\.VB_ProcData\.VB_Invoke_Func = ""(\S+)(?=\)"
End With
Set FSO = New FileSystemObject
Set VBProj = ActiveWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
Select Case VBComp.Type
Case Is = vbext_ct_StdModule
VBComp.Export FN
Set TS = FSO.OpenTextFile(FN, ForReading, Format:=TristateFalse)
S = TS.ReadAll
TS.Close
FSO.DeleteFile (FN)
If RE.Test(S) = True Then
Set MC = RE.Execute(S)
For Each M In MC
Debug.Print VBComp.Name, M.SubMatches(0), M.SubMatches(1)
Next M
End If
End Select
Next VBComp
End Sub
