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
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 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.
|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.
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
|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
|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
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.
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' which has links to the pages which explains each under section'Get information about a variant.'
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 .
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.