使用VBA vs .Net自动化Excel的优缺点 [英] Pros and Cons of automating Excel using VBA vs .Net

问题描述

我的任务是在Excel中创建一个可以从一些自定义函数/宏中获益的财务计划工具。

我最初的反应是使用VBA。我以前用它来驱动Excel（比如5年前）。但是我开始怀疑我是否会使用VSTO更好。

有没有人有使用这两种技术的经验，可以列出优缺点，以便我可以评估哪些课程将是最好的。

解决方案

我建议您使用VBA保持Excel的标准开发，并学习.NET这边。使用.NET绝对是下一步，但它使您的Excel开发变得更加困难。

此外，VSTO不允许创建用户定义的工作表函数（UDF ），所以您需要一个VBA前端，或者创建一个托管COM加载项而不使用VSTO，以便做到这一点。相比之下，VBA允许您几乎没有任何努力来创建UDF。

使用.NET有许多优点，主要是关于强类型，完整的OOP功能，以及组织大型项目。但是当涉及到部署时，VBA具有超过.NET的巨大优势，这在处理.NET或VSTO时与Excel非常复杂。 VBA也是一个更容易学习和开始的语言。

总的来说，我建议您使用VBA进行日常开发，但要学习VB。 NET或C＃，使您的编程技能可以在Excel竞技场之外成长。最终，您的.NET技能可以变得足够强大，以便您更喜欢在VBA上使用它，但是您必须在当天的.NET中变得非常擅长。

（关于另一个类似的意见，请参阅如果我在Visual Studio中开发Excel应用程序，我会失去宏记录的好处吗？）

编辑：关于Andy的评论的更新：

像部署，调试
和UDF这样的问题是I
正在寻找比较信息
。根据对
问题的回应，我应该提到
我有5年以上的C＃，
的经验，而我的VBA技能（或缺少
）只能出来3或者每年4次

好的，你应该说！大多数有这样的问题的人是想要进入.NET的VBA程序员。所以我误解了。

在你的情况下，你应该使用C＃，但是我强烈建议在Visual Studio 2010中使用C＃4.0，这将大大提高语法对于COM对象模型（如Excel）进行操作时是必需的。 VS 2010目前处于beta 2，RTM日期设定为4月12日，所以我们几乎在那里。

至于部署，根据您的经验，我不认为您将会遇到安装程序包或其他类似的问题，Visual Studio Tools for Office（VSTO）对于两件事情是无可辩驳的：

1. 通过拖放设计器为您的加载项创建自定义功能区安排。没有拖放设计器，你必须提供XML。如果您问我，XML就可以正常使用，但拖放设计师真的是使用




2. 在工作表上使用.NET控件的梦想。我不知道这是否是您打算做的一部分，但VSTO可以在工作表上使用.NET控件。对于.NET程序员来说，这是一个非常好的功能，因为这些控件看起来更加平滑，并且专门设计为使用.NET。

不幸的是，VSTO仅适用于Excel 2003及以上版本，我认为您必须为Excel 2003和Excel 2007创建单独的加载项。另一方面，不使用VSTO进行管理的COM加载项，可以与Excel 2000及以上版本兼容，无困难。最后，VSTO不支持UDF的创建，因此，您必须为此创建一个受管理的自动化加载项，或者使用调用VSTO函数的VBA前端。

总体而言，如果您能够将自己限制在Excel 2007及以上版本，我将与VSTO一起使用。如果您的要求适用于Excel 2003及以上版本，我将考虑使用VSTO。如果您需要能够在Excel 2000及更高版本上运行，我将使用托管COM加载项。

对于UDF支持，我将创建一个托管自动化加载项，这对Excel 2002及更高版本是可行的。如果您需要在Excel 2000或更低版本上使用UDF，那么您需要一个VBA前端，它会在.NET程序集中调用COM可见方法。

这些是基本的专业人员和con，就像我看到的那样。让我知道如果你需要了解更多。

- Mike

I've been tasked with creating a financial planning tool in Excel that would benefit from some custom functions/macros.

My initial reaction was to use VBA. I've used it to drive Excel before (say 5 years ago). But I then began to wonder if I would be better off using VSTO.

Has anyone has experience using both techs and can list the pros and cons so that I can evaluate which course would be best.

解决方案

I would suggest that you stick with VBA for your standard development with Excel, and learn .NET on this side. Using .NET is definitely the next step, but it makes your Excel development much harder.

Further, VSTO does not enable the creating of user defined worksheet functions ("UDFs"), so you would need a VBA front-end, or create a managed COM add-in without using VSTO, in order to do this. By comparison, VBA allows you to create UDFs with virtually no effort.

Using .NET has many advantages, mostly regarding strong-typing, full OOP capabilities, and the ability to organize larger-sized projects. but VBA has enormous advantages over .NET when it comes to deployment, which is quite complicated with Excel when dealing with .NET or VSTO. VBA is also an easier language to learn and start out with.

Overall, I would suggest that you use VBA for your day-to-day development, but learn VB.NET or C# on the side so that your programming skills can grow outside of the Excel arena. Eventually, your .NET skills can become strong enough so that you will prefer to use it over VBA, but you will have to become pretty good at .NET for that day to come.

(For another similar opinion on this, see Do I lose the benefits of macro recording if I develop Excel apps in Visual Studio?.)

Edit: Update regarding Andy's comment, below:

Issues like deployment, debugging and UDFs were ones that I was looking for comparison information on. Judging by the responses to the question I should have mentioned that I have 5+ years experience with C#, whereas my VBA skills (or lack thereof) only come out 3 or 4 times a decade

Ok, yes, you should have said! Most people with questions like this are VBA programmers who are looking to get into .NET. So I misunderstood.

In your case, you should use C#, but I would strongly suggest using C# 4.0 on Visual Studio 2010 for this, it will greatly improve the syntax that is required when operating against a COM object model such as Excel. VS 2010 is currently in beta 2 and the RTM date is set for April 12th, so we are almost there.

As for deployment, with your experience I don't think you'll have too much trouble with setup packages or the like and Visual Studio Tools for Office (VSTO) is excptionally good for two things:

1. Creating a custom ribbon arrangement for your add-in via a drag and drop designer. Without the drag-and-drop designer you have to provide XML instead. XML is just fine if you ask me, but the drag-and-drop designer really is a dream to use

2. Utilizing .NET controls on a worksheet. I don't know if this is part of what you plan on doing, but VSTO enables .NET controls to be used on the worksheet. This is a really nice capability for a .NET programmer since these controls are a bit smoother looking and are specifically designed to work with .NET.

Unfortunately, VSTO is only available for Excel 2003 and above and I think you have to create separate add-ins for Excel 2003 and Excel 2007. Managed COM add-ins made without using VSTO, on the other hand, can be made compatible for Excel 2000 and above with no difficulty. Lastly, VSTO does not support the creation of UDFs and, therefore, you'd have to either create a managed automation add-in for that or utilize a VBA front-end which calls your VSTO functions.

Overall, I would go with VSTO if you can limit yourself to Excel 2007 and above. I would consider VSTO if your requirements are for Excel 2003 and above. And I would go with a managed COM add-in if you need to be able to run on Excel versions 2000 and above.

For UDF support, I would create a managed automation add-in, which would be viable for Excel 2002 and above. If you need UDFs on Excel 2000 or below then you would need a VBA front end which calls COM-visible methods in your .NET assembly.

These are the basic pro's and con's, as I see it. Let me know if if you need to know more.

-- Mike

这篇关于使用VBA vs .Net自动化Excel的优缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！