What is a VBA Procedure, Sub Procedure, Function Procedure?

What is a VBA Procedure?

• The word Macro is slang for the word VBA procedure. It is a throw back to pre 1994 Microsoft Excel.

• A procedure is defined as a named group of statements that are run as a unit.

  • A statement is simply 1 complete line of code.

• VBA procedures are used to perform tasks such as controlling Excel’s environment, communicating with databases, calculating equations, analyzing worksheet data, creating charts…etc

• A VBA procedure unit or block consists of a procedure statement (Sub or Function) and an ending statement with statements in between.

• A VBA procedure is constructed from three types of statements: executable, declaration and assignment statements. The statements between a procedure’s declaration and ending statement (i.e. Sub and End Sub) perform the procedure's task; what you are trying do.

  • For example, the Sub procedure pictured below commands Excel's Sort feature on the active worksheet when it is run.

• Procedures are typed and stored in a Module

• Procedures are executed or run in order to carry out their statements. When a procedure is run, its statements (i.e. lines) are executed in a top-down line by line fashion performing operations. Think of reading a book page. Note that typing a procedure in a module does not run it. You must do this after typing it by variety of different methods. Until you run it, it is just basically text sitting in a document.

• VBA has two basic types of procedures that you can create: Sub procedures and Function procedures.

Sub Procedures

• Sub procedures are written when you want to command Excel like creating a chart, analyzing data, coloring cells, copying and pasting data...etc. A simple Sub procedure is pictured below.

VBA Sub Procedure

Function Procedures

• Function procedures are created when you want to make your own custom worksheet functions or perform a calculation that will be used over and over again. Note that Sub procedures can also be used to do calculations. A simple function procedure is pictured below.

VBA Function Procedure

What VBA Procedure to Write

• So if you want to do a task in Excel, you write a Sub procedure, if you want to write a custom worksheet function, you write a Function procedure. Are their grey areas between the two where they can do the same thing, yes, but do not worry about them when first starting out.

<<What is a VBA Module and How is It Used?                How is VBA Used?>>