A procedure with a Private scope can be referred to only by other procedures in the same module, whereas procedures with a Public scope can be referred to by any procedure in any module. Unless otherwise stated, every procedure is a Public procedure. If you're writing a set of macros you don't want to be seen (or run) by anyone who knows how to open the Macro dialog box, you can put an Option Private Module statement in the declarations section at the top of the code module to make very procedure, even those that use the Public keyword, private.



You can run the procedure by linking the macro to a trigger, but you can also run the macro by calling it from within another macro. To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.

[Public] or [Private] Sub ShowTheTime

MsgBox (NOW ())

End Sub

The second optional element in a procedure declaration is whether or not to make the procedure retain the values generated by previous executions of the procedure. When you add the Static keyword to the Sub statement, Excel knows to create a durable storage space for each variable and to maintain that space and its contents until you close the workbook.

Defining Function Procedure

A Function procedure is similar to a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure. As with Sub procedures, you can make your Function procedures available to procedures in every other workbook by putting the Public keyword in front of the declaration.

Running Function Procedure

If you want to run a Function procedure, you can do so using one of the following methods:

1. Use the Function procedure in a formula.

2. Call the Function procedure from within another procedure.

3. Call the Function procedure from a cell on a worksheet.

Your Function procedures don't appear in the Macros dialog box. The third way to run a Function procedure is to call it from a cell on a worksheet. To do so, you can call it the same way you would call any other function (for example, =Amortize (ActiveCell.Value)).

There might be times where you want to operate on a value that's stored in a variable in a procedure, not in a worksheet cell. That's when you need to tell the procedure the values on which you want it to operate; those values are called arguments. The element in the parentheses, curItemPrice as Currency, is the name and data type of the variable that's being passed to the function. Where is the value calculated by the procedure stored? It's stored in a variable with the same name as the Function procedure.

Function MarkupPrice (curItemPrice as Currency) as Currency

MarkupPrice = curItemPrice * 1.8

End Function

You can consider using the ByVal keyword to have the procedure use a copy of the data and not the original cell value