Using Visual Studio Instead of Built in VBA Editor

From my perspective

Use VBA editor (VBE) if you

• develop ad-hoc apps for yourself, friends
• are the only one who works on a project
• mainly works with the Office object model - no communicating with System, Web, database etc.
• don't use source control (Note: Rubberduck is here to help if you do so)
• don't include too many dlls from the Reference form
• don't want to learn new platform (.NET)

Note, I did all the above and I could live with just VBA for long time without too many problems and in a few ways VBA is much better than VSTO e.g. in debugging (here I mean, you can change almost everything when you are debugging VBA and you'll be still able to continue, there is no way to do the same in VSTO)

Use VSTO if you

• want to better support for System, Web, XML, database tasks. NET is much more better especially if we're talking about web or database stuff

• want to have better support (sometime out of box) for version control (GIT, SVN, TFS, Perforce)

• don't won't to have problems with dependencies (this could be actually painful as well)

• want to use new VSTO objects like NamedRange incl. events

• want to learn new platform

I remember myself a few years ago when I thought VSTO brigs something new to Office object model, and it doesn't if you don't count the VSTO objects. So if you're heavily working with just Excel object model (manipulating with ranges, sheets, workbooks, calculating) you can still live with VBA only. I also remember that with VSTO you can't easily create UDF (custom function)

Also seems like that MS is not going to invest too much effort to add/update/improve VSTO, they rather focus on Office API

VSTO blog - comments

Thanks for your question Ben, we have already publically announced that we will not be turning off VSTO or VBA in the next version of Office. All of our new investments are in Apps for Office and Apps for SharePoint and making the Office 365 APIs more robust. We will not be making any future investments in VSTO or VBA

It might look like I'm against VSTO but I'm not, I've been using it for more than 4 years daily and returning back to VBA just for quick testing or quick projects.

Hope this helps

From developing language, Excel Editor is VBA, and VS is C#/VB.NET. If you have a light function, you could use VBA directly, if you have a large requirement, .NET/VSTO would be suitable, it would be convenient to manage your code in VS, .NET.VSTO is managed code, it’s performance is better than VBA. With VSTO, you could custom Office UI. From distributing your project, you need to copy the office file with macro enabled. You could use ClickOnce or Windows installer to distribute .net/VSTO solution.