CS2113
- Programming with VBA/EXCEL
Fall
2008
My
lecture notes:
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): quadraticEquation0.xlsm
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
· 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
Arrays and
Records:
· 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:
· 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
· Sorting items in Excel: Sorter.xls
Midterm problems (Fall 2007): Exam questions CS2113f07e1.pdf
New
(Fall 2008) materials:
Sub
Procedures:
Converting from Cartesian to polar coordinates
(angles in radians and can be negative): Cartesian2PolarRadian.pdf
Computing the exponential function by summing it
Taylor series: computeExp.xlsm
A Five-Cards-Game, using the VBA random number
generator to generate random integers: FiveCardsGame2.xlsm
Computer simulation, to compute the area of a circle
and the value of pi: MonteCarloCircle.xlsm
Using the “Preserve” keyword in a ReDim
statement: Preserve.xlsm
“UBound”, “LBound” and the
array size: arraySize.xlsm
Using the function “Cells” to write to a
range block on an Excel sheet: CellsExample.xlsm
Using string functions “Len”,
”InStr”, “Left”, and “Right”: testName.xlsm
ASCII representation of characters: AscChrTest.xlsm
Read data from a range block on an Excel sheet into
an array: From_sheet_make_array.xlsm
Write content from an array to a range block on an
Ex
Pattern matching with allowed errors: finding a given gene in a given DNA
sequence: dozerDNA.xlsm
Dozer
gene sequence: dozer.dna
Using VBA functions UBound, LBound to find the upper
and lower bound for the index of an array, as well as the size of the array: arraySize.xlsm
Using the function Cells(i,j) to write to a range
block in Excel sheet: CellsExample.xlsm
Read values from a range of cells in a spreadsheet
to an array. These values are written back to the sheet at a different
location, and are summed: sumArraySheet.xlsm
Read values from a range of cells in a spreadsheet
to an array. The average and the standard deviation of these values are
computed: standardDeviation.xlsm
Using a dynamic array to keep track of MP3 song
titles: MP3Songs.xlsm
Exam 1
solutions:
Computing the GCD: GCD.xlsm
Exam 2
solutions:
Exam 2:
will be on 11/24 (9:00am – 10:50am)
Lecture
on 11/19 will be moved to 12/8 (reading day)
Topics
that will not be on the Final Exam: TopicsFinal.docx
·
Assignment 1: problem 1
·
Assignment 2: problems 2 and 3
(due September 22 before class starts)
·
Assignment 3: problem 4 (due
September 24 before class starts) change3.xlsm
·
Assignment 4: problem 5 (due
October 6 before class starts) Cartesian2polar.xlsm