When you use Excel, quite often you select a range of cells. You either format the range, add data to it, or copy data from one location to the range of selected cells. In this article, we'll discuss cell ranges, referencing these ranges and then using different Visual Basic functions to control the range format and data.

Named Ranges

You can give a range of cells a name. This name can then be used to manipulate cell data and formatting. Before you write any VBA code that references a named range, you must first give a set of cells a name.

In your Excel spreadsheet, highlight a group of cells. Click the Formulas tab and then click the "Define Name" button. A window opens where you can give the range a name. For this example, name the range MyRange. We'll use this name in the next sections.

The dialog box also prompts you for a scope. The scope is where you can reference the range. For most applications, you want the scope to be the entire Workbook, so you can reference the range regardless of the active worksheet.

Click "OK" when you're finished and open the VBE by pressing ALT+F11.

If you should ever need to change the cell ranges for the given name, you can return to the Formula tab and click the Name Manager button.

You can also use VBA to add a named range to your workbook. The code below shows you how to create a named range using VB.

Dim cell As Range

Dim range As Range

Dim rangename As String

Dim cellname As String

rangename = "MyRange"

cellname = "A1:A10"

Set cell = Worksheets("Sheet1").Range(cellname)

ThisWorkbook.Names.Add Name:= rangename, RefersTo:=cell

In the above code, we first define four variables used to work with our named range. We need to define the range name and the cells included in the range. The "rangename" variable contains the MyRange value. This is the name we'll give to the range. The "cellname" variable contains a range from A1 to A10.

With the range name given and the cells defined, we can now set up the range in the workbook. The next line of code sets a range in Sheet1. We want the range to have the workbook scope, but we still need to set the range on a specific sheet. The next line of code adds a range to the workbook. This is the syntax to add a ranged name in VBA.

Referencing Ranges

With your named range set, you can now reference it in your code. Since you've set the scope as the workbook, you can now reference the range from either the sheet object or the workbook object. You can also reference it in a module.

Choose one of the modules from the list of objects in project explorer or create a new one. We'll use this file to use example code using named ranges.

First, let's take a look at how you format the named range. Add this subroutine to your module.

Sub RangeFormat()

   Range("MyWorkbook.xls!MyRange").Font.Italic = True

End Sub

Notice the way we reference the named range. We first specify the workbook file name. In this example, it's MyWorkbook.xls. An exclamation mark is then used followed by the name of the range. We gave our range the name MyRange, so VBA automatically selects the cells A1 to A10. We then set the font format to italic. This property can be true or false.A property requires a certain data type. In this example, the Italic property requires a boolean data type, which can be true or false.

The following code is another example of formatting the interior color of the cells.

Sub RangeFormat()

   Worksheets("Sheet1").Range("MyRange").Interior.Color = vbRed

End Sub

In the above example, we reference the range differently than we did in the previous code snippet. In this example, we reference it from the worksheet named Sheet1. We then change the color to a pre-defined color variable named vbRed. If you recall, any variable with the vb prefix indicates that it's a pre-defined VB variable with a value assigned to it. The vbRed variable contains the code for the color red.

Finally, you can clear the content of a named range to delete any data from it. The following code shows you how to clear a range's data.

Sub RangeFormat ()

  Application.Goto Reference:="MyRange"

  Selection.ClearContents

End Sub

In the above code, we've cleared the contents within MyRange. You usually delete data in a named range when you want to add new data. Note that the ClearContents function clears only the data and not the formatting options you've added. You must write code that sets up a different format if you want to remove any custom formatting to the named range.

Selecting Relative Ranges

 You use relative ranges when you want to work with cells relative to the currently selected cell. Relative cells avoid using statically defined cell names and ranges, so you can dynamically place data within your spreadsheets.

There are a couple of ways you can select ranges. The first way is to select cells in the currently active row. Let's take a look at the code to do this.

Range(Cells(Selection.Row, 1), Cells(Selection.Row, 3)).Select

In the code above, a range is selected relative to the current row, regardless of which row is selected. The range is set to select the first three cells in the row. Notice that we use the Selection.Row property to specify the currently selected ro. We then use 1 and 3 to determine the start and finish of the cell selection. The code is in parenthesis with the Select function appended. This tells Excel to select the first three cells in the current row.

You can also choose to use the Offset function.Remember that positive numbers move the active cell to the right and down the spreadsheet. Let's take a look at an Offset function that moves the active cells to the left and up the sheet.

Range(ActiveCell.Offset(-2, -4), ActiveCell.Offset(0, 11)).Select

The parameters in the Offset function are the row and column from the current cell. In this example, the Offset function moves the active cell up 2 rows and to the left by 4 columns. The number zero in the second Offset function means to use the current row. If you pass the parameters 0, 0 to the Offset function, it will not move anywhere and will keep the current active cell.

Referencing Tables

Many complex Excel spreadsheets have tables embedded in them. Tables are also composed of rows and columns just like a spreadsheet. However, you must reference these objects differently than regular sheet cells. If you need to use VBA to reference tables, the first step is to define a named range for the table. You can do this by clicking the table, opening the Formula tab and then clicking Define Name. Name the table MyTable, which we'll use in the next example tab.

With the table named, you can now select it using named ranges and VBA. The following code shows you how to select the table.

ActiveSheet.ListObjects("MyTable").Range.Select

Notice that the code is different than selecting cells. You must tell Excel that it's selecting an object, in this example it's a ListObject. The ListObjects function has the MyTable parameter, which is then set as the selected range.

VBA lets you select the table's column or row headers. The following code shows you how to select the row headers in the spreadsheet.

ActiveSheet.ListObjects("MyTable").HeaderRowRange.Select

In the code above, the MyTable object is selected, but only the row's headers are set as the active selection.

When you work with tables, you usually want to add rows or columns. VBA has internal functions that insert a row or column at the end of the table or within a position that you define in the code. Let's take a look at code that adds a row and a column to the table MyTable.

ActiveSheet.ListObjects("MyTable").ListColumns.Add

ActiveSheet.ListObjects("MyTable").ListRows.Add (6)

The first line of code adds a column to the end of the table. This means that if you have 5 columns in your table, VBA adds a 6 at the end of it.

The second line of code adds a row to the table. Notice that we have a parameter ( 6 ) to the end of the function. Instead of just placing the new row at the end of the table, this parameter tells the Add function to add the row above row 6. Note that the row is added above row 6 and not underneath. This makes the new row number 6.

The same can be done with columns. If you specify a location for a column, the new column is inserted before the current column. If you specify that you want to insert the column at location 6, the new column is inserted prior to column 6, making the new column number 6. You need to keep this in mind as you dynamically build tables in VBA and add data to the new rows and columns.

Looping

With any programming language, looping is necessary to iterate through a group of values. With each loop, you perform some kind of data extraction or manipulation. Visual Basic has the same common looping structures as most common languages. In this section, we'll discuss the common looping structures in Visual Basic: For, While, and Do loops.

The For-Next Loop

The For-Next loop is probably the most popular in any programming language including Visual Basic. The For loop iterates through a numbered list and then lets you manipulate or edit data depending on the value. It's often used with arrays.

Let's first look at a small example of a For loop.

For j = 1 To 10

  Total = Total + iArray(i)

Next j

The first line of code defines the "iterator." In this example, the "j" character is the iterator. It's used to define each time a new iteration occurs. It keeps track of each loop and lets you reference each iteration value.

In this example, we iterate from 1 to 10. If you're used to other languages, they usually start with 0. You can start with 0 in Visual Basic as well. You can start the iteration with any numeric value that you want. The For loop can iterate once, ten times or a thousand times. The number of iterations is completely up to you.

We want to iterate 10 times, so we set the iteration number from 1 to 10. Within the loop is where all of the data manipulation happens. In this example, we have a variable named iArray that continues to add to a total. Loops are often used for adding numbers within your code. The Total variable starts at 0 and continues to add the next array value to the Total.  but just know that the second line of code aggregates a total from the iArray variable during each iteration.

The last line of code sends code execution flow to the next For loop iteration. For instance, if this is the first loop iteration, the Next j line of code tells the compiler to move back to the original line of code. Each time the For loop iterates, it first determines the next value of j. After the first iteration, a value of one is added to j. Therefore, the second iteration assigns the value of 2 to j. Since j is less than 10, the next iteration continues within the For loop.

Once the value of j reaches 10, the loop will no longer continue. You must remember this logic as you create your programs. If you loop too many times, the program will throw an error since the array doesn't contain enough values. If you don't loop enough, you have a bug in your program and the proper Total isn't added. Logic errors are very detrimental to your programs, and they are difficult bugs to troubleshoot.

Let's look at a more detailed example.

Sub GoLoop()

   Dim start As Integer

   Dim end As Integer

   end = 5

   For start = 1 To end

   MsgBox start

   Next start

End Sub

In the example above, we create variables start and end. We then dynamically assign the value of 5 to the end Integer variable. This end variable is used to stop the iteration at 5.

We then have our For loop. We set the start variable to 1 and then use the end variable to stop the loop. Since we set the end variable to a value of 5, the loop will stop once the start variable reaches the value of 5.

This example doesn't add any values. It simply displays the current value of the start variable as the loop iterates and the start variable is iterated with an added 1. When you practice this example, make sure you use a low value or you'll need to loop through several MsgBox functions, which could be tedious.

You can prematurely exit a For loop. Suppose you want to loop through a list of values, but if you find a specific value, you want to exit the loop immediately. You can do this using the Exit For statement. Let's take a look at an example.

Sub GoLoop()

   Dim start As Integer

   Dim end As Integer

   end = 5

   For start = 1 To end

   MsgBox start

   If start = 2 Then

   Exit For

   End If

   Next start

End Sub

In the above example, we added an If statement.  for now know that an If statement evaluates a condition and then executes code depending if the condition evaluates to true.

If the start variable evaluates to 2, then the evaluated value is true. When an If statement evaluates to true, then the statements within the If statement are executed. In this example, the only line of code executed is the Exit For statement. When this statement is executed, the For loop is prematurely exited, and any code after the "Next start" statement is executed. Since we have no code after the Next statement, the only other option is for the subroutine to exit. The result of the above code is that the MsgBox only displays values 1 and 2 and then the For loop exits.

Do Loops

Do loops are not as common as For loops, but they are still necessary. The Do loop tells the compiler to keep looping until certain criteria is met. The criteria can be an integer value, a string value or any data that you need to use the stop the loop. Because you can use any type of variable to stop the Do loop, it makes it beneficial over a For loop. If you use integers, it's better to use a For loop, but if you need to work with any other variable type, the Do loop is a good alternative.

Let's take a look at a simple example.

stop = 1

Do Until IsEmpty(Cells(stop, 1))

myarray(stop) = Cells(stop, 1).Value

stop = stop + 1

Loop

In the above example, the "stop" variable is defined with a value of 1. The goal of this loop is to go through a list of cells in a spreadsheet, capture the cell's data, and then assign the value to an index in the myarray array.  Just know that each time the loop iterates, the next index in the myarray array is used and the next cell row in the iteration is used. The Cells function retrieves the value of each cell in column one. The rowl number is dynamic based on the current value of the stop variable. This is one example of how you can iterate through an Excel spreadsheet and retrieve the current cell's value.

The Do While Loop

The Do While loop is a bit different than the Do loop. The Do loop first evaluates the condition for the loop. The Do While loop always loops at least once regardless of the condition for the iteration.

Let's take a look at some sample code.

Dim j As Integer

j = 1

Do While j < 7

  Cells(j, 1).Value = 10

   j = j + 1

Loop

As you can see, the code for the Do While loop is different than the regular Do loop. We first set a variable name and then call the Do While loop based on the initial value. Regardless of that value, the Do While loop always executes once. In this example, we assign values dynamically to cells within a specific row. The row is dynamically called, but the column is always 1. This means that the code iterates down the number of rows but stays in the first column in the spreadsheet. The value of 10 is assigned to each cell called in the Cells array.

Once the Do While loop executes once, a value of 1 is added to the j integer variable and the result is evaluated during the next loop. Since we start the j variable with the value of 1, the next iteration contains a j value of 2, so the loop continues. The loop will continue until the value of j reaches a value of 7. Since the logic says that the loop should only continue as long as the value of j is less than 7, once it reaches 7 the loop no longer iterates.

The For Each Loop

In many cases, you'll need to loop through several objects. The objects could be a spreadsheet, a workbook or a group of cells. The For Each loop makes it much easier to loop through a group of objects and their values. Let's take a look at some sample code.

Dim sheet As Worksheet

For Each sheet in Worksheets

   MsgBox "Worksheet name: " & sheet.Name

Next sheet

In the above code, we first create a variable named "sheet" that is set with the data type of Worksheet. We use this variable to loop through each worksheet in the workbook. Notice that we loop through a worksheet object within a workbook. The For Each loop lets you iterate through each object within another object. In this example, it's a list of worksheets within a workbook. We display a list of worksheet names to the user as we find a sheet. Once no more sheets are found in the collection, the For loop ends.

Loops are a convenient way to iterate through a list of values or objects. You'll find loops are a valuable addition to any coding language. They let you review and manipulate a list of objects or data without manually using several unnecessary variables to represent each object.