_{Polytechnic University of NYU, Fall 2008}

_{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:}

• _{Monday: 1:00 - 3:00 pm}
• _{Wednesday: 10:00 – 12:00 pm}
• _{Friday: 10:00 - 11:00 am, 12:00 pm – 1:00 pm, (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:

_{Two midterm exams 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}

_{Grading Scheme: HW (20%) + 1st Midterm (20%) + 2nd Midterm (20%) + Final Exam (40%)}

_{Midterm Exams:}

_{· 1st Midterm Exam: October 20}

_{· 2nd Midterm Exam: November 24}

_{Link to Lecture Notes and Homework Assignments:}

_{LecturesNotesFall08.html}