Extraction

Extracting data into a separate data set:

Makes it easier to write clean code, like this:

extractData dataOK
If Not dataOK Then
    End
End If
processData
outputResults

Code that's easier to test and debug. More reliable. Less screaming.

You've seen how to put the extracted data into a different part of a worksheet. But that's not the only place. You can also put extracted data into an array.

What's an array?

Arrays are a cross between variables and worksheets.

The goal

Let's write code that extracts sales above 250. The worksheet starts out as:

When the button is clicked, the program extracts the values above 250 to another part of the worksheet. It also shows the size of the new data set:

How does the program work? Basically, it runs through the original data. When it finds a value that's OK, it copies that value to the new data set.

h1.

Just some of the data

Often you want to process some of the data in a data set. Two main reasons.

Dirty data

Data from the RL is often dirty. For example, suppose you had this Web form:

A bunch of people filled it in. Their responses were copied to an Excel worksheet.

Here are some things people might type:

• 1
• None
• two
• six lovely kitties
• 2 real, 2 stuffed

Ack!

Indexes and loops

Remember indexes? Use a variable to refer to another piece of data. For example:

Cells(row + 3, column + 2)

You've seen loops that change numbers, like this:

1. Dim x As Integer
2. x = 1
3. Do
4.     MsgBox "The square of " & x & " is " & x * x
5.     x = x + 1
6. Loop While x <= 5

A loop is when a program executes the same code again and again. Sometimes a loop runs a fixed number of times, like 100. More often, the loop runs until something happens.

Logical loops for input validation

An example. Click the button below and a program will ask you to type a number. Type anything you want.