Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument value, application.run
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument value, application.run
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument value, application.run
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument value, application.run
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difference application.quit & application.close | Excel Programming | |||
Application.GetOpenFilename vs Application.Dialogs(xlDialogsOpen) | Excel Programming | |||
Replace application.RTD property by Application.RTDServers collect | Excel Programming | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming |