是否可以在 Visual Studio 中编写 Excel VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23173694/
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
Is it possible to write Excel VBA Code in Visual Studio
提问by Dblock247
Is there a way to write VBA Code in Visual Studio. If not is there any other alternatives?
有没有办法在 Visual Studio 中编写 VBA 代码。如果没有，还有其他选择吗？
采纳答案by Ade Miller
VBA code for Excel can only be written inside Excel using the VBA IDE. VBA projects are stored as part of the Excel file and cannot be loaded into Visual Studio.
Excel 的 VBA 代码只能使用 VBA IDE 在 Excel 中编写。VBA 项目作为 Excel 文件的一部分存储，无法加载到 Visual Studio 中。
However, you can write VSTO (Visual Studio Tools for Office) managed add-ins for Excel using Visual Studio. The following MSDN page covers both developing with VBA and VSTO.
但是，您可以使用 Visual Studio 为 Excel 编写 VSTO（Visual Studio Tools for Office）托管加载项。以下 MSDN 页面涵盖了使用 VBA 和 VSTO 进行开发。
You could also use the interop features of VBA to consume a (COM) object written in Visual Studio from your VBA code.
您还可以使用 VBA 的互操作功能从 VBA 代码使用在 Visual Studio 中编写的 (COM) 对象。
回答by GollyJer
The best you can do is bend the office Visual Basic Editor (VBE) tool to your liking. If you stay in it's native environment you get the full power of error detection, Intellisense, live code running, etc.
您能做的最好的事情就是根据自己的喜好使用 Office Visual Basic Editor (VBE) 工具。如果您留在它的本机环境中，您将获得错误检测、智能感知、实时代码运行等的全部功能。
My tips...
我的建议...
In the VBE go to Tools > Options > Editor tab.
Turn off 'Auto Syntax Check'. You still get code highlighted errors but no annoying popups.Go to the Editor Format tab and change the Font to
Consolas (Western), Size11.For code indenting install the awesome, free, Code Manager. It adds some sick keyboard shortcuts.
Make the Edit toolbar easily accessiblefor code commenting/uncommenting.
Use Rubberduckto add unit testing, source control, code inspections and refactoring functionality.
在 VBE 中，转到工具 > 选项 > 编辑器选项卡。
关闭“自动语法检查”。您仍然会收到代码突出显示的错误，但没有烦人的弹出窗口。转到 Editor Format 选项卡并将 Font 更改为
Consolas (Western), Size11。对于代码缩进，请安装很棒的免费代码管理器。它添加了一些病态的键盘快捷键。
使编辑工具栏易于访问以进行代码注释/取消注释。
使用Rubberduck添加单元测试、源代码控制、代码检查和重构功能。
With those simple changes you end up with a half way decent, useful, and keyboard friendly environment to write your visually appealing code. :-D
通过这些简单的更改，您最终会获得一个体面、有用且键盘友好的环境来编写具有视觉吸引力的代码。:-D
回答by nora
I've been looking for an answer to this question myself.
我自己一直在寻找这个问题的答案。
Best I've found myself is the option of exporting a Module ect from Excel with the code you've already written (or blank) and load that up in the Visual Studio Environment.
我发现自己最好的是使用您已经编写（或空白）的代码从 Excel 导出模块等，并将其加载到 Visual Studio 环境中。
It doesn't offer much, but the highlighted text and auto indenting is nice and makes it much easier to read compared to the standard VBA environment.
它提供的内容不多，但突出显示的文本和自动缩进很好，与标准 VBA 环境相比，它更易于阅读。
Then once you're done just import it back into Excel.
然后，一旦完成，只需将其导入回 Excel。
回答by Mark Zhukovsky
You can certainly add and edit a VBA file (.vb) in your Visual Studio solution, but the intellisense will be worthless/screwed up. This extension for VScodewould probably provide a much better experience: https://marketplace.visualstudio.com/items?itemName=spences10.VBA
您当然可以在 Visual Studio 解决方案中添加和编辑 VBA 文件 (.vb)，但智能感知将毫无价值/被搞砸。这个VScode扩展可能会提供更好的体验：https: //marketplace.visualstudio.com/items?itemName=spences10.VBA
If your goal is have your VBA code exposed to source control so you can track changes, then it's still worth it to include in your Visual Studio solution, but just store that VBA code in a plain text file and then use the Excel interop to load it into the appropriate module within the excel workbook, e.g.:
如果您的目标是将 VBA 代码公开给源代码管理以便您可以跟踪更改，那么将其包含在您的 Visual Studio 解决方案中仍然是值得的，但只需将该 VBA 代码存储在纯文本文件中，然后使用 Excel 互操作来加载将其放入 excel 工作簿中的相应模块中，例如：
xlWorkbook.VBProject.VBComponents["ThisWorkbook"].CodeModule.AddFromFile(@"C:\PathToYour\VBAcode.txt");
And there are other methods to delete/replace code lines, etc....
还有其他方法可以删除/替换代码行等......
