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)
"Neal Zimm" <(E-Mail Removed)> wrote in message
news:E3D40BBD-59B7-4BCB-84DE-(E-Mail Removed)...
> 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
>
>
> --
> Neal Z
>
>
> "NickHK" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:F417DC6C-7CA4-4829-A43C-(E-Mail Removed)...
>> > 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
>> > --
>> > Neal Z
>>
>>
>>
|
Similar Threads
