How to programmably add a macro to an Excel workbook using VSTO c# code

Question

Hi everybody,
I have a question concerning how to programmably add a macro to an Excel workbook using VSTO c# code.
Basically what I want to do is: I have a VSTO add-in and based on some condiction it will create a new Excel workbook, and I want the current VSTO add-in to also programmably add a macro to this newly created workbook so that even  other people who don't have the VSTO installed on their PC can still use the functionality that this macro can provide.
Actually the origin of this question is: my VSTO add-in will extract some information from an Excel workbook(lets say "Original workbook") and save this data in a newly created Excel workbook(lets say "New workbook"). Those New workbooks may be examined by some other people. I want to facilitate the examine process by letting the user double the cells in the New workbook and the focus will automatically go to a particular row in the original Workbook so that they can reference the detailed information in the original workbook. This can be easily done by writing a VSTO application-level add-in, but it requires that the examiners have the VSTO installed on the PC. This should also be easy to do using VBA code or a macro, but I need to include the VBA code along with those New workbook document. So to achieve both, I'm thinking that in my current VSTO add-in I programmably add a macro or inserting VBA code into the newly created workbook. But I don't know how to implement it in C#  or whether it is feasible to do this.

I searched on the web for some time and haven't got the results yet. So I greatly appreciate anybody who can help me with this. Many thanks in advance!

Answer 1

For this, you need to use the Visual Basic Extensibility type library (vbe.interop). You'll need to add a reference to this in your solution.

There's some general information in this thread.

You'll find a number of code samples, many of the VBA, if you search the Internet. Here are a few discussions that are in the Microsoft forums (I found them in a Live Search)

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/81046fcf-166a-4d3f-9c46-196b7dd23eaf/

See the part of the code in the first message, starting with VBComponent oModule. The problem mentioned in this message is probably due to the Trust settings on the client machines. Access to VBA projects must be specifically allowed, and it's off by default. In order to create VSTO document-level solutions this is activated the first time a VSTO solution is created, which is probably why the OP was able to get this to work on the development machine but not later. Also, if the OP had put his code in a try-catch block he would have likely gotten error information.

http://social.msdn.microsoft.com/Forums/en-US/csharpide/thread/012a2700-0884-4352-9b18-0d84445e2f22/
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/5b2f5387-930a-4932-bae0-f42ddbd9cbc6/

Also contain code samples on how to create a macro, look for the same code as above.

Here are some threads with related uses of the VBE interop

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/c4dd66dc-1169-45e2-b724-0aeedcfc1025/
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/0e040a26-cfbe-4fc0-b498-a01ceda3573f
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/5193f91b-6cd9-4663-be40-9e7f9a5ab751/

---

Cindy Meister, VSTO/Word MVP

Answer 2

  Hi Cindy,
Thank you so much for your answer and information. I got what I want and it works well! Thanks again!!