Excel Shortcut for VSTO SE Addin

I found it difficult to assign a shortcut key for VSTO SE Excel addin while I am working on my first project in VSTO. Then I got help from msdn forum and Zi Zhou - MSFT helped me to figure out the below described solution. Thanks a ton to him as it saved a lot of my time.

To assign shortcut key for VSTO SE addin from Excel 2007 is not possible and we have do some workaround to fix this. I am not sure about the VSTO v3 but for VSTO SE we have to follow the below given procedure.

We can’t get the button’s handle created from ribbon.xml file from VBA. We need to expose this COM addin to let VBA call the function. Let’s see how we can do this in the following steps:

Step 1: Create an Excel Addin named ExcelAddin using VSTO SE and Excel 2007.

Step 2: Add a ribbon support to the project and keep the default name (ribbon.xml)

Step 3: Ribbon call back would be something like this

Public Sub MyModule(ByVal control As Office.IRibbonControl)
Select Case control.Id
Case "BlueUL"
Globals.ThisAddIn._addinUtilities.BlueUL()
Case “SetPrintArea”
Globals.ThisAddIn._addinUtilities.SetPrintArea()
End Select
End Sub

Step 4: Add Interface to the project - Right Click on the Project --> Click Add New item --> Select “Interface” from the available templates and name it as “ExcelAddinInterface” and add the below code block.

Imports System.Runtime.InteropServices
<ComVisible(True)> _
<Guid("85B3534B-DD1A-4f07-B604-4EE610453F78")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface ExcelAddInterface
Sub BlueUL()
Sub SetPrintArea()
End Interface

Step 5: Add a class to the project – Right Click on the Project --> Click Add New item --> Select “Class” from the available templates and name it as “ExcelAddinClass” and add the below code block

Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports Microsoft.VisualBasic
<ComVisible(True)> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class LazAddinClass
Implements LazAddinInterface

Public Sub BlueUL() Implements LazAddinInterface.BlueUL
On Error Resume Next
With Globals.ThisAddIn.Application.Selection.
_Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 7
End With
End Sub

Public Sub SetPrintArea() Implements LazAddinInterface.SetPrintArea
Dim res As String
res = MsgBox("Are you sure to reformat the print area?",
MsgBoxStyle.YesNo)
If res = vbYes Then
Globals.ThisAddIn.Application.ActiveSheet.PageSetup. _
PrintArea = Globals.ThisAddIn.Application.Selection.Address
End If
End Sub
End Class

Step 6: In ExcelAddinClass we need to override the RequestComAutomationService() to return a new instance of the class we created above and make the field public, which makes the call in Step 3 can be compiled.

Public _addinUtilities As LazAddinClass
Protected Overrides Function RequestComAddInAutomationService() As Object
If (_addinUtilities Is Nothing) Then
_addinUtilities = New LazAddinClass
End If
Return _addinUtilities
End Function

We are done with the VSTO part and we have to create an Excel Addin to expose this procedure.

Step 7: Open Excel, Click Developer Tab --> Code Group --> Visual Basic. In Visual Basic editor, add a module and add the below code block

Dim addin As Office.COMAddIn
Dim automationObject As Object

Sub BlueUL()
Set addin = Application.COMAddIns("ExcelAddin")
Set automationObject = addin.Object
automationObject.BlueUL
End Sub

Sub SetPrintArea()
Set addin = Application.COMAddIns("ExcelAddin")
Set automationObject = addin.Object
automationObject.SetPrintArea
End Sub

Sub Auto_Open()
Application.OnKey "+^{F3}", "BlueUL"
Application.OnKey "+^{F4}", "SetPrintArea"
End Sub

Save the workbook as an Addin (“ExcelShortcut.xlam”)

Step 8: Click Office button --> Excel Options --> Add-ins tab, Choose Excel Add-ins, click Browse and select “ExcelShortcut.xlam” file and click OK

Step 9: Now goto VSTO addin, compile and run the project. Press “Ctrl+Shift+F3”. The bottom of the selected cells will be changed to pink color

Please let me know if you face any difficulty in this process.