CS2113 - Programming with VBA/EXCEL

Fall 2007

My lecture notes:

· Introduction to Computers and VBA/EXCEL

· More on EXCEL

· Building Formulas

· Using Functions

· Everyday Functions

· Macros

· VBA in a Nutshell

· VBA Essential Elements

Other examples covered in lecture but may not be in the above notes (they are separated loosely into categories, although many examples actually fall into multiple categories:

Branches:

· An example involving using the Select Case statement: TestSwitch.xls

· Computing roots of a quadratic function (involving branches): quadraticEquation.xls

· Problem 11.6: problem11p6.xls

· Problem 11.7: problem11p7.xls

Loops:

· An example using a nested double loop to draw the stars on the American flag: AmericanFlag.xls

· Computing the factorial function using iteration and the use of a recursive function: TestFactorial.xls

· Problem 12.1: problem12p1.xls

· Problem 12.2: problem12p2.xls

· Problem 12.3 (flow chart): flowChart12p3.xls

· Summing the Taylor series to compute the cosine function: cosEvaluate.xls

· Summing the Taylor series to compute the cosine function (better control of the number of terms): cosEvaluate2.xls

· Summing the Taylor series to compute the cosine function (better control of the number of terms):cosEvaluate3.xls

· An example using a do/while loop for data validation: doWhile.xls

Functions:

· An example involving parameters of a function from the book: ParamList.xls

· An example illustrating call-by-value and call-by-reference arguments of a function from the book: ValRef.xls

· Problem 5.4: Calc5p4.xls

· Problem 5.5: Calc5p5.xls

Arrays and Records:

· Problem 13.3: problem13.3.xls

· Problem 13.5 (mean and standard deviation): problem13p5.xls

· Problem 13.11 (Equation of State): problem13p11.xls

· Temperature convention example from book: temperatureArray.xls

· Finding the lowest score from an array of scores: lowestScoreArray.xls

· Finding the differences in scores from the highest score: differenceScores.xls

· Searching an array for a target value: Array1DSearch.xls

Miscellaneous:

· An example of an InputBox: InputBoxTypeError.xls

· The kickCalc example from our textbook: KickCalc.xls

· Count number of items in spreadsheet (rows): CountRow.xls

· Count number of items in spreadsheet (columns): CountColumn.xls

· Problem 8.1: problem8p1.xls

· Problem 8.3: problem8p3.xls

· Sorting items in Excel: Sorter.xls

Midterm problems:

· #1: e1Problem1.xls

· #2: e1problem2.xls

· #3: e1Problem3.xls

Homework Assignments (from our textbook):

· Problems: 3.2 and 3.3 (Due September 19)

§ Solution: CountRow.xls , Sorter.xls

· Problems: 8.1 and 8.3 (Due October 2)

§ Solution: problem8p1.xls , problem8p3.xls

· Problems: 11.7 and 11.6 (Due October 9)

§ Solution: problem11p7.xls , problem11p6.xls

· Problem: Write a VBA sub procedure, named compute_x, that ask the user to input integer, x, using an InputBox, then computes that final value of x according to the flow chart for problem 12.2 on p. 143 of our textbook. The final value of x should be displayed using a message box. (Due October 15)

§ Solution: problem12p2.xls

· Problems: 13.5 and 13.11 (Due Nov. 26)

---

[E-mail]| [My home page]