CODEWITHSUNDEEP
excelvba
Ege Ozlem
Ege Ozlem

Reputation: 943

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.

Upvotes: 36

Views: 595999

Answers (19)

DSlomer64
DSlomer64

Reputation: 4283

I had my code working, then decided to rename my function insertSelection to the more sensible insertAtSelection. Then I immediately went to lunch. (What could go wrong?)

When I got back to it, I started getting the Cannot run the macro insertSelection; the macro may not be available in this workbook, which floored me. All was well an hour ago.

So I then took a long break to try to clear my brain.

The OnKey hotkey was originally (and still was) defined as OnKey "^+I", "insertSelection" (Ctrl+Shift+I), but after the change I didn't remember to change the function name in my OnKey definition to include At. The OnKey definitions are in another module. Eventually I recalled this omission.

So the error message was correct: the macro insertSelection WAS NOT available in this workbook--insert*At*Selection was.

OnKey "^+I", "insertAtSelection" worked and I said to myself, "CAREFUL!!!"

Upvotes: 0

carusyte
carusyte

Reputation: 1769

Anyone tried setting the Application.AutomationSecurity to 1 (msoAutomationSecurityLow)?

Ref:

https://learn.microsoft.com/en-us/office/vba/api/excel.application.automationsecurity https://learn.microsoft.com/en-us/office/vba/api/office.msoautomationsecurity

Upvotes: 0

Marcin Janowski
Marcin Janowski

Reputation: 94

I got this error when a sub was assigned programically using OnAction property. To make it work you need to provide the exact path for example 'My Workbook.xlsm'!MyMacroScript

.OnAction = "'" & ThisWorkbook.Name & "'!" & "MyMacroScript"

Upvotes: 0

ernesthm
ernesthm

Reputation: 421

In my case it was a security issue that I resolved by unblocking the file in explorer (Properties / Unblock).

Upvotes: 0

Naresh Bisht
Naresh Bisht

Reputation: 719

You actually have to create a module and write the code there.

Watch this video: https://www.youtube.com/watch?v=_EVxKkE9p1M

Upvotes: 1

Philip Swannell
Philip Swannell

Reputation: 935

In my case, the macro I wished to run was assigned to a button on a worksheet (Forms button as opposed to ActiveX button).

The macro was defined not in the workbook, but in a separate .xlam addin. Clicking the button gave an error: Cannot run the macro 'NameOfMyMacro'. The macro may not be available in this workbook or all macros may be disabled.

None of the answers given here worked for me, but what did work was to make the folder of the workbook a "Trusted Location".

See Ribbon -> File -> Options -> Trust Center -> Trust Center Settings -> Trusted Locations -> Add new Location

Upvotes: 0

Jeff Robson
Jeff Robson

Reputation: 13

I had the same error message "Cannot find the macro ___ etc" and this problem persisted even after I'd saved the file as an XLSX. This seemed very odd ... how could it be running a macro when the file has no macros!

When I examined the file using the Custom UI Editor, I found that a macro was being called when the file was initialized and another custom macro was being called when the user clicked the Save button.

Using the Custom UI Editor, I removed all the XML code and the error messages disappeared.

Upvotes: 0

Brennan Casey
Brennan Casey

Reputation: 897

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.

Upvotes: 4

Matt R.
Matt R.

Reputation: 31

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

Upvotes: 3

JovialJohn
JovialJohn

Reputation: 35

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"

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.

Upvotes: 3

elano7
elano7

Reputation: 2265

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.

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

Upvotes: 0

Peter Liapin
Peter Liapin

Reputation: 1275

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

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

Upvotes: 6

Mor Sagmon
Mor Sagmon

Reputation: 1045

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

Upvotes: 15

Heap of Pinto Beans
Heap of Pinto Beans

Reputation: 677

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.

Upvotes: 8

Vignesh
Vignesh

Reputation: 167

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

Upvotes: 8

ChipsLetten
ChipsLetten

Reputation: 2953

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"

Upvotes: 23

Ved Rai
Ved Rai

Reputation: 119

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.

Upvotes: 7

Doylet
Doylet

Reputation: 19

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

' Comment comment  

Options Explicit  

Sub someMacroMakechart()

in a sub module, instead of correct;

' Comment comment  

Option Explicit  

Sub someMacroMakechart()

Upvotes: 0

Dhitta Hananda
Dhitta Hananda

Reputation: 19

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

Upvotes: 1

Related Questions