VBA VS。VSTO有什么区别，我应该升级吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16770082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA VS. VSTO what is the difference, should I upgrade?
提问by
I am a huge fan of the KISS principle (Keep it simple) and a long time VBA for excel user. However all the articles I can find on VBA vs. VSTO (Visual Studio Tools for Office) seem very complicated.
我是 KISS 原则（保持简单）的忠实粉丝，并且是 Excel 用户的长期 VBA。然而，我能找到的关于 VBA 与 VSTO（Office 的 Visual Studio 工具）的所有文章似乎都非常复杂。
Firstly, I am sick of the very poor IDE built into Excel and I am looking for a new one. VSTO using Visual Studio as the IDE seems to be the only alternative.
首先，我厌倦了 Excel 内置的非常糟糕的 IDE，我正在寻找一个新的。使用 Visual Studio 作为 IDE 的 VSTO 似乎是唯一的选择。
So I seek to understand what is the difference between VBA and VSTO to know if it is worth me learning to use VSTO?
所以我试图了解 VBA 和 VSTO 之间有什么区别，以了解是否值得我学习使用 VSTO？
采纳答案by Charles Williams
Which toolset you use is largely a matter of personal preference, but here are some things to consider:
1. Performance: For most Excel-related tasks VSTO performs very slowly compared to VBA because of the additional Interop layer.
2. UDFs: VSTO does not support UDFs
3. Object Model: Its harder to work with the Excel object model with VSTO than with VBA because there is no .NET Macro recorder to give you hints.
4. IDE: With VSTO you get access to the latest Visual Studio IDE (assuming you have a Visual Studio license)
5. .Net Framework: VSTO gives you access to the very rich and powerful .NET framework
6. Learning curve: VB.NET is a different language to VBA and the .NET framework and object model differences mean that the transition is not as simple as it could be.
7. If you want to go the visual studio/.NET route I would recommend you look at Addin Express or XLDNA rather than VSTO: both of these have less limitations than VSTO
您使用哪种工具集在很大程度上取决于个人喜好，但这里有一些需要考虑的事项：
1. 性能：对于大多数 Excel 相关任务，由于额外的互操作层，与 VBA 相比，VSTO 的执行速度非常慢。
2. UDF：VSTO 不支持 UDF
3. 对象模型：使用 VSTO 使用 Excel 对象模型比使用 VBA 更难，因为没有 .NET 宏记录器可以给您提示。
4. IDE：通过 VSTO，您可以访问最新的 Visual Studio IDE（假设您拥有 Visual Studio 许可证）
5. .Net 框架：VSTO 可让您访问非常丰富和强大的 .NET 框架
6. 学习曲线：VB.NET 是一种与 VBA 不同的语言，.NET 框架和对象模型的差异意味着转换并不像它想象的那么简单。
7. 如果你想走 Visual Studio/.NET 路线，我建议你看看 Addin Express 或 XLDNA 而不是 VSTO：这两者的限制都比 VSTO 少
回答by Check-Kay Wong
VBA in Excel gives you the advantageof having fast results between editing your code and running your code. Because you are a good VBA programmer, you could live without the super feature from Visual Studio (intellisense, try-catch-fanally, inherits, every .NET objects, multi-threading ...)
Excel 中的 VBA 为您提供了在编辑代码和运行代码之间快速获得结果的优势。因为您是一名优秀的 VBA 程序员，所以您可以没有 Visual Studio 的超级功能（智能感知、try-catch-fanally、继承、每个 .NET 对象、多线程......）
VSTO is a way to work inside a very good IDE (Visual Studio), but i am afraid you will waste a lot of time switching between VB.NET and your Excel. It is not as direct as your VBA inside your Excel.
VSTO 是一种在非常好的 IDE (Visual Studio) 中工作的方法，但恐怕您会浪费大量时间在 VB.NET 和 Excel 之间切换。它不像 Excel 中的 VBA 那样直接。
If your main purpose of programming is to stay inside your Excel file, then stay with VBA. If you really need to work outside your excel file or if you want to do something that doesn't necessary depend on your excel files, maybe you could consider VSTO. Beside, VSTO is pretty new and documentation may not be complete.
如果您编程的主要目的是留在 Excel 文件中，那么请继续使用 VBA。如果你真的需要在你的 excel 文件之外工作，或者你想做一些不需要依赖于你的 excel 文件的事情，也许你可以考虑 VSTO。此外，VSTO 还很新，文档可能不完整。
I would stay with VBA if I were you. THIS IS MY HUMBLE OPINION. I wrote a little comparative between VBA and VB.NET (VSTO): Here's Difference between VB.NET and VBA!
如果我是你，我会留在 VBA。这是我的拙见。我写了一些 VBA 和 VB.NET (VSTO) 之间的比较：这是VB.NET 和 VBA 之间的区别！
回答by Will Ayd
This is 6 years after this question was originally asked so it's possible that at the time of the OP a comparison between these two tools were different, but I am actually very surprised at how favorable the existing answers are towards VBA!
这是最初提出这个问题的 6 年后，所以在 OP 时，这两种工具之间的比较可能有所不同，但实际上我对现有答案对 VBA 的支持程度感到非常惊讶！
VBA is interpreted and housed entirely within the application. The advantages here are:
VBA 被完全解释并包含在应用程序中。这里的优点是：
- Coding can be done directly within the application, requiring no external tools
- Application-specific features like Macro recording can enable quick prototyping
- You can leverage Excel-specific ActiveX controls like RefEdit
- UDFs can be developed easily
- AddIns are distributed as documents (.xla format)
- 编码可以直接在应用程序内完成，不需要外部工具
- 宏录制等特定于应用程序的功能可以实现快速原型设计
- 您可以利用 Excel 特定的 ActiveX 控件，例如 RefEdit
- 可以轻松开发 UDF
- 插件作为文档分发（.xla 格式）
For small applications and UDFs VBA works fine. You can build quickly and easily in Excel and things "just work."
对于小型应用程序和 UDF，VBA 工作正常。您可以在 Excel 中快速轻松地构建，一切“正常工作”。
If your program has any type of complexity however, it would be very worthwhile to explore VSTO. Advantages there include:
但是，如果您的程序具有任何类型的复杂性，那么探索 VSTO 将是非常值得的。那里的优势包括：
- Ability to use multi-paradigm programming languages like VB.NET or C#
- Better collection support (generic lists, dictionaries, tuples, etc...)
- Support for threads, asynchronous execution
- Deployment of AddIns as standalone applications
- Ability to use native WinForms and develop non-blocking UIs
- 能够使用多范式编程语言，如 VB.NET 或 C#
- 更好的集合支持（通用列表、字典、元组等...）
- 支持线程，异步执行
- 将插件部署为独立应用程序
- 能够使用本机 WinForms 和开发非阻塞 UI
VSTO is at this point is pretty well documented:
VSTO 在这一点上有很好的记录：
The topic of performance is rather vague but to contrast some of the previous answers this really depends on your bottlenecks. COM interoperability may add overhead, but at the same time the VBA execution model is much slower than what can be offered by C#. Particularly when you take into account the various paradigms that programming a VSTO can offer three are applications where a VSTO will be orders of magnitude faster than a similarly coded VBA program.
性能的主题相当模糊，但与之前的一些答案相比，这实际上取决于您的瓶颈。COM 互操作性可能会增加开销，但同时 VBA 执行模型比 C# 提供的要慢得多。特别是当您考虑到 VSTO 编程可以提供的各种范式时，VSTO 将比类似编码的 VBA 程序快几个数量级的应用程序。
回答by E?aut
I'm very surprised by your answers towards VBA.
我对您对 VBA 的回答感到非常惊讶。
Working with VBA for some years (mainly with outlook), I was bored with various negative aspects and I'm moving to VSTO since it is much more efficient.
在 VBA 工作了几年（主要是 Outlook），我对各种消极方面感到厌烦，我正在转向 VSTO，因为它效率更高。
I was confronted to many negative points:
我遇到了很多负面的问题：
Developping
发展中
- Vba IDE is very limited,
- Userforms are limited (try to make a rich text box)
- Vba IDE 非常有限，
- 用户表单有限（尝试制作富文本框）
Deploying
部署
- User need to do too many actions (modify security settings, import references, modify ribbon ...)
- 用户需要执行太多操作（修改安全设置、导入引用、修改功能区...）
Updating
更新中
- Can't be updated automatically, you need to send new files for each update to the user .
- 不能自动更新，每次更新都需要向用户发送新文件。
Add-ins are much more powerfull (by powerfull I mean it's easier to make complexe things), can add buttons to ribbons to lauch functions and are more easy to deploy (take a look at ClickOnce).
插件功能更强大（我所说的强大是指制作复杂的东西更容易），可以将按钮添加到功能区以启动功能，并且更易于部署（看看 ClickOnce）。
Generally, VBA macros are made for a personnal use while VSTO applications are made to be distributed as you can read here:
通常，VBA 宏是供个人使用的，而 VSTO 应用程序是供分发的，您可以在此处阅读：
If you are developing a solution that you intend to distribute to more than a few people, you should convert your VBA code into an Outlook COM or VSTO add-in or an Office add-in for Outlook.
如果您正在开发打算分发给多个人的解决方案，则应将 VBA 代码转换为 Outlook COM 或 VSTO 加载项或 Outlook 的 Office 加载项。
