Course Code:60.01
Course Subject:Excel Visual Basic for Applications
Course Title:Excel VBA Introduction
Aims:To provide the student with the knowledge and confidence to write Visual Basic Code in the Excel environment.
Introduction:Visual Basic for Applications is based on the very popular Visual Basic programming language. It provides a means of controlling an Excel application (Workbooks, Worksheets, Ranges and Cells). The key difference between VBA and Visual Basic is that VBA resides within Microsoft applications like Excel, Word, Visio etc. whereas Visual Basic is a standalone application. VBA code allows the programmer to implement functions and take control of Excel objects way beyond conventional Spreadsheet use. This course is intended to provide the student with sufficient knowledge and skills to implement VBA code in Excel while also understanding the security and power considerations. The course covers the key areas of writing VBA code with reference to Excel and the control of Workbooks, Worksheets, Ranges and Cells. The inherent flexibility of VBA makes it a subject that can be studied for years so one of the main goals of this course is to provide the student with a base upon which to build. On completion the student will be able to write effective, efficient code and take their Excel spreadsheet use to a whole new level..


The learner will be able to:

Assessment Criteria

The learner has achieved this objective because he/she can:

1. Create Visual Basic Code in the Excel VBA environment

  1. Consider the Security implications regarding VBA code

  2. Open and access the Personal Macro Workbook for storing VBA code

  3. Use the Visual Basic Editor

  4. Understand the structure and meaning of Subroutines

  5. Use the Range Object to read and write values to cells

  6. Use the Active Cell to control cell contents

  7. Use Operators to calculate new values

  8. Use Variables to hold values

  9. Understand the concept of and use Constants

2. Build Formal Control Structures

  1. Use Comparison operators

  3. Construct For Loops

  4. Employ Do loops

3. Create and Control single and multiple Objects

  1. Understand the concept of Objects

  2. Understand the concept of Collections

  3. Refer to Objects and Collections in Code

  4. Set and Reset Object Properties

  5. Initiate Object Methods

4. Use Visual Basic Functions

  1. Use the InputBox Function

  2. Use the MsgBox Function

5. Trigger Code to Run

  1. Use the Menu Bar

  2. Use the Keyboard Shortcuts

  3. Use the Toolbar Icons

  4. Use the Menu Bar

  5. Use an ActiveX Control Button

  6. Use an Area, Hot Spot, or Graphic Object (Shape)

  7. Write an Excel Add-in

  8. Use a VBA Event

  9. Manage VBA code through use of the Macro Window