Procedure Builder
What does it do?
The Procedure Builder (ALT + P) quickly generates a procedure based on your desired settings and inserts that procedure directly into the active VBA code module.
Procedure Builder:
Output:
How to Access it?
Shortcut: ALT + P
VBA - Intro To Procedures / Code Builder Walkthrough
This section will serve as an introduction to Procedures in VBA and contains a walk-through of AutoVBA's Procedure Builder.
Save Settings as Default
The Procedure Builder allows you to define default settings that will appear each time you load the Procedure Builder. It's great if you're a beginner VBA user: research and set your settings once, and then use the Procedure Builder to generate all your procedures in the future. No more remembering what settings are needed!
This feature is located at the bottom right of 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: a Subroutine (Sub) and a Function. Subs and Functions have three primary differences:
- Functions return a value
- Functions can be used in Excel formulas
- 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:
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:
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:
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:
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:
