Databases Reference
In-Depth Information
shown in this textbox. This textbox is referred to as the “Name Box” in the Excel environment
because it displays the “name” of the currently selected cell or cells.
By default, Ranges are shown in the “Name Box” using an A1 style of addressing. However,
it is possible to select a single or range of contiguous cells and type a new unique name for them
in the Name Box. For example, a single cell could have its range named “SalesSubtotal” and,
presumably, this cell could be referenced as such to update the Sales Subtotal for the Worksheet.
Such a range could then be activated using the following code:
Range(“SalesSubtotal”).Select
In addition to manually setting the range, it can be set from VBA code by using code such as the following:
Range("A10:B12").Select
ActiveWorkbook.Names.Add Name:="AtenthruB12", RefersToR1C1:=
"=Sheet1!R10C1:R12C2"
In this code snippet, a contiguous range of cells (A10:B12) is assigned the name “AtenthruB12”
using VBA code. The above code could easily be modified to reference a single cell.
Custom-named Ranges have one limitation — they cannot encompass multiple discontinuous
areas of a worksheet. Thus, a Range such as that previously illustrated in Figure 1.6
(B8:D10,F8:H10), could not be assigned a custom name. Also notice that when a custom range
encompasses multiple adjacent cells, and a single cell within the custom range is selected, the cell
address is shown instead of the custom name range.
The entire custom range, not a subset of the
range, must be selected for the custom Range name to be shown in the Name Box
.
One last topic on ranges will be covered, which is of paramount importance when constructing
automated data analysis systems. When building functions, it is necessary that every cell referenced by
the function contain data. Because functions are often built utilizing looping structures over a range of
columns and rows (Chapter 4 covers functions in great detail), it is important to have a way of determining
if a range of Cells has any empty Cells. An empty cell is regarded by an Excel function as 0, and if a
division by zero operation is performed in Excel, this message will appear as the result: #DIV/0!
Function EmptyCellsinRange(Workbook, Worksheet, toprow,
leftcol, bottomrow, rightcol) As Boolean
'The following function uses CountA to return the number of non
empty
Cells in a Range
Dim TotalCells As Long, NonEmptyCells As Long
ActivateWorkbook (Workbook)
If OneVisibleWorksheet(Workbook) = False Then
Sheets(Worksheet).Activate
TotalCells = ((bottomrow - toprow) + 1) * ((rightcol - leftcol) + 1)
NonEmptyCells = Application.CountA(Range(Cells(toprow,
leftcol), Cells(bottomrow, rightcol)))
If TotalCells - NonEmptyCells = 0 Then
'All Cells in Range Contain SOMETHING
EmptyCellsinRange = False
Else
'Some Cells in Range ARE EMPTY
EmptyCellsinRange = True
End If
End Function
