Pattern catalog

Patterns are common ways of doing things, like recipes. People have patterns for solving equations, making web pages, wrapping gifts, all sorts of things. Part of learning a skill is learning the patterns that help you be more effective.

Here are patterns on this website. This list can help you find the patterns you need to do a task.

Name Situation Tags Action Where referenced

You have a data set, and you want to total up or count the values.


Loop over a data table. For each record, add a field to a total, count, or whatevs.

Needs: A data set.

Provides: A variable with a total or count.

Processing a Range, Data tables, Validate then process
Create a Range

You have a set of records in a worksheet that you want to process.


Set rSomething = Range(top left cell, bottom right cell)

Needs: A worksheet with records in it.

Provides: An object that you can loop across.

Find smallest/largest values

You have some records in a Range.

Range, Loop

Loop across some records. Put an If in the loop, comparing the current value with the smallest/largest so far.

Needs: You want the smallest and/or largest value(s) for a field in the records.

Provides: Smallest/largest values.

Min and max

You have a bunch of code. You want to see whether something happened in the code. It could happen in more than one place in the code. Validation is a common example, where user input could be invalid in a number of different ways.

Validation, If

A variable that summarizes the results of a bunch of code.

Needs: A variable you can use as a flag.

Provides: A value in the flag, that you can test for.

Validate then process, Range validation, Basic validation: strings, Better validation
Guard pattern

Your program could do something stupid, like try to divide by zero.


Prevent your code from doing something crazy.

Needs: Some data to test.

Provides: Usually an error message.

Validate then process, Guard pattern
Hungarian variable names

You need to name some variable names.


"Sales tax rate" is sSalesTaxRate. Price is just sPrice.

Needs: A cell in a worksheet.


Easy is good
Input from a cell

You want to get one data value from the user.


Get input from a cell.

Needs: A cell in a worksheet.

Provides: A variable with a value.

Input from a dialog

You want to get one data value from the user.


Use a dialog to get some input.

Needs: A human to type a value into a dialog, and/or read a value from a dialog.

Provides: A variable with a value.


Data needs to be transformed into other data.

A program inputs data from somewhere, does computations with the data, and outputs the result.




A variable has one of several distinct values, like different colors ("red", "green", or "blue"), or different ranges (1 to < 10, 10 < 30, ...). You want to do different things, depending on the value.

Use a chain of If statements.

Needs: A variable with several distinct values or ranges.

Provides: Different program states, depending on the variable's value.

Basic validation: strings, Multiway