Patterns

A pattern is a common way of doing things. Here are the patterns on this site.

Pattern Keywords Situation Actions Referenced in
Abstracting the similar Subroutine, Function, Procedure, Parameter

There are several tasks that are similar, but not identical.

Create a Sub or a Function that does the task. Use parameters to pass in the things that are different between the tasks.
Accumulator

You want to summarize some aspect of a data set, like the average, or highest value.

Use a variable as an accumulator.

  • Initialize the variable.
  • Loop over the cells you want to process. Update the value of the accumulator each time through the loop.
  • After the loop, the accumulator has the value you want.
 Accumulators

Basic data analysis programs
Array copying Array, For-Next, Loop

You want to do something with an array that will change it, like sort it. You want the original array as well.

Make a copy of the array. Mess with the original or the copy.
Array sort Array

You want to sort data in an array. For a report, for example.

Use a proc in the explanation section of this pattern. Note: the array passed into the proc is processed in-place. Its original order is lost. If you want to keep the original order, make of copy of the array before you sort it.

Basic data analysis

You have a bunch o' data. You're asked to write a program to summarize or transform the data in some way.

Use the pattern:

Extract
Process
Output

This is a variant of the input-process-output pattern.
Common unit Select Case, Switch

There are a large set of units, like currencies. You want to convert from one unit (the sources) to any other unit (the destination). There are many possible combinations.

Pick one of the units. Call it the common unit. Convert the value from the source unit to the common unit. Then convert the value from the common unit to the destination.

File input loop Loop, Do-Loop, Input, File

You have a file with data that you want to process. The amount of data in the file could change.

Use a Do-Loop to read data from the file. Loop until the program reaches EOF (end of file). See a flowchart and code in the this pattern's Explanation.

File input loop with accumulator Loop, Do-Loop, Input, File

You have a file with data that you want to process. The amount of data in the file could change. You want to know something about the data set, like its total.

Initialize an accumulator variable. Loop while not EOF (end of file), reading data, and updating the accumulator each time through the loop. See the Explanation of this pattern for a flowchart and code.

File open, process, close File

You have data in a file that you want to process.
  • Use the "Open" statement to open the file.
  • Do your processing.
  • Use the "Close" statement to close the file.

See the Explanation of this pattern for code.
Filtering file input Condition, File, Input

You have a data file and want to process just some of the data.

Use a loop to read data from the file. Use an If statement to select data you want to process.

Find the end of a data set Loops, Do-Loop

The number of items in a data set varies. Maybe 502 one day, and 534 the next. You want to find the number of items.

Use a Do loop and an index variable. Check cells one by one, until there's an empty cell.

 Finding the end

Filtering data
Flag Condition

You want to keep track of whether something happened. For example, whether one of several input errors happened.

Use a variable as a flag. Write code that initializes it, sets it if some condition occurs, then checks it. See this pattern's Explanation for an example.

 User input and flags

Indexes
Flag-controlled loop Loop, Condition

You want to a program to keep doing something until (or while) something happens.

Use a flag to remember whether something happened. Use a Do-Loop to test the flag.
Incremental testing Testing

You have code to write. You'll need to test it.

Test each chunk of code after you've written it. Write some, test it, write some, test it. Don't wait until you've written all of the code.
Initialize

Some things need to be done every time a program starts.

Create a Sub for the code called initializeProgram, or some such.

 User input and flags
Input from a file - known amount of data Input, File

Your program needs input from the user. You know exactly how many elements you need, and what type each one is. The data can be obtained in a file.

Use the File open, process, close pattern to open and close the file. Use the Input statement to read data.

 Patterns nest
Input from keyboard - known amount of data Input

Your program needs input from the user. You know exactly how many elements you need, and what type each one is. The tip program is an example.

Use the InputBox function.

Patterns nest
Input from worksheet cells - known amount of data Input, Worksheet

Your program needs input from the user. You know exactly how many elements you need, and what type each one is. The tip program is an example.

Choose a few cells on a worksheet. Tell users what data to put in the cells.

 Patterns nest

Dudes at work
Input number as string Input, Input validation

You want a user to input a number. You want to make sure it's a valid number, and give an error message if it isn't.

Put the user's input into a string variable. Test whether the variable has something that could be interpreted as a valid number. If so, convert the string into a number.
Input validation If, Condition

You get input from the user. S/he might have made a mistake.

Add an If statement to check the data before processing it.

 Numeric expressions