The big chunks
Part I. Getting ready
Goals: Help students get what they need to benefit from the course.
Part II. How computers run programs
Goal: help students build a mental model of how computers run Excel VBA programs.
Part III. Expressions and statements: the building blocks of programs
Goal: Help students learn about the components they will put together to make programs.
Part IV. Writing programs to do useful things
Goal: Help students learn patterns they can use to combine statements into useful programs.
Blend concepts through the course
The course chunks are not independent. Important ideas should not be confined to one section of the course. They should be ongoing themes. Like:
- Understand task goals before coding.
- Thinking in chunks, program structure, and patterns.
- Decomposition and encapsulation.
- Debugging. Test early, test often.
- Make code readable.
- Have a growth mindset.
- Have realistic expectations for effort and failure (bugs).
The chunks in more detail
Part I. Getting ready
Goals: Help students get what they need to benefit from the course.
What you'll learn
Explain course outcomes. Explain what students will have to do: read content, do exercises, work regularly.
What you'll need
Resources. Computer, Excel, browser.
Mindsets
Beliefs. Growth mindset. Expect programs to fail. Debugging is part of it.
Using this site
Create account, log in, do exercises…
Exercise: submit a joke.
Part II. How computers run programs
Goal: help students build a mental model of how computers run Excel VBA programs.
CPUs and memory
Lesson goal: CPU does calcs on data in memory.
Two parts of the computer are the most important: CPU and memory.
Programs that add two numbers, fruit is apples plus oranges
Programs tell the CPU what to do
Lesson goal: program is a sequence of instructions to the CPU.
Show a program in VBA.
apples=4 <- command to CPU, not an equation oranges=3 fruit = apples + oranges: fetch apples, fetch oranges, add them, put results into fruit.
Use EVA. Remove SS.
Computing the tip
Goal: programmer writes sequence of instructions to the CPU.
EVA without SS.
The outside world
Lesson goal: programmers tells CPU to get data from places (input), and put data places (output).
Full EVA, with tip program that uses Excel worksheet cells.
Aussie rules
Lesson goal: show how programs follow patterns.
From EVA to Excel
Lesson goal: Run simple programs in Excel
Programs and variables
Goal: Write code that messes with variables so that, when the program is done, variables have the data you want.
More simple IPO examples. Points in an Oz Rules game. Sound your favorite animal makes.
Each one: code messes with variables to get the job done.
Program structure? Talk about how the programs have the same structure. IPO.
From Eva to Excel
Move code to Excel. Show students how to run programs with buttons.
Show the debugger. It can do the same things as Eva, most of them, anyway. Show equivalents.
Exercise x 2: Create a workbook with different programs that we looked at in Eva.
Part III. Building blocks of programs
Goal: Help students learn about the components they will put together to make programs.
Keep program-as-task-doer in students' minds. This section focuses on statements, but is about tasks as well.
Start debugging strategy here; mechanics have already been introduced.
Expressions: putting the CPU's evaluator to work
Goal: Expressions are calculations done by the CPU's evaluator. They appear all over the place.
Numeric expressions
Operators. Order of operations. Fill-in-the-blank exercises.
String expressions
Operator. Fill-in-the-blank exercises.
If statements and logical expressions
Thunking
Programs are too complex to think about all at once.
Way of thinking about programming that you should learn. Less screaming.
You've already seen it in action. It's thinking-in-chunks, or thunking.
Manikins
When humans design things, they use thunking. Manikin. Could start carving, wouldn't work.
Step back from the details of the carving, to organize your work.
You have a pattern, more or less. The human and cat bodies. Gives you some chunks: head, legs, etc.
Decide how they're going to connect. Can make each one separately, then assemble.
Could have other people do it.
Do this right, design is easier, testing is easier, debugging is easier, less screaming.
Patterns nest
The most common programming pattern is IPO. You've used it already.
Pieces change, using their own patterns.
Input from cell pattern.
Add input validation pattern.
Direct user input pattern.
Input from file pattern.
Output to screen pattern.
Output to file pattern.
Processing patterns. Multibranch.
Use it to write to a Web page. More specific version of output to screen.
Test in pieces. Write I. Test. Write P. Test. Will miss bugs, but will get most of them.
Exercises – all IPO, switch up the computation, and the I, P, and O patterns.
Bugs
Types of bugs
Compute sales tax given amount (taxable and nontaxable) and state.
Syntax error. A bug the computer can detect and tell you about.
A logic error. The program is valid code, but doesn't compute the right result.
E.g., mistyped state name, wrong sales tax value, referenced the wrong cell…
Documentation error
User experience error.
Show examples in the program. Each one shows up through its effects on variables.
Finding bugs
Look at variable values. Compare what the values should be with what they are.
Example with program: pseudents find an error value. Tax is wrong, or something. How to find the error? Compare the values of variables – what they should be, with what they are.
You need a way to find out the values of variables in the middle of a program. Breakpoints, as before.
Where to put breakpoints?
One strategy: halve and halve again.
Play number game.
Play debug game x 3.
Watch your classmates
Lily and Klaus test and debug a program someone else wrote.
Exercises
Exercise: find the bug x 3.
Exercise: improve a program
Exercises x 3: minimal transfer version of programs in this section.
Summary
Subroutines
Goal: Subs are a way to break programs into chunks. They make programs easier to understand, easier to test, and easier to have a team work on.
Subs only, no functions, ByRef only (don't bring it up)
Better thunking
Chunks are visible. Connections are visible.
Sub statement. Example of IPO with parameters.
Change input. Files version, Web version.
Working in teams. Don't need to understand all how every piece works.
Pieces won't interfere with each other.
Encapsulation: name of the principle of wrapping chunks of code in subs, so they don't interfere with each other.
Same when you're working on your own. Reduce the ways one of your chunks can interfere another chunk. Less debugging, less frustration and screaming.
Encapsulation: name of the principle of wrapping chunks of code in subs, so they don't interfere with each other.
Params pass data into and out of a sub. Each has a type.
Caller names for params don't have to be the same as subs. They are matched in order.
See the reason that the names don't have to match in the next lesson.
Local variables. Variables with the same names in different subs don't interfere with each other. Helps with encapsulation.
Reusing code
Within one program, want to do the same thing again and again.
Example: tax program, validation.
Subs – write the code once, and use it again and again.
Have to write the sub so that it can be reused.
Think about what is the same and different about each validation task.
Put the things that are the same into the sub. Pass the things that are different as params.
That's why the names of params in a sub don't have to match the names on the call. So can use the sub in different situations.
User input and flags
Goal: Subs show state to caller through flags.
So far, one user error stops the program.
Often want to show user all the errors in the data, so they can clean them up at once.
Logic:
Check input 1
Check input 2
Check input 3
...
If any errors
End the program
Else
Continue processing...Use the flag pattern. A flag is a variable the tracks whether something has happened or not.
Take some code from before:
(Two validations)
Add set flag code.
Add check flag code.
Add init code.
Sub version.
Stubs and testing
Incremental design and testing with stubs. Can have an input routine that just sets values of params, and nothing else.
Lets one person work on processing, while someone else works on input.
As long as they agree on the params first.
More exercises
Summary
Part IV. Analyzing data
Goal: learn common programming patterns in a task context.
Two big ideas
Indexes
Cells(3,4) – 3 and 4 are pointers to other data.
can use any numeric expression.
row=3 col=4 cells(row, col)
Daily sales
Year of sales, user enters month and day, lookup sales.
Tic tac toe
Board is offset into worksheet, so get to do index arithmetic.
Loops
Doing something again and again, with something being a little different each time.
Uses logical expressions, as in ifs, but whether to repeat.
JS example – keep looping until student types a positive number
Pseudocode:
- repeat
- get input
- until input is a positive number
Enter a positive number
Enter row and col of an MT sudoku cell
Indexes and loops are BFFs
Use indexes and loops together to process an entire data set.
Ex: Col, make each neg number 0.
Want to process first item, then second, then third, etc.
Variable to use an an index. 1, 2, 3, etc.
Have a loop that adds 1 to index each time through the loop.
See loop working – change background color of cell at start and end of the loop.
Summarizing data
Common business operation, uses loops and indexes.
Accumulators
Totals and counts
Highest and lowest
Finding the end
MT cell
Specific value
Nonnumeric cell
Filtering data
Don't want to process all of the data. Errors, selecting piece of it.
Start with raw data. Have sub filter out bad data.
Marking bad data
Extracting good data
Where put good data? Another section of worksheet, another worksheet, array.
Frequencies
The most common
Array?
Beyond one dimension
Summary worksheets
Prepend a worksheet for summarizing results.
Data records
Data organized as records, one row for each item, one col for each variable.