Excel VBA 编译错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18116285/
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
Excel VBA Compile Error
提问by SamoanProgrammer
We have an excel spread sheet which we use and it works for most machines but bombs out with 'Compile Error in Hidden Module - General' on others, and the reason appears to be due to missing References.
我们有一个我们使用的 excel 电子表格，它适用于大多数机器，但在其他机器上用“隐藏模块中的编译错误 - 常规”轰炸，原因似乎是由于缺少引用。
We check that Macros is enabled but still doesn't help.
我们检查宏是否已启用，但仍然没有帮助。
Since we protect this excel spread sheet with a password, we don't want to be giving this password out to our users to check the References, and wondered if anyone had any idea how I can add VBA code to check whether the References required for the excel spread sheet is there and if not then bring up a message box to advise the user.
由于我们使用密码保护此 excel 电子表格，因此我们不想将此密码提供给我们的用户来检查参考资料，并且想知道是否有人知道我如何添加 VBA 代码来检查参考资料是否需要excel 电子表格在那里，如果没有，则显示一个消息框以告知用户。
The References we use are as follows:
我们使用的参考资料如下：
- Visual Basic For Applications
- Microsoft Excel 11.0 Object Library
- Microsoft Forms 2.0 Object Library
- Microsoft Windows Common Controls 5.0 (SP2)
- Visual Basic 应用程序
- Microsoft Excel 11.0 对象库
- Microsoft Forms 2.0 对象库
- Microsoft Windows 通用控件 5.0 (SP2)
Alternatively, if anyone has any other suggestions on how to go about this problem, that would be great.
或者，如果有人对如何解决这个问题有任何其他建议，那就太好了。
采纳答案by Dick Kusleika
The only reference you have listed that could possibly be missing is the common controls. The rest are default in every version of Excel. The Forms one is only if you have a userform or explicitly set it, but that's not your problem. Common Controls is your problem. It doesn't ship with Office anymore. If you have Visual Studio or VB6 you probably have it. Or an old version of Office like XP Developer Edition.
您列出的唯一可能缺少的参考是通用控件。其余的在每个版本的 Excel 中都是默认的。只有当您有一个用户表单或明确设置它时，表单才会出现，但这不是您的问题。通用控件是您的问题。它不再随 Office 一起提供。如果你有 Visual Studio 或 VB6，你可能已经有了。或者像 XP Developer Edition 这样的旧版 Office。
Anyway, you can check for the existence of the OCX file in the System folder. I don't think it's required to be in that folder, but I've never seen it anywhere else.
无论如何，您可以检查系统文件夹中是否存在 OCX 文件。我认为不需要在该文件夹中，但我从未在其他任何地方见过它。
It's been quite a while since I've seen a reference to 5.0, so I included how to find 6.0 in the code below. Check to make sure you know what version you're using.
自从我看到对 5.0 的引用已经有一段时间了，所以我在下面的代码中包含了如何找到 6.0。检查以确保您知道您使用的是哪个版本。
In a standard module:
在标准模块中：
Private Declare Function GetSystemDirectory Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
Public Function HasCommonControl() As Boolean
Dim sFolder As String
Dim lReturn As Long
Const lSIZE As Long = 255
Const sDLLNAME As String = "COMCTL32.OCX" 'For windows common control 5.0
'Const sDLLNAME As String = "MSCOMCTL.OCX" 'For windows common control 6.0
sFolder = Space(lSIZE)
lReturn = GetSystemDirectory(sFolder, lSIZE)
sFolder = Left$(sFolder, lReturn)
HasCommonControl = Len(Dir(sFolder & Application.PathSeparator & sDLLNAME)) > 0
End Function
Having said all that, why are you using common controls? If it's for a treeview on a userform, then check out this all-vba treeview
说了这么多，为什么要使用通用控件？如果它用于用户窗体上的树视图，请查看此 all-vba 树视图
http://www.jkp-ads.com/articles/treeview.asp
http://www.jkp-ads.com/articles/treeview.asp
Since jkp wrote that, I haven't used common controls. So few normal-people PCs have it installed that it's just a pain.
自从 jkp 写了那个，我还没有使用通用控件。很少有普通人的 PC 安装它，这只是一种痛苦。
回答by Steve Rindsberg
Depending on a reference to a specific Excel or other component version is one possible problem. Switching to late binding would solve that problem, so long as you're careful not to use any commands/objects/methods/properties that are supported in one version and not another.
依赖于对特定 Excel 或其他组件版本的引用是一个可能的问题。切换到后期绑定可以解决这个问题，只要您小心不要使用一个版本而不是另一个版本支持的任何命令/对象/方法/属性。
Following up on RowanC's link (good choice), you can add a reference to Excel, for example and write your code like so:
跟进 RowanC 的链接（不错的选择），您可以添加对 Excel 的引用，例如并像这样编写代码：
Dim xlWks as Excel.Worksheet
'Dim xlWks as Object
Once everything's debugged, remove the Excel reference and change the declarations to:
调试完所有内容后，删除 Excel 引用并将声明更改为：
'Dim xlWks as Excel.Worksheet
Dim xlWks as Object
That gives you the benefit of intellisense while coding/debugging but removes the dependency on a specific version of Excel later.
这在编码/调试时为您提供了智能感知的好处，但稍后会删除对特定版本的 Excel 的依赖。
Might be mistaken, but IIRC the Common Controls component is part of Visual Basic, not Office, so unless you distribute it and register it along with your app, it might not be present on some systems.
可能是错误的，但是 IIRC 公共控件组件是 Visual Basic 的一部分，而不是 Office，因此除非您分发它并与您的应用程序一起注册它，否则它可能不会出现在某些系统上。
回答by RowanC
To try late binding, which doesn't need the references setup (although it may have a performance hit, and it will mean that autocomplete doesn't work in your code) instead of calling excel in the following way:
要尝试不需要引用设置的后期绑定（尽管它可能会影响性能，并且这意味着自动完成在您的代码中不起作用）而不是通过以下方式调用 excel：
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
try calling it this way, and drop the reference to the excel object model.
尝试以这种方式调用它，并删除对 excel 对象模型的引用。
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
