Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Calculated Value from a Custom Function in the Calling Sub
I do not know how to use a calculated value from a custom function in
its calling subprocedure. In other words, how do you move the calculated value back into the sub that called it? Here is a simple example of a function (AddValues) that adds two values, which are arguments passed from its calling procedure (TotalValues). If you run the sub with the Call AddValues(Value1, Value2) statement, the function returns a Total = 12, but how can that value be transferred back to the sub and used there, say in additional calculations(for which no code is currently shown)? As an alternative to the Call AddValues(Value1, Value2) statement, I tried to import the calculated value of 12 directly into the sub by assigning a variable mytotal in the sub to the Application.Run("MacroPractice.xls!AddValues", 5, 7) statement. The result was that the two arguments of 5 and 7 were passed from the sub to the function and 12 was still calculated for Total, but nothing was returned to the mytotal variable in the sub. Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 MsgBox Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 Call AddValues(Value1, Value2) mytotal = Application.Run("MacroPractice.xls!AddValues", 5, 7) MsgBox mytotal End Sub I use Excel for Mac 2004, which is similar to Excel for Windows 2003. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Calculated Value from a Custom Function in the Calling Sub
You return the value in its name:
Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 AddValues = Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 something = AddValues(Value1, Value2) ' then use something to calculate mytotal MsgBox mytotal End Sub -- Gary''s Student - gsnu200853 "bluebird" wrote: I do not know how to use a calculated value from a custom function in its calling subprocedure. In other words, how do you move the calculated value back into the sub that called it? Here is a simple example of a function (AddValues) that adds two values, which are arguments passed from its calling procedure (TotalValues). If you run the sub with the Call AddValues(Value1, Value2) statement, the function returns a Total = 12, but how can that value be transferred back to the sub and used there, say in additional calculations(for which no code is currently shown)? As an alternative to the Call AddValues(Value1, Value2) statement, I tried to import the calculated value of 12 directly into the sub by assigning a variable mytotal in the sub to the Application.Run("MacroPractice.xls!AddValues", 5, 7) statement. The result was that the two arguments of 5 and 7 were passed from the sub to the function and 12 was still calculated for Total, but nothing was returned to the mytotal variable in the sub. Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 MsgBox Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 Call AddValues(Value1, Value2) mytotal = Application.Run("MacroPractice.xls!AddValues", 5, 7) MsgBox mytotal End Sub I use Excel for Mac 2004, which is similar to Excel for Windows 2003. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Calculated Value from a Custom Function in the Calling Sub
You don't "call" a function, you just reference it (in the same way you do
functions like Len, Sin, DateSerial, etc.). Here is your calling subroutine rewritten to do that... Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 mytotal = AddValues(Value1, Value2) MsgBox mytotal End Sub or, if you don't need the mytotal variable for anything else, then eliminate it and MessageBox the result of your formula directly... Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 MsgBox AddValues(Value1, Value2) End Sub -- Rick (MVP - Excel) "bluebird" wrote in message ... I do not know how to use a calculated value from a custom function in its calling subprocedure. In other words, how do you move the calculated value back into the sub that called it? Here is a simple example of a function (AddValues) that adds two values, which are arguments passed from its calling procedure (TotalValues). If you run the sub with the Call AddValues(Value1, Value2) statement, the function returns a Total = 12, but how can that value be transferred back to the sub and used there, say in additional calculations(for which no code is currently shown)? As an alternative to the Call AddValues(Value1, Value2) statement, I tried to import the calculated value of 12 directly into the sub by assigning a variable mytotal in the sub to the Application.Run("MacroPractice.xls!AddValues", 5, 7) statement. The result was that the two arguments of 5 and 7 were passed from the sub to the function and 12 was still calculated for Total, but nothing was returned to the mytotal variable in the sub. Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 MsgBox Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 Call AddValues(Value1, Value2) mytotal = Application.Run("MacroPractice.xls!AddValues", 5, 7) MsgBox mytotal End Sub I use Excel for Mac 2004, which is similar to Excel for Windows 2003. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Calculated Value from a Custom Function in the CallingSub
On May 18, 3:46*pm, Gary''s Student
wrote: You return the value in its name: Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 AddValues = Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 something = AddValues(Value1, Value2) ' then use something to calculate mytotal MsgBox mytotal End Sub -- Gary''s Student - gsnu200853 "bluebird" wrote: I do not know how to use a calculated value from a custom function in its calling subprocedure. In other words, *how do you move the calculated value back into the sub that called it? *Here is a simple example of a function (AddValues) that adds two values, which are arguments passed from its calling procedure (TotalValues). *If you run the sub with the Call AddValues(Value1, Value2) statement, the function returns a Total = 12, *but how can that value be transferred back to the sub and used there, say in additional calculations(for which no code is currently shown)? *As an alternative to the Call AddValues(Value1, Value2) statement, I tried to import the calculated value of 12 directly into the sub by assigning a variable mytotal in the sub to the Application.Run("MacroPractice.xls!AddValues", 5, 7) statement. *The result was that the two arguments of 5 and 7 were passed from the sub to the function and 12 was still calculated for Total, but nothing was returned to the mytotal variable in the sub. Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 MsgBox Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 Call AddValues(Value1, Value2) mytotal = Application.Run("MacroPractice.xls!AddValues", 5, 7) MsgBox mytotal End Sub I use Excel for Mac 2004, which is similar to Excel for Windows 2003. Thank you. Thanks. I appreciate your help. George Boynton |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Calculated Value from a Custom Function in the CallingSub
On May 18, 3:47*pm, "Rick Rothstein"
wrote: You don't "call" a function, you just reference it (in the same way you do functions like Len, Sin, DateSerial, etc.). Here is your calling subroutine rewritten to do that... Public Sub TotalValues() * Dim Value1 As Integer, Value2 As Integer, mytotal As Integer * Value1 = 5 * Value2 = 7 * mytotal = AddValues(Value1, Value2) * MsgBox mytotal End Sub or, if you don't need the mytotal variable for anything else, then eliminate it and MessageBox the result of your formula directly... Public Sub TotalValues() * Dim Value1 As Integer, Value2 As Integer, mytotal As Integer * Value1 = 5 * Value2 = 7 * MsgBox AddValues(Value1, Value2) End Sub -- Rick (MVP - Excel) "bluebird" wrote in message ... I do not know how to use a calculated value from a custom function in its calling subprocedure. In other words, *how do you move the calculated value back into the sub that called it? *Here is a simple example of a function (AddValues) that adds two values, which are arguments passed from its calling procedure (TotalValues). *If you run the sub with the Call AddValues(Value1, Value2) statement, the function returns a Total = 12, *but how can that value be transferred back to the sub and used there, say in additional calculations(for which no code is currently shown)? *As an alternative to the Call AddValues(Value1, Value2) statement, I tried to import the calculated value of 12 directly into the sub by assigning a variable mytotal in the sub to the Application.Run("MacroPractice.xls!AddValues", 5, 7) statement. *The result was that the two arguments of 5 and 7 were passed from the sub to the function and 12 was still calculated for Total, but nothing was returned to the mytotal variable in the sub. Function AddValues(Val1 As Integer, Val2 As Integer) as Integer Dim Total As Integer Total = Val1 + Val2 MsgBox Total End Function Public Sub TotalValues() Dim Value1 As Integer, Value2 As Integer, mytotal As Integer Value1 = 5 Value2 = 7 Call AddValues(Value1, Value2) mytotal = Application.Run("MacroPractice.xls!AddValues", 5, 7) MsgBox mytotal End Sub I use Excel for Mac 2004, which is similar to Excel for Windows 2003. Thank you. Thanks very much. George Boynton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Custom Calculated Items in a PivotTable | Excel Worksheet Functions | |||
How do you get calling cell for custom function | Excel Programming | |||
Custom Function not being auto calculated when cells change..help? | Excel Worksheet Functions | |||
Calling a Custom Function within a Procedure | Excel Programming |