Using Named Ranges | Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

< Day Day Up >

You might already know that you can select a range of cells on a worksheet and give it a name by typing it in the Name Box. This lets you quickly select the range by choosing its name from the drop-down list provided in the Name Box. These same names are available to you within a macro. Instead of providing the cell coordinates for a range, you can use the name of the range instead.

Defining a Named Range

Excel stores the names of defined ranges within the Names collection, which is a property of the Workbook object. Using the Add method, you can create a Named range within the workbook by specifying the name you want to use and the range it should point to.

If you wanted to do some analysis of the sales data for each hourly time period within the Y2001ByQuarter.xls workbook, you could make your formulas easier to read by defining each time period as a range. Rather than manually selecting the range and typing a name, you can use a macro to do the work for you. The following CreateNames routine will build a named range for each of the time periods on the current worksheet and name it based on the name of the worksheet and the time period. The basic syntax is expression.Add(Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local).

Of the various parameters that are passed to the method, only four of them are of importance.

Name A variant value that specifies the name that is to be given to the range.
RefersTo A variant value that specifies the cells to be included in the range using A1 notation. Required if RefersToR1C1 is not used.
RefersToR1C1 A variant value that specifies the cells to be included in the range using R1C1-style notation. Required if RefersTo is not used.
Visible An optional variant value that determines whether the Name is visible or not. Setting the property to True (the default) means the Name will appear in the Define Name, Paste Name, and Goto dialog boxes, while setting the property to False means the Name won't appear in those three dialog boxes (although you may still refer to the Name in formulas and VBA code).

Inside Out
Naming Ranges

There are a few guidelines to follow when naming a range:

A name must start with a letter or an underscore (_) character. The rest of the name can contain any combination of letters, digits, periods (.), or underscores.
A name cannot be the same as an existing cell reference (B22, CB76, R2C20, and so on).
A name cannot contain spaces or other special characters, curly braces, square brackets, or parentheses.
A name cannot exceed 255 characters. Also, names over 253 characters are not selectable from the drop-down list.
Names are not case sensitive. MyRange is the same as myrange.
A name must be unique to workbook; you can't use the same name to refer to ranges on different worksheets.

The following sample macro, CreateNames, works by looping through each of the columns that contains entries based on time. On each pass of the loop, the name of the current worksheet and the label of the column being marked are used to generate the name of the range in the format MonthHourAMPM, as shown in Figure 8-6. (The 1:00 P.M. entries on the February worksheet would be named as February1PM.)

Sub CreateNames()
Dim strRangeName As String, strWorksheetName As String
Dim intCounter As Integer, strRangeFormula As String
Dim strColumn As String, strR1C1Formula As String
Dim strA1Formula As String
 
    For intCounter = 1 To 12
        strWorksheetName = ActiveSheet.Name
        
        'Calculate proper column name (D-O) by converting 
        'from R1C2 notation to A1 notation
        strR1C1Formula = "R5C" & Format(intCounter + 3, "#0")
        strA1Formula = Application.ConvertFormula(Formula:=strR1C1Formula, _
            FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
        strRangeName = strWorksheetName & Format(Range(strA1Formula), "hAMPM")
        
        'Format column portion of range formula
        strColumn = "C" & Format(intCounter + 3, "#0")
        strRangeFormula = "=" & strWorksheetName & "!R6" & _
            strColumn & ":R36" & strColumn
            
        'Add new range and continue
        ActiveWorkbook.Names.Add Name:=strRangeName, _
            RefersToR1C1:=strRangeFormula
    Next intCounter
End Sub

click to expand
Figure 8-6: This procedure creates names in the Y2001ByQuarter.xls workbook.

Changing Notation Styles

The CreateNames routine uses the ConvertFormula method of the Application object to facilitate the use of a counter to specify the column being referenced. Using a counter or any numeric variable to point to a specific column makes it very easy to move left or right among the columns. Using a value of 4 to point to column D, 6 can be added to point to column J or 2 can be subtracted to point to column B.

However, the Range object will take cell pointers only by using the A1 style of notation or the Cells method. Rather than use the Cells method, the procedure takes advantage of the ConvertFormula method to build a reference in R1C1 notation and convert it to A1 notation.

The ConvertFormula method also lets you convert from the A1 notation to the R1C1 notation. It will also allow you to change a formula's reference type from absolute to relative or vice versa.

In the CreateNames routine, the ConvertFormula method converts a simple cell address from one reference type to another. As the name implies, you can pass a more complex formula for conversion, such as this example, which computes the daily average for the first two Mondays in January using the Y2001ByQuarters workbook.

strA1Formula = Application.ConvertFormula( _
    Formula:=" =AVERAGE(R5C2:R5C13, _
    R12C2:R12C13), fromReferenceStyle:=xlR1C1, _
    toReferenceStyle:=xlA1)

The ConvertFormula method has the following syntax:

 expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

Expression A required expression that returns an Application object.
Formula A required variant that provides the formula to be converted as a string.
FromReferenceStyle A required integer that matches one of the two xlReferenceStyle constants.
ToReferenceStyle An optional integer that matches one of the two xlReferenceStyle constants. If not supplied, the reference style is not changed.
ToAbsolute An optional integer that matches one of the xlReferenceType constants. If omitted, the reference type is not changed.
RelativeTo An optional variant that returns a range object that points to a single cell. Relative references relate to this cell.

Table 8-3 lists the available xlReferenceStyle and xlReferenceType constants you can use with the ConvertFormula method.

Table 8-3: Constants Used with the *ConvertFormula* Method
Constant	Integer
xlReferenceStyle
xlA1	1
xlR1C1	-4150
xlReferenceType
alAbsolute	1
xlAbsRowRelColumn	2
xlRelRowAbsColumn	3
xlRelative	4

Inside Out
Names as Formulas

Excel actually stores the names of ranges as a formula. You can take advantage of this handling in several ways to enhance the shortcuts you use in your spreadsheets. Ordinarily, you cannot use a name more than once within a single workbook, but what if you have a workbook with multiple sheets that are all similar, such as the Y2001ByMonth.xls workbook? It would be handy to be able to use the same name to refer to the same area of a worksheet regardless of which worksheet it is. You can do this by specifying the name to be specific to the worksheet and not available to the entire workbook.

Select the cells you want to name as you usually would, and click in the name box to type a name. This time, instead of just typing the name, type the name of the worksheet first, followed by an exclamation point (!), and then the name of the range. (You must still follow normal naming rules behind the exclamation point.)

The exclamation point serves as a separator between the sheet name and the range name. When Excel sees a sheet name, it knows to define the name as being specific to the worksheet.

You can also expand names even further by using relative references. Names will use an absolute reference by default, but if you enter the range that the name refers to manually by clicking Insert, Name, Define, you can use a relative reference. As an example, open the Y2001ByMonth.xls workbook, display the January worksheet, and follow these steps:

Select any cell in column A.
Click Insert, Name, Define.
In the Name box, type DailyValues.
In the Refers To box, type =A$6:A$36.
Click the Add button and then the OK button.
Select cell D38.
Type in =Sum(DailyValues) and press Enter.

The total that appears in the cell should be the same, $5,571.00. Examine the definition of the DailyValues name. Go back into the Define Name dialog, and select DailyValues from the Names list box. The Refers To box will show =January!D$6:D$36. The column names are relative, but the rows are absolute. You can use the name DailyValues within a formula if you need to reference the entire range of values entered within that column, from row 6 through row 36.

Warning
You can use the names you create in any row except rows 6 through 36; if you use the names in a formula in those rows, you create a circular reference that invalidates your formula.

Reserved Range Names

There are a few reserved names that Excel uses for internal purposes and that can't be used when defining your own ranges. However, knowing what the names are used for and how to apply them lets you extend the usability of your macros further. The reserved names are Consolidate_Area, Criteria, Database, Data_Form, Extract, Print_Area, Print_Titles, and a collection of others that begin with the prefix Auto_.

The Print_Area name is perhaps the most useful; it lets you set the range of cells that will be printed. Although this task can also be done using the PrintArea property of Worksheet.PageSetup object, using the Print_Area range is more flexible. The PrintArea property lets you set only the range of cells to print using A1 style notation or other range names. Furthermore, any examination of the property will reveal the range in A1 notation, even if you used the name of a range.

The Print_Area range lets you set the range of cells using your choice of A1 notation, R1C1 notation, or range names. Examination of the Print_Area range properties will show you the range in the style that was used to set it.

click to expand

The following SelectPrint routine allows the user to choose between printing the entire sheet or just the evening values from the Y2001ByMonth workbook. The Print_Area range is used to inform Excel what values to print.

Sub SelectPrint()
Dim intReturn As Integer, strPrompt As String, strTitle As String
Dim strRange As String, objName As Name, intCounter As Integer
 
    'Call CreateNames subroutine to ensure properly named ranges
    'are available
    CreateNames
    
    'Prompt for values to print out
    strPrompt = "Click Yes to print only the evenint values " _
     & "and click No to print all values."
    strTitle = "Microsoft Office Excel 2003 Programming Inside Out"
    intReturn = MsgBox(strPrompt, vbYesNoCancel, strTitle)
    
    'Check response from user
    Select Case intReturn
        Case vbNo 'Print full chart
            strRange = "=" & ActiveSheet.Name & "!" & _
                ActiveSheet.Name & "AllValues"
        Case vbYes 'Print evening times only
            strRange = "=" & ActiveSheet.Name & "!" & _
                ActiveSheet.Name & "1PM:" & _
                ActiveSheet.Name & "8PM"
        Case vbCancel 'Cancel completely
            Exit Sub
    End Select
                
    'Loop through Names collection. Delete Print_Area or
    'Print_Titles if found.
    intCounter = ActiveSheet.Names.count
    While (intCounter > 0)
        If ActiveSheet.Names(intCounter).Name = ActiveSheet.Name _
            & "!Print_Area" Then
            ActiveSheet.Names(intCounter).Delete
            intCounter = ActiveSheet.Names.count
        ElseIf ActiveSheet.Names(intCounter).Name = ActiveSheet.Name _
            & "!Print_Titles" Then
            ActiveSheet.Names(intCounter).Delete
            intCounter = ActiveSheet.Names.count
        End If
        intCounter = intCounter - 1
    Wend
    
    'Set print area
    ActiveWorkbook.Names.Add Name:=ActiveSheet.Name & _
        "!Print_Area", RefersTo:=strRange
    'Set titles if needed
    If vbYes Then
        ActiveSheet.PageSetup.PrintTitleColumns = _
            ActiveSheet.Columns("B:C").Address
        ActiveSheet.PageSetup.PrintTitleRows = _
            ActiveSheet.Rows("5:5").Address
    End If
    'Print worksheet
    ActiveWorkbook.PrintPreview
End Sub

click to expand

Copying Data Between Ranges and Arrays

All of the information within a workbook is easily available for manipulation through a VBA macro. Why would you want to copy that information to someplace else before working with it? Speed. It's a very time-consuming process for VBA to read or write information to a worksheet. By minimizing the number of times that VBA needs to read or write to the worksheet, you can greatly reduce the amount of time needed for your procedure to operate.

How can you reduce the number of read and writes to the worksheet? By reading or writing a range of cells at a time. It's the setup time that VBA needs to access a worksheet that takes time. Unfortunately, VBA goes through the same setup process every time it needs to read or write another range, whether the range consists of only one cell or several hundred cells.

Warning

As with most things, there is a point of diminishing returns or outright failure. VBA cannot transfer more than about 3000 cells at one time. As long as you stay well below that number, you should have no problems.

So the question now becomes how can you read or write to multiple cells at one time? Transferring multiple cells between a workbook and VBA is done through the use of variant arrays. A variant data type can hold any other data type, including arrays. More importantly, the variant variable does not have to be declared as an array to hold an array. In fact, for the purposes of copying data to and from a worksheet, the variant variable cannot be declared as an array. The variable needs to be declared as a simple variant type, as in the following statement.

Dim varA As Variant

Reading the cells into a variant variable is done through an assignment statement, that is, varA = Range("January1PM") or varA = Range("H6:H36"). These two examples would read the same range into the variable varA, with the individual cells accessed the same as a single element of an array. Once you have assigned values to the variant, you can use the variable as a reference to the range. For example, you could use this code to display a message box containing just the sum of the values in cells E12:E15.

Sub VariantSum()
Dim varA As Variant, intSum As Integer
varA = Range("E12:E15")
intSum = WorksheetFunction.Sum(varA)
MsgBox (intSum)
End Sub

Note

A range will always return a multi-dimensional array, usually two dimensions, regardless of the number of columns. The second dimension will contain at least one element but could have more depending upon the number of columns within the range.

If you need to determine the size of an array, you can use the LBound and UBound functions to give you the lower and upper boundaries of the array. LBound(var) returns the lower boundary, usually a 0 or 1, whereas UBound(var) will return the upper boundary.

To determine the boundaries of all dimensions in a multi-dimensional array, specify the dimension you want the boundary of. For example, using the statement varA = Range("JanuaryAllValues") in the Y2001ByMonth.xls workbook would read all the cells from B3:Q38. To find the number of rows, you could use UBound(varA, 1) or simply UBound(varA). Retrieving the number of columns would use the command UBound(varA, 2).

When reading the values from a named range, you can also shrink or expand the number of cells being read by using the Resize method. As an example, to read only the first seven rows of data, but three hours worth of data from 1:00 P.M. in the Y2001ByQuarter.xls workbook, you could use the following statement:

varA = Range("January1PM").Resize(7, 3)

Writing information back into the range is a simple reversal of the statement, Range("January1PM") = varA. However, some care should be taken when writing information back into the cells. You should ensure that the range is the same size as the array to prevent overwriting the wrong cells. This is to preserve data integrity and is easily done by combining the UBound function with the Resize method, as follows:

Range("January1PM").Resize(UBound(varA, 1), UBound(varA, 2)) = varA.

A simpler method of ensuring the integrity of your data is to fill the array with the values from the range first. This method will actually serve two purposes: it will size the array to match the range, and it will fill the array with the current values contained within the range so that when the data is written back Excel will maintain the values that have not been changed within the procedure.

The following procedure helps illustrate the difference in processing times between using direct access to the cells and copying the cells to an array first. (Each method is repeated 50 times so that the time needed to perform the reads is high enough to measure.)

Sub ProcessTime()
Dim rgeCells As Range, intCount As Integer
Dim strPrompt As String, strTitle As String
Dim sglStart As Single, sglEnd As Single
Dim rgeCell As Range, varCells As Variant
Dim intRows As Integer, intColumns As Integer
Dim intLoop As Integer
 
    intCount = 0
    strTitle = "Microsoft Office Excel 2003 Programming Inside Out"
    sglStart = Timer
    Debug.Print sglStart
    For intLoop = 1 To 50
        For Each rgeCell In Range("JanuaryAllValues")
            intCount = intCount + 1
        Next rgeCell
    Next intLoop
    sglEnd = Timer
    Debug.Print sglEnd
    strPrompt = "Processing time range method: " & _
        Format(sglEnd - sglStart, "#0.00000") & _
        "   Total Cells:" & Str$(intCount) & vbCrLf
        
    sglStart = Timer
    intCount = 0
    For intLoop = 1 To 50
        varCells = Range("JanuaryAllValues")
        For intRows = 1 To UBound(varCells)
            For intColumns = 1 To UBound(varCells, 2)
                intCount = intCount + 1
            Next intColumns
        Next intRows
    Next intLoop
    sglEnd = Timer
    
    strPrompt = strPrompt & "Processing time array method: " & _
        Format(sglEnd - sglStart, "#0.00000") & _
        "   Total Cells:" & Str$(intCount)
    MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
 
End Sub

< Day Day Up >