{"id":33,"date":"2014-01-24T17:01:22","date_gmt":"2014-01-25T00:01:22","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=33"},"modified":"2016-10-04T11:30:46","modified_gmt":"2016-10-04T18:30:46","slug":"procedures","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/procedures\/","title":{"rendered":"Procedures and Functions"},"content":{"rendered":"

A procedure is a group of statements called and executed as a unit to perform a task. Note: a broader concept in programming language is a function, however we will refer to procedures here to avoid confusion with a function<\/em> procedure in VBA.<\/p>\n

1. Designing procedures<\/h2>\n

When coding\u00a0a procedure it is necessary to clearly know two things. First, what type of inputs and outputs must the function produce? This is called the signature<\/em> of a\u00a0procedure:<\/p>\n

\u00a0Input<\/span> DataType\u00a0\u2192<\/span><\/strong> Output<\/span> DataType<\/p>\n

Secondly, what is\u00a0the purpose<\/em> of the procedure?\u00a0\u00a0Always write it in a one-line description that you can include as a comment in your code. A good programming practice is\u00a0to define procedures that perform one specific task.<\/p>\n

2. Sub and Function Procedures<\/h2>\n

In VBA there are two type of procedures: Subs (like sub-routines) and Functions.<\/p>\n

Sub procedure<\/strong>: it does not return a value to the statement that called it, although it may perform other operations like modifying cells in a worksheet.<\/p>\n

Sub SubName<\/span> (arg)
\nstatements
\nEnd Sub<\/p>\n

To call a procedure within another procedure you just put the procedure name in a code line (with any necessary arguments), or you can use the Call statement:<\/p>\n

Call<\/span> SubName<\/p>\n

Function procedure<\/strong>: this can return a value to the statement that called it, therefore you need to set the the output data type in the function declaration.<\/p>\n

Function FunctionName<\/span> (arg) as DataType<\/span>
\nstatements
\nFunctionName<\/span> = value<\/span>
\nEnd Function<\/p>\n

To set the value that the function should return, you have to define a variable with the same name as the function within the function body.\u00a0 To call the function you just set a variable with the same data type of the function.<\/p>\n

y = FunctionName(x)<\/p>\n

3. Passing Arguments to Procedures<\/h2>\n

You can pass values to a procedure for processing within the procedure. These values are called arguments.<\/p>\n

Sub ProcedureName<\/span> (ArgName<\/span> as DataType<\/span>)
\nFunction FunctionName<\/span> (ArgName<\/span> as DataType<\/span>) as DataType<\/span><\/p>\n

Optional Arguments<\/strong><\/p>\n

You can create procedures with optional arguments using the Optional statement.<\/p>\n

Optional<\/span> ByVal<\/span> ArgName as DataType = DefaultValue<\/span><\/p>\n

You must also declare the default value and any arguments that follow an Optional argument must also be optional<\/p>\n

Procedure Scope<\/strong><\/p>\n