What is a VBA procedure, Sub procedure, and Function procedure? - VBA Tutorial

What is a VBA procedure, how is it declared, and how is it named?

A procedure is defined as a named group of statements that are run as a unit. A VBA procedure is defined by a beginning declaration statement of Sub, Function, or Property and an ending statement of End Sub, End Function, or End Property. Your code is placed between the start of the procedure and the end of the procedure. In a procedure, a statement is simply a complete line of code. The following code illustrates these simple concepts:

Sub ClearCell( )
    Range("A1").Clear 'Statement
End Sub

A VBA procedure is tracked by its name. The name of a procedure is typed after the Sub, Function, or Property syntax. Its name may be up to 255 characters long, alphanumeric, and you can use an underscore in the name but no spaces. Try to keep your procedure names short but meaningful as in being able to look at the name and figure out what purpose it generally serves. If you create long names, you will tire of typing them.

You can have many different procedures in the same module >, just make sure to name them uniquely. Try to group procedures in the same module that relate to each other in task. It helps you locate things in large programs as well as makes debugging easier.

VBA procedures are used to perform many tasks such as performing calculations, analyzing worksheet data, creating charts, formatting tables, inserting and deleting columns...etc. They can also be used to develop sophisticated Excel apps that rival commercial software, but are built at a fraction of the cost.

What you want to do with Excel VBA determines the type of procedure to build. A common mistake is that all the code in a module runs at once. In reality, only one procedure runs at any given time and only when it is specifically executed by clicking a button, call statement...etc.

What type of statements are in a procedure and typing tips.

Each line of code in a procedure is called a statement. 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.

Normally you do not refer to code so formally as the three types stated above. Most of the time code is identified by keywords and the word statement. For example, your going to write an IF statement or a FOR...NEXT statement. The keywords used to identify lines of code color blue when typed in a procedure.

When you type a statement in a procedure, VBA will generally handle the spacing. Just type the code and follow basic grammar rules like leaving a space between words. It helps to look at a lot code examples when first learning to type. You will learn to become a syntax reader meaning you will observe every character when looking at VBA code.

One place you really want to focus your attention when typing is in between a set of quotes in a line of code. Quotes indicate a piece of text which could be a name of an Excel element you are tracking or something you want to compare in say logic. When typing the name of an element you are trying to command, the text is not case sensitive. If comparing text in logic, the text is case sensitive by default so if you compare "Text"="text" then that is False. Everything is space sensitive so if you type an extra space in the quotes, the code will error when run.

The following code if typed in a module will auto space around the , >  and = signs. The cell references in the "" could be upper or lower case, it does not matter. You want to type spaces between End and IF and Sub and the name. VBA and Excel commands will take care of how they are capitalized.

Sub ColorCell( )
   If Range("A1").Value > 1000 Then
        Range("A2").Interior.Color = RGB(255, 0, 0)
   End If
End Sub

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

VBA procedures are typed and stored in a module that is viewed in the VBA Editor > as previously discussed in this tutorial.

Modules form projects and a project > is part of a workbook file. There is only one project per workbook file unlike other programming environments. Save the workbook, save the VBA code.

When saving the workbook, make sure it is saved as a macro enabled workbook which is a .xlsm file. Saving it as a .xlsx file will strip the VBA code from it when it is closed. Distribute the workbook and the code goes with it.

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.

A simple way to run a Sub procedure is to type it in a Standard module, click inside the procedure boundaries with the mouse, and press the F5 function key. You do this a lot when debugging it before assigning it to a button. Note this technique will not work if the procedure has an argument list, names between the ( )'s like Sub format_table(ByVal ws As Worksheet).

How are Sub procedures used in VBA.

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 run via shapes, pictures, shortcut keys, events, and the F5 function key in the Editor. A simple Sub procedure is pictured below.

How are Function procedures used in VBA.

Function procedures are written when you want to perform a calculation in your code over and over again. For example, if you have to calculate x = a + b in your code 25 times and it is spread out over 20 procedures, it would be a pain to have to comb through all your code and find every instance to upgrade. If it is in a Function procedure, you can upgrade it in one place and then call it 25 times.

What makes a Function procedure different from a Sub is that it can return a value through its name as Subs do not. The word calculation here has a broad definition meaning anything you want to generate, not just math.

In addition to running Functions in VBA, they can also be run from worksheet formulas like you would SUM, VLOOKUP...etc. This allows a developer to replace a series of formulas or a complex calculation on the worksheet with a simple Function call. Also, Functions allow you to perform calculations that cannot be performed in any formula including Lambda. A simple function procedure being run from a formula is pictured below.