Muralidhar M S

We have hundreds of Excel tools to automate. What makes VSTO better than VBA to automate Excel Getting Visual Studio or the framework installed on user machines is not a problem.



Re: Visual Studio Tools for Office VSTO over VBA

X4U

Hello Muralidhar,

From my point of view the most benefit is:

- you have full access to the .Net Framework

- you can use a more powerfull programming language

- you can hide the code in a dll

- you can have a Server Document

- you can have Custom Task Panes

- you can have Custom Action Panes

The Excel Object Modell is still the same.

Hope this helps,

greets - Helmut






Re: Visual Studio Tools for Office VSTO over VBA

Cindy Meister

Expanding on Helmut's reply...

One of the main issues is separating the code from the "view" (the worksheet). This makes it easier to update the code; you could have the code in a network location and the files on the local machine, for example. You also have the benefit of the .NET Framework's security features. (Not so much hiding your code in a DLL - if you don't obfuscate anyone can extract it, but the run-time security.)

Adding to Helmut's list, you can also use Winforms controls within the workbook (instead of ActiveX or Excel forms controls).

You have the extended capabilities for binding outside data into the document. The data can be populated/extracted server-side without needing to run the Excel application (what Helmut refers to as "Server Document").

A drawback is that automation in VSTO will be slower than native VBA (what the Framework executes internally will probably be quicker).

Note that you have the option of using your existing VBA and building VSTO around that. This is an area that the new version (Orcas) is addressing specifically.






Re: Visual Studio Tools for Office VSTO over VBA

Dennis Wallentin

Hi,

Since Helmut & Cindy have give You the list of advantages I thought I would view it from a different angel, i e disadvantages:

Andrew Whitechapel has recently published a list of "no no" when it comes to VSTO developing which are important to take part of:

Can you build one add-in for multiple versions of Office

Why is VS development not supported with multiple versions of Office

Deployment:

If You got a 100 % controled enviroment and it exist a good knowledge of creating robust MSI packages within the corporate then it will not be a problem that cannot be solved. At present there is no easy way to deploy VSTO solutions for Vista. The security aspect needs to be evaluated before any decision is taken. It's subject to what the needs are to read/write with the present VBA solutions.

Performance:
Compared with VBA the performance with VSTO solutions is at best slow. VSTO needs to communicate via layers (Interop & VSTO) which significant has a negative impact on performance.

Requirements:

The following blogpost indicates what is required to develop & run VSTO solutions: Requirements VSTO Solutions

I don't say "No" but in my experience the needs needs to be evaluated against what the VSTO technology actually offer. VSTO itself is an interesting technology but not necessarily has it reached the production stage. The next coming version looks great in many ways but will only targeting Office 2007 and later.






Re: Visual Studio Tools for Office VSTO over VBA

JocularJoe

... and another disadvantage that might be important in some apps, is that with VSTO you can't easily automate other Office apps without the risk of leaving zombie Office application processes running, for the reasons discussed here: http://support.microsoft.com/default.aspx scid=kb%3ben-us%3b317109





Re: Visual Studio Tools for Office VSTO over VBA

JocularJoe

> One of the main issues is separating the code from the "view" (the worksheet). This makes it easier to update the code; you could have the code in a network location and the files on the local machine, for example

You can do the same thing with Excel VBA, by putting the VBA code in an XLA.





Re: Visual Studio Tools for Office VSTO over VBA

X4U

This seems to be a hot issue.

to add salt into injury

my personal thoughts:

VBA is more for Power Users who hack something as fast as possible togehther or record a macro to archive the goal.

VSTO is better for developing serious applications, at last because you have an Visual Studio IDE to develop your Application.

with a call stack, fancy debug windows, codesnippets and templates, intellisense support, and and and...

- not a better script editor.

Also every VSTO AddIn runs in a own Appdomain wich means - even when you have a serious programming error causing a crash

- all other functionality or AddIns remains functional.

Otherwise you have to restart the whole application or the complete OS.

Greets, Helmut






Re: Visual Studio Tools for Office VSTO over VBA

Dennis Wallentin

Helmut,

Primo:
Joe is 100 % correct when it comes to separating data from business logic in OBA solutions with VBA. We, the group of professional OBAD, have used that approach for the last 15 years.

Except for that it's de facto standard for all software development so it's not invented by VSTO.

Secundo:


VBA is more for Power Users who hack something as fast as possible togehther or record a macro to archive the goal.

How is this related to the subject being discussed here You don't know if You have offended the OP or not by this comment.


VSTO is better for developing serious applications, at last because you have an Visual Studio IDE to develop your Application.

In what way does the OP benefit from this I believe we all aware of that the VBIDE in Excel is old and less fancy then VS IDE.

with a call stack, fancy debug windows, codesnippets and templates, intellisense support, and and and...

- Call stack is available in Excel's VBIDE via the command Ctrl+L. Due to age it's not "fancy" compared with VS IDE but it exist.

- Codesnippets is a great utility that is missing in VBA.

- Templates can be created in VBA

- Intellisense support has been around since the early days of the 90's in VBIDE

- not a better script editor.

I'v never view VSTO or VBA as a script editor.

Also every VSTO AddIn runs in a own Appdomain wich means - even when you have a serious programming error causing a crash

- all other functionality or AddIns remains functional.

Otherwise you have to restart the whole application or the complete OS.

The isolation paradigm is, from a standalone point of view, far better then what the COM platform can ever provide. However, the reason for ending up in the blacklist list is poor coding which has nothing to do with wether VBA or VSTO is in use. So far I have never heard about the need to restart the computer due to poor VBA code.

Of course, there exist a great number of good utilities in VSTO but they can only be evaluated in view of the requirements from the OP.

Except for the technical aspects, I believe that the OP both deserve and benefit from a more professional discussion in terms of:

Is it economical feasible to port a great number of VBA solutions to VSTO What are the key issue to set focus on and what about the time frame If Office 2003 or Office 2007 is in use all over the corporate then one key issue is solved. But what if it exist several Office versions in use from 2000 to 2007

Before Cindy gets irritated on my poor comments I end my engagement in this thread with this post






Re: Visual Studio Tools for Office VSTO over VBA

X4U

Hi Dennis,

but the question was "What makes VSTO better than VBA" and i gave my personal thoughts.

In the past I developed with VBA too, then with VB6 through C# ending up with VSTO - why

To build qualitativ maintainable solutions for the customers.

Only my expirience and personal thoughts (as I said).

And it's still a hot topic and the Office Version problem is still there.

And you are right too. :-)

Greets, Helmut






Re: Visual Studio Tools for Office VSTO over VBA

Cindy Meister

JocularJoe wrote:

> One of the main issues is separating the code from the "view" (the worksheet). This makes it easier to update the code; you could have the code in a network location and the files on the local machine, for example

You can do the same thing with Excel VBA, by putting the VBA code in an XLA.

Question about this (since I'm primarily Word): Can you change the XLA while users are working with it It's not locked open






Re: Visual Studio Tools for Office VSTO over VBA

Denis Pitcher

I'll add to this thread as well.

Advantages of VSTO:

Autoupdate - big pain in the butt though if you want to run disconnected from the network as your addin disables if you do.

I've discussed solutions to this here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1753127&SiteID=1

Threading - I had a scenario where my users were fed up with waiting 10 min for an excel file to load so I rewrote the addins to use threading so that you could asynchronously make requests

Disadvantages

No specific UDF support in excel

This was a big one for me as that is what most of my excel addin's use. There are ways around them but they're extra work.

Addin-disabling is a huge pain

If an addin fails (for example, one reference is improperly linked), it gets disabled in the registry and is a huge pain in the butt to reenable.

Code access security is a huge pain. It has it's advantages in terms of better security but figuring out all the intriquite details of how to configure it properly can drive you mad.

Overall, I'm very pleased with my VSTO solution but it's been a long road getting it working. Much more work than if I'd just stuck it out with VBA.

I'll think of whether I can think of anything else.






Re: Visual Studio Tools for Office VSTO over VBA

JocularJoe

> Can you change the XLA while users are working with it

No, but there are workarounds which are in common use.

For example, what we do is put each new version of the application XLA in a version-specific subdirectory on a network share.

Then the root directory contains a trivial "loader" xla and a simple text configuration file that contains the path to one of the version-specific subdirectories.

The "loader" XLA reads the configuration file in its Workbook_Open event handler, and loads the appropriate version of the application xla referenced in its configuration file.

Users always install the "loader" xla, never a specific version of the application xla.

Thus to migrate users to a new version, we just need to:

- Create a new version-specific subdirectory with the new version of the XLA

- Modify the "loader" configuration file to reference the new version

- Notify users that they need to restart Excel to get the new version

- Old versions can be deleted as soon as all users have restarted Excel.

We are using a similar technique to provide interop between VBA and ComVisible managed code in a VSTO application domain, which provides a good migration path for progressively moving business logic out of VBA into managed code (VSTO support for this important scenario is currently woeful, though I understand this will improve in Orcas).

The way we achieve this is to create a VSTO document-level customization, then save the customized Excel document as an XLA. It can then be loaded on demand from another XLA that contains VBA code. When the VSTO app starts up, it calls Application.Run to excecute a VBA macro in the VSTO XLA, passing as an argument a reference to an instance of a ComVisible managed factory class. The VBA macro can then save this reference, and use it to instantiate other objects in the VSTO AppDomain.

This provides almost all of the benefits of a VSTO Excel Add-In, with much simpler XCopy deployment (after the root directory on the network share has been given full-trust on each user's workstation).

Another benefit is the fact that the VSTO XLA is loaded on demand by another XLA, so that the performance penalty of creating the VSTO AppDomain is only incurred when/if it is actually used, not at Excel startup.

Joe





Re: Visual Studio Tools for Office VSTO over VBA

Cindy Meister

<<We are using a similar technique to provide interop between VBA and ComVisible managed code in a VSTO application domain...>>

The mind boggles <g> That's very interesting. Thanks for taking a moment to write that all out.