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 Tags Situation Pattern summary, needs, provides Where referenced
Input from a file (CSV) Input, Files, CSV

You have a CSV file you want to read. Each line is one record. Each record has one or more fields.

Open the file, Input a record, Close the file.

Needs: A CSV file. You have to know the name of the file.

Provides: Variable(s) with data read from the file.

Reading CSV files
Input from a file (plain text)

You want to read some plain text data from a file.

Needs: A file with plain text data, usually with a @.txt@ extension. Put it in the same folder as the workbook.

Provides: Variables with data read from the file.

Reading unformatted files

Data needs to be transformed into other data.

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



Multiway If statements

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.

Multiway, Basic validation: strings, More than two groups
Normalize a variable Variables

You have a variable that can have many formats, maybe leading and trailing spaces, and upper- and lowercase. You want to test the variable with an If.

Convert a variable that can have many formats, into one format.

Needs: A variable that can have many formats.

Provides: A variable in a single formats.

Logical operators, Basic validation: strings, Normalize during CSV input, Normalizing during Range processing
Numeric type check Validation

You want to check whether a value is a valid number.

Put a value into a string. Use the IsNumeric() function.

Needs: A value to check.

Provides: A variable that you know is a number.

Basic validation: numeric
Open-process-close file Files

You want to process data in a file, or create a new data file.

Open a file, process it with Input, Write or whatevs, and Close it.

Needs: The name of a data file, in the same folder as the workbook.

Provides: Data to analyze (input), or processing results (output)

Reading unformatted files
Output to a cell Output

You want to show the user some output.

Copy a value to a cell in a worksheet.

Needs: A variable with a value in it. The row and column of the cell that the value will be copied into.

Provides: A happy user.<br /> <br /> &nbsp;

Output to a dialog Output

You want to show a message to a user. Could be a variable's value, an error message, or something else.

Use a MsgBox to show a dialog box.

Needs: A message to show. Might include a variable's value.

Provides: Happy user.

Output to a file (plain text) Files, Output

You have data in a variable, and want to get it into a file.

Open the file, Print to it, and Close the file.

Needs: Data to write to the file, and a file name.

Provides: A file with data in it.

Writing unformatted files, Sales report