Return value in "Sub"

[Jeff]

Hi,

I was trying to figure out how to return a value from a "Sub" procedure. I
understand that if you write a function you can set a value equal to that
function and that will return the value. For example

Public Function TestFunction(iVal as integer)
(
TestFunction = iVal * iVal
)

Var = TestFunction(4) ' will return 4 * 4 to the variable "Var"

But in a Sub

Sub TestSub(iVal as integer)
(
TestSub = iVal * iVal
)

Call TestSub

Does not work. I dont want to use functions because I cannot do as much as
in a sub and this is just a simple example, I forget the rules but I dont
think you can edit ranges or edit objects.

 

[Gary''s Student]

You can use a public (global) variable to hold the return value:

Public outputvalue As Integer

Sub mainroutine()
Call ordinate(3)
MsgBox (outputvalue)
End Sub

Sub ordinate(iVal As Integer)
outputvalue = iVal * iVal
End Sub

 

[Jim Rech]

Since arguments are passed ByRef by default:

Sub a()
Dim Argument As Integer
Argument = 1
TestSub Argument
MsgBox Argument
End Sub

Sub TestSub(Arg As Integer)
Arg = Arg * 2
End Sub

--
Jim
| Hi,
|
| I was trying to figure out how to return a value from a "Sub" procedure.
I
| understand that if you write a function you can set a value equal to that
| function and that will return the value. For example
|
| Public Function TestFunction(iVal as integer)
| (
| TestFunction = iVal * iVal
| )
|
| Var = TestFunction(4) ' will return 4 * 4 to the variable "Var"
|
| But in a Sub
|
| Sub TestSub(iVal as integer)
| (
| TestSub = iVal * iVal
| )
|
| Call TestSub
|
| Does not work. I dont want to use functions because I cannot do as much
as
| in a sub and this is just a simple example, I forget the rules but I dont
| think you can edit ranges or edit objects.

 

[JE McGimpsey]

???

You can do anything in a Function that you can do in a Sub.

Neither a Sub nor a Function can change formatting, edit ranges, etc. if
called from the context of a worksheet cell.

 

[JE McGimpsey]

One way:

Public Sub TestSub(ByRef Result As Long, ByVal iVal As Integer)
Result = iVal * iVal
End Sub


Public Sub foo()
Dim nResult As Long
Dim iTest As Integer
iTest = 2
TestSub nResult, iTest
MsgBox nResult
End Sub

There's usually no reason to do this, since functions can do anything a
Sub can do.

 

[Ron Rosenfeld]

Hi,

I was trying to figure out how to return a value from a "Sub" procedure. I
understand that if you write a function you can set a value equal to that
function and that will return the value. For example

Public Function TestFunction(iVal as integer)
(
TestFunction = iVal * iVal
)

Var = TestFunction(4) ' will return 4 * 4 to the variable "Var"

But in a Sub

Sub TestSub(iVal as integer)
(
TestSub = iVal * iVal
)

Call TestSub

Does not work. I dont want to use functions because I cannot do as much as
in a sub and this is just a simple example, I forget the rules but I dont
think you can edit ranges or edit objects.

Here's a routine with an example. The variable sent to the Sub by foo() will
be modified by that Sub, and can then be used in foo():

========================
Option Explicit

Sub TestSub(iVal As Integer)
iVal = iVal * iVal
End Sub

Sub foo()
Dim TestNo As Integer
TestNo = 23
TestSub TestNo
Debug.Print TestNo
End Sub
============================
--ron