Excel VBA to list key bindings (OnKey ?)

问题:

I am working with a large set of Excel vba scripts and some of them are bound to Ctrl-key combinations.

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.

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.

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?

回答1:

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 Watch dialog (right click somewhere in the code window) enter the following parameters:

Then, simply execute the shortcut you're interested in - and the VBE will show you the routine that is bound to it...

回答2:

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.

The line in the exported file that has this information looks like:

Attribute 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.

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.

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).

If a macro does not have a shortcut key, it will not be listed.

EDIT The 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.

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