vba 无法运行宏...宏在此工作簿中可能不可用

Cannot run the macro... the macro may not be available in this workbook

提问by Ege Ozlem

I am trying to call a sub on a different worksheet but I got a run time error message.

我试图在不同的工作表上调用子程序，但收到运行时错误消息。

Specifically, I have two worksheets and multiple VBA sub s in those worksheets. In one of the VBA Project (say workbook1.xlsm), I have the following code:

具体来说，我在这些工作表中有两个工作表和多个 VBA sub 。在 VBA 项目之一（比如 workbook1.xlsm）中，我有以下代码：

Sub AnalysisTableMacro()
Workbooks("Python solution macro.xlsm").Activate
Application.Run "Python solution macro.xlsm!.PreparetheTables"
End Sub

But I got the following error. The macros on both worksheets are enabled. In both worksheets, the subs are in Module1.

但我收到以下错误。两个工作表上的宏都已启用。在这两个工作表中，subs 都在 Module1 中。

Cannot run the macro 'Workbook.xlsm!PrepareTheTables'. The macro may not be available in >this workbook or all macros may be disabled.

无法运行宏“Workbook.xlsm!PrepareTheTables”。宏可能在>此工作簿中不可用，或者可能禁用了所有宏。

I also tried Application.Run "Python solution macro.xlsm!Module1.PreparetheTables"but did not work.

我也尝试过，Application.Run "Python solution macro.xlsm!Module1.PreparetheTables"但没有奏效。

回答by ChipsLetten

If you have a space in the name of the workbook you must use single quotes (') around the file name. I have also removed the full stop.

如果工作簿名称中有空格，则必须在文件名周围使用单引号 (')。我也删除了句号。

Application.Run "'Python solution macro.xlsm'!PreparetheTables"

回答by Vignesh

Had the same issue and I 'Compiled VBA Project' which identified an error. After correction and compiling, the macros worked.

遇到了同样的问题，我“编译了 VBA 项目”发现了一个错误。更正和编译后，宏工作。

回答by Ved Rai

Per Microsoft's KB, try allowing programmatic access to the Visual Basic project:

根据Microsoft 的 KB，尝试允许以编程方式访问 Visual Basic 项目：

1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Trust Center.
3. Click Trust Center Settings.
4. Click Macro Settings.
5. Click to select the Trust access to the VBA project object model check box.
6. Click OK to close the Excel Options dialog box.
7. You may need to close and re-open excel.

1. 单击 Microsoft Office 按钮，然后单击 Excel 选项。
2. 单击信任中心。
3. 单击信任中心设置。
4. 单击宏设置。
5. 单击以选中对 VBA 项目对象模型的信任访问复选框。
6. 单击“确定”关闭“Excel 选项”对话框。
7. 您可能需要关闭并重新打开 excel。

回答by Heap of Pinto Beans

You also run into this issue when you are creating routine in a class module.

在类模块中创建例程时也会遇到此问题。

When you try to run the code externally, you get this error.
You can't assign macro to button to a member of a class module either.

当您尝试在外部运行代码时，您会收到此错误。
您也不能将宏分配给按钮给类模块的成员。

If you try to run from within the code by pressing green play button you will also see the same error.

如果您尝试通过按绿色播放按钮从代码中运行，您也会看到相同的错误。

Either move the routine in to a regular module or create a new routine in a regular module that calls the class member.

要么将例程移到常规模块中，要么在常规模块中创建一个调用类成员的新例程。

回答by Mor Sagmon

In my case this error came up when the Sub name was identical to the Module name.

在我的情况下，当子名称与模块名称相同时出现此错误。

回答by Peter Liapin

In my case the error happened when I placed my macro (public sub) into a ThisWorkbooksection of the file expecting it will make it visible for Application.Runfunction. This was not the case and I got that error you mentioned.

在我的情况下，当我将我的宏（公共子）放入ThisWorkbook文件的一部分时发生了错误，期望它将使其对Application.Run函数可见。事实并非如此，我得到了你提到的那个错误。

I moved my macro into a separate Module and it resolved the problem.

我将我的宏移到一个单独的模块中，它解决了问题。

回答by JovialJohn

I had a problem with this error as well, turned out the the filename was causing the problem.

我也遇到了这个错误的问题，结果是文件名导致了问题。

I was calling it like this: Application.Run "'" & strPath & strFName & "'!UPC.PrintaFew"

我是这样称呼它的： Application.Run "'" & strPath & strFName & "'!UPC.PrintaFew"

The variable strFName contained an apostrophe in it which, of course messed things up. Took me hours to figure it out. But once the apostrophe was removed from the filename it worked.

变量 strFName 包含一个撇号，这当然把事情搞砸了。我花了几个小时才弄明白。但是一旦从文件名中删除了撇号，它就起作用了。

回答by Matt R.

I had to remove all dashes and underscores from file names and macro names, make sure that macro were enabled and add them module name.macro name

我必须从文件名和宏名中删除所有破折号和下划线，确保启用了宏并将它们添加到 module name.macro name

This is what I ended up with: Application.Run ("'" & WbName & "'" & "!ModuleName.MacroName")

这就是我最终的结果： Application.Run ("'" & WbName & "'" & "!ModuleName.MacroName")

回答by elano7

This error also occurs if you create a sub or function in a 'Microsoft Excel Object' (like Sheet1, Sheet2, ...) instead to create it in a Module.

如果您在“Microsoft Excel 对象”（如 Sheet1、Sheet2 等）中创建子或函数而不是在模块中创建它，也会发生此错误。

For example: you create with VBA a button and set .OnAction = 'btn_action'. And Sub btn_actionyou placed into the Sheet object instead into a Module.

例如：您使用 VBA 创建一个按钮并设置.OnAction = 'btn_action'. 并且Sub btn_action您将其放入 Sheet 对象而不是放入 Module 中。

回答by Brennan Casey

Go into task manager and see if you have any Microsoft Excel Processes running in the background. I closed my excel background processes and my code worked again.

进入任务管理器，看看是否有任何 Microsoft Excel 进程在后台运行。我关闭了我的 excel 后台进程，我的代码再次运行。