Although VBA has been very successful as a customization tool for Office, there are new tools such as VSTO which can take your applications to the next level. But you want to be able to leverage the existing investment that you already have in VBA. Now VSTO 3.0 makes this super easy. Just by setting a single property in your VSTO code you can access the VSTO code from your VBA project with full intellisense support.
1.Create a new VSTO VB Word or Excel project.
2.Set the EnableVbaCaller to True. When you flip this to true the VSTO project system does all the work to make this available to the VBA project.
3. F5 to run the project.
4. Write your VBA code to call the VSTO code. You can see the VBA helper code, CallVSTOAssembly, which was written by the VSTO project system when you set the EnableVbaCaller to true. You can also see that you now have intellisense support as well.
That's it. For VB projects that is all you need to do. You can set EnableVbaCallers for any HostItem (ThisWorkbook, Sheet1,Sheet2, etc or ThisDocument for Word). You can also do this using C#, although you must do all of the steps by hand. In addition to passing HostItems, you can pass your own custom types. I will be at TechEd 2007 in Orlando next week and will be doing a talk on VBA Interop (OFC07-TLC, Wednesday, June 06, 2007 2:00 PM-3:15 PM) Stop by and watch me walk through all of these simple and advanced scenarios. And if you don't get a chance to make the talk come by the VSTO booth and talk about the "Sunshine".