Procedure Builder

The Procedure Builder (ALT + P) quickly generates a procedure with specific settings (ScreenUpdating, etc.). You can save your default procedure settings to save time when writing new procedures.

Output:

procedure builder output
Use Cases
  • Quickly create sub procedures or functions with your desired settings.
  • Save settings for future use.

Save Settings as Default

To save your current settings as default (for easy access in the future), click the bottom in the bottom-right of the builder:

procedure builder save settings

Description of Settings

Read below for a description of the settings available with the Procedure Builder.

Functions and Subs

When coding (VBA or any other language) typically code is stored in procedures. Procedures are just blocks of code that can be called (ran). You are probably familiar with the concept of recording a Macro in Excel. When you record a Macro, you’re actually just creating a VBA procedure.There are two types of procedures in VBA: Subroutines (Subs) and Functions. Subs and Functions have three primary differences:

  1. Functions return a value
  2. Functions can be used in Excel formulas
  3. Functions can only be called via other procedures (or Excel formulas). You can not assign a function to a command button or access them via the Macro list.

Completed Functions and Subs look like this:

example procedures

Because Functions return values, you must tell VBA what type of value to expect. Notice above we declare the Function to be a Boolean value (TRUE or FALSE):

Function Ex_Function() As Boolean

Instead, you can declare a Function as a Variant type which will accept all value types:

Function Ex_Function() As Variant

The Procedure Builder will set all Functions to be type Variant.

Toggle between Sub and Function here:

procedure builder sub function

Name and Description

It’s important to descriptively name procedures and add comments to describe what a procedure does. Your code may make sense to you now, but it may not make sense to someone else (including your future self). It’s best practice to add comments for every procedure you create.

Add Names and Comments Here:

procedure builder name description

Private vs Public (Advanced)

By default, procedures are accessible from the Macro list (subs only), and can be called by any other procedure. The Private declaration hides the procedure from the Macro List and sets the procedure to be only accessible from the current code module (note: technically you can get around this by using Application.Run).

To make the Private Declaration, add Private before the procedure name:

Private Sub Ex_Sub

You can also declare entire modules as “private”. By adding Private Module to the top of your code module:

Private Module

When marking an entire module as private you hide all procedures from the Macro list, but they can be easily referenced from other modules.

Define Public or Private Here:

vba code module

Settings

All applications (Excel, PowerPoint, Outlook, etc.) have application-level settings. Some of these settings are very important for VBA coding. Important: Application-level settings are independent of procedures. They will not manually “re-set” after a procedure finishes. Instead they must be manually changed again when desired.

The Procedure Builder allows you to set the following application-level settings:

Note: “Display Message Box After Complete” is not an application.setting, we will discuss it in the next section.

Disable ScreenUpdating – The screen will not refresh while the procedure runs. This can drastically cut down processing time.

Disable Automatic Calculations – This is an Excel setting that turns off Automatic Calculations. This can drastically speed up processing time.

Disable Alerts – Warning messages and prompts will not be shown to the user. Instead VBA will process the default action.

Disable Events – Events are “triggers” that can cause code to run (Exs. workbook open, sheet activate, cell change, etc.). If your workbook has events, you probably want to disable events while your code runs to prevent unintended behavior (including endless loops).

Message Box On Procedure Completion – For procedures that take more than a few seconds to run, it often makes sense to display a message box upon completion. This alerts the user that the procedure has finished running. For our clients, we generally set up message box alerts for all procedures that take longer than 15 seconds to run.

Error Handling – By default, VBA will stop executing code if it encounters an error and will prompt a user to “Debug” the code. This is desirable during testing/development. However, there are many scenarios where it’s desirable to handle errors in other ways.

Error handling is defined at the procedure level. It’s not an application-level setting. So any procedure that needs the non-default error handling needs an error handling declaration:

  • On Error Resume Next: VBA continues by skipping the line(s) containing errors and proceeds with the next line of code.
  • On Error Goto Error_Handle: On error, VBA will “goto” a defined section of the procedure (‘Error_Handle’ in this case). This is useful to display a message box on an error, repeat/try again, or skip a section of code.
  • On Error Goto End: This is actually the same as the previous setting, except here “go to” goes to a section at the end of the procedure.

To restore the default error handling, enter the following:

On Error Goto 0

This is desirable during testing/development. However, there are many scenarios where it’s desirable to handle errors in other ways.

Note: It’s “best practice” to avoid intentionally allowing errors and using error handling to process them. Instead you should use smarter code to avoid generating VBA errors in the first place.

Procedure Builder: Error Handling:

< Previous

Array Builder

Next >

Class & Property Builders

Start Automating Excel

automacro
Get AutoMacro