Macros And Functions

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.

Why You Can't Call Macros From Worksheet Cells The reason you cannot have a formula like =IF(A1>10,MyMacro)in a worksheet cell is because Excel must keep track of which cells are dependents and precedents of which other cells. It must do this in order to calculate the worksheet in the proper order. VBA code which could change worksheet cells could irreversible confuse the order of calculations. Therefore, Excel forbids code called from a worksheet cell from changing anything in the Excel environment. A FUNCTION procedure can only return a value to Excel, nothing more.