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
You would call this function from a worksheet just like any of Excel's built-in
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)
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.