在 Excel 2007 中编写 VBA 以在 Excel 2003 中使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/198247/
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
Writing VBA in Excel 2007 for use in Excel 2003
提问by Joel Coehoorn
Where I'm at the developers have been updated to Excel 2007, but most of the users haven't. I'm building a spreadsheet template (*.xlt) for a user that's gonna need some vba code included, and I'm wondering what issues I'm likely to run into building this in 2007 instead of 2003? I don't have access to a machine with Excel 2003 for testing, and I'm worried this particular project is headed for disaster.
我所在的开发人员已更新到 Excel 2007，但大多数用户尚未更新。我正在为需要包含一些 vba 代码的用户构建一个电子表格模板 (*.xlt)，我想知道在 2007 年而不是 2003 年构建它时可能会遇到什么问题？我无法使用装有 Excel 2003 的机器进行测试，我担心这个特定项目会走向灾难。
回答by billb
The VBA language hasn't changed, but there are additional objects in Office 2007 that are not in Office 2003. Naturally, this will cause a runtime error when you try to access these items in a 2003 environment. What's stopping you from setting about a virtual machine with Excel 2003 to develop under?
VBA 语言没有改变，但 Office 2007 中有 Office 2003 中没有的其他对象。当然，当您尝试在 2003 环境中访问这些项目时，这将导致运行时错误。是什么阻止您设置使用 Excel 2003 进行开发的虚拟机？
回答by Joel Coehoorn
Rather than depending on a probably incomplete list of objects and methods which have been added to Excel 2007's object library, the best (mmost relliable) practice is to always develop in the oldest version of Excel likely to be used to run the code.
最好（最可靠）的做法是始终在可能用于运行代码的最旧版本的 Excel 中进行开发，而不是依赖于可能不完整的对象和方法列表，这些对象和方法已添加到 Excel 2007 的对象库中。
回答by Chris Spicer
One difference I discovered is that a subroutine must have a different signature to be called from a menu (in Excel 2003) than when called from the ribbon (in Excel 2007). Whatsmore, Excel 2003 doesn't recognise IRibbonControl and throws compile errors.
我发现的一个区别是，从菜单（在 Excel 2003 中）调用的子例程必须具有与从功能区调用（在 Excel 2007 中）不同的签名。此外，Excel 2003 无法识别 IRibbonControl 并引发编译错误。
To work toward cross-version compatability, I use a Conditional Compilation Argument and then check that in pre-processor macros.
为了实现跨版本兼容性，我使用了条件编译参数，然后在预处理器宏中进行检查。
e.g.
例如
#If USINGRIBBON Then
Public Sub CallFromRibbon(control As IRibbonControl)
#Else
Public Sub CallFromRibbon()
#End If
' Code here
End Sub
This does mean that you have to save one version of your add-in with the USINGRIBBON flag set to false (for Excel2003) and another with the USINGRIBBON flag set to true (for Excel2007), but this is far easier than maintaining two completely separate codebases.
这确实意味着您必须保存一个将 USINGRIBBON 标志设置为 false（对于 Excel2003）和另一个将 USINGRIBBON 标志设置为 true（对于 Excel2007）的加载项版本，但这比维护两个完全独立的版本要容易得多代码库。
回答by SpyJournal
billb2112 is right. There are numerous changes to Excel 2007 over excel 2003 that are not backward compatible. While the language might not have changed, the objects have been updated. Some have had additional properties added, some work differently and some functions in Excel have changed.
billb2112 是对的。与 Excel 2003 相比，Excel 2007 有许多不向后兼容的更改。虽然语言可能没有改变，但对象已经更新。有些添加了额外的属性，有些工作方式不同，Excel 中的某些功能也发生了变化。
You need to be very careful that what you code works in Excel 2003. I would suggest as billb2112 said that you get a virtual machine to not only test in but also to code in. I do all my Excel development for clients who only have 2003 in a 2003 machine. Note that if users have Excel 2002 or 2000 there are even more differences as you go back and you will simply get runtime errors on any code that these older versions don't support.
您需要非常小心，您的代码在 Excel 2003 中是否有效。我建议正如 billb2112 所说，您获得一个虚拟机，不仅可以进行测试，还可以进行编码。我为只有 2003 的客户进行所有 Excel 开发在 2003 年的机器中。请注意，如果用户使用 Excel 2002 或 2000，则返回时会有更多差异，并且您只会在这些旧版本不支持的任何代码上遇到运行时错误。
update unfortunately jeffs answer is not quite correct. while yes the vba language hasn't been updated it is not the same in 2007 as in 2003. as in 2003 its not the same as in 2002 etc. what has happened is extra and additional functions and arguments for functions have been added. for example there are more options in the FIND function in Excel in 2003 than in 2002. thus if you record a macro (the best way to find these problems) in 2003 and then run it in 2002 you will have run time errors relating to the new arguments that simply do not work in the 2002 VBA editor. following the same process for functions that have changed in excel 2007 and then going back to 2003 to test will help you locate these problems. some examples include conditional formatting, colours (themes) and numerous new spreadsheet functions. jon peltier has the best advice from that regard - develop in the oldest possible version that the client /user will use. Virtual PC 2007 is free to download and install. you will just need a licensed copy of XP/Vista and office to install to run in it.
不幸的是，更新 jeffs 的答案并不完全正确。虽然是的 vba 语言尚未更新，但 2007 年与 2003 年不同。2003 年与 2002 年不同，等等。发生的事情是额外的，并添加了额外的函数和函数参数。例如，2003 年 Excel 中的 FIND 函数中的选项比 2002 年多。因此，如果您在 2003 年记录一个宏（找到这些问题的最佳方法），然后在 2002 年运行它，您将遇到与在 2002 VBA 编辑器中根本不起作用的新参数。对 excel 2007 中已更改的函数执行相同的过程，然后返回 2003 进行测试将帮助您定位这些问题。一些示例包括条件格式、颜色（主题）和许多新的电子表格功能。jon peltier 在这方面有最好的建议 - 开发客户端/用户将使用的最旧版本。Virtual PC 2007 可免费下载和安装。您只需要获得许可的 XP/Vista 和 office 副本即可安装以在其中运行。
回答by SpyJournal
i used to develop a lot of macros under 2003, what a POTA,, things like Find, Dir and some others are not available or something change. therefore some erros can be expected, i used to count from 65000 row to the first non empty row to count the rows to work on.... now more rows means more work to do
我曾经在 2003 下开发了很多宏，什么是 POTA，诸如 Find、Dir 和其他一些不可用或更改的东西。因此可以预期一些错误，我曾经从 65000 行计数到第一个非空行来计算要处理的行数......现在更多的行意味着更多的工作要做
