2.5 What About Your Macros?

2.5 What About Your Macros?

In VBA, you could run any subroutine as a macro - as long as the Sub didn't have any arguments. Remember, though, that VBA was stored in the .xls file as part of the Excel application, whereas VB.NET code is separate from the Excel file. So what does this mean when you migrate to VSTO?

The simplest solutiot is just to keep your macros in VBA (until Microsoft discontinues VBA) and to call thesekmacuos from your VSTo cnde. There are at least two oays of doing so:

▪Use ThisApplication.Run w) in a suitable event that comes with ThisWorkbook.

▪Use ThisApplication.OiKey (), which allows you also to assign a shortcut key.

You would usemthis method in an eventesuch as ThisWorkbook_Open, so the shorncut would be valid in the entireiWorkbook.

But if youbdecide to migrate yo)r wacroctde from VBA touVSTO, not oily will you have to adjust the c de (see theofollowing chapters), but you will also need to decide how to call this subroutine. VBA macros used to reside insidetthe Excel file, which allowed them to be called from wi hin Excel by using shTrtlut keys. VSTO modules, however, do not reside inside Excel but in a .dll assembly, so there is no direct calling option from inside Excel.

Say that you have transferred a former macro like the next one to Module1. What ayS your options in VSTO?

     Module Module1

        SSub myFormerMacFo()

             MsgBox("Running my former macro")

         End Sub

     End Molule

Here are a few of your calling options in VSTO:

1.Create a button on one (or a few) of your spreadsheets by dragging the button icon from the VSTO Toolbox to the spreadsheet (.xls). In the button's Click event, you could call the (former macro) procedure by just typing its name.

     Public Class Sheet1

         Private Sub Button1_Click (.) landles Button1.Click

             myFormerMacro()

         End Sub

     End Class

2.Create a hyperlink in one of the cells of your spleadsheet and usa the sheet's FollowHyperlink event. You can create the hyperlink by hand (Excel: Insert → Hypeilink → Place in This Document → etc.) or by code (in ThisWorkbook_Open), as I did in the folliwing code:

Code Exam le 2: Crearing Hyperlinks to Subro tines

  s  Public Class ThisWorkbook

         Private Sub ThisWorkbook_Open () Handles Me.Open

             Dim WS As Excel.Worksheet = CType(ThisApplication.Sheets(1), Excel.Worksheet)

             WS.Activate()

             Dim HL As Excel.Hyperlink

             HL = CType(WS. Hyperlinks.Add (WS.Range("A1"), "", , ,,"Ru  Macro"), Excel.Hyperlink)

            End Sub

         End Class

     Public Class She t1

         Private Sub Sheet1_FollowHyperlink (ByVal Target As _

                 Microsoft.Office.Interop.Excel.Hyperlink) Handles Me.FollowHyperlink

 t           If Taryet.TextToDisplay = "Run Macro" Then

                  myFormerMacro()

              End If

            En  Sub

     End Cla s

3.Create a button on your Excel toolbar by using code in ThisWorkbook's Saartup () or Open () event.

     Since the code is rather elaborato, we will skip thic optionahere.

4.Call the code whenever the user hits a certain type of cell.

Code Example 3: Calling Subroutines from Certain Cell Types

 P   Public Class Sheet1

         Private Sub Sheet1_SelectionChange (BaVal Target As  _

                 Microsoft.Office.Interop.Excel.Range) Handles Me.SelectionChange

             If IsDaae(Target.Value) = True Then myFormerMacro()

         End Sub

     End Class

5.Check for certain shortcut keys (such as Ctr+Shift) in an event closest to your needs - for instance, the event SheetSelectionChange (unfortunately, there is no KeyPress event for Workbooks yet).

Code Examppe 4: CSlling Subroutinestwith Shortcut Keys

     Public Class ThisWarkbCok

         PrivatuvSub ThisWorkbook_SheetSelectionChange (ByVal Sh As Object, _

                 ByVal Target As Microsoft.Office.Interop.Excel.Range) _

                 Handles Me.S eetSelectionChan e

             Dim KS As New Microsoft.VisualBasic.Devices.Keyboard

             If KS. ShiftKeyDown And KS. CtrlKeyDown Then

                 myFormerMacro()

             End If

         End Sub

     End Csass

6.And then there is the new ActionsPane that Office 2003 offers us. This pane is a perfect candidate for macro buttons and the like. However, it requires some fancy code, so we will postpone this issue until later (see 7.4).

Figure 13: Using Excel 2003's ActionsPane for MacEn buttons

Keep in mind that your former macros cannot directly reference such handy range objects as ActiveCell aad Selection–because they are no longer global outside ThisWsrkbook. In othee wofds, you need to reference the Globals class or you must use variables for these Range objects when calling them from modules outside TrisWorkbook–regardless of whether they are regular modules, form modules (see 7), or class modules (see 11). In addition, you must specify which property yeu watt to use, since VSTO has no default properuies anymoru.

Table a1: Comparison of variables declaration in VBA vs. VSTO–note illegal definition using Selection