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

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:

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.

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

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

Solution 1:

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"

Solution 2:

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

Solution 3:

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.

Solution 4:

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

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.

Solution 5:

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