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

Since 1998

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

Excel VBA Tutorial

Presented below is a brief introduction to VBA procedures and their types.

A VBA procedure is the main building block of an Excel VBA program. Sub and Function procedures are the main procedure units used when creating VBA programs.

What is a VBA procedure?

A procedure is defined as a named group of statements that are run as a unit. Visualize a paragraph in a Word document now put a name at the beginning. A statement is simply 1 complete line of code. In Excel VBA, procedures are often referred to as macros. The word macro is slang and is a throw back to pre VBA times in Excel when you used Macro sheets to create code in Excel. VBA procedures are used to perform tasks such as automating Excel’s environment, communicating with databases, calculating formulas, analyzing worksheet data, creating charts, inserting and deleting columns...etc.

What type of statements are in a procedure?

A VBA procedure is defined by a beginning declaration statement (Sub or Function) and an ending statement with statements in between. The block portion of a VBA procedure is constructed from three types of statements: executable, declaration and assignment statements. The statements are placed between a procedure’s beginning and ending statements (i.e. Sub and End Sub) and perform the procedure's task; what you are trying accomplish.

Excel VBA Modules
Scroll Picture > > >
In the Excel VBA example above, a Sub procedure has been constructed that copies the table from the active worksheet, pastes it to a new worksheet excluding formulas, then sorts it. All three of the statement types can be seen in the code between the Sub and End Sub statements.

How does a VBA procedure work and where is it stored?

VBA procedures are typed and stored in a module that is viewed in the VBA Editor. Modules form projects and a project is stored in a workbook file. There is only one project per workbook file.

Procedures are executed or run to perform their tasks. 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 page in a book. 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.

Excel VBA Build Shape
Scroll Picture > > >
In the Excel VBA example above, a Sub procedure has been constructed that draws a shape on the active sheet at cell B6, then puts text inside of it. Think of the possibilities here. The procedure runs top to the bottom so the order that the code appears in matters in this particular example because certain things must happen before other things.

The types of VBA procedures used in Excel VBA.

Excel VBA has two basic types of procedures that you can create: Sub procedures and Function procedures. A third type can also be made called a Property procedure.

Sub procedures are written when you want to command Excel like creating a chart, analyzing data, coloring cells, copying and pasting data...etc. Sub procedures can be executed via shapes, pictures, shortcut keys and events. A simple Sub procedure is pictured below.

Excel VBA Build Chart
Scroll Picture > > >
In the Excel VBA example above, a Sub procedure has been constructed that creates a chart on the active sheet from the data set on the page. A Sub procedure was selected due to the fact that Excel was being commanded.

Function procedures are generally created when you want to perform some type of calculation that will be used over and over again in your computer code. The word calculation here has a broad definition meaning anything you want to generate. You can also make your own custom worksheet functions that run from formulas. What makes a Function procedure different from a Sub is that it returns a value through its name. A simple function procedure is pictured below.

Excel VBA Tally Red Cells Function
Scroll Picture > > >
In the Excel VBA example above, a Function procedure has been constructed that sums all the cells that have been colored red. It is run from the formula in the D7 cell. Function procedures can execute thousands of lines of computer code from a single cell and provide unbelievable data processing and modeling capabilities.
< Back
Next >
Contact Us Newsletter
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2020

EMAGENIT All Rights Reserved