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.


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


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.


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.


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.


Exercise: find the bug x 3.

Exercise: improve a program

Exercises x 3: minimal transfer version of programs in this section.



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.


Check input 1
Check input 2
Check input 3
If any errors
    End the program
    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


Part IV. Analyzing data

Goal: learn common programming patterns in a task context.

Two big ideas


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.


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


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


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.


The most common


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.

Summarizing variables
Summarizing relationships between variables
When variables are categories

Data grids

Making a grid from records
Summarizing across rows or cols
Summarizing across all data


Programming charts

Workbook structure

Assemble the pieces