CORRECT SYNTAX FOR RUN...

[ianmashal]

Hi,

I've been trying to solve this problem for a few days now. What is the
correct
syntax for Run, when executing a sub procedure in a DIFFERENT workbook
under the following conditions: the procedure has arguments, and you
are passing the code to run the macro with its arguments as 1 string:

sub RunMacro(STRING)

Application. Run STRING

End Sub

Where STRING has a value like "FILE.XLS!MACRO ARG1, ARG2"

Thanks for any help you can give

Regards,

Ian

 

[Ron de Bruin]

Try this

"FILE.XLS!MACRO", "ARG1", "ARG2"

 

[Bob Phillips]

You cannot do it in a single string, you need

sub RunMacro(STRING, arg1, arg2)

Application. Run STRING,arg1,arg2

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Ian,

My caveat is that I've only ever made this call from a VBScript...

Dim strMacroName
strMacroName = "'" & strPath & "\" & objArgs(0) & "'" & "!Sheet1.MyMacro"
Run strMacroName

Where strPath is the directory containing the .xls file. If the file is
Test.xls, then the filename might be: 'c:\Temp\Test.xls' Notice that there
are single quotes around the fully qualified path and filename.

Sheet1 is the sheet that the macro is defined in, and then the name of the
macro. So putting it all together as a complete string it would look like
this:

'c:\temp\test.xls'!Sheet1.MyMacro

Hope that helps,

Kim

 

[ianmashal]

Hi,

Thanks everyone for your help.

I think Bob Phillips may be right.

if the macro to run were in the same file as the macro calling it, the
following would work:

STRING = "'MACRO_WITHOUT_FILEPATH, "ARGUMENT 1", ETC '"

Run STRING

if the macro to run were in another file but had no arguments the
following works:

STRING = "FILE.xls!MACRO"

Run STRING

But no variations of these seem to work for the case of a macro taking
arguments, and located in another file

Thanks again.

Ian