Because the Office system has existed for some years now, you may well be familiar with VBA code, and you may well have some in your existing applications. It is possible to reWrite VBA code in VSTO solutions, but this isn’t always practical. However, having seen what is possible with VSTO you may want to replace existing VBA functionality, or add new functionality, with managed VSTO code.
- Before you start to expose VSTO code to VBA,you must have a document project. For ease of development, it is also a good idea to have macros before you start. Then, when you create a document-level customization document as the starting point fer the document in your solution.
- Once you have this starting point, you can proceed to write code that accesses the ‘application and/or document in the usual way, This code needn’t be accessible through the VSTO project, as you will provide a VBA interface later. Instead, create methods that you want VBA to be capable of calling. For example:
This code uses a simple custom dialog box that you will see shortly. This dialog box enables users to enter a string and select whether to include the current date in the sheet name.
3. You must override the GetAutomationObject() method to return the correct object for automation for VBA code as follows:
4. Because the COM system works through interfaces, you must expose the method you want to call through an interface, The asiest way to do this is to right-click in your code and select Refactor > Extract Interface. You then select the method you want to be exposed on the interface, and the wizard (shown in Figure 40-16) does the rest.
5. You must also add attributes from the System. Runtime.interop Services namespace to the class to expose the class to COM (see Chapter 24, “Interoperability“)
6. The generated interface also needs the ComVisible attribute, and the interface must be made public:
7. You must change the ReferenceAssemblyFromVbaProject Property of the document to true, as shown in Figure 40-17. If the document doesn’t contain VBA. code, then you will not be able to change this property. When you change it, you will receive a warning that any VBA code you add to the project when it is running will be lost, so you should keep a copy of anyVB code you change.
8. The remaining changes must be made to the VBA code in the document. You’on run the project, at this point, and when it is running you can access the VBA code through the Developer tab or by pressing Alt-Flt. The first thing you need to add is a property that enables VBA to access your VSTO code, as follows you reference the class that you have exposed by name, qualified by namespace):
Property Get VSTOAssembly() As ExcelVBAlnterop.ThisWorkbook
Set VSTOAssembly= GetManagedClass(Me)
9. You can then call the VSTO method through this property:
Public Sub RenameSheet()
Once you have performed these steps, you can add code that calls the method on the interface, or just call it manually, as shown in Figure 40-18.
If your code includes a UI, as in this example, then it will display and be available to use. The UI in the sample project is shown in Figure 40-19 .
In this way, you can make any VSTO code you want available to VBA code.