PASSING MACROS WITH ARGUMENTS FROM OTHER FILES TO BE RUN

[ianmashal]

Hi,

I am trying to setup procedures that perform some steps then run
whichever macro is passed to them as a string argument

the actual code leaving the STRING variable containing the macro and
its arguments blank is:

Sub SendMacroName()

UseMacroStringToRunMacro STRING

End Sub

Sub UseStringToRunMacro(STRING)

....PERFORMS SOME STEPS...

'runs the macro represented in STRING
Application.Run STRING

End Sub

This works fine in all but one case: when the macro to be run is in
another file and has arguments. the actual string value that would work
in such a case:

Application.Run "FILE CONTAINING MACRO TO RUN.xls!MACRO TO RUN",
"ARGUMENT 1", "ARGUMENT 2", etc

i can give STRING that exact value by either of the following code, but
neither work on the Application.Run STRING command

STRING = """FILE.xls!MACRO""" & ", " & """ARGUMENT 1""" etc
or
STRING = """FILE.xls!MACRO"", ""ARGUMENT 1"" etc

both result in the error message: method 'run' of object application
failed


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

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

[notice the string is bracketed by single quotes]
but if the macro is in another file this doesn't work [and it is NOT
b/c the file containing the macro isn't referenced, i tried
"'FILE.xls!MACRO, "ARGUMENT 1", ETC '" [that produces a macro cannot be
found error]

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

STRING = "FILE.xls!MACRO"

my guess is it has something to do with extra quotation marks around
the string, or how to represent the file name when the macro has
arguments. i think what may be happening is the STRING is interpreted
as being the macro to run not as encompassing the name of the macro and
each of its arguments.

Thanks for your help,

Ian

 

[Peter T]

Hi Ian,

Not sure you can send a single string that includes arguments to the Run
method (unlike calling with Ontime which does require macro + arg's as a
single string). Could you do it like this -

Sub test()
sArg1$ = "AA"
sArg2 = "BB"

sMacro$ = "Personal.xls!foo\" & sArg1 & "\" & sArg2

MsgBox appRun(sMacro)

End Sub

Function appRun(strIn As String)
Dim va
va = Split(strIn, "\")

appRun = Application.Run(va(0), va(1), va(2))

End Function

'in Personal.xls
Function foo(a$, b$) As String
foo = a & b
End Function

Ensure whatever delimiter does not exist elsewhere in the arguments, a bit
more checking 'va' after the Split to ensure you've got an appropriate
array.

Regards,
Peter T

 

[ianmashal]

Hi Peter,

Thanks that helped. I didn't know of the Split function before. Now all
that is left is figuring out how to allow the run statement to vary the
# of arguments. I already have code that can search through VBE modules
Find X and replace it. so I'll adapt it to this code.
Find each instance of "Application.Run va(0)" and replace the line with
the # of arguments passed in the string.

Thanks alot,

Ian

 

[Peter T]

Now all that is left is figuring out how to allow the

run statement to vary the # of arguments.

I don't think you can do that, instead

dim ub as long

if isArray(va) then
ub = ubound(va)
else ub = -1
end if

Select case ub
case -1: application.run va
case 0: application.run va(0)
case 1: application.run va(0), va(1)
'case etc
end select

Obviously the arguments need to be consistent with the called routine.

Do you particularly need to build a string, if not send an array with
vArr(0) = sMacro and avoid the Split function (which BTW is n/a in xl97)

Regards,
Peter T

 

[ianmashal]

Thanks again Peter.

What you wrote makes sense. I manage to replace the code based on the #
of arguments sent. it works, except that VBE doesn't update the lines
of code while the macro is running [eventhough the text is updated]. so
I think your select case solution will work best.

Thanks,

Ian