How to Write a Visual Basic Function Procedure

This is from Microsoft Excel 2000 Visual Basic Help, with changes and additions.

A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is like a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as constants, variables, or expressions.  These can be passed to it by a calling procedure or the spreadsheet.  If a Function has no arguments, its Function statement must have empty parentheses. A function returns a value by assigning a value to its name in one or more statements of the procedure.

In the following example, the Celsius function calculates degrees Celsius from degrees Fahrenheit. When the Main procedure calls the function, it passes a variable containing the argument value to the function. The function returns the result of the calculation to the calling procedure, which displays it in a message box.

Option Explicit

Sub Main()
  Dim Temp&
  Temp = Application.InputBox(Prompt:= _
    "Please enter the temperature in degrees F.", Type:=1)  ' Type:=1 = Number
  MsgBox "The temperature is " & Celsius(Temp) & " degrees C."
End Sub

Function Celsius&(fDegrees As Long)
  Celsius = (fDegrees - 32) * 5 / 9
End Function

Note: Using Option Explicit, as above, is highly recommended to reduce errors in your code.  But then, the code given by Microsoft for Sub Main above will neither compile nor run.  You need the extra line above, Dim Temp&, to define the long integer variable Temp&.

Above, we see a Function called from Visual Basic code.  You can also call it from the Excel spreadsheet, just as you would with a built-in function.  For example:

Note that the type declaration character (“&” for Long Integer) is not used here.  Thus, we use =Celsius(A2) and not =Celsius&(A2).