Using a Calculated Value from a Custom Function in the Calling Sub

[bluebird]

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.

 

[Gary''s Student]

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

 

[Rick Rothstein]

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

 

[bluebird]

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

Thanks. I appreciate your help. George Boynton

 

[bluebird]

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

Thanks very much. George Boynton