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

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子。在一个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.

我也尝试过应用程序。运行“Python解决方案macro.xlsm ! Module1的。准备好了，但没有成功。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

14 个解决方案

#1

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

根据微软的知识库，尝试允许编程访问Visual Basic项目:

Click the Microsoft Office Button, and then click Excel Options.

单击Microsoft Office按钮，然后单击Excel选项。

Click Trust Center.

单击“信任中心。

Click Trust Center Settings.

单击“信任中心设置。

Click Macro Settings.

单击宏设置。

Click to select the Trust access to the VBA project object model check box.

单击此处可选择VBA项目对象模型信任访问复选框。

Click OK to close the Excel Options dialog box.

单击OK关闭Excel选项对话框。

You may need to close and re-open excel.

您可能需要关闭并重新打开excel。

#2

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"

#3

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

有同样的问题，我“编译VBA项目”发现了一个错误。修改和编译之后，宏开始工作。

#4

Delete your name macro and build again. I did this, and the macro worked.

删除您的名字宏并再次构建。我做了这个，宏观工作了。

#5

I had the same problem as OP and found was due to the options declaration being misspelled:

我遇到了与OP相同的问题，发现是由于选项声明被拼错了:

' Comment comment  

Options Explicit  

Sub someMacroMakechart()

in a sub module, instead of correct;

在子模块中，不正确;

' Comment comment  

Option Explicit  

Sub someMacroMakechart()

#6

This Error pops up when opening a macro which is last saved having a syntax error in it. The Message though will refer to the macro which is calling the erroneous sub or the function. To solve this issue I usually try to run the last edited macros separately.

当打开一个宏时，这个错误就会弹出，这个宏最后一次保存时，其中有一个语法错误。消息将指向调用错误子或函数的宏。为了解决这个问题，我通常尝试分别运行最后编辑的宏。

#7

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.

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

#8

The most probable reason for this error is that the security feature in Excel VBA that would not allow the VBA code to be run. The user should explicitly give permission to running the excel macros alongside the workbook. This may or may not require programmatic access to the Visual Basic project.

出现此错误的最可能原因是Excel VBA中的安全特性不允许运行VBA代码。用户应该明确地允许在工作簿旁边运行excel宏。这可能需要也可能不需要对Visual Basic项目进行编程访问。

What solved this error for me was :-

我解决这个错误的办法是:-

Enable Editing and Enable Content

启用编辑和启用内容
Changing macro settings without programmatic access Steps and details
更改宏设置，无需编程访问步骤和细节。
Changing macro settings along with programmatic access(not recommended if the any of the above process works out. This would give code control to change the elements in the VBA Project itself including References and Code itself - Reference)

更改宏设置和编程访问(如果上述任何一个过程都成功，则不建议这样做。这将使代码控制更改VBA项目本身的元素，包括引用和代码本身—引用)

Steps Here

这里的步骤

#9

Save as .xlsm (Excel Macro-Enabled Workbook).

保存为.xlsm(支持Excel宏的工作簿)。

#10

In my case it was due to having a public procedure in a different module and a private procedure in the calling module with the same name. VBA does not distinguish between the two, and hence gave the obtuse error.

在我的例子中，这是因为在另一个模块中有一个公共过程，在调用模块中有一个同名的私有过程。VBA不区分这两者，因此给出了钝化错误。

#11

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

我必须从文件名和宏名中删除所有的破折号和下划线，确保启用了宏并添加了它们的模块名。宏的名字

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

这就是我最后得出的结论:应用。运行(“& WbName &“”&“ModuleName.MacroName !”)

#12

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。运行"'" & 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中包含一个撇号，当然会把事情搞砸。我花了好几个小时才弄明白。但是一旦撇号从文件名中删除，它就可以工作了。

#13

I had the same problem and took a while to figure out. My goal was to call code in a separate workbook. I had the target sub in a sheet's code and not a module. Turns out that it would appear that one cannot remotely call a sub or a function that is not in a module.

我遇到了同样的问题，花了一段时间才弄明白。我的目标是在一个单独的工作簿中调用代码。我在一个表的代码中有目标子程序，而不是模块。结果显示，我们不能远程调用模块之外的子函数或函数。

#14

-2

check for:

检查:

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

use the module name.

使用的模块名称。

#1