vba 宏失败，“宏在此可能不可用...”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22797779/
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
Macro Fails, 'The macro may not be available in this...'
提问by Smith78
Hello and thank you in advance for your assistance.
您好，提前感谢您的帮助。
I have some code that I admittedly borrowed from a site. It changes the sheet that is being displayed every X seconds. In my case 3 seconds. When I run it it will change to the next sheet one time and then error out after the 3 seconds.
我有一些代码，我承认是从一个网站借来的。它每 X 秒更改一次显示的工作表。在我的情况下 3 秒。当我运行它时，它将一次更改为下一张纸，然后在 3 秒后出错。
The error I receive is "Cannot run the macro "C:\users\BenjaminSmith\Desktop\Book1.xlsm'!displaysheets'. The Macro may not be available in this workbook or all macros may be disabled."
我收到的错误是“无法运行宏“C:\users\BenjaminSmith\Desktop\Book1.xlsm'！displaysheets'。此工作簿中的宏可能不可用，或者可能禁用了所有宏。”
Here is the code for my Macro
这是我的宏的代码
Sub displaysheets()
ShtNum = ActiveSheet.Index
ShtNum = ShtNum + 1
If ShtNum > Sheets.Count Then
ShtNum = 1
End If
Sheets(ShtNum).Activate
Application.OnTime Now + TimeValue("00:00:03"), "displaysheets"
End Sub
If I remove the line
如果我删除该行
Application.OnTime Now + TimeValue("00:00:03"), "displaysheets"
I can run the macro over and over and there are no issues. Other than the fact it doesn't continue on its own...
我可以一遍又一遍地运行宏，没有问题。除了它不会自行继续的事实之外......
The spreadsheet is an XLSM. MS VBA is 7.0. Excel is 2010.
该电子表格是一个 XLSM。MS VBA 是 7.0。Excel是2010年的。
I am thinking maybe the issue is because the code is recursive?
我在想问题可能是因为代码是递归的？
Thanks for your suggestions.
感谢您的建议。
回答by Siddharth Rout
Further from the comments...
进一步从评论...
The code didn't work because you didn't paste the code in a module. This is a very common mistake among new programmers. In such a case, Excel is unable to find the code as it searches the module.
代码不起作用，因为您没有将代码粘贴到模块中。这是新程序员中非常常见的错误。在这种情况下，Excel 在搜索模块时无法找到代码。
@Siddharth Rout I had the code in 'ThisWorkbook' I inserted a module 'Module1' and moved the code there and everything works as expected. What is the difference with these two places?
@Siddharth Rout 我在“ThisWorkbook”中有代码我插入了一个模块“Module1”并将代码移到那里，一切都按预期工作。这两个地方有什么区别？
I would recommend going through Chip Pearson's link HERE
我建议在此处浏览 Chip Pearson 的链接
Extract from the link if the link ever rots.
如果链接腐烂，则从链接中提取。
Standard Code Modules, also called simply Code Modules or just Modules, are where you put most of your VBA code. Your basic macros and your custom function (User Defined Functions) should be in these modules. For the novice programmer, all your code will be in standard modules. In addition to your basic procedures, the code modules should contain any Declare statements to external functions (Windows APIs or other DLLs), and custom Data Structures defined with the Type statement.
Your workbook's VBA Project can contain as many standard code modules as you want. This makes it easy to split your procedure into different modules for organization and ease of maintenance. For example, you could put all your database procedures in a module named DataBase, and all your mathematical procedures in another module called Math. As long as a procedure isn't declared with the Private keyword, or the module isn't marked as private, you can call any procedure in any module from any other module without doing anything special.
Workbook And Sheet Modules are special modules tied directly to the Workbook object and to each Sheet object. The module for the workbook is called ThisWorkbook, and each Sheet module has the same name as the sheet that it is part of. These modules should contain the event procedures for the object, and that's all. If you put the event procedures in a standard code module, Excel won't find them, so they won't be executed. And if you put ordinary procedures in a workbook or sheet module, you won't be able to call them without fully qualifying the reference.
User Form Modules are part of the UserForm object, and contain the event procedures for the controls on that form. For example, the Click event for a command button on a UserForm is stored in that UserForm's code module. Like workbook and sheet modules, you should put only event procedures for the UserForm controls in this module.
Class Modules are used to create new objects. Class modules aren't discussed here, except to say that a class module is used to handle Application Event Procedures.
标准代码模块，也简称为代码模块或模块，是您放置大部分 VBA 代码的地方。您的基本宏和自定义函数（用户定义函数）应该在这些模块中。对于新手程序员，您的所有代码都将在标准模块中。除了基本过程之外，代码模块还应包含外部函数（Windows API 或其他 DLL）的任何 Declare 语句，以及使用 Type 语句定义的自定义数据结构。
您工作簿的 VBA 项目可以包含任意数量的标准代码模块。这使您可以轻松地将您的程序分成不同的模块，以便于组织和维护。例如，您可以将所有数据库过程放在名为 DataBase 的模块中，并将所有数学过程放在另一个名为 Math 的模块中。只要过程未使用 Private 关键字声明，或者模块未标记为私有，您就可以从任何其他模块调用任何模块中的任何过程，而无需执行任何特殊操作。
工作簿和工作表模块是直接绑定到工作簿对象和每个工作表对象的特殊模块。工作簿的模块称为 ThisWorkbook，每个工作表模块都与它所属的工作表具有相同的名称。这些模块应该包含对象的事件过程，仅此而已。如果将事件过程放在标准代码模块中，Excel 将找不到它们，因此它们不会被执行。如果您将普通过程放在工作簿或工作表模块中，您将无法在不完全限定引用的情况下调用它们。
用户窗体模块是用户窗体对象的一部分，包含该窗体上控件的事件过程。例如，用户窗体上命令按钮的 Click 事件存储在该用户窗体的代码模块中。与工作簿和工作表模块一样，您应该只在此模块中放置用户窗体控件的事件过程。
类模块用于创建新对象。这里不讨论类模块，只是说类模块用于处理应用程序事件过程。
回答by Patrick Lepelletier
Try : (i use this code)
尝试：（我使用此代码）
With Application
.EnableEvents = True 'needed
.OnTime EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="displaysheets", Schedule:=True
End With
回答by Roger Barreto
Try to put your timer in a global variable and add it each time you run the function, also configure OnTime to be schedulable
尝试将您的计时器放在一个全局变量中并在每次运行该函数时添加它，并将 OnTime 配置为可调度
Global tmrTimer1
Sub displaysheets()
tmrTimer1 = Now + TimeValue("00:00:03")
'Enable the schedule
Application.OnTime tmrTimer1 , "displaysheets", , True
End Sub
