Functions are named blocks of program code that perform a specific task and return a result. The task can be as simple as adding two numbers or as complex as launching a spacecraft. A subroutine is like a function, just that it does not return a result.

An example function definition:

PublicFunctionSum(ByRefNumber1AsDouble,ByRefNumber2AsDouble)AsDouble'Return the result by writing it to a variable having the same name as the functionSum=Number1+Number2EndFunction

An example subroutine definition:

PublicSubTell(ByValMyString1asString,ByValMyString2asString)MsgBoxMyString1&MyString2EndSub

Note the following:

• The arguments to the function are declared asByRef which requires the compiler to make sure that only arguments of the specified type are used, in this case Double.
• The function returns a value by assigning it to the function name as though the function were a variable. This contrasts with the use of the keywordreturn in many other languages.

You can use or call the two procedures defined in the previous sections as follows:

'On the next line, argument order mattersTell"Hello there.",""'On the next line, names of arguments are used and argument order does not matter.TellMyString1:="Hello there,",MyString2:=" again."'Tell ("Hello there.","") -- syntax errorMySum=Sum(123.456,234)MySum2=Sum(Number2:=8,Number1:=6)'MySum3 = Sum Number2:=8, Number1:=6 -- syntax error

Note the following:

• The arguments (argument list) passed to afunction must be enclosed in round brackets, whereas those supplied to a subroutine need not.

Each function and subroutine has the following parts, some of the optional:

Visibility

Public,Friend orPrivate

Procedure Type

Sub,Function,Property Let,Property Get,Property Set

Name

A freely chosen name that starts with a letter and contains only letters, numbers and underscores.

Argument List

A list of the items of data that the procedure reads or writes into.

Return Type

For aFunction orProperty Get, the data type returned, such asDouble orString.

Body

All the statements that do the work.

Only theName and theProcedure Type are mandatory. Of course, a procedure without a body doesn't do anything.

This seems like a very unimportant part of a procedure declaration tomost people but in fact it is a very helpful feature. With it you canshow that some procedures are just for use inside a module(Private), some only for use in this component (Friend) oravailable for the whole world (Public). You should markproceduresPrivate unless they will be called from outside themodule. this will encourage you, and anyone who edits yourprogram, to place related procedures in the same module whichobviously makes maintenance easier.

Marking a procedurePrivate also means that you can have anotherprocedure with exactly the same name in another module.

UseExit Function orExit Sub to terminate a procedure in the middle, like this:

SubLengthyComputation(fat,n)Ifn=0Orn=1Thenfat=1' Now terminateExitSubEndIf' Now compute fat ...EndSub

All procedures are either functions that return a result as the value of the function, or subroutines that are called for their side effects. To return a value, you can use both, but with subroutine, you need to do it via an argument:

PrivateFunctionFunctionHalf(ByRefyasDouble)asDoubleFunctionHalf=y/2EndFunctionPrivateSubSubroutineHalf(ByRefyAsDouble,ByRefResultAsDouble)Result=y/2EndSub

The two procedures do essentially the same thing, that is, divide a number by two. TheFunction version does it byassigning the new value to the name of the function while theSub version assigns it to the name of one of thearguments. This affects how you use them.

The function version can be used in an expression as follows:

Debug.PrintFunctionHalf(10)'Prints 5

To use the subroutine to return value, you need to store the value in a variable, like this:

DimnHalfasDoubleSubroutineHalf10,nHalfDebug.PrintnHalf

Generally, you use aFunction when the result is a single thing (number, string, object) and aSub when you either want to return several distinct things or nothing at all.

Properties are also a form of procedure.Property Get is a function;Property Let andProperty Set are subroutines. For more discussion ofProperties, see theObject Oriented Programming chapter.

You can specify optional arguments and default values:

FunctionMySum(i1,i2,Optionali3)IfIsMissing(i3)ThenMySum=i1+i2ElseMySum=i1+i2+i3EndIfEndFunctionFunctionMyConcatenate(String1,String2,OptionalSeparator=" ")'Default value for Separator specifiedMyConcatenate=String1&Separator&String2EndFunctionSubTest()Debug.PrintMySum(1,2)*MySum(1,2,3)Debug.PrintMyConcatenate("Hello","World")Debug.PrintMyConcatenate("Hello","World",", ")EndSub

Once an argument is declared optional, all arguments at the right of it have to be declared optional as well.

Links:

• Using Optional Arguments, Office 2000, msdn.microsoft.com

• Book:Visual Basic