Saturday, February 7, 2009

VBA Macro Excel Tutorial: Sub Procedure

A procedure is a series of VBA Macro Excel statements that resides in a VBA module. Sub procedure syntax:

[Private | Public] [Static] Sub name ([arglist])
[Exit Sub]
End Sub

Private (optional) indicates that the procedure is accessible only to other procedures in the same module.

Public (optional) indicates that the procedure is accessible to any procedures in any modules.

Static (optional) indicates that the procedure's variables are preserved when the procedure ends.

Sub (required) indicates the beginning of a procedure.

instructions (optional) represents valid VBA code.

Exit Sub (optional) a statement that forces an immediate exit from the procedure.

End Sub (required) indicates the end of a procedure.

Scope of Sub procedure

A procedure with a Private scope only accessible by procedures in the same module, whereas procedures with a Public scope can be accessed by any procedure in any module. By default, every procedure is a Public procedure.

Executing Sub procedures

There are many ways to execute Sub procedure:
  • With the Run -> Run Sub/UserForm command. Or by pressing the F5 shortcut key.
  • Form Excel's macro dialog box, which you can open by clicking Tools -> Macro -> Macros. Or by pressing the Alt+F8 shortcut key to access the Macro dialog box.
  • By clicking a button or shape on a worksheet. You must have the procedure assigned to the button first.
  • Form another procedure you write. To call a Sub procedure from another procedure you must type the name of the procedure and include arguments values, if any.
  • When an event occurs. For example, when you open a workbook, saving a workbook, or closing a workbook, etc.
  • From a toolbar button.

Related posts:
If you like posts in this blog, you can to support me :)

No comments:

Post a Comment