NCGordon

Hello:

During the transition from VBA tools to .NET tools, and as a stop gap, we would like to call existing libraries of VBA macros from new .NET solutions, whether in Office 2003 or 2007. We've got tons of legacy code in MS Word and Excel templates which still work perfectly well, and would be far too costly to completely overhaul/migrate to .NET.

I've looked without success for guidance on the subject. Can anyone recommend useful documents/ code samples

(Yes, I know this is the VSTO forum. I'd like to use VSTO to build 2003 templates and 2007 add-ins, mixing use of new code and legacy VBA macros.)

Thanks for both your time and patience.

N.C. Gordon




Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

Dennis Wallentin

Hi N C,

Well, it's true that this is a VSTO forum (in fact the only one around as far as I known). You also can expect to receive some VSTO related answers on the subject but not from me


Why do You want to integrate VSTO and VBA in the first place I can understand it if it corporate policy and/or there exist some VSTO features that solve some major present limitations in VBA. From a strictly business point of view, if the VBA solutions are well written, documented/commented and works as expected then I can't see any reason to involve VSTO at all.

Edit: I forgot to mention that per se VSTO & VBA is not a 'happy marriage' and it's not the recommended path to go.






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

NCGordon

No, it's not a happy marriage at all, but with tons of existing code, and a corporate imperative to migrate our machines to Office 2007, it would be nice to have a method to ease the painful transition. We could go with the legacy route incorporated in Office 2007 to load existing Office 2003 templates and display the old control and tool bars as a very ugly and unwieldly Ribbon, and we will if we must.

However, it would be nice to find some way to create a new task pane or ribbon from which to call the legacy code while we make the transition from one paradigm to the next. So, here are two scenarios for which I'd like to find a solution:

1) Use an existing Word 2003 template containing macros with the VSTO (for 2003) template, and then create a task pane containing buttons to call the legacy VBA macros.

2) Do the same thing in VSTO SE (for Office 2007) to create an add-in Ribbon or TaskPane that would load with an existing Office 2003 template, and contain buttons to call the VBA macros. (NOT global based but template/document based, so it loads and unloads with the template.)

Eventually, we'll have to re-write it all, I suppose, but it certainly would be nice to have a means to make a less painful transition from the old to the new.

If someone has a helpful suggestion, or know of someone who could make a helpful suggestion, please let me know. Thank you.

N.C. Gordon.






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

Steve Hansen

Hi,

The simple way to call VBA code from a VSTO customization is using Application.Run. For example, suppose you have a VBA macro in a workbook called "MyVBAMacro". You can call this macro from within a VSTO project by using something similiar to:

Application.Run("MyVBAMacro", missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing);

Depending on what you're trying to do, you might also consider Excel UDFs to help bridge the gap. Kathleen and Paul cover this in the VSTO for Mere Mortals book. Also, see one of Paul's a blog postings on the subject.

Regards,

Steve Hansen






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

Dennis Wallentin

N.C,

We could go with the legacy route incorporated in Office 2007 to load existing Office 2003 templates and display the old control and tool bars as a very ugly and unwieldly Ribbon, and we will if we must.

1) Use an existing Word 2003 template containing macros with the VSTO (for 2003) template, and then create a task pane containing buttons to call the legacy VBA macros.


No reason to have 'ugly' UI which the following blogpost shows when it comes to Excel:
http://www.dailydoseofexcel.com/archives/2007/04/01/teaching-an-old-dog-new-tricks-a-2007-skin-for-older-add-ins/


2) Do the same thing in VSTO SE (for Office 2007) to create an add-in Ribbon or TaskPane that would load with an existing Office 2003 template, and contain buttons to call the VBA macros. (NOT global based but template/document based, so it loads and unloads with the template.)


Creating and deploying VSTO add-ins






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

NCGordon

Thanks, Steve, for your input.

Could you explain the reason for including the 30 "missing" parameters

N.C. Gordon






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

Steve Hansen

Hi N.C.,

One of the downsides of using C# for Office development stuff is handling all of the "optional" parameters which aren't optional when using C#. If you're using VB.NET they wouldn't be necessary. The Run method allows you to pass up to 30 arguments to the macro specified by the first parameter.

Thanks,

Steve






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

NCGordon

Whoops. You already answered this one. Thanks again.






Re: Visual Studio Tools for Office Using legacy VBA code in .NET and VSTO solutions

Steve Hansen

Yep - same procedure will work except all you need is: Application.Run("MyMacroName"). Use the other parameters as needed to pass arguments to the macro.

Thanks,

Steve