使用 VBA 与 .Net 自动化 Excel 的优缺点

Pros and Cons of automating Excel using VBA vs .Net

提问by Andy

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

我的任务是在 Excel 中创建一个财务规划工具，该工具将从一些自定义函数/宏中受益。

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.

我最初的反应是使用 VBA。我以前用它来驱动 Excel（比如 5 年前）。但随后我开始怀疑使用 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.

有没有人有使用这两种技术的经验，可以列出利弊，以便我可以评估哪门课程最好。

采纳答案by Mike Rosenblum

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.

我建议您坚持使用 VBA 进行 Excel 的标准开发，并在这方面学习 .NET。使用 .NET 绝对是下一步，但它会使您的 Excel 开发更加困难。

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.

此外，VSTO 不支持创建用户定义的工作表函数（“UDF”），因此您需要一个 VBA 前端，或者在不使用 VSTO 的情况下创建托管 COM 加载项，以便执行此操作。相比之下，VBA 允许您几乎毫不费力地创建 UDF。

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.

使用 .NET 有许多优点，主要是强类型、完整的 OOP 功能以及组织大型项目的能力。但是在部署方面，VBA 比 .NET 有巨大的优势，在处理 .NET 或 VSTO 时，这与 Excel 相比相当复杂。VBA 也是一种更容易学习和入门的语言。

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.

总体而言，我建议您使用 VBA 进行日常开发，但同时学习 VB.NET 或 C#，以便您的编程技能可以在 Excel 领域之外增长。最终，您的 .NET 技能会变得足够强大，因此您会更喜欢使用它而不是 VBA，但在未来的那一天，您必须变得非常擅长 .NET。

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

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

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

部署、调试和 UDF 等问题是我正在寻找比较信息的问题。从对这个问题的回答来看，我应该提到我有 5 年以上的 C# 经验，而我的 VBA 技能（或缺乏）十年只出现 3 或 4 次

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.

好吧，是的，你应该说！大多数有类似问题的人都是希望进入 .NET 的 VBA 程序员。所以我误会了。

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.

在您的情况下，您应该使用 C#，但我强烈建议为此在 Visual Studio 2010 上使用 C# 4.0，它将大大改进针对 COM 对象模型（如 Excel）操作时所需的语法。VS 2010 目前处于测试版 2 中，RTM 日期设置为 4 月 12 日，所以我们快到了。

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:

至于部署，根据您的经验，我认为您不会在安装包等方面遇到太多麻烦，而且 Visual Studio Tools for Office (VSTO) 在两方面非常有用：

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.

1. 通过拖放设计器为加载项创建自定义功能区排列。如果没有拖放设计器，您必须提供 XML。如果你问我，XML 很好，但拖放设计器真的是一个梦想

2. 在工作表上使用 .NET 控件。我不知道这是否是您计划执行的一部分，但 VSTO 允许在工作表上使用 .NET 控件。这对于 .NET 程序员来说是一个非常好的功能，因为这些控件看起来更流畅，并且专门设计用于与 .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.

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

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.

总的来说，如果您可以限制自己使用 Excel 2007 及更高版本，我会选择 VSTO。如果您的要求是 Excel 2003 及更高版本，我会考虑 VSTO。如果您需要能够在 2000 及更高版本的 Excel 上运行，我会使用托管 COM 加载项。

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.

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

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

在我看来，这些是基本的利弊。如果您需要了解更多信息，请告诉我。

-- Mike

——迈克

回答by Charles Williams

Given your familiarity with C# I would recommend you look at Addin Express (costs) and Excel DNA (free), as well as VSTO. Addin Express has a version neutral PIA which simplifies deployment, can handle both functions and commands and can use both the Automation and XLL interfaces (XLL is much faster than Automation). Excel DNA is more optimised towards the XLL interface but is great for developing worksheet functions.

鉴于您对 C# 的熟悉程度，我建议您查看 Addin Express（费用）和 Excel DNA（免费），以及 VSTO。Addin Express 有一个与版本无关的 PIA，它简化了部署，可以处理功能和命令，并且可以使用自动化和 XLL 接口（XLL 比自动化快得多）。Excel DNA 针对 XLL 界面进行了更优化，但非常适合开发工作表功能。

Disclaimer: I have no connection whatsoever with either Addin Express or Excel DNA

免责声明：我与 Addin Express 或 Excel DNA 没有任何联系

回答by Tim Jarvis

I don't think that there is anything inherently wrong with coding this in VBA within Excel. It has the advantage of running in excel's process space and the VBA interaction with Excels object model is very simple. However you (and your users) need to really think of your tool as an Excel add-in.

我不认为在 Excel 中的 VBA 中进行编码本身就没有任何问题。它的优点是运行在excel的进程空间，VBA与Excels对象模型的交互非常简单。但是，您（和您的用户）需要真正将您的工具视为 Excel 加载项。

If you want your tool to have a separate identity than that of Excel, then Automation is your option. It's a little slower (you are "out of process") and the object model is marginally less clean, but your tool will have it's own identity.

如果您希望您的工具具有与 Excel 不同的身份，那么自动化是您的选择。它有点慢（您“不在进程中”）并且对象模型稍微不那么干净，但是您的工具将拥有自己的身份。

(note if you use C# and if you have the option use VS2010 and C#4 there are a bunch of automation enhancements that are worth the upgrade)

（请注意，如果您使用 C#，并且可以选择使用 VS2010 和 C#4，则有许多值得升级的自动化增强功能）

回答by Joe Erickson

SpreadsheetGear for .NETlets you add an Excel compatible spreadsheet component to your .NET (WinForms, ASP.NET, etc...) applications without the downsides (performance, ease of use) of COM Interop.

SpreadsheetGear for .NET允许您将 Excel 兼容的电子表格组件添加到您的 .NET（WinForms、ASP.NET 等）应用程序中，而没有 COM Interop 的缺点（性能、易用性）。

You can learn more about the SpreadsheetGear Windows Forms spreadsheet control here, see live ASP.NET samples hereand download the free trial hereif you want to try it yourself.

您可以了解更多有关的SpreadsheetGear Windows窗体电子表格控件这里，看到现场ASP.NET样品这里并下载免费试用这里，如果你想自己尝试一下。

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

回答by aevanko

Many great answers here so I'll try to make a point that wasn't made yet. I would stick with VBA. There are MANY reasons, but the main factors for me would be:

这里有很多很棒的答案，所以我会尝试提出一个尚未提出的观点。我会坚持使用 VBA。原因有很多，但对我来说主要的因素是：

• Doesn't require the client side to have any specified ver. of .Net installed

• 不需要客户端具有任何指定的版本。.Net 安装

However, VBA obviously is notvery protected, so if you have code that you want to hide, it's best to go with .Net probobly.

然而，VBA 显然不是很受保护，所以如果你有你想隐藏的代码，最好使用 .Net probobly。