They are sometimes called user-defined function.Ī function returns a value to the procedure that calls it. Type the name of the procedure in the Name text boxįunction procedures are similar to built-in functions such as Sum().To display the Add Procedure dialogue box: Press Enter and VB inserts the parenthesis after the name and the End Sub line. Type in the word Sub, followed by a space and the Procedure name. Type in the Sub procedure using the relevant syntax.Create or display the module to contain the new sub procedure.
End Sub structure can be typed directly into the code window or inserted using the Add Procedure dialogue box. Private indicates the procedure is only available to other procedures in the same module. Public indicates procedure can be called from within other modules. Sub procedures have the following syntax: This can be done by either recording a macro or entering the code directly into the VB Editor’s Code window. Most Excel tasks can be automated by creating procedures. If procedures are related try and place the words that vary at the end of the nameįollowing these conventions, here is an example of procedure names:.Use the proper case for the word within the procedure name.As procedures carry out actions, begin names with a verb.You should consider these naming conventions when naming procedures: Cannot contain spaces or any of the following characters.Maximum length of the name is 255 characters.The following rules must be adhered to when naming procedures: While rules must be followed or an error will result, conventions are there as a guideline to make your code easier to follow and understand. There are rules and conventions that must be followed when naming procedures in Visual Basic. The procedures can then be called, in order, from another procedure. It is easier to find errors in smaller procedures than in a large one. If you require 10 stages to solve a problem write 10 sub procedures. Function procedures perform a task and return a value, as well as control, to the calling procedure.Sub procedures perform a task and return control to the calling procedure.There are two general types of procedures: When a procedure has finished executing it returns control to the procedure from which it was called. To execute the code in a procedure you refer to it by name from within another procedure. In the Properties window change the name of the moduleĪ procedure is a named set of instructions that does something within the application.Display the Properties window if necessary.To create a standard module in the VB Editor: Standard modules are also used to declare global variables and constants. You should store related procedures together within the same module. Within a project you can create as many standard modules as required. These procedures are usually generic and can be called by another procedure while the workbook is open. Standard modules can be used to store procedures that are available to all forms, worksheets and other modules. In this unit we will look at both Modules and Procedures. In Excel, procedures are stored in objects called Modules. Procedure is a term that refers to a unit of code created to perform a specific task. The following content is an extract from a handbook used on our training courses, when learning VBA Excel.Įxcel VBA Introduction Unit 2 Developing with Procedures and Functions