一种自动进行“编译"的方法. MS Office的VBA代码的功能 [英] A Way to Automate the "Compile" Function of MS Office's VBA Code
问题描述
通常，当我对VBA文件进行更改时，我希望对其进行编译以确保所做的更改不会破坏任何内容:
Typically when I make a change to a VBA file I like to compile it to ensure my changes didn't break anything:
但是在具有不同版本Office的不同计算机上进行编译会导致不同的结果，有时会编译，有时却不会...... 情况可能会有所不同(尽管这是最常见的问题，但不仅仅是引用).
But compiling on different machines with different versions of the office will result in different results, sometimes it will compile, sometimes not... Things like this can happen, or maybe this. Turns out in each version of excel all sorts of things can be different (not just references though that is the most common issue).
我将如何自动执行VBA代码的编译?我希望能够在Excel，PowerPoint和Word等多种产品中做到这一点，我希望能够在2010、2013、2016等版本中分别编译为32位和64位.
How would I automate the compiling of my VBA code? I would like to be able to do this in multiple products such as Excel, PowerPoint, and Word, I would like to be able compile as 32 and 64 bit, with 2010, 2013, 2016, etc...
是的，这仍然是一个主要的痛点，现在我有一系列的手动测试人员(人员)根据我们的发布时间表审查了各种不同配置上的所有相关文件，有别忘了有更好的方法.
Yes this is still a major pain point, right now I have a series of manual testers (people) review all relevant files on various different configurations based on our release schedule, there has got to be a better way to do this.
我希望使用某种PowerShell脚本/.Net项目( C#，VB.NET)，完成此操作，即使我必须使用一堆Office版本安装服务器，我认为这也很值得.
What I would prefer is some sort of PowerShell script/.Net project(C#, VB.NET) that would accomplish this, even if I had to setup a server with a bunch of versions of office, I think it would be well worth the investment.
我想，在最坏的情况下，您可以将所有这些不同版本安装到各种VM上，然后使用 AutoHotKey 加上某种PowerShell脚本进行编译.宏在Macro的乐趣之上...
I'd imagine, worst case you could install all of these different versions onto various VM's, then use AutoHotKey plus some sort of PowerShell script to compile them. Macro's on top of Macro's fun...
这次冒险之旅向我突显了VBA开发有多么困难.我真的是第一个在不同版本的excel之间出现问题的人吗?要求能够在不同版本下进行编译是否合理?
This odyssey just underlines to me how difficult VBA development is. Am I really the first person to have issues between different versions of excel? Is it unreasonable to ask to be able to compile under different versions?
MS 可以喜欢它，但对我来说，几乎就像这种语言并没有真正的长期计划仅支持旧版代码 >.它只是继续存在而没有任何重大的未来官方迭代或考虑，因为它与核心 开发 挑战，例如此类.
MS may love it, but to me it's almost like this language doesn't really have a long term plan past just supporting legacy code. It just continues to exist without any major official future iterations or considerations as it relates to core development challenges such as this one.
推荐答案
您需要转到Excel->文件->选项->信任中心->信任中心设置，然后检查选项Trust access to the VBA project object model(如果不这样做) t选中以下代码将引发运行时错误1004，因此无法信任对Visual Basic项目的编程访问.)
You need to go to Excel -> File -> Options -> Trust Center-> Trust Center settings and check the option Trust access to the VBA project object model (if you don't check it the below code will raise the run-time error 1004 programmatic access to visual basic project is not trusted).
Sub Compiler()
Dim objVBECommandBar As Object
Set objVBECommandBar = Application.VBE.CommandBars
Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578)
compileMe.Execute
End Sub
在C语言上，不要忘记将excel软件包添加到名称空间.
on C something like that, Don't forget to add excel packages to namespace.
void Main()
{
var oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
try{
var WB = oExcelApp.ActiveWorkbook;
var WS = (Worksheet)WB.ActiveSheet;
//((string)((Range)WS.Cells[1,1]).Value).Dump("Cell Value"); //cel A1 val
oExcelApp.Run("Compiler").Dump("macro");
}
finally{
if(oExcelApp != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
oExcelApp = null;
}
}
