Argument value, application.run

[Neal Zimm]

Hi -
I don't know where to go next in getting the value to return from a Sub
executed from one Wbk to another.
I got The function to return the correct value.
Running the Sub gets the correct debug.print, But the returned argument is
either the same as the function or blank in the final examples below.
Why ?
Thanks,
Neal

' IN 1125 Wbk, Which is ACTIVE
Function zRmVerF(Dummy) As String
zRmVerF = "v01.03.00"
Debug.Print zRmVerF
End Function

'Sub in same as above
Sub zRmVerSub(Ver As String)
Ver = "vRm.Ver.Sub"
Debug.Print Ver
End Sub

' executed from VBE in personal.xls
Sub RUN_MACRO_WAYS()
Dim Ver As String, MacName As String
'Ver = Application.Run("'d1125.xls'!zRmVerF", "")
'MsgBox Ver 'WORKS prints and value passed

Dim WbkNa As String
WbkNa = ActiveWorkbook.Name
MacName = "zRmVerF"
Ver = Application.Run(WbkNa & "!" & MacName, "")
MsgBox Ver 'WORKS prints and value passed


MacName = WbkNa & "!" & "zRmVerSub"
'Application.Run (MacName, Ver) ??? compile error: Expected: =

'Application.Run MacName, Ver 'debug.print good,
'MsgBox Ver 'shows v01.03.00 from function, NOT vRm.Ver.Sub as expected


Dim DiffVer As String
Application.Run MacName, DiffVer 'debug.print is good
MsgBox DiffVer, , "DiffVer" 'no value for DiffVer ???
End Sub

 

[NickHK]

Neal, There are a couple of points in you various call:

'Application.Run (MacName, Ver) ??? compile error: Expected: =

This is expect, as you are using the braces ..(....).., so Excel expects you
to assign the return value to a variable; which you are not doing here.
But this will work:
Application.Run MacName, Ver

According to the help:
Objects passed as arguments to the macro are converted to values (by
applying the Value property to the object). This means that you cannot pass
objects to macros by using the Run method.

Whilst it not explicit about normal data type, it seem that you cannot pass
any argument ByRef with .Run, only ByVal. Hence your sub routine would never
return to expected value.
If in doubt, check the help for the difference between ByRef (the default in
VB/VBA) and ByVal in passing arguments.

NickHK

 

[Neal Zimm]

Nick -
Thanks, but perhaps my write up was not clear, as the final examples,
repeated below are pretty close to what you said. Perhaps you could show me
how to use the = sign to get what I want.

MacName = WbkNa & "!" & "zRmVerSub"
'Application.Run (MacName, Ver) ??? compile error: Expected: =
' the above did not work so I tried what's just below. I could not find any
' examples of using the = sign.


'Application.Run MacName, Ver 'debug.print good,
'MsgBox Ver 'shows v01.03.00 from function, NOT vRm.Ver.Sub as expected
application.run MacName, Ver IS what you wrote too. My comment
just after it said the print was good but the WRONG argument was
passed back. The function's value was passed, not the value from the Sub.
My final try below just tried a different var name since what WAS BEING
PASSED BACK was wrong.
Thanks again, Neal

Dim DiffVer As String
Application.Run MacName, DiffVer 'debug.print is good
MsgBox DiffVer, , "DiffVer" 'no value for DiffVer ???
End Sub

 

[Bob Phillips]

What Nick is saying is that you CANNOT get a sub to change one of the
arguments passed to it by an Application.Run command. To do that, you must
use a Function as per your first example.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)