The Visual Basic Integrated Development Environment

 

Some Background.  Everyone in this class has supposedly taken CS 110 where Visual Basic is used to develop the basis of working with databases.  Because of this our review of Visual Basic will be cursory.

While it is possible to gain access to spreadsheets from within the Visual Basic you used in CS 110, we will focus on using VB from within Excel.  To do this you must first open a workbook in Excel.  Now you want to work through the following steps.

  1. Click on the View menu.

  2. Select the Toolbars item.

  3. Click on the Visual Basic item in the submenu that appears.

You should see the following toolbar appear someplace on your screen.

 

 

The meaning of the buttons from left to right appear in the following table.

 

Icon Purpose
Execute or run the macro
Record the macro as you run through the steps in the spreadsheet
Resume the execution of the macro
Start the Visual Basic Editor within this workbook
Open the toolbox of the controls to put on UserForms.
Work in design view

 

You should probably dock this toolbar up with your other toolbars.  We will be using it all semester.

Now you want to click on the Visual Basic Editor icon to get you into the IDE.  The image below for the Excel VB IDE looks very similar to the Visual Basic 6 IDE.

 

 

Notice the
  • Project Explorer where you can manage the overall project and its components
  • Properties Window where you can modify the properties of the currently selected object
  • Code Window where you can edit Visual basic code

There are plenty of other windows that can be viewed in this IDE but rather than continue with what should be review we will start with some relatively simple implementations which should also help you review.

Procedures and Routines.  I will use the word routine to denote all of the different approaches for coding.   Within these we will focus on Macros, Sub Procedures and Function Procedures.

 

Macro

 We will use the word macro to describe a routine that a developer can create using steps that could actually be performed directly on the spreadsheet.  Developing a macro implies that this sequence of spreadsheet steps is something that will have to be used fairly often and someone would prefer to have this macro to implement the steps rather than repeat the steps each time.

 

We will focus on developing macros in the next webpage.  It is important to remember that macros have some definite limitations.  For example you can't use loop structures such as For - Next, Do - Until or Do - While  Loops in macros.  You can't use Select - Case or If - Then decision structures either.  To expand on the capability of macros we will also make considerable use of two other types of routines described in the following table.

 

Sub Procedure

 These are how you do the rest of your necessary operations.  It is composed of a set of Visual Basic instructions that perform some action.

Function Procedure

 This is just a procedure that returns some value or values.