Excel 编程方法
UDF、宏、外接程序、自动化外接程序、XLL 或 VSTO 之间的 Excel 编程有何区别。
我应该使用哪一个以及在什么情况下使用？
What are the differences in Excel programming between a UDF, Macro, Add-in, Automation Add-in, XLL, or VSTO.
Which one I should use and under which circumstances?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我将尝试对您提到的一些元素进行分组/反对：
VSTO 与 VBA：
VBA（Visual Basic for applications）是编写办公自动化的“经典”方式。 Excel 有一个开发界面，您可以在办公室中使用它来编写宏和 UDF，并且该界面大约 10 年没有改变。从好的方面来说，部署很简单，而且 VBA 提供了很好的功能，例如宏记录，它将您的操作记录到代码中，并提供了一种找出对象模型的好方法。
VSTO 最近出现，允许您使用 .NET（使用 COM）实现办公自动化。您可以利用所有 .NET 和 Visual Studio（例如：添加 WPF 表单），这提供了极大的灵活性，但部署更加复杂。
UDF 与宏与插件
用户定义的函数是定制的方法，将附加到您的工作簿中；添加后，将获得与“内置”Excel 函数相同的状态：您可以从 =MyFunction() 等工作表中调用它们
宏是将附加到您的工作簿的过程。它们可以由用户直接调用，或附加到事件（例如：选择工作表时，执行此操作）。
加载项不附加到特定文档，而是附加到应用程序本身。通常，宏或 UDF 附加到工作簿：当您打开文档时，代码将可供您使用，当您提供文档时，代码也会被复制。相比之下，加载项附加到应用程序：启动 Excel 时，加载项就可供您使用。像求解器这样的工具是一个插件。请注意，加载项可以用 VBA 或使用 VSTO 编写（您也可以在 .NET 中编写 UDF，但这不是典型的）。
何时使用什么
加载项与宏/UDF：如果您的功能应该可以从任何工作簿访问，请编写一个加载项。
VSTO 还是没有 VSTO：这是一个有争议的问题。大多数熟悉“经典”VBA 自动化的人不太喜欢 VSTO，因为学习曲线有点陡峭。如果您习惯使用 .Net，这应该不是什么问题 - 但与“正常”.Net 应用程序开发相比，VSTO 有点奇怪。
另请注意，如果您使用 VSTO，则用户无法编辑您的代码。这可以说是可取的，但与此同时，Excel 高级用户通常知道如何使用 VBA 和宏，并期望能够调整代码。这可能会引发有趣的讨论。
就我个人而言，我通常将 VSTO 用于加载项，当我看到大量逻辑/过程代码进入 VBA 中的宏时，我就会使用它。 VSTO 给我的是在 Visual Studio 中编写可测试代码的能力，以及在涉及大量计算时编写高性能代码的能力。使用 VSTO 的另一个原因是利用 WPF 来定制 Office。
I'll try to group/oppose some of the elements you mention:
VSTO vs. VBA:
VBA (visual basic for applications) is the "classic" way to write Office automation. Excel has a development interface which you can fire from office to write macros and UDFs, and which hasn't changed for about 10 years. On the plus side, deployment is trivial, and VBA offers nice features like macro recording, which record your actions into code, and provide a good way to figure out the object model.
VSTO came about more recently, and allows you to automate office using .NET (uses COM). You can leverage all .NET and Visual Studio (ex: add WPF forms), which gives great flexibility but the deployment is more complex.
UDF vs. Macro vs. Add-In
User defined functions are custom-made methods which will stay attached to your workbook; once added gain the same status as the "built-in" Excel functions: you can call them from worksheets like =MyFunction()
Macros are procedures which will stay attached to your workbook. They can be called directly by the user, or attached to events (ex: when a worksheet is selected, do this).
Add-ins are not attached to a specific document, but to the application itself. Typically a macro or UDF is attached to the workbook: when you open the document, the code becomes available to you, and when you give the document, the code is copied as well. By contrast, Add-Ins are attached to the application: the moment you launch Excel, the add-in becomes available to you. Tools like the Solver are an Add-in. Note that Add-Ins can be written in either VBA or using VSTO (you can also write UDFs in .NET, but it's atypical).
When to use what
Add-In vs Macro/UDFs: write an add-in if your functionality should be accessible from any workbook.
VSTO or no VSTO: this is a matter of debate. Most people who are familiar with "classic" VBA automation don't like VSTO too much, because the learning curve is a bit steep. If you are used to working with .Net, this should not be a bit issue - but VSTO is somewhat quirky compared to "normal" .Net apps development.
Note also that if you use VSTO, your code can't be edited by the user. This is arguably desirable, but at the same time, Excel power-users usually know how to use VBA and macros, and expect to be able to tweak the code. That can lead to interesting discussions.
Personally, I typically use VSTO for add-ins, and I use it as soon as I see that lots of logic / procedural code is going into macros in VBA. What VSTO gives me is the ability to write testable code, in Visual Studio, and also high-performance code when heavy calculation is involved. The other reason to use VSTO is to leverage WPF to customize office.
将 VBA 用于 Excel 2003 及更早版本（尽管如果您愿意，您仍然可以在 2007 年使用它）。
VSTO 适用于 Excel 2003 或 2007。
旧的 Excel 4.0 宏语言绝对不应该再使用，它们不断禁用某些部分并且功能正在消失。我们必须重写所有电子表格才能删除旧的宏。
宏也是 VBA 代码的别称。
外接程序只是另一个包含代码的 Excel 工作簿（如 VBA），因此您可以在不同的工作簿中使用代码，并将代码直接保留在用户工作簿之外。
UDF 只是用 VBA 编写的函数，在电子表格的单元格中作为工作表函数进行调用（也可以存储在外接程序中）。
Use VBA for Excel 2003 and earlier (though you could still use it in 2007 if you want).
VSTO is for Excel 2003 or 2007.
The old Excel 4.0 macro language should definitely not be used anymore, they keep disabling parts and the functionality is disappearing. We're having to rewrite all our spreadsheets to get the old macros out.
Macros are also another name for VBA code.
An Add-in is just another Excel workbook that has code in it (like VBA), so that you can use the code in different books, and keep the code directly out of the users book.
An UDF is just a function written in VBA that get's called as a worksheet function in a cell in a spreadsheet (it can also be stored in an Add-in).
兰斯对你的问题有很好的定义。就何时使用什么而言，VBA 与 VSTO——如果您需要使用 .net 框架，请使用 VSTO。例如，我想制作一个带有电子表格的 VB.net 程序界面，学习 VSTO 将是一个可行的方法（而且 vba 没有像 vb.net 这样的 array.sort，这让我发疯）。然而，如果您正在制作一个漂亮的电子表格，可以为您的最终用户做一些很酷的事情（公式、图表等），只需在电子表格中编写一些 VBA 代码即可。这完全取决于你必须做什么。适合这项工作的最佳工具。
Lance has good definitions to your questions. In terms of what to use when, VBA vs VSTO--if you are needing to use .net framework go with VSTO. For instance is I wanted to make a VB.net program interface with a spreadsheet learning VSTO would be the way to go (that and vba does not have an array.sort like vb.net which drives me crazy). However if you are making a nifty spreadsheet that will do some cool things for your end users (formulas, charts etc) just whip up some VBA code within the spreadsheet. It all depends on what you have to do. Best tool for the job.
从 VBA 方面来看，还有几点：
外接程序 - 所有重要的代码都应该放在外接程序中。对我来说，一个典型的项目由一个包含所有代码的加载项、一个用于保存数据的数据存储（数据库、文本文件、Excel 工作簿）以及一个或多个作为表示层或用户界面的模板组成。您不必使用加载项；代码将在模板内的模块中正常工作。但是，将项目层分开是一个很好的做法。代码放入加载项，数据放入数据存储，演示文稿放入一个或多个工作簿。这样，您可以更改代码而不影响其他两层。或者，您可以更改演示模板而不影响现有数据。
UDF - 这是使用 Function 关键字声明的。如果需要返回单个值（或数组或对象实例等），请使用函数。自定义工作表函数是 UDF，因为它们仅向单元格返回值。除 Value 属性外，它们不会更改单元格的任何属性。例如，您不能通过函数应用边框或字体。
宏 - 也称为过程，它们使用 Sub 关键字声明。如果您需要执行除返回值之外的其他操作，例如需要操作 Excel 对象模型的某些部分，请使用过程。
A few more points from the VBA side of things:
Add-in - All of your non-trivial code should go in an add-in. A typical project for me consists of an add-in that contains all of the code, a data store (database, text file, Excel workbook) to hold the data, and one or more templates as presentation layer or user interface. You don't have to use an add-in; code will work just fine in modules within your templates. However, it's a good practice to keep your project layers separate. Code goes in an add-in, data goes in a data store, presentation pieces go in a workbook or workbooks. This way, you can change your code without affecting the other two layers. Or you can change a presentation template without affecting the existing data.
UDF - This is declared with the Function keyword. Use a function if you need to return a single value (or array or object instance etc). Custom worksheet functions are UDFs because they only return a value to a cell. They don't change any properties of the cell except the Value property. You can't apply borders or fonts, for example, via a function.
Macro - Also called a procedure, they are declared with the Sub keyword. Use a procedure if you need to do something other than return a value, such as if you need to manipulate parts of the Excel object model.