This week I got the following question in email: I’m writing a COM component in C# that is used in Excel VBA macros. I really need to debug the VBA macro and the C# component at the same time. Is that possible? Absolutely! Here’s the steps:
- Start Visual Studio with no projects open.
- Select the File menu, Open, Project/Solution to get to the Open Project dialog.
- Navigate to the directory where EXCEL.EXE is located. For Office 2007, that’s C:Program FilesMicrosoft OfficeOffice12. On x64, it’s in the C:Program Files (x86)Microsoft OfficeOffice12 directory. Double click on EXCEL.EXE. If you’re using Visual Studio 2008, you’ll be prompted about elevation, but you can click the Ignore button. Solution Explore will look like the following:
- Right click on EXCEL.EXE in Solution Explorer and select Properties to get to the Property Pages.
- The only property page available is Debugging. In the Debugger Type field, select Managed Only and click the OK button. This sets the Visual Studio debugger to only do managed debugging.
- You may want to save the solution at this point so from now on so you don’t have to go through these steps each time you need to debug.
- Open up a source file from your manage project in Visual Studio and set a breakpoint on a line.
- Start debugging in Visual Studio by pressing F5.
- In Excel, open up your worksheet and start debugging your VBA code using Excel’s debugger.
- When the managed code gets executed where you set the breakpoint, you’ll stop in Visual Studio.
Now you can debug both the VBA and managed side of your project relatively easily. If you need to debug a native COM component in the same scenario, the only difference with the steps above is not setting the Debugger Type to managed. The default is Auto, which looks at the binary you’re about to debug and if it’s native, it will do native debugging.