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
Normalize a variable

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

Normalizing, Basic validation: strings, Logical operators
Numeric type check

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
Output to a cell

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 /> <br /> &nbsp;

Output to a dialog

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.

Range check

You have a number that might be too small, or too high. You want to check it, to make sure it's OK.

Validation, If

Check whether a number is between two other numbers.

Warning: "range" has two meanings. This pattern is not about Range objects.

Needs: Minimum, maximum, and a value to check.

Provides: A validated number, or an error.

Basic validation: numeric, Logical operators

You need to do a simple computation.

A simple sequence of statements.

Needs: Some variables whose values need to be changed.

Provides: Variables with changed values.

Validation (better)

You have several pieces of data to check. You want to show all the errors at once.


Wrap a bunch of individual checks inside the flag pattern.

Needs: Several pieces of data you don't trust.

Provides: Checked data you can work with.

Better vladidation
Validation (numeric, basic)

You have some data that's supposed to be numeric. Could be from a cell, a file, a dialog box, wherevs. You want to check the data.


Test that data is numeric. Add range checks and other tests, as needed.

Needs: A data value.

Provides: Number that's valid, or an error message.

Validation (string, basic)

You have some string data you don't trust. Could be from a cell, a file, a dialog box, wherevs. You want to check the data.


Normalize, then test with If(s).

Needs: Some string data.

Provides: String data that's valid, or an error message.

Basic validation: strings
Variable increase/decrease

You are accumulating a value, like a total, or count.

An assignment statement, like sTotal = sTotal + sSales. Adds to a variable, then puts the result back in the variable.

Needs: A variable to be increased/decreased, and a value to add or subtract.

Provides: A variable that has been increased/decreased.

Numeric expressions