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

 

Textbook:

 “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

 

Exams:

One midterm and one final exam.

 

Homework:

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).