Start using Code VBA

Code VBA helps you create better Visual Basic for Applications code faster. It helps inserting VBA statements or complete blocks of code. Additionally it gives you direct access to Office VBA documentation in case you are in the dark about details of the VBA language.

Tips if this is the first time you work with VBA
You should have at least some understanding of programming and initial knowledge of VBA. If this is not the case you may want to make use of our tutorial pages on programming VBA or free e-books to get you started. If you have trouble finding the Visual Basic Editor window (VBE) - it can be started under the Developer Tab on the ribbon. If you can't find that, read How to: Show the Developer Tab on the Ribbon

Tips if this is the first time you work with VBA

You should have at least some understanding of programming and initial knowledge of VBA. If this is not the case you may want to make use of our tutorial pages on programming VBA or free e-books to get you started.
If you have trouble finding the Visual Basic Editor window (VBE) - it can be started under the Developer Tab on the ribbon. If you can't find that, read How to: Show the Developer Tab on the Ribbon

Code VBA context sensitive menu

The Code VBA setup adds a toolbar in the Visual Basic editor VBE . To really increase productivity (speed), make a habit of activating the menu using your keyboard Alt-key: Alt-C » Declare ». That way you don't have the extra movement to the mouse and back.

Open a code module

When you start the VB Editor and no module is open you will notice most menu items are disabled. The reason is most Code VBA actions are about inserting code, so first you need to open a code module. By disabling menu items which can't be used in the current coding context we make it easy to find which coding features you may consider. As an example if your cursor is not inside a procedure you will not be allowed to insert a procedure call, therefore the menu that gives you access to all VBA procedures is disabled.

Understanding the menu organisation

Most of the items on the menu are concerned with inserting blocks of code. The following sections will walk you through a representative sample.

Click this Start demo screencast to start a demo which should give you a basic idea of how it works.

On nearly all menus levels you will find two extra features at the bottom:

The ability to Add and Manage fragments
Direct access to relevant reference pages by Microsoft and Tutorials

The core of Code VBA supports declaration of procedures and variables, calling procedures in your project or VBA built-in, insertion of handling error and so on. For Office application objects and libraries (e.g. DAO) has special add-ons available, e.g. for MS Access. These can be found further down in the Code VBA main menu popup.

Tip: F1
Pressing F1 makes a tooltip available that shows the what procedure code will be inserted and a description of the procedure or class - if available. If you see the text hyperlinked you can press F1 again which opend the relevant help page in the browser.

Declare

Alt-C » Declare » opens the submenu used for declaring procedures, variables and the like.

Declare Sub or Function

Alt-C » Declare » Function » Boolean inserts a function which returns a value of the selected type (here Boolean). The cursor is automatically placed in the position where you can start typing the function name, here: IsMember


Function IsMember() As Boolean

End Function

Note
VBA intellisense will be useful to quickly insert the procedure name to assign it a returnvariable. Here: type `ism` followed by `Ctrl-Space` allows you to select the name IsMember which then gets inserted.

Add argument to a procedure

In the previous example you may want to give the function an argument strName As String. To do this

Move the cursor between the brackets in the position where you want to add the argument
Alt-C » Declare » Variable » String inserts str As String
Add the argument name to the prefix str - here Name

Note
The String prefix 'str' has been added automatically, see VB naming conventions. Again, having these standardized prefixes helps speeds up coding when you use the intellisense feature.

Inserting control statements

what is on the If menu Direct below Declare on the menu are the conditional control statements If and Select and the Iteration statements Do and For. These are discussed in detail on the page Adding Control Structures. Note in the image showing the If menu that common uses have been added as fragments appearing automatically on top. Fragments can be recognized by the fact that they don't have a shortcut key, so you will select them using Arrow and Enter.

Conditional statements

Under Alt-C » If » Is » ... a list of common built-in VBA functions is made available that work on variants such as IsNull and IsEmpty. If you feel unsure which function is appropriate in your case click in the menu (bottom) on the Reference 'Info on Variant types' Reference Info on Variant types which has links to the pages which explains each under section 'Get information about a variant.'

Insert procedure call

In the menu, below the control statements, is the VBA menu group which gives access to both the procedures in your project and the VBA built in functions. Below example is code that would be inserted when you insert a DateDiff function - specifies the number of time intervals between two specified dates.

Alt-C » VBA » Date » DateDiff » Year

Because of the logical organization of the menu, you will soon remember the keyboard shortcuts to get to the function you need.

Working with dates can be tricky. A reference to MS documentation for the Date related stuff is included in the menu reference page .

Notes
Here all arguments are explicit which makes the code more reliable between Office versions and self-documenting. If you don't like explicit arguments you can change this behavior using Preferences in the Code VBA menu. The code inserter tries to match the arguments with variables already available in the procedure based on type. If no variables can be matched, new variables are declared and a beginning of a value assign statement is added. The variable names are based on argument and procedure name. If default values for optional arguments are known, these are made explicit. If no default value is known for an optional arguments, the argument is not included.

Notes

Here all arguments are explicit which makes the code more reliable between Office versions and self-documenting. If you don't like explicit arguments you can change this behavior using Preferences in the Code VBA menu.
The code inserter tries to match the arguments with variables already available in the procedure based on type. If no variables can be matched, new variables are declared and a beginning of a value assign statement is added. The variable names are based on argument and procedure name.
If default values for optional arguments are known, these are made explicit. If no default value is known for an optional arguments, the argument is not included.


Dim dtDate1 As Date: dtDate1 =
Dim dtDate2 As Date: dtDate2 =
Dim lng As Long
lng = DateDiff(Interval:="yyyy", Date1:=dtDate1, Date2:=dtDate2, FirstDayOfWeek:=vbSunday, FirstWeekOfYear:=vbFirstJan1)

Other areas of VBA support

Some major VBA areas have been promoted to the first level of the menu to make them more prominent:

User interaction concerns the ways the user interacts with the software. This can be through specially designed forms, but also using message and input boxes. Another interaction type concerns file and folder selection dialogs.