Function and Sub

[Jeff]

Hi,

I have a function that returns a value. I just set the value = to the name
of the function. Is there a way to do this with a "Sub" procedure - I mean
return a value without creating a global variable. I wanted to use a Sub in
the future because you can do more manipulation.

Function MaxValue(a, b)

If a < b Then
MaxValue = b
Else
MaxValue = a
End If

End Function

 

[Rick Rothstein \(MVP - VB\)]

You can do the exact same manipulation in a Function that you can do in a
Sub. What is it you want to do that you think you need a Sub for?

Rick

 

[Harald Staff]

A sub is a function that returns nothing (Void). Or a function is a sub with
a return value. No differences in power. Practical diffenrences in Excel is
that a Sub is in the macro-run menu and can be assigned to clickable
objects, whereas functions can be used as cell formulas.

When used from a cell, then the function is pretty crippled, it is limited
to what formulas can do; return a single value. Which I guess is the
limitation in mention. Call the function from a sub, like

Sub Test()
MsgBox MaxValue(2, 1)
End Sub

and it can do just anything; kill files, partition disks, email your inlaws,
....

HTH. Best wishes Harald

 

[Jeff]

Hi,

I guess your point is that the sub and function have the same computing
power. The limitations of a function, are that you cant manipulate cell
data. It is strange
that there is no way to return a value or multiple values in a sub procedure
though.

 

[Dave Peterson]

How about just manipulating the values, er, variables passed to the subroutine:

Option Explicit
Sub testme01()
Dim myLong As Long
Dim myDbl As Double
myLong = 5
Call testme02(myLong, myDbl)
MsgBox myLong & "--" & myDbl
End Sub
Sub testme02(ByVal a As Long, ByRef b As Double)
b = a / 2
a = a + a
End Sub

Since a is passed by value, mylong won't change.

Since b is passed by reference, then any change made to b is a change to mydbl.