Do I lose the benefits of macro recording if I develop Excel apps in Visual Studio?

Question

I've written lots of Excel macros in the past using the following development process:

1. Record a macro.
2. Open the VBA editor.
3. Edit the macro.

I'm now experimenting with a Visual Studio 2008 "Excel 2007 Add-In" project (C#), and I'm wondering if I will have to give up this development process.

Questions:

1. I know I can still record macros using Excel, but is there any way to access the resulting code in Visual Studio? Or do I just have to copy and paste then C#-ize it?

2. What happens with my "Personal Macro Workbook"? Can I use the macros I have stored in there within C#? Or is there some way to convert them to C#?

3. If there is some support for opening and editing VBA macros in Visual Studio, can you provide a very brief summary of how it works or point me to a good reference?

4. Do you have any other tips for transitioning from writing macros in VBA using Excel's built-in editor to writing them in C# with Visual Studio?

Answer 1

You are in for a world of hurt, if you want to port VBA macros to C#. If they are complex at all, I would just rewrite them in Visual Studio or keep them in Excel.

Also, as much as it pains me to say this, VB.net is really a better choice to do office development in at the moment. VB.net supports missing parameters which will come in handy for the Excel methods with a zillion parameters. This support will be added to C# for .NET 4.0.

You should be able to call a VBA method from .NET. Take a look at this: http://msdn.microsoft.com/en-us/library/bb608609.aspx

You can also look at this article on codeproject: http://www.codeproject.com/KB/office/extending_excel.aspx