Wednesday, March 25, 2015

How To: Access the Visual Basic Environment (VBE)

Introduction

This is probably the most basic of my posts.  However, I find it's important information that I often want to include in other posts.  For that reason, here it is.

VBE: What and why?

The VBE is the development environment for customization of MS-Office application (e.g. MS-Word, MS-Excel). Visual Basic for Applications (VBA).  It is the environment for creating
  • User Defined Functions, aka UDFs, that can be invoked by the user.  For example, a UDF may return a value based on other information in the file.
  • Subroutines or "Subs" perform behind-the-scenes actions such as opening and merging files.
  • UserForms are created to provide interactive input and reporting.
  • Event Procedures are Subs triggered when one of a defined set of events occur.  For example, if an email is to be sent when a workbook is saved and closed, then the Workbook_Close event would include code to send the email (or call a different Sub to do this).
  • Links to other files and applications (binding)

VBE: Where

The VBE presents and helps manage the "project" of an MS-Office file.  Therefore, the VBE only has anything in it when a file is open
Empty VBE
When a file is opened, it can be seen in the Projects area
VBE with Project


Opening the VBE

Accessing the V BE can be done only when there is application is open. This can be done when in the  User Interface (UI) of the host application by one of these means.

Alt-F11

Hold the ALT key and click F11.  This works under all conditions, even when no files are open.

Alt-F8

This works only when there are Macros available to run from the User Interface or you want to create a new macro. This is because the Edit button is not available unless there is something to edit
Macro Dialogue: No Macros Available
 If there are any macros available, select the one you would like to edit and click the Edit button.
Macro Dialogue: Edit button enabled
To add a new macro, and thus make the VBE available through the ALT-F8 method, start typing in the Macro name: text box. As soon as the first letter is typed, the Create button is enabled.  When the Create button is clicked the VBE is opened to the macro you've just created.  This works when there are already macros available in the list, too.
Macro Dialogue: Enabling the Create button

Developer Menu

This can be performed on if the Developer menu has been enabled, Clicking on the Macros icon acts like the ALT-F8 method described above.  Clicking on the Visual Basic icon opens the VBE.
Ribbon with Developer menu selected and highlighted, Visual Basic and Macros icons highlighted