我应该用 C# 还是 VBA 开发这个应用程序？

Should i develop this application in C# or VBA?

提问by sigil

First off, this question is not meant to be a debate of the overall merits of C# vs VBA. I have a project that has some parts better suited to C# and other that seem more appropriate for VBA, and I'm trying to figure out how best to structure my development, as I don't have time to build it several different ways and see which one is faster. I'm posting the question here because there might be something about one language that I haven't considered that clearly makes it the more attractive choice for this project.

首先，这个问题并不是要讨论 C# 与 VBA 的整体优点。我有一个项目，其中一些部分更适合 C#，而其他部分似乎更适合 VBA，我正在尝试找出如何最好地构建我的开发，因为我没有时间以几种不同的方式构建它看看哪个更快。我在这里发布这个问题是因为可能有一种语言我没有考虑过，这显然使它成为这个项目更有吸引力的选择。

The project:

该项目：

1. get a data set from a Sharepoint list, either with a web services query or by refreshing the contents of an Excel sheet that's linked to this list.

2. join the Sharepoint data set to an Excel data set of workbook filenames using inequality relationships between the sets' fields. this will be a many-to-many.

3. for each workbook in each set of matches, export some of its data to a new workbook in a different folder, so that each match set is in its own folder.

4. zip all the folders in one archive.

1. 通过 Web 服务查询或刷新链接到此列表的 Excel 工作表的内容，从 Sharepoint 列表中获取数据集。

2. 使用集合字段之间的不等式关系将 Sharepoint 数据集连接到工作簿文件名的 Excel 数据集。这将是多对多。

3. 对于每组匹配中的每个工作簿，将其某些数据导出到不同文件夹中的新工作簿，以便每个匹配组都位于其自己的文件夹中。

4. 将所有文件夹压缩到一个存档中。

Step 1 seems like either .NET or VBA would be satisfactory. Step 2 seems better suited to C# because it involves a join query that would be easier to accomplish with LINQ rather than with a bunch of VBA loops. Step 3 involves Excel data transfer, which makes VBA a better candidate, as I've read a lot of posts agreeing that VBA is faster than .NET Interop for Office operations. Step 4 seems like it would be better in C# because I can use DotNetZip rather than having to rely on shell commands.

第 1 步似乎 .NET 或 VBA 都令人满意。第 2 步似乎更适合 C#，因为它涉及一个连接查询，使用 LINQ 比使用一堆 VBA 循环更容易完成。第 3 步涉及 Excel 数据传输，这使 VBA 成为更好的候选者，因为我读过很多帖子，我都同意 VBA 比 .NET Interop 进行 Office 操作更快。第 4 步似乎在 C# 中会更好，因为我可以使用 DotNetZip 而不必依赖 shell 命令。

But a mixture of C# and VBA would be much less convenient to maintain than a single-language solution. So I'm not sure what approach I should take.

但是，与单一语言解决方案相比，C# 和 VBA 的混合维护要方便得多。所以我不确定我应该采取什么方法。

采纳答案by Pragmateek

The only point in favor of VBA you mention is supposed performance gain when interacting with Excel.

您提到的支持 VBA 的唯一一点是在与 Excel 交互时应该提高性能。

The overhead of interacting with Excel from C# compared to VBA will be negligible compared to the cost of the other tasks : data retrieval, calculations...

与其他任务的成本相比，从 C# 与 Excel 交互的开销与 VBA 相比可以忽略不计：数据检索、计算......

If not it means you are doing things badly : e.g. filling values or applying formatting of cells one by one.

如果不是，则意味着您做的事情很糟糕：例如，一一填充值或应用单元格格式。

As a general rule you should only use VBA if the mainteners of the application have not enough development skills to use C# ANDdo not want to learn a new language.

作为一般规则，如果应用程序的维护人员没有足够的开发技能来使用 C#并且不想学习新语言，那么您应该只使用 VBA 。

This is typically the case when the maintener is the final client who knows only how to craft basic macros, and would be completly powerless if your C# addin had any issue.

当维护者是只知道如何制作基本宏的最终客户时，通常会出现这种情况，如果您的 C# 插件有任何问题，将完全无能为力。

回答by James Snell

Looking at your requirements...

看你的要求...

Part 1 - Excel has no real problem getting xml data or doing web service queries. I was doing that sort of thing back in Excel 2002.

第 1 部分 - Excel 在获取 xml 数据或执行 Web 服务查询方面没有实际问题。我在 Excel 2002 中做那种事情。

part 2 - Probably you're right that the data integration would be syntactically nicer and probably better optimised in C# - linq is very handy. But it depends on what that integration is.

第 2 部分 - 可能你是对的，数据集成在语法上会更好，并且可能在 C# 中得到更好的优化 - linq 非常方便。但这取决于集成是什么。

part 3 is definitely vba territory, all the updates etc.

第 3 部分绝对是 vba 领域，所有更新等。

part 4 doing that in c# might look prettier UI wise by not opening cmd windows - but how often is the code really going to run vs how easy they are to hide.

第 4 部分在 c# 中执行此操作可能通过不打开 cmd 窗口看起来更漂亮的 UI 明智 - 但代码真正运行的频率与隐藏它们的容易程度。

Other considerations...

其他考虑...

Unless you do late binding, your C# app will be version-tied to Excel (as given away by the inclusion of excel version when you add the reference). If you had a new version of Excel deployed then you'd have to update your C# app where vba would most likely 'just work'.

除非您进行后期绑定，否则您的 C# 应用程序将与 Excel 版本绑定（因为在添加引用时包含 excel 版本而放弃）。如果您部署了新版本的 Excel，那么您必须更新您的 C# 应用程序，其中 vba 最有可能“正常工作”。

There shouldn't be an issue with resourcing either - you said you have C# devs and anyone who has the skills to call themselves a developer should be able to adapt pretty quickly to the major procedural languages and libraries they might reference for .net development could be in either C# or VB.Net equally - it's not like you're writing it in F#.

资源也不应该有问题——你说你有 C# 开发人员，任何有能力称自己为开发人员的人都应该能够很快适应他们可能会在 .net 开发中引用的主要程序语言和库同等地使用 C# 或 VB.Net - 这不像你在 F# 中编写它。

There's a lot to be said for keeping all the dependencies together as well since you have an excel dataset to merge in.

由于您有一个要合并的 excel 数据集，因此将所有依赖项保持在一起还有很多话要说。

The biggest downside with vba is that not being compiled if you give it to someone else they might be tempted to tinker. :)

vba 的最大缺点是，如果您将其提供给其他人，他们可能会尝试修补，则不会被编译。:)

All things considered I'd suggest a VBA solution as it would be the most appropriate tool for this job. There aren't any 'showstopping issues' which would dramatically swing it either way though.

考虑到所有因素，我建议使用 VBA 解决方案，因为它是最适合这项工作的工具。尽管如此，没有任何“令人瞩目的问题”会大大改变它。