The words macro and function are often used interchangeably to
refer to procedures written in Visual Basic For Applications (VBA). However, it is
important to distinguish between SUB procedures, or macros, and FUNCTION procedures.
The difference between the two are how they are invoked, and their ability to
change the working environment.
A procedure declared with the FUNCTION keyword can accept passed arguments and return a
value, and can be invoked directly from a worksheet cell. A function procedure
cannot change the contents or format of any cell. For example, a simple procedure
could convert temperatures from Fahrenheit to Celsius:
Public Function Celsius (Degrees) As Double
Celsius = (Degrees-32)*(5/9)
End Function
You would call this function from a worksheet just like any of Excel's built-in
functions:
=CELSIUS(A2). You do not need to declare the return
type of a function. By default, Excel will treat the return type as a Variant.
If you are using the function in the same workbook in which you coded it, you can
call it in the normal manner. If it exists in another workbook, you must include
the name of the workbook when you call it:
=SomeBook.xls!CELSIUS(A2)
The workbook SomeBook.xls must be open (though it
may be hidden) in order to call the function.
A function or sub called directly or indirectly from a worksheet cell cannot change another cell's value. For example, it you tried to include
the statement Range("A3").Value = 123 in a
function procedure, it would cause an error and the #VALUE!
error would be returned to the worksheet cell.
Procedures declared with the SUB keyword can accept passed arguments, but they cannot
return a value, and cannot be invoked directly from a worksheet cell.
Generally, you cannot call a
macro directly from a worksheet cell. For example, the following code does not
work: =IF(A1>10,MyMacro).
You can simulate the statement =IF(A1>10,MyMacro)by
using the worksheet's Change event.
Private Sub Worksheet_Change (ByVal Target As Excel.Range)
If Target = [A1] Then
If Target.Value > 10 Then
MyMacro
End If
End If
End Sub
For more information about using event procedures in Excel97 and later,
see the Event Procedures page.
In both Function and Sub procedures, the Public keyword
allows the procedure to be called by any other procedure, in any code module. The Private keyword indicates that the procedure may be called only
by procedures in the same code module. Public
is the default.