Application.run error 1004

[sheekom]

Hi,
I am trying call VBA code from another worksheet in another workbook and It get error 1004. Can not figure out, and would appreciate help. here is my code:

Public Sub RunSheetMacro()
Dim Obj As Object
Dim wbk1 As New Workbook
Dim wks1 As New Worksheet
Set wbk1 = Application.Workbooks("Management of Academy Topics.xlsm")
Set wks1 = wbk1.Sheets("oea_TopicsLibrary")
wbk1.Activate
wks1.Select

'RunString = "'" & wbk1.Name & "'!" & wks1.Name & "." & "updateTopicLib, obj"
'Debug.Print RunString

Application.Run "'Management of Academy Topics.xlsm'!oea_TopicsLibrary.updateTopicLib"

End Sub

The VBA code routine is in sheet oea_TopicsLibrary of workbook Management of Academy Topics.xlsm and the routine name is updateTopicLib. I know I am something wrong here in syntax. Can it work ? Would appreciate any direction.

Thanks in Advance

 

[Joe4]

Welcome to the Board!

Which line of code gives you the error?
Is the other file already open?
Is it open in the same session of Excel, or a different session (they need to both be in the same Excel session)?

 

[sheekom]

Yes, All workbooks are open. I get error on application run line. It is in the same Excel session.

 

[Joe4]

When you say:

The VBA code routine is in sheet oea_TopicsLibrary of workbook

do you mean that the code is in the oea_TopicsLibrary sheet module in VBA?

I think in order to access it from other workbooks/worksheets, it needs to be put in a General module, and needs to be marked as "Public".

 

[sheekom]

Hi Joe4,

Thanks for the reply and made me realize that (for the benefits of all who visit this thread)

1. VBA code can be worksheet specific,i.e. in a addin module (".xlam") or in a General Module (can be called anything like (WbkCodeModule) of a workbook. Workbook general module is created by create a module action at the workbook level in VBE.
2. Worksheet specific VBA code/Macro can be called only in that specific worksheet only.
3. addin module (*.xlam) code can be called from anywhere across worksheets and workbooks and must be public.
4. Any worksheet can call general module code from within same workbook or different workbook. The Call syntax is different as follows:

From same workbook - Call MacroName
From different workbook Call "'WorkbookName'!MacroName" - Note the single quite around the workbook name and double quote around the entire call string. The workname and macroname can be created dynamically or passed in a general purpose external macro calling routine and again must be placed in the general module.

5. Parameters can be passed in both above cases

Once I realized and followed above rules. All is good ! Thanks

 

[Joe4]

You are welcome.
Glad to hear that you got things working!