Working with Cells and Ranges

When writing VB code, you can use variables, for loops, and all other VB types and statements.  However, most of your code will be dealing with values stored in cells and ranges.

A range is set of cells.  It can be one cell or multiple cells.  A range is an object within a worksheet object.  For example, the following statement sets the value of cell C23 to a formula referencing a set of named cells:

Worksheets("trapchan").Range("C23").Value = "=(u/n)*A*Rh^(2/3)*S^(1/2)"

If the "trapchan" worksheet is the active sheet, the first part can be left off as follows:

Range("C23").Value = "=(u/n)*A*Rh^(2/3)*S^(1/2)"

The ".Value" part is optional.  You can also write:

Range("C23") = "=(u/n)*A*Rh^(2/3)*S^(1/2)"

You have to remember to put the double quotes around the cell address.  If the cell C23 has been named "Q" in the spreadsheet, you can reference the range as follows:

Range("Q").Value = "=(u/n)*A*Rh^(2/3)*S^(1/2)"

To get something from a cell and put it in a variable, you just do things in reverse:

x = Range("B14").Value

If X is a double or integer, you may also want to use the Val function:

x = val(Range("B14").Value)

Another way to interact with a cell is to use the Cells(rowindex, columnindex) function.  For example:

Cells(2, 5).Value = "=(u/n)*A*Rh^(2/3)*S^(1/2)"

or

x = val(Cells(2, 14).Value)

Once again, the value part is optional since it is the default property of both the cell and range object.

A range can also encompass a set of cells.  The following code selects a block of cells:

Range("A1:C5").Select

or

Range("A1", "C5").Select

In some cases, it is useful to reference a range of cells using integers representing the row and column of the cell.  This can be accomplished with the Cells object.  For example:

Range(Cells(1,1), Cells(3,5)).Select

The problem with referring to specific cells in your code is that if you change the location of data on your spreadsheet, you need to go through your code and make sure all of the addresses are updated.  In some cases it it useful to define the ranges you are dealing with using global constants at the top of your VB code.  Then when you reference a range, you can use the constants.  If the range ever changes, you only need to update your code in one location.  For example:

Const TableRange As String = "A4:D50"
Const NameRange As String = "A4:D50"
Const ScoreRange As String = "D4:D50"

.
.
Range(TableRange).ClearContents
.
.

An even better approach is to get into the habit of naming cells and ranges on your spreadsheet.  Then your VB code can always refer to ranges by names.  Then, if you change the location or domain of a named range, you generally don't need to update your VB code.  For example:

.
.
Range("TableRange").ClearContents
Range("NameRange").ClearContents
Range("ScoreRange").ClearContents
.
.

One of the most common things we do with VB code is to traverse or loop through a set of cells.  There are several ways this can be accomplished.  One way is to use the Cells object.  The following code loops through a table of cells located in the range B4:F20:

Dim row As Integer
Dim col As Integer

For row = 4 To 20
   For col = 2 To 6
      If Cells(row, col) = "" Then
         Cells(row, col).Interior.Color = vbRed
      End If
   Next col
Next row

In most cases, it doesn't matter what order the cells are traversed, as long as each cell is visited.  In such cases the For Each ... Next looping style may be used.  The following code does the same thing as the nested loop shown above:

Dim cell As Variant

For Each cell In Range("B4:F20")
   If cell = "" Then
      cell.Interior.Color = vbRed
   End If
Next cell

Another option is to create your own range objects.  A range object is essentially a range type variable.  The following code defines three ranges:

Dim coordrange As Range
Dim xrange As Range
Dim yrange As Range

Set xrange = Range(Cells(3, 1), Cells(100, 1))
Set yrange = Range(Cells(3, 2), Cells(100, 2))
Set coordrange = Range(Cells(3, 1), Cells(100, 2))

Once a set of range objects has been defined, you can easily manipulate the cells in the range object.  For example, the following code clears the contents of all the cells in the coordrange object:

     coordrange.Clear

Once again, these ranges can be traversed using the For Each ... Next syntax.

Dim cell As Variant

For Each cell In xrange
    cell.Value = "0.0"
Next cell

One of the most useful things you can do with VBA in Excel is to allow the user to enter a list of numbers where the size of the list can vary.  The following code searches through a list and copies the numbers in the list into an array.  It stops copying the numbers when it reaches a blank cell.

'Get the x values
i = 0
For Each cell In Range("B4:B23")
    If cell.Value = "" Then
        numpts = i
        Exit For
    Else
        i = i + 1
        x(i) = Val(cell.Value)
    End If
Next cell