宏失败，'这个宏可能不可用...' [英] Macro Fails, 'The macro may not be available in this...'

问题描述

您好，谢谢您的协助。

我有一些我承认从一个网站借来的代码。它会更改每X秒显示的工作表。在我的情况下3秒。当我运行它会改变到下一张纸一次，然后在3秒后出错。

我收到的错误是无法运行宏C： \users\BenjaminSmith\Desktop\Book1.xlsm '！displaysheets'。宏可能在此工作簿中可用，或者所有宏可能被禁用。

以下是我的宏的代码

 子显示表（）
 
 ShtNum = ActiveSheet.Index 
 
 ShtNum = ShtNum + 1 
如果ShtNum> ; Sheets.Count然后
 ShtNum = 1 
如果
表单（ShtNum）.Activate 
 Application.OnTime Now + TimeValue（00:00:03），显示表
 
 
 End Sub 
  

如果我删除行

  Application.OnTime Now + TimeValue（00:00:03），displaysheets
  

我可以一遍一遍地运行宏，没有问题，除了它不会自动继续的事实上

电子表格是XLSM
MS VBA是7.0。
Excel是2010年。

感谢您的建议。

解决方案

Furt她的评论...

代码不起作用，因为您没有将代码粘贴到模块中。这是新程序员中非常常见的错误。在这种情况下，Excel在搜索模块时无法找到代码。

@Siddharth Rout我在ThisWorkbook中有代码我插入了一个模块Module1，并将代码移到那里，一切都按预期工作。这两个地方有什么区别？我将建议您通过Chip Pearson的链接 HERE

如果链接发生故障，则从链接中提取

标准代码模块，也称为简单的代码模块或只是
模块，是您放置大部分VBA代码的地方。您的基本宏
和您的自定义函数（用户定义函数）应位于这些
模块中。对于新手程序员，所有的代码都将在标准的
模块中。除了基本的程序之外，代码模块
还应该包含对外部函数（Windows
API或其他DLL）的任何Declare语句以及使用Type
语句定义的自定义数据结构。

您的工作簿的VBA项目可以包含所需的许多标准代码模块
。这样可以轻松地将程序分成
个不同的模块，以实现组织和易于维护。对于
示例，您可以将所有数据库过程放在名为
DataBase的模块中，并将所有数学过程放在另一个模块
中，称为Math。只要没有使用Private
关键字声明一个过程，或者模块没有被标记为私有的，那么可以在任何其他模块的任何模块中调用任何
过程，而无需执行任何
特别。

工作簿和工作表模块是直接绑定到
Workbook对象和每个Sheet对象的特殊模块。
工作簿的模块称为ThisWorkbook，每个Sheet模块具有与其一部分的工作表相同的
名称。这些模块应该包含
对象的事件过程，这就是所有。如果将
事件过程放在标准代码模块中，Excel将不会找到它们，因此
将不会被执行。如果您将普通程序放在
工作簿或工作表模块中，则您将无法在没有完全符合条件的情况下调用它们。

用户表单模块是UserForm对象的一部分，并包含该表单上的控件的
事件过程。例如，用户窗体上的命令按钮的
Click事件存储在
UserForm的代码模块中。像工作簿和工作表模块一样，您应该
仅为此模块中的UserForm控件放置事件过程。

类模块用于创建新对象。类模块不是
，除了说一个类模块用于处理
应用程序事件过程。

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.

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."

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.

I am thinking maybe the issue is because the code is recursive?

Thanks for your suggestions.

解决方案

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.

@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?

I would recommend going through Chip Pearson's link HERE

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.

这篇关于宏失败，'这个宏可能不可用...'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！