How to fix if macro is trying to call sub from another workbook

[dmadhup]

Hi,
I open two or more workbooks on my computer. I am using the syntax:

Call macro_name

to call another macro in a module from the same workbook I am in. But sometimes it tries to call from another workbook where not found and throw the error like:

Method name of method not found.

Any help is appreciated.

Thanks

 

[Dryver14]

try qualifying it a little more call Module2.subname

 

[JoeMo]

If you want to call macro_name from another workbook (for this example say its My Workbook.xlsm) and both workbooks are open, try this syntax from the calling workbook"

Application.Run "'My Workbook.xlsm'!macro_name"

Be sure to use the combination of single and double quotation marks as shown.

 

[dmadhup]

Actually, I am calling macro on same workbook. I am not calling macro_name from another workbook. The second workbook is just opened with some data.

 

[dmadhup]

Let me explain little more to my question:

If I have two workbook opened. All macro contents is on first workbook. The second workbook just contains data and there is no macro there.
So, when I hit the macro button on first workbook, then button event starts looking on second workbook where it doesn't find anything and throw an error called "Method name method of class....not found".
My intention is to stay on first workbook, not to look on second workbook when you hit the button.

Using Syntax:
Call macro_name

Or

Call module.macro_name

 

[JoeMo]

If you post all your code and tell us which line is highlighted when you get the error and what the code is intended to accomplish, maybe someone can help you.

 

[dmadhup]

Code Example:

Workbook 1 - Module1:
Sub Generate()
Call Module2.Calculate
End Sub

Module2-Workbook1:

Sub Calculate()
Dim a, b, c As Long
a=5
b=6
c = 5+6
MsgBox "Result is" & c
End Sub

Above is my code in workbook 1. There is nothing in workbook 2. I just open to do something else.
When I hit button attached to sub Generate then it points to workbook 2 and there is no any macro or modules. And throw error.

 

[JoeMo]

I wouldn't use Calculate as a subroutine name. Calculate is a keyword in VBA. Try calling it "MyCalculator" (without the quote marks). Then try this:

Sub Generate()
Call MyCalculator
End Sub

 

[dmadhup]

Same issue. This issue is not all the time, often times

 

[JoeMo]

Sorry, I can't reproduce your problem so have no way to diagnose it. I ran Sub Generate() 10 consecutive times successfully which rules out an error that occurs "often times".