Polytechnic University, Fall 2007

CS 2113, Programming with VBA/EXCEL

Course Description:


This is an introductory course in computer programming and problem solving for undergraduate students in the Biology/Molecular Science program who have no prior experience in programming in any language. The course covers the fundamentals of computer programming and its underlying principles using the programming language Visual Basic for Applications (VBA). Subroutine and function procedures are created to be run within the Excel environment.


Course Objectives:

1) Knowledge of fundamental programming concepts and structures in the VBA/Excel environment.

2) Ability to formulate solutions to problems and write programs to be run in VBA/Excel.


Prerequisite or co-requisites: None


Restrictions on who may receive credit for this course: Biology/Molecular Science students


This course will be offered (indicate campuses & semesters): Brooklyn, every fall semester



Power Programming with VBA/Excel, S.C. Chapra, Prentice-Hall, 2003. ISBN: 0-13-047377-4.


Instructor: K. Ming Leung


Office (phone): LC 127 (718-260-3380)


Office Hours: Tuesday: 2 -3, Wednesday: 2 3, 4 5, Friday: 10 11, 11 12 (Or simply drop by my office any time.)


Email: :mleung@duke.poly.edu


Computer needs: Laptop with Microsoft EXCEL installed.


Lectures: (approximately)

2.5 hours a week


Recitation: (approximately)

0.5 hours a week



One midterm and one final exam.



Weekly homework assignments


Recitation topics:

Coordinated and integrated with lecture material.


ABET Competencies: This course addresses the following ABET competencies :

a) Students learn fundamental computer programming concepts and structures.

e) Students identify, formulate and solve scientific and engineering problems.


Content by Category: Engineering Science = 3 credits (100%)

Lecture topics: (tentative)


Week 1 The VBA Front-end: Excel

  • Worksheets in Excel
  • Building formulas
  • Operator precedence, using cell references
  • Naming cells and cell ranges: using names in formula
  • Understanding error-values


Week 2 Using Build-in Functions

  • Using built-in function references
  • Exploring the syntax of functions: expressions as arguments, types of arguments
  • Mathematical functions: sum, rounding and other mathematical functions
  • Text functions: substring and other text functions
  • Logical functions: AND, OR, NOT, branching mechanisms


Week 3 Macros

  • Recording and running a VBA macro
  • The VBA environment: objects, methods and properties, manipulating object properties, naming arguments to methods
  • Adding code to an existing macro


Week 4 VBA Essentials

  • Comments
  • Using variables and constants
  • Variable Data Types
  • Scope and lifetime of variables


Week 5 VBA expressions

  • VBA operators
  • Operator precedence
  • Numeric, string, and logical expressions


Week 6 Creating custom functions

  • Using custom functions
  • Understanding custom function rules
  • Using VBA keywords
  • Passing arguments by Value or by Reference
  • Custom function with default arguments


Week 7 Objects, Properties and Methods

  • Overview of Objects
  • VBA Intermediate Window, Object Browser
  • Logical functions: AND, OR, NOT, branching mechanisms


Week 8 Working with range objects

  • Ways to refer to a range
  • Some useful range object properties
  • Some useful range object methods


Week 9 Conditional Branching

  • If Then Else
  • Select Case


Week 10 Looping Mechanisms

  • Do Loop, While and Until
  • For Next and For Each Next


Week 11 Creating and managing arrays

  • Declaring arrays
  • Resize an array
  • Multidimensional arrays
  • Dynamic arrays


Week 12 Sorting and searching elements in arrays

  • Sorting an array
  • Searching for an element in an array
  • Finding the maximum or minimum


Week 13 Debugging macros and custom functions

  • Using design-time tools: catching errors, stepping through code, setting breakpoints, setting conditional breakpoints
  • Dealing with run-time errors



Link to Lecture Notes and Homework Assignments: LecturesNotesFall07.html


Midterm Examination: October 30 (Open book: our textbook and my lecture notes only).


Announcement: Although October 9th is a Tuedsday, it follows a Monday schedule, and therefore we are supposed to have a one hour class from 2 3pm. I am cancelling this class and it will be made up on December 10 (Monday, the first Reading Day).


Final Examination: December 17, Monday (Open book: our textbook and my lecture notes only): LC 102 (Backup room: RH 202).