Efficient procedure call insertion using Code VBA

Typing procedure calls is one of the main things you do when writing code. Code VBA provides several ways to insert procedure calls easily without having to type in the procedure and its arguments, which are presented here.

Types of statement involving procedure calls
Menu continuations vs procedure call builder

Types of statement involving procedure calls

Sub procedure calls

Subs and Application methods In the following examples are taken from Application methods because they work the same and there are fewer built-in Sub procedures.

Procedure call insertion using continuations menu

An example of a situation where a procedure has 1 parameter is Application.ActivateMicrosoftApp. In image below shows it selected with the menu continuation showing possible Office applications.

Application.ActivateMicrosoftApp menu continuation

Insert procedure call with no parameters The simplest procedure has no parameters, of which there are only few. As an example we have the Application.Quit. In Excel, this would be directly selected from the Code VBA toolbar: Excel » Methods » Quit.

Procedure call insertion using procedure call builder

Selecting a procedure with more than one parameter will open a procedure call builder.

An example procedure with more than one parameter is copying a file. Using Code VBA you would select this from the VBA menu under 'Manage File'. The image on the left shows how you can follow the menu to find the procedure. The ... signals selecting this menu item will open a dialog. Further, the tooltip gives an initial version of the code it will produce.

After having selected the required item, the procedure call builder (image to the right) opens and shows two parameters in a property sheet, Source and Destination. The parameters can be set with suitable values obtained from the code context based on the parameter type. One of these will be the ability to create new variables of that type, e.g. strSource As String. A preview of the code is in the bottom textbox. Pressing OK inserts the code.

Function procedure call

Functions are generally used to produce a value. These values can be assigned to variables or object properties. VBA built-in functions, custom functions in your project, Object properties and functions inside the VBA code library can all be found through the Code VBA menus.

Having found the required function, you can have it inserted with existing or new variables set for the function's parameters.

Function procedure call in an assigment statement

A function procedure call may be used in an assigment statement:


strReplaced = Replace(Expression:=strWelcome, Find:="Hello", Replace:="Hi")

The above statement is part of code that would be produced by the Replace Function call builder. The line can be produced from the VBA » String » Replace menu starting from an empty line, or after the strReplaced= . The Function call builder works slightly different in these cases.

Firstly, In the empty line version (left) there is an extra field 'Return variable' (top left) to be specified.

Replace menu starting from an empty line — Replace function call insertion starting from an empty line.

Second, selecting the function is different with the type of the return variable already known. Under the VBA menu only items are included that return the appropriate type (here String).

Boolean function procedure calls as condition expressions

Condition expressions in If and Loop statements generally use boolean functions (in addition to comparison expresssions). The image below shows the continuations Code VBA proposes including numerous VBA built-in functions and custom functions included in the VBA code library.

boolean assignment in a loop statement block