Application.Run Call Err: 1004 Cannot Find Macro
This topic has been discussed numerous times that I can find, but even
with all the discussion I cannot get Application.Run to work for me. So here's what I did. I set up a test.xls and a test2.xls. In test.xls, I created a basTest and put in the following code: Public Sub GetMessage() MsgBox "Message from test was received.", vbOKOnly, "GetMessage" End Sub In test2.xls, I've created a button and tried to call application.run, here's what I currently have: Private Sub CommandButton1_Click() Dim WB As Excel.Workbook Dim Path as String On Error GoTo ErrorHandler Path = Application.GetOpenFilename("Excel File,*.xls", , "Test", "Open", False) Set WB = GetObject(Path) WB.Application.Run "!GetMessage'" 'GetMessage Exit Sub ErrorHandler: MsgBox "Err: " & Err.Description & " Number: " & Err.Number End Sub This throws the Err: 1004, Cannot Find Macro. As well as do the following changes that I've made to the code. 'Tried this, and got the same error. WB.Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run FileName & "!GetMessage'" I've tried several other variations and absolutely cannot access the sub. What am I doing wrong??? |
Application.Run Call Err: 1004 Cannot Find Macro
I try:
Application.Run "'" & WB.Name & "'!GetMessage" Note that the apostrophes are in different locations from what you tried. Travich wrote: This topic has been discussed numerous times that I can find, but even with all the discussion I cannot get Application.Run to work for me. So here's what I did. I set up a test.xls and a test2.xls. In test.xls, I created a basTest and put in the following code: Public Sub GetMessage() MsgBox "Message from test was received.", vbOKOnly, "GetMessage" End Sub In test2.xls, I've created a button and tried to call application.run, here's what I currently have: Private Sub CommandButton1_Click() Dim WB As Excel.Workbook Dim Path as String On Error GoTo ErrorHandler Path = Application.GetOpenFilename("Excel File,*.xls", , "Test", "Open", False) Set WB = GetObject(Path) WB.Application.Run "!GetMessage'" 'GetMessage Exit Sub ErrorHandler: MsgBox "Err: " & Err.Description & " Number: " & Err.Number End Sub This throws the Err: 1004, Cannot Find Macro. As well as do the following changes that I've made to the code. 'Tried this, and got the same error. WB.Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run FileName & "!GetMessage'" I've tried several other variations and absolutely cannot access the sub. What am I doing wrong??? -- Dave Peterson |
Application.Run Call Err: 1004 Cannot Find Macro
And if that macro were in the same workbook as the code calling it, I'd just
use: Call GetMessage Travich wrote: This topic has been discussed numerous times that I can find, but even with all the discussion I cannot get Application.Run to work for me. So here's what I did. I set up a test.xls and a test2.xls. In test.xls, I created a basTest and put in the following code: Public Sub GetMessage() MsgBox "Message from test was received.", vbOKOnly, "GetMessage" End Sub In test2.xls, I've created a button and tried to call application.run, here's what I currently have: Private Sub CommandButton1_Click() Dim WB As Excel.Workbook Dim Path as String On Error GoTo ErrorHandler Path = Application.GetOpenFilename("Excel File,*.xls", , "Test", "Open", False) Set WB = GetObject(Path) WB.Application.Run "!GetMessage'" 'GetMessage Exit Sub ErrorHandler: MsgBox "Err: " & Err.Description & " Number: " & Err.Number End Sub This throws the Err: 1004, Cannot Find Macro. As well as do the following changes that I've made to the code. 'Tried this, and got the same error. WB.Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run WB.Name & "!GetMessage'" 'Tried this and got the same error. Application.Run FileName & "!GetMessage'" I've tried several other variations and absolutely cannot access the sub. What am I doing wrong??? -- Dave Peterson |
Application.Run Call Err: 1004 Cannot Find Macro
Ah! The single quotes may be the problem. I will try it now and let
you know. These are in seperate workbooks. As a tangeant, can someone explain why I would use "call" in front a sub instead of just calling the sub by its name? Thanks! |
Application.Run Call Err: 1004 Cannot Find Macro
Wow, great catch on the single quotes!! That solved the problem!!!!
One final question - does anyone know if I have the modules password protected, if this would possibly prevent a macro from being called (one in another workbook?). I'm going to test it out now. Thanks! |
Application.Run Call Err: 1004 Cannot Find Macro
I think you'll find your answer to the second question pretty quickly!
Travich wrote: Wow, great catch on the single quotes!! That solved the problem!!!! One final question - does anyone know if I have the modules password protected, if this would possibly prevent a macro from being called (one in another workbook?). I'm going to test it out now. Thanks! -- Dave Peterson |
Application.Run Call Err: 1004 Cannot Find Macro
Besides the syntax differences, I think it's just a matter of personal
preference. I like to use Call. I think it makes the code easier to read. Others disagree, though. Travich wrote: Ah! The single quotes may be the problem. I will try it now and let you know. These are in seperate workbooks. As a tangeant, can someone explain why I would use "call" in front a sub instead of just calling the sub by its name? Thanks! -- Dave Peterson |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com