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.
谢谢你的建议。
3 个解决方案
#1
13
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.
标准代码模块,也简称代码模块或只是模块,是放置大部分VBA代码的地方。您的基本宏和自定义函数(用户定义函数)应该在这些模块中。对于新手程序员,您的所有代码都将使用标准模块。除了基本过程之外,代码模块还应包含对外部函数(Windows API或其他DLL)的任何Declare语句,以及使用Type语句定义的自定义数据结构。
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.
您的工作簿的VBA项目可以包含任意数量的标准代码模块。这使您可以轻松地将程序拆分为不同的模块,以便组织和维护。例如,您可以将所有数据库过程放在名为DataBase的模块中,将所有数学过程放在另一个名为Math的模块中。只要未使用Private关键字声明过程,或者模块未标记为私有,您就可以从任何其他模块调用任何模块中的任何过程,而无需执行任何特殊操作。
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.
工作簿和图纸模块是直接绑定到Workbook对象和每个Sheet对象的特殊模块。工作簿的模块称为ThisWorkbook,每个Sheet模块与其所属的工作表具有相同的名称。这些模块应包含对象的事件过程,这就是全部。如果将事件过程放在标准代码模块中,Excel将无法找到它们,因此不会执行它们。如果您将普通过程放在工作簿或工作表模块中,则在没有完全限定引用的情况下,您将无法调用它们。
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.
用户表单模块是UserForm对象的一部分,包含该表单上控件的事件过程。例如,UserForm上的命令按钮的Click事件存储在该UserForm的代码模块中。与工作簿和工作表模块一样,您应该只为此模块中的UserForm控件添加事件过程。
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.
类模块用于创建新对象。这里不讨论类模块,只是说类模块用于处理应用程序事件过程。
#2
1
Try : (i use this code)
尝试:(我使用此代码)
With Application
.EnableEvents = True 'needed
.OnTime EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="displaysheets", Schedule:=True
End With
#3
0
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
#1
13
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.
标准代码模块,也简称代码模块或只是模块,是放置大部分VBA代码的地方。您的基本宏和自定义函数(用户定义函数)应该在这些模块中。对于新手程序员,您的所有代码都将使用标准模块。除了基本过程之外,代码模块还应包含对外部函数(Windows API或其他DLL)的任何Declare语句,以及使用Type语句定义的自定义数据结构。
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.
您的工作簿的VBA项目可以包含任意数量的标准代码模块。这使您可以轻松地将程序拆分为不同的模块,以便组织和维护。例如,您可以将所有数据库过程放在名为DataBase的模块中,将所有数学过程放在另一个名为Math的模块中。只要未使用Private关键字声明过程,或者模块未标记为私有,您就可以从任何其他模块调用任何模块中的任何过程,而无需执行任何特殊操作。
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.
工作簿和图纸模块是直接绑定到Workbook对象和每个Sheet对象的特殊模块。工作簿的模块称为ThisWorkbook,每个Sheet模块与其所属的工作表具有相同的名称。这些模块应包含对象的事件过程,这就是全部。如果将事件过程放在标准代码模块中,Excel将无法找到它们,因此不会执行它们。如果您将普通过程放在工作簿或工作表模块中,则在没有完全限定引用的情况下,您将无法调用它们。
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.
用户表单模块是UserForm对象的一部分,包含该表单上控件的事件过程。例如,UserForm上的命令按钮的Click事件存储在该UserForm的代码模块中。与工作簿和工作表模块一样,您应该只为此模块中的UserForm控件添加事件过程。
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.
类模块用于创建新对象。这里不讨论类模块,只是说类模块用于处理应用程序事件过程。
#2
1
Try : (i use this code)
尝试:(我使用此代码)
With Application
.EnableEvents = True 'needed
.OnTime EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="displaysheets", Schedule:=True
End With
#3
0
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