Chapter 5: Techniques of Programming
This chapter provides answers to a host of everyday questions about programming: How do I access tables, cells , and regions of cells? How do I carry out calculations with dates and times? How are character strings processed ? How are new worksheet functions defined?
5.1 Cells and Ranges of Cells
Accessing individual cells or entire groups of cells belonging to different worksheets is a bit confusing, because Excel distinguishes among numerous similar objects and concepts. In many cases there are, in fact, several correct ways to proceed.
We first describe all the important objects, methods , and properties that provide for accessing ranges of cells. Then we introduce concrete techniques for applying these keywords to such tasks as editing individual cells of a range of cells and carrying out multiple selection. We then explore the possibilities of data transfer via the clipboard.
Objects, Methods, Properties
The active cell ( ActiveCell ) is the cell of a worksheet in which the cell pointer is located. At this location it is possible to input data via the keyboard. (With program code you can edit inactive cells as well.) A Range ( Range ) is a group of cells. A selection ( Selection ) encompasses the currently selected range. Selection is possible only in an active workbook.
Here is an example to illustrate these concepts: If you select several cells in a worksheet, then these cells are considered a selection. One cell in this selection is the active cell. The selection represents one of infinitely many possible ranges.
The Range Object (Range of Cells)
Range : This keyword can refer to the Range object as well as to the like-named method (see below). The Range object is the central object of this section. A range can consist of individual cells or a group of cells. Even an entire row or column can be a range. Although in general, Excel can also work with three-dimensional ranges (for example, = SUM(Sheet1:Sheet2!A1:B2) forms the sum over eight fields), the Range object in the present version of Excel is limited to ranges of cells in a single worksheet.
VBA does not recognize an object for a single cell. Cells are considered a special case of a range (with Range.Count =1). Numerous properties of ranges can be applied to the special case of a single-celled region, for example, Formula (returns or alters the formula of a cell).
It is possible to edit a range either directly or via the detour of selection. In the first variant the properties and methods of a range are executed immediately after the Range method is executed, as in, for example, Range("A1:B2").Count . In the second variant a range is first made the "active selection" by means of the methods Select or Activate . Then the range is accessed via ActiveCell or Selection , with Selection.Count , say.
Instead of the long-winded Range("A1:B2") for selecting the range A1:B2, the shorthand [A1:B2] is allowed. It is also possible in Range as well as in the shorthand notation to give a named range of cells. Thus with [profit] you can access cell C20 if this cell has previously been associated with the name "profit" by means of INSERT NAME DEFINE.
If now the cells of this range are to be edited individually, each cell of the range can be accessed by means of Cells . ( Cells again returns Range objects!)
Ranges of cells composed of several rectangular regions (manually selected with the mouse and Ctrl) frequently cause difficulties: Most properties and methods that refer to a Range object take into account only the first rectangular segment of the range! To work through all the parts of the range the method Areas is available, which will be discussed below in greater detail.
In many cases, Evaluate can be used instead of Range . Evaluate evaluates a character string passed to it and returns the associated object. Range("A1") corresponds to Evaluate("A1") , and that corresponds in turn to the shorthand [A1] . All three variants return a Range object as result. Furthermore, with Range and Evaluate it is permitted to express the range by means of a string variable, which in the shorthand notation is not possible.
Moreover, Evaluate does not have precisely the same function as Range , even if at first glance that seems to be the case. While Range is suitable exclusively for ranges of cells, Evaluate can also be used with other named objects (such as drawing elements). For almost all applications of Evaluate the shorthand notation with square brackets can be used.
Access to Selected Ranges of Cells
ActiveCell : This property points to the active cell of the application or of a window (that is, to the cell in which the cell pointer is located). ActiveCell can be read, but it cannot be changed. If you wish to move the cell pointer into another cell, you have to use the method Activate , Select , or Offset .
ActiveCell returns Nothing if no worksheet is displayed in the active window (for the Application object) or in the given window. ActiveCell cannot be used for a particular worksheet. (This property is defined only for the Application object and the Window object.) If you wish to determine the active cell of a worksheet that is not currently active, you must first make this worksheet the active sheet of the current window with Worksheets( ).Activate .
Selection : This property is, like ActiveCell , defined only on the Application and Window levels. It refers to the object that has just been selected in the current window. It can be related to a single cell, a selection of cells, or even a chart or button. (You can establish the object type by means of Typename(Selection) .) This property can only be read; it cannot be altered directly. The selected object can, rather, be manipulated by means of the method Select Activate .
RangeSelection : This property is a variation on Selection . It returns the selected range of cells even when another object (such as a chart or button) is active.
UsedRange : This property returns the range of a worksheet whose cells are actually being used. In contrast to Selection , this property can be used with worksheets (not only windows ).
Selection of a Range
Range : The Range method returns a Range object. A Range represents a group of worksheet cells (in the simplest case only a single cell). Examples: Range("A1") , Range("A1:B3") , Range("Table2!B7") .
Range usually refers to the active worksheet. If Range is applied to another Range object, then the cell references are considered to be relative to the upper left corner of the range. Example: Range("B3:D4").Select: Selection.Range("B1") returns a reference to cell C3. (Thus in this example B1 means, "column plus 1, same row," and refers to the starting point B3.)
In program code it is often awkward to give references to cells in the form "A1". The reason is that within Range , Cells(row, column) can be used. Range(Cells(1, 1), Cells(4, 2)) corresponds to Range("A1:B4") . The advantage of writing things this way with Cells is that Cells expects numerical parameters and therefore can easily be placed within a loop.
| Tip | Cells automatically refers to the active worksheet. If you want to access cells of another sheet, then the correct way of writing it is not Worksheets(n).Range(Cells( ) , Cells( )) , but rather Range(Worksheets(n).Cells( ) , Worksheets(n).Cells( )) ! |
Note that with Range(Cells( ), Cells( )) only simple rectangular ranges can be defined. Ranges of a complex form must be constructed out of several rectangular ranges with Union . For individual cells the expression Range(Cells(z, s)) is not allowed ”in this case, however, Range can simply be avoided, since Cells directly delivers a single cell.
Offset : This method returns a range that is offset to the range given as object. Thus, for example, [A1].Offset(3, 1) returns a reference to the cell B4. This method does not change the active range (in contrast to Select and Activate ). The instruction ActiveCell.Offset(0, 1).Select moves the cell pointer one cell to the right.
| Caution | Both Offset and Cell expect the parameters in the order (row, column). This is the opposite of the usual order in the way that cells are named (such as B5, where first column B is given and then row 5), as well as the mathematical custom by which first the x and then the y coordinate is given. |
Select and Activate : The first of these two methods selects the given object, while the second activates it. Both methods are used without parameters and do not return a direct result. They merely change properties such as ActiveCell and Selection .
According to the on-line help, Activate is intended for individual cells and Select for ranges of cells. In fact, there is difference in most cases between the two methods. Regardless of whether you select the range Range("A1:B3") via Activate or Select , A1 becomes the active cell and A1:B3 the active range.
With both methods only cells or cell ranges in the active worksheet can be selected. Range("Table2!A1").Activate leads to an error if at the time Table1 is the active worksheet.
One can see a difference between Activate and Select when one attempts to select a cell within a range of cells. In the following example, first B2:C3 is selected, and then the third cell in this block is activated.
[b2:c3].Select Selection.Cells(3).Activate 'Selection --> B2:C3, ActiveCell --> B3
In the second example, first the same range of cells is selected, but then the third cell is selected with Select . However, with the second Select method the first selection is undone. Therefore, B3 is now both the selected range and the active cell.
[b2:c3].Select Selection.Cells(3).Select 'Selection --> B3, ActiveCell --> B3
GoTo : This method selects a range, and is thus comparable in its effect with Select . However, the syntax is quite different from that of Select ”the range to be selected is given not as an object (via Range ) but in a parameter: GoTo Worksheets(n).Range("C10") . It is allowed for the range of cells to be located in another worksheet (this worksheet will then be automatically activated). With the optional scrolling parameter it is possible to set the scroll bars in such a way that the range that is selected is, in fact, visible. This does not always happen automatically.
Access to Cells and Ranges of Cells
Cells : With the method Cells it is possible to access an individual cell of a worksheet or of a rectangular range of cells. One may give two-dimensional input in the form Cells(row, column) or by a sequential number: Cells(n) . In the second variant the cells are numbered row by row. When an entire worksheet is used as object, then 1 corresponds to cell A1, 2 to cell B1, 256 to cell IV1, 257 to cell A2, and so on. (Note that 256 is the maximum number of columns allowed. It is possible that a future version of Excel would allow more than 256 columns !)
The row-by-row numbering also holds for ranges of cells: Range("A1:C2").Cells(4) refers to cell A2. The cell given by Cells can lie outside the given range, for example, Range("A1:C3").Cells(10) for A4 or Range("C3:D4").Cells(4, 5) for G6. (G is the fifth column if numbering begins with C, while 6 is the fourth row if the numbering begins with 3.)
| Tip | You can try out the addressing of cells in the immediate window of the development environment. Just use the property Address , which gives the resultant address of a range of cells: ?Range("a1:c2").Cells(4).Address $A |
| Tip | In ranges composed of several rectangular blocks, only the first block can be accessed with Cells . To access all the ranges the method Areas must be used. |
Areas : This method is similar to Cells , but it returns connected (rectangular) ranges of cells. The application of Areas is necessary for working on ranges that comprise several rectangular ranges (for example, after several uses of Ctrl ).
Row , Column : These properties return the row and column number of a cell (or the number of the first row or column of a range).
Columns , Rows : These two methods enable convenient access to required columns or rows of a range of cells. The number of columns or rows of a range can be determined with range.Columns.Count or range.Rows.Count .
EntireColumn , EntireRow : These two properties return the columns or rows in which the given range is located. Rows and columns are not treated as actual objects, but as normal ranges of cells.
Offset , Resize : Offset returns a range offset by a number of rows or columns from the given range. For example, [A1].Offset(3,1) returns the cell B4. With Resize you can alter the size of a range. The desired number of rows and columns is passed as parameter. Thus [A1].Resize(2,3) returns the result A1:C2.
Union and Intersect : The first of these two methods forms a single range out of several individual ranges (their union), while the second returns the range of cells common to all the given ranges (their intersection). For experienced programmers: Union corresponds to logical "or," Intersect to logical "and." Intersect is suitable, for example, to select from a range of cells all those cells that lie in a particular row or column. With Union you can form a multiarea range out of several rectangular ranges.
SpecialCells , RowDifferences , ColumnDifferences , CurrentRegion , CurrentArray , [Direct]Precedents , [Direct]Dependents : With these methods and properties particular cells of a region can be referenced: for example, all empty cells, all visible cells, all connected cells. These keywords make it possible to access all regions that can be selected with EDITGO TO SPECIAL.
| Caution | The methods SpecialCells and CurrentRegion do not work if they appear in the execution of user -defined worksheet functions. Instead of using these methods, you must recreate these functions using loops , which is tedious to program and slow in execution. In this regard see the user-defined function Holiday , which is described later in this chapter. |
Determining the Address of a Range of Cells
Address : This method returns the address of a range in the form of a character string. If the range A1:B4 is selected, then Selection.Address returns the string "$A$1:$B$4" . By means of various parameters one can control the transformation into a string (such as absolute vs. relative address, A1 or R1C1 format, local vs. external reference). AddressLocal functions like Address in that it returns addresses, but it does so in the regional format of the language of the particular country.
If you have an address, whether in A1 or R1C1 format, you can work with the address further with the Application method ConvertFormula , which makes possible, among other things, a conversion between A1 and R1C1 notation and between absolute and relative addressing.
| Tip | In the "R1C1 notation" (R for "row," C for "column"), cells are specified by row and column numbers . In "A1 notation" the column letter is followed by the row number. The A1 notation is default in Excel, but you may use the R1C1 notation instead: ToolsOptionsGeneralR1C1 Reference Style. |
Named Ranges
Names : This method of the currently active worksheet makes it possible to access named ranges of cells. With Add a new name can be defined or an already existing name changed. What is important is that the range of cells is given absolutely and with a prefixed equal sign, say, Names.Add "rangename", "=$d$5" . In what follows , named ranges can be used with Range or in the shorthand form [rangename] . Names("rangename").Delete deletes the definition of a name. With the method Goto you can quickly move the cell pointer into a named cell or region.
| Tip | Many short programming examples related to named ranges can be found in the example file Names.xls . |
Furthermore, Name objects can be used not only for naming ranges of cells, but also for other tasks. For example, the additional program MS-Query saves its query parameters in a palette of Name objects. Here the Visible property of the objects is set to False , so that these objects do not appear in the form INSERTNAMEAPPLY. The contents of the parameter can be obtained from the Value property.
| Caution | Microsoft was apparently not clear as to whether the definition of a new named range should be associated to the workbook or to a special worksheet: On the one hand, it should be possible to use the same name in every worksheet without one instance of the name interfering with another. On the other hand, it is also desirable that a name that has been globally defined be available in all worksheets. The result is that the first time a name is defined, its definition is valid for the entire workbook. If the same name is used again in another worksheet, then this definition is local for this worksheet. The old definition is valid for all other worksheets. The consequence is that it is sometimes extremely difficult to determine whether a definition of a name must be invoked by ActiveWorkbook.Names( ) or by ActiveSheet.Names( ) . In case of doubt, decide in favor of the first variant. There the Names listing contains all local definitions (of the currently active worksheet) as well as all global definitions that are overlaid by the current worksheet. |
Notes/Comments
Every cell can have a note stored with it. Setting or retrieving it is accomplished with the method NoteText . Since with Excel methods character strings to be passed as parameters can have a maximum of 255 characters, this method has two parameters by which the start and end position within the note can be given. These parameters make it possible to read or edit notes that are longer than 255 characters .
Starting with Excel 97 notes are also called comments. They are controlled by the new Comment object. (However, NoteText may still be used.) With the method AddComment new comments can be defined. ClearComment deletes existing comments. The listing of Comments for the WorkSheet object helps in tracking down all comments in a worksheet.
Adding and Deleting Ranges of Cells
There are five methods available for deleting cells: ClearContents deletes only the contents of cells, leaving the format intact. ClearFormats has the opposite effect, clearing only the formatting. ClearNotes deletes the notes associated to the cells. Clear deletes both the formatting and the contents. A quite different effect is exhibited by Delete . With it the cells of a worksheet are made to disappear; the cells to the right or below are promoted to the vacated location(s) (this corresponds to the command EDITDELETE).
For inserting new cells into the worksheet there exists the method Insert . It is similar to Delete in that with an optional parameter you can determine whether cells should be shifted to the right or downwards. (When no parameter is given, Excel attempts to figure out the more logical choice.)
Content and Format of Cells
The following paragraphs describe the most important properties of the Range object for setting the content and format of cells. Most of the properties listed in what follows will be used in the usual way in single-celled ranges. Reading the properties in multicelled ranges leads to various results (such as an error or setting the first cell of the range). More standard is the reaction in changing the properties of multicelled ranges. Here the settings of all affected cells are changed.
Value : This property contains the value of the cell (in the case of formulas, the result). Empty cells can be determined with IsEmpty(obj.Value) . With the definition of the Value property the content of cells can be changed. Formulas are input as character strings that begin with an equal sign: obj.Value= "=A4" . The Value property is the default property. This means that the shorthand obj="=A4" is also permissible. Value2 differs from Value in that dates and currency values are not identified in the formats of Date and Currency , but as floating point numbers. In many cases this simplifies further processing.
Text : This property contains the content of a cell as a character string. Text is distinguished from Value by two particular features: With values, Text returns an appropriately formatted string (while Value returns the value directly, as a number or a date); furthermore, Text can only be read (and not changed). References to cells must be handled with Value .
Characters : With this method one can access individual characters of a text constant in a cell (for example, in order to set the type characteristics of a single character).
FormulaLocal and FormulaR1C1Local : These properties return the formula of a cell in the A1 or R1C1 format (see below) in the local language. In the case of empty cells an empty string is returned, while with formulas with constants the values of the constants are returned. For example, if A5 contains the formula = SUM(A1:A4) , then in Germany, say, [A1].FormulaLocal returns the string = SUMME(A1:A4) , while [A1].FormulaR1C1Local returns = SUMME(Z(-4)S:Z(-1)S) .
Formula , FormulaR1C1 : These two related properties return formulas in international (that is, English) syntax in the A1 or R1C1 format. [A1].Formula returns the string = SUM(A1:A4) , while [A1].FormulaR1C1 returns = SUM(R[-4]C:R[-1]C) .
Formula returns or expects the formula in English. The formula is also saved internally in this format and is displayed with the properties of the local language only in the Excel interface. A formula that is displayed in German Excel in a worksheet as = Euroconvert(1, 2; "DEM"; "EUR";WAHR) corresponds to the Formula character string "=Euroconvert(1.2, 'DEM', 'EUR', TRUE)" (period instead of comma for the decimal point, commas instead of semicolons as separators, TRUE instead of WAHR ).
HasFormula : This property tells whether a formula exists in a cell ( True or False ).
Font : This property refers to the Font object, which determines a number of properties of the typeface in a cell, such as Name (of the font), Size , Bold , Italic .
Orientation , HorizontalAlignment , VerticalAlignment , WrapText : These properties determine the orientation of the text (horizontal or vertical), the justification (left/centered/right/justified or top/middle/bottom), and line breaking (true or false). New in Excel 97 was the possibility of using Orientation to give an angle in the range ˆ’ 90 ° to 90 ° for the orientation of text. Zero degrees corresponds to normal horizontal text, with the angle being determined from this position in the counterclockwise direction (the standard in mathematics). With 45 the text runs diagonally up from left to right, while ˆ’ 45 yields text running down and to the right.
With IndentLevel one can determine how far to the right the content of a cell should be shifted (the permissible range is 0 to 15).
ColumnWidth , RowHeight : These properties determine the width of the entire column and the height of the entire row.
Borders : This method refers to six Border objects (left/right/above/below and diagonally up and down) whose properties control the appearance of the border: LineStyle , Weight , Color .
BorderAround : With this method the entire border can be set.
Formatting of Numbers (NumberFormat, NumberFormatLocal, and Style)
NumberFormat returns the number format of the cell as a character string. NumberFormatLocal carries out the same task, but the string returned conforms to the conventions of the local language. Finally, Style refers to a formatting template ( Style object).
To obtain a fuller understanding of the situation it would be a good idea to begin with styles. As was shown already in the introductory example back in Chapter 1, a style can be used to set a number of format characteristics of a cell, such as the font, text orientation, and color. Most formatting instructions are easily understood , and so in this section we shall limit ourselves to a discussion of number formats.
In VBA, styles are invoked as Style objects. Every Excel file ( Workbook object) can use Styles to access available styles in the file. Some format templates are predefined ( Builtin=True ) and are thus always available for use. The following loop returns a table of all such styles.
Dim s As Style For Each s In ThisWorkbook.Styles If s.BuiltIn = True Then Debug.Print s.Name, s.NumberFormat End If Next
Predefined Styles
| NAME | NUMBERFORMAT |
|---|---|
| Comma | _(* #,##0.00_);_(* (#,##0.00);_(* "-"_);_(*@_) |
| Comma [0] | _(* #,##0_);_(* (#,##0);_(* "-"_);_(*@_) |
| Currency | _($* #,##0.00_);_($* (#,##0.00);_($* "-"_);_(*@_) |
| Currency [0] | _($* #,##0_);_($* (#,##0);_($* "-"_);_(*@_) |
| Normal | General |
| Percent | 0% |
| Tip | In the English version of Excel the properties Name and NameLocal as well as NumberFormat and NumberFormatLocal contain identical character strings. However, in international versions this is not the case. There the Local properties contain the region-specific settings, which take precedence over the international settings. For example, in the German version NameLocal contains "W hrung" instead of "Currency" . The connection between basic properties and their Local variants is unfortunately extremely poorly documented. |
The difference between Comma and Comma [0] and between Currency and Currency [0] is that in each case the first variant displays two places to the right of the decimal point, while the [0] variant displays none.
All cells that are not otherwise explicitly formatted are automatically formatted with the style Styles("normal") . It is not possible to define the Style property of a cell to be Nothing . Every cell must be formatted with one style or another.
When you format a cell directly, this format takes precedence over the settings of the overall style. However, the style remains valid for all formatting that is not directly altered.
With the currency, percent, and comma style buttons in the formatting toolbar the style in the affected cell can be changed to Comma , Currency , or Percent .
| Tip | If you use the euro add-in, then a further style is defined. Euro is used for Name and NameLocal . The NumberFormat[Local] character strings look as follows: _-* #.##0, 00 [$ -1]_-;-* #.##0, 00 [$ -1]_-;_-* "-"?? [$ -1]_- |
Let us proceed to the property NumberFormat , which is used for styles ( Style object) as well as for direct cell formatting ( Range object). In the case of an unformatted cell, NumberFormat contains the character string "General" , which is based on the style Normal . However, "General" is an exception. As a rule, NumberFormat is set by a rather horrifying string.
Here is some brief information on constructing this string. It normally consists of four parts, separated by semicolons:
positive;negative;zero;strings
The first part is concerned with positive numbers, the second with negative numbers, the third with the value zero, and the fourth with character strings. If you provide only the first part, then all numbers will be formatted according to this format. Strings will be formatted in the standard way (flush left, without indentation).
The following list describes the meaning of the most important symbols in NumberFormat :
| ; | separates the four parts of the string |
| # | placeholder for a digit or for all significant digits |
|
| placeholder for a digit; if the place is not significant, then instead a 0 is shown. For example, 123.00 or 0.12 is obtained from #0.00 |
| ? | placeholder for a digit; if the place is not significant, then a space character is displayed |
| . | decimal point (displayed according to the local format. In Germany, for example, a comma is displayed; in any case, in NumberFormat a period must be given) |
| , | thousands separator |
| % | placeholder for a number in percent format (0.1 is transformed to 10%) |
| _x | leaves blank space in the size of the following character x ; this character itself is not shown; _( means, for example, that a space the size of a parenthesis is left |
| _ | is often inserted to ensure that numbers are displayed aligned and in fact, independently of whether they are positive or negative ( enclosed in parentheses) or whether they are displayed with or without the currency symbol |
| "x" | indicates a character string between the quotation marks |
| *x | fills the remaining space with the symbol x ; *x can be used only once in each part of the NumberFormat character string |
| *-# | means that before the number enough hyphens are inserted so that the cell is completely filled, for example, " ” ” ” ” 123" |
| "DM"* | # means that the symbol DM (the symbol for German marks) appears flush left and the number flush right, with the necessary number of blank characters in between |
| $ | placeholder for the currency symbol defined in the system settings; this placeholder $ is unfortunately not documented |
| *@ | placeholder for a character string (for the fourth part of NumberFormat ) |
| Tip | In addition to the symbols defined here there are numerous additional symbols for formatting of dates, times, fractions, exponentials, and so on. Further information about these symbols can be found in the Excel on-line help under "Create a custom number format." (If you have opened the VBA on-line help, then you have to close it, switch to Excel, and summon the Excel on-line help. This is the only way to open the correct help file! The on-line help for NumberFormat is useless.) In most cases the easiest way to achieve a correct NumberFormat character string is to set a format with FORMATCELLS and view the resulting string in the immediate window with Debug.Print ActiveCell.NumberFormat . |
The last property left to be explained is NumberFormatLocal . This property's documentation is practically nonexistent. Experiments with the German version of Excel has shown that there is no simple one-to-one translation of code into the local format. For example, the position of the currency symbol is shifted. With the format Currency positive numbers are formatted, according to NumberFormat _($* #,##0.00_) (that is, with the currency symbol before the number). However, according to NumberFormatLocal they are formatted with _ ˆ’ * #.##0,00 ‚ _ ˆ’ (that is, with the currency symbol at the end). NumberFormatLocal has precedence over NumberFormat .
For this reason an attempt to change NumberFormat with program code can lead to curious results. Let us suppose that we have formatted the number 1234 as currency ( 1.234,00 ‚ ). If the instruction
ActiveCell.NumberFormat = _ Replace(ActiveCell.NumberFormat, "$", """DEM""")
is carried out, to replace the system-setting currency symbol with the string "DEM" , then 1.234,00 ‚ is suddenly turned into DEM 1.234,00 , that is, the currency sign has been moved from the back to the front. To achieve the desired result you have to execute the following instruction instead:
ActiveCell.NumberFormatLocal = _ Replace(ActiveCell.NumberFormatLocal, " ", """DEM""") However, the direct editing of NumberFormatLocal is not an optimal solution, because your program code becomes country-specific , which leads to unexpected (and seldom correct) results with Excel versions in other languages.
Let us consider one more example. You set NumberFormatLocal="T.M.JJJJ" (which returns, for example, 1.12.1999, which is a date in German format; T, M, and J are abbreviations for Tag (day), Monat (month), and Jahr (year)). If you now select NumberFormat , then you get "d/m/yyyy" , which is more or less in one-to-one correspondence, even if written with slashes instead of periods.
On the other hand, if you set NumberFormatLocal="TT.MM.JJJJ" (which returns 01.12.1999), then you get NumberFormat="m/d/yy" ! The order of month and day has been switched! (The reason is perhaps that Excel recognizes "TT.MM.JJJJ" as a predefined German format and has a table of international correspondences. Perhaps "T.M.JJJJ" does not appear in this table, and the transformation for NumberFormat is accomplished by some other mechanism. All in all, there are many open questions in connection to NumberFormatLocal , questions that could be answered only by Microsoft, which for some reason known perhaps only to its chairman has neglected to provide adequate on-line documentation.)
Find and Replace
With the method rng.Find "character string" you can search for text within a range of cells. The method returns a Range object with the first cell that contains the search text. If Find doesn't find anything, then the method returns Nothing as result.
To replace one character string by another within a range of cells, use rng.Replace "abc", "efg" . The method always returns True as result, regardless of whether or how often the first character string is replaced by the second.
| Pointer | Extensive information on the numerous optional parameters associated with the Find method, as well as an example of the use of the method, can be found in Chapter 11. These same parameters can be used with Replace . Please note that you cannot rely on the default settings of the two methods! If any parameter is not specified, the previously used setting is employed. If the search result or replacement is to be independent of previous settings, then you must always specify all parameters! |
Extended Search Functions in Excel 2002
In Excel 2000 you can search by content, but not by format. This option is offered by the method Find and Replace beginning with version 2002. But first, the desired formatting must be set using one or two CellFormat objects. Access to these objects is obtained via the properties FindFormat and ReplaceFormat of the Application object. Then, when the Find or Replace method is called, the parameters SearchFormat := True and ReplaceFormat := True are specified, allowing the format specifications to be searched.
The following example shows how all bold-formatted cells in the first worksheet can be formatted in italic. Note in particular that with both FindFormat and ReplaceFormat , first Clear is used in order to delete previous settings.
Application.FindFormat.Clear Application.FindFormat.Font.FontStyle = "bold" Application.ReplaceFormat.Clear Application.ReplaceFormat.Font.FontStyle = "italic" Worksheets(1).Cells.Replace "", "", _ SearchFormat:=True, ReplaceFormat:=True
A1 Versus R1C1 Notation
In general, cell references are given in the A1 notation. Range("R1C1") is not permitted. In program code it is often more practical to set cell references with Cells . This method expects numeric parameters as input of the row and column numbers and therefore corresponds to the R1C1 syntax.
Formulas in worksheets represent a special case. With the property Formula the formula of a cell in the A1 format can be read or edited, while FormulaR1C1 does the same for the R1C1 notation, and FormulaR1C1Local in, for example, the Z1S1 notation in the German version ( Zeile = row, Spalte = column).
The property Application.ReferenceStyle determines how cell references are displayed in Excel. This property can exhibit either of the values xlA1 or xlR1C1 . The format in which the cell reference will be shown can also be set in TOOLSOPTIONSGENERAL.
Programming Techniques
Setting and Moving the Active Cell
Range("B1").Select ' activates B1 [B1].Select ' likewise activates B1 Cells(1, 2).Select ' likewise activates B1 ActiveCell.Range("A2").Select ' activates the cell one row down ActiveCell.Offset(1, 0).Select ' activates the cell one row down | Tip | All of the lengthier examples of this section can be found in the example file Cells.xls . |
Selecting and Editing Ranges of Cells
As a rule, the macro recorder leads to code that looks something like that in the example below.
Range("D11:F14").Select With Selection.Font .Name = "Courier New" .Bold = True End With The pattern that generally prevails in macro recording ”first select a range of cells, then execute various settings ”is not compulsory. The following example code fulfills the same task without altering the current position of the cell pointer or the current range of selected cells:
With Range("D11:F14").Font .Name = "Courier New" .Bold = True End With In many cases the use of With leads to more readable and efficient code, but it is not compulsory, as our last example shows:
Range("D11:F14").Font.Name = "Courier New" Range("D11:F14").Font..Bold = True However, giving cell references in the A1 form is, on the one hand, difficult to read, and on the other, extremely inflexible in changing the structure of the worksheet. If you were to add a row or column to the worksheet, you would have to alter the code of the entire macro!
For this reason you should provide names for frequently used ranges of cells, which can be done via INSERTNAMEDEFINE. Then you can use these names in your code: Range("Name") . (The macro recorder is, unfortunately, not able to use predefined names automatically. You have to massage the code that it generates.)
Copying and Moving Cells
| Pointer | Copying, cutting, and inserting ranges of cells are generally accomplished with the Range methods Copy, Cut, and Paste . These will be discussed, together with other commands, in the section "Data Transfer via the Clipboard" in this chapter. |
Selecting More Complex Ranges of Cells
In practice, it happens frequently that you wish to select associated ranges of cells whose sizes are variable. You often select such ranges via the keyboard with End , Shift+Cursor key. In VBA code you can use the method End(xlXxx) to select a single cell at the end of a block of cells. With Range you can then access a range of cells between two corner cells. (The macro recorder does not use this method, sad to say, and instead produces inflexible cell references. The macro thus created is thus unable to cope with an altered worksheet structure and so must be changed manually.)
Sometimes, the properties CurrentRegion and CurrentArray or the Method SpecialCells can be of assistance. However, in user-defined worksheet functions these functions often cause trouble. If necessary, you must work through the range in question cell by cell to find the positions of the initial and terminal cells.
The sample procedure SelectRow selects ”beginning with the current position of the cell pointer ”all connected cells of a row. By this we do not mean the entire row of a worksheet, but only a group of occupied (not empty) cells.
This procedure merely shows how such a selection is made. However, the two cells cell1 and cell2 could more easily be determined with End instructions.
First a few words about how the procedure functions: The starting point is the active cell startcell . If this cell is empty, then the procedure is exited at once. Otherwise, in a For loop a search is made for the last nonempty cell to the left of the current cell. With Set a reference to this cell is stored in cell1 . The If test at the end of the loop takes care of the special case that the row contains values all the way to the first column, in which case the loop terminates without a reference being stored in cell1 .
' example file Cells.xls ' selects a connected range of cells within a row Sub SelectRow () Dim startCell As Range, cell1 As Range, cell2 As Range Dim rowNr&, colNr& Set startCell = ActiveCell rowNr = startCell.Row: colNr = startCell.Column If IsEmpty(startCell) Then Exit Sub ' look for left end of row; store end cell in cell1 For colNr = startCell.Column To 1 Step -1 If IsEmpty(Cells(rowNr, colNr).Value) Then Set cell1 = Cells(rowNr, colNr + 1) Exit For End If Next colNr If cell1 Is Nothing Then Set cell1 = Cells(rowNr, 1) ' search for right end of row; end cell stored in cell2 For colNr = startCell.Column To 256 If IsEmpty(Cells(rowNr, colNr).Value) Then Set cell2 = Cells(rowNr, colNr - 1) Exit For End If Next colNr If cell2 Is Nothing Then Set cell2 = Cells(rowNr, 256) ' select the range between cell1 and cell2 Range(cell1, cell2).Select End Sub
In analogy to the first loop, in the second loop the last nonempty cell to the right is sought and then stored in cell2 . Finally, the range between cell1 and cell2 is selected.
Combining Ranges (Union and Intersection)
The method Union is used to combine several ranges of cells into a composite range. The resulting range does not have to be connected. To edit such a disconnected range requires the Areas object (see below).
In the first example Union is used to enlarge a preexisting selection to include the cell A4 (corresponds to clicking on A4 while holding down the Ctrl key).
Union(Selection, Range("A4")).Select In the second example first the range A1:D4 is selected. Then Intersect is used to select that part of the range that is contained in column A. The new selection thus comprises the range A1:A4.
Range("A1:D4").Select Intersect(Selection, Range("A:A")).Select | Caution | If you join two overlapping Range objects with Union , the cells common to both objects are multiply contained in the united object. The effect is the same as that obtained by selecting overlapping ranges of cells with the mouse while holding down the Ctrl key (see Figure 5-1). |
Figure 5-1: Cell D6 is doubly selected.
Editing All Cells of a Rectangular Range
With the method Cells you can access all the cells of a rectangular range. The example below shows a concrete application: The macro IncreaseFontSize sets a 2-point larger typeface for all cells of the current selection. Therefore, the macro has a similar function to that of the INCREASE FONT SIZE tool.
The significant difference is that the macro edits each cell individually, while clicking on the tool results in the font size of all the selected cells being determined by the font size of the first selected cell.
The test If Selection Is Nothing is necessary to avoid an error in the case that the macro is inadvertently launched in a chart of a module sheet. (There are no cells in this case whose font can be changed.)
Sub IncreaseFontSize () Dim cell As Range If Selection Is Nothing Then Exit Sub For Each cell In Selection.Cells cell.Font.Size = cell.Font.Size + 2 Next cell End Sub
The loop could have been formulated differently (though less elegantly):
Dim i As Integer If Selection Is Nothing Then Exit Sub For i = 1 To Selection.Cells.Count Selection.Cells(i).Font.Size = _ Selection.Cells(i).Font.Size + 2 Next Cell
Edit All Cells of a Compound Range
The example above has one shortcoming: It does not work with compound ranges of cells. Such ranges result when you select several ranges with the Ctrl key or when you create ranges using Union or Intersect . In such a case the above macro changes only the cells of the first rectangle. In order that all cells be changed, the individual component ranges must be accessed via the Areas method:
' example file Cells.xls Sub IncreaseFontSize () Dim rng As Range, ar As Range If Selection Is Nothing Then Exit Sub For Each ar In Selection.Areas For Each rng In ar rng.Font.Size = rng.Font.Size + 2 Next rng Next ar End Sub
| Caution | If a user first selects D3:D10 and then B6:F6 using Ctrl (see Figure 5.1), then cell D6 is contained in both ranges, and is therefore doubly selected! With the procedure IncreaseFontSize above this would result in the font of cell D6 being increased not by 2 points like all the others, but by 4 points. There can exist applications in which a multiple editing of the same cell must be excluded. In such cases a list must be kept of all cells that have already been edited. The following adapted version of IncreaseFontSize demonstrates one possible way of proceeding. |
' in cellsDone is kept an address list of all cells ' that have already been edited in order to avoid ' a possible multiple editing of the same cell Sub IncreaseFontSize () Dim rng As Range, ar As Range Dim cellsDone$, thisCell$ If Selection Is Nothing Then Exit Sub For Each ar In Selection.Areas For Each rng In ar thisCell = "[" + rng.Address + "]" If InStr(cellsDone, thisCell) = 0 Then rng.Font.Size = rng.Font.Size + 2 cellsDone = cellsDone + thisCell + " " End If Next rng Next ar End Sub
Setting a Font
Setting the font of an entire cell can be accomplished simply by changing the properties of the Font object.
With Selection.Font .Name = "Courier New" .Bold = True .Size = 10 .Strikethrough = False ' etc. End With
It is more complicated to edit the font characteristics of individual letters than those of the entire cell. In general, this is possible only if the cell contains a text constant (not a number, not a formula). Access to individual characters is accomplished with the method Characters . The example macro below formats the characters of a cell with increasingly larger fonts; that is, the first letter is set to 10 points, the second to 11, and so on.
Sub SpecialFont () Dim i& If IsEmpty(ActiveCell.Value) Or ActiveCell.HasFormula Then Exit Sub If IsNumeric(ActiveCell.Value) Then Exit Sub For i = 1 To ActiveCell.Characters.Count ActiveCell.Characters(i, 1).Font.Size = 9 + i Next i End Sub
Our last example is a bit more practical. It changes the text style of the selected cells among the styles normal, bold, italic, and bold italic. Each time it is called, the text is transformed into the next style. The macro can then be assigned to a new tool in the toolbar. This tool can replace the two existing tools BOLD and ITALIC, while occupying only half of the high-priced real estate on the toolbar, which is always too small for everything that you want to put on it.
' shifts among normal, bold, italic, and bold italic. Sub ItalicBold () Dim bld As Variant, ital As Variant If Selection Is Nothing Then Exit Sub bld = Selection.Font.Bold: ital = Selection.Font.Italic If Not bld And Not ital Then bld = True ElseIf bld And Not ital Then ital = True: bld = False ElseIf Not bld And ital Then bld = True Else bld = False: ital = False End If Selection.Font.Bold = bld: Selection.Font.Italic = ital End Sub
Borders
For each cell, Excel manages six Border objects, which describe the left, right, upper, and lower borders of the cell and the diagonal lines within the cell. You can access the individual border objects with Cell.Borders(n) , where n can be replaced by any of the following constants:
| xlEdgeTop | upper |
| xlEdgeBottom | lower |
| xlEdgeLeft | left |
| xlEdgeRight | right |
| xlDiagonalDown | diagonal from upper left to lower right |
| xlDiagonalUp | diagonal from upper right to lower left |
| xlInsideHorizontal | horizontal lines within a group of cells |
| xlInsideVertical | vertical lines within a group of cells |
xlInsideVertical and xlInsideVertical can be used to draw lines in ranges that stretch over several cells. They cause a change in the affected Top/Bottom/Left/Right borders. Internally, however, for each cell only six border lines are managed (corresponding to the first six constants in the above table).
| Tip | For some strange reason the loop For Each b In rng.Borders does not always encompass all the borders of a cell or region of cells. Instead, use the following: For Each i In Array(xlEdgeTop, xlEdgeBottom, xlEdgeLeft, _ xlEdgeRight, xlDiagonalDown, xlDiagonalUp) Here i must be declared a Variant variable. |
| Caution | When in Excel a border line between two cells lying one above the other is visible, there are three possibilities: It is the lower border of the top cell, the upper border of the lower cell, or both of these. When you use FORMATCELLSBORDER in interactive mode, then when a border is removed, Excel acts automatically on the neighboring cells to remove all affected borders. In changing a border with VBA code you must take care of this yourself. The following example shows the correct way to proceed. |
The following macro offers a solution. It removes all borders of the previously selected cells, even if the borders actually belong to a neighboring cell:
' example file Cells.xls Sub RemoveAllBorders () Dim calcMode&, updateMode&, i Dim rng As Range, ar As Range Dim brd As Border If Selection Is Nothing Then Exit Sub ' speed optimization calcModue = Application.Calculation updateModue = Application.ScreenUpdating Application.Calculation = xlManual Application.ScreenUpdating = False ' For Each ar In Selection.Areas ' for each region of cells For Each rng In ar ' for each cell ' delete all borders of the current cell For Each i In Array(xlEdgeTop, xlEdgeBottom, xlEdgeLeft, _ xlEdgeRight, xlDiagonalDown, xlDiagonalUp) rng.Borders(i).LineStyle = xlLineStyleNone Next i ' remove the right border of the cell bordering on the left If rng.Column > 1 Then rng.Offset(0, -1).Borders(xlRight).LineStyle = xlLineStyleNone End If ' remove the left border of the cell bordering on the right If rng.Column < 256 Then rng.Offset(0, 1).Borders(xlLeft).LineStyle = xlLineStyleNone End If ' remove the lower border of the cell above If rng.Row > 1 Then rng.Offset(-1, 0).Borders(xlBottom).LineStyle = xlLineStyleNone End If ' remove the upper border of the cell below If rng.Row < 65536 Then rng.Offset(1, 0).Borders(xlTop).LineStyle = xlLineStyleNone End If Next rng Next ar ' end speed optimization Application.Calculation = calcMode Application.ScreenUpdating = updateMode End Sub
Speed Optimization
The execution of a procedure that makes extensive changes in a worksheet can be quite slow. Two possible reasons for this are the time it takes for constant updating of the display and the time required for recalculating the worksheet after each change. You can improve the performance of your macros dramatically if you deactivate screen updating and worksheet recalculation during execution. To do this you have to set the Application properties ScreenUpdating and Calculation at the beginning and end of the procedure.
The procedure RemoveAllBorders (see the example above) saves the current values of both properties at the start, and then sets them to False and xlManual , respectively. At the end of the procedure the original values are restored.
Goalseek and Solver Add-In
The command TOOLSGOAL SEEK allows one to have a value in one cell set in order to attain a specified value in another cell. You can use the Goalseek method in program code. For example, the following instruction changes the content of A2 in such a way that in A1 the goal value 0.6 is achieved as closely as possible. (Of course, this will work only if A1 contains a formula whose result depends in some way on A2.) Goalseek returns the result True or False according to whether the goal value is or is not achieved.
Range("A1").GoalSeek Goal:=0.6, ChangingCell:=Range("A2") Goalseek has the disadvantage that only a single dependent cell is varied. For more complicated situations one has the Solver add-in (command TOOLSSOLVER), which deals with several dependent cells and moreover, can consider side conditions. This Add-In must first be activated with TOOLSADD-INS.
The Solver add-in can be run via VBA code, although it is much more complex than the Goalseek method. The main problem is that the functions of the add-in accessible via VBA are not documented. Therefore, the only way to achieve functioning code is usually by way of macro recording, which was used in developing the following example. The attempt is made to find values for cells A7 and A8 that give A6 the largest possible value (a local maximum, MaxMinVal:=1 ).
The basic process is not difficult to understand: SolverOptions sets the options of the solver. SolverOK specifies the cells to which the solver is to be applied and the goal of the optimization (parameter MaxMinVal ). SolverSolve then carries out the optimization. After the optimization is complete, a dialog appears that, thanks to SendKeys , is acknowledged with Return , after which it disappears.
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, _ AssumeLinear:=False, StepThru:=False, Estimates:=1, _ Derivatives:=1, SearchOption:=1, IntTolerance:=5, _ Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOk SetCell:="$A", MaxMinVal:=1, ByChange:="$A:$A" SendKeys "~" 'corresponds to <Return> SolverSolve
| Tip | You have to set a link to the Solver Add-In in the VBA development environment with TOOLSADD-INS before you can use the Solver Add-In. Please notice as well that applications that depend on Add-Ins have always caused problems when a new version of Excel appears. Further information on VBA programming of the Solver can be found at the following location: http://support.microsoft.com/support/excel/content/solver/solver.asp |
Syntax Summary
| ACCESS TO SELECTED RANGES OF CELLS | |
|---|---|
| ActiveCell | active cell (position of the cell pointer) |
| Selection | selected range or selected object in the window |
| RangeSelection | selected range (even when additionally another object was selected) |
| UsedRange | range used in the worksheet |
| SELECTION OF RANGES | |
|---|---|
| Range("A3") | a cell |
| Range("A3:B5") | a range of cells |
| Range("A3:B5, C7") | disconnected ranges |
| Range("name") | access to a named range |
| Evaluate("name") | access to a named range; second variant |
| [A3] or [A3:B5] or [name] | shorthand for Range , respectively Evaluate |
| Range(range1, range2) | range between two cells; range1 and range2 can also be given by Cells |
| range.Offset(r, c) | returns a range displaced by r rows and c columns |
| range.Resize(r, c) | changes the size of the range to r rows and c columns |
| range.Select | selects the given range |
| range.Activate | as above |
| GoTorange | selects the given range |
| GoTorange, True | as above, but also scrolls as necessary to make the range visible |
| Union(range1, range2,..) | union of the given ranges |
| Intersect(range1, range2,..) | intersection of the given ranges |
| ACCESSING PARTICULAR CELLS | |
|---|---|
| range.Cells | enumeration object of all cells |
| range.Cells(n) | n th cell (1 = A1, 2 = B1, 257 = A2, etc.) |
| range.Cells(r, c) | cell of the r th row and c th column |
| range.Areas | enumeration object of all rectangular ranges |
| range.Areas(n) | n th rectangular range |
| range.EntireColumn | rows in which the range is located |
| range.EntireRow | as above for rows |
| range.Columns(n) | access to individual columns |
| range.Rows(n) | access to individual rows |
| range.SpecialCells(type) | access to empty, visible, subordinate, etc., cells |
| range.End(xlXxx) | access to the last cell in a direction |
| range.CurrentRegion | access to a connected range of cells |
| range.[Direct]Precedents | access to preceding cells (raw data) |
| range.[Direct]Dependents | access to following cells (formulas) |
| range.ListHeaderRows | returns the number of header rows |
| NAMED RANGES, ADDRESSES OF RANGES | |
|---|---|
| Names.Add "test", "=$d$5" | defines the name "test" with reference to cell D5 |
| [test].Select | selects the range "test" |
| Names("test").RefersTo | returns address of a range (e.g., "=Table1!$F$4:$G$6" ) |
| Names("test").RefersToR1C1 | as above, but in R1C1 notation |
| Names("test").RefersToR1C1Local | as above but in local language format |
| Names("test").Delete | deletes the name "test" |
| range.Address(..) | returns a string containing the address of the range |
| range.AddressLocal(..) | as above, but in local language format |
| INSERTING AND DELETING DATA IN CELLS | |
|---|---|
| range.ClearContents | clear content of cells |
| range.ClearFormats | clear formatting of cells |
| range.Clear | clear content and formatting |
| range.ClearNotes | clear notes |
| range.Delete [xlToLeft or xlUp] | delete cells |
| range.Insert [xlToRight or xlDown] | insert cells |
| CONTENT AND FORMAT OF INDIVIDUAL CELLS | |
|---|---|
| range.Value | value of a cell |
| range.Text | formatted string with content of the cell (read-only) |
| range.Characters(start, number) | individual characters of text |
| range.Formula | cell formula in A1 format, English function names |
| range.FormulaR1C1 | formula in R1C1 format, English function names |
| range.FormulaLocal | cell formula in A1 format, local language names |
| range.FormulaR1C1Local | formula in local format, local function names |
| range.HasFormula | tells whether the cell contains a formula |
| range.NoteText(text, start, end) | reads or changes up to 255 characters of a cell note |
| range.Font | refers to a font object |
| range.VerticalAlignment | vertical alignment (left/right/center/justified) |
| range.HorizontalAlignment | horizontal alignment (upper/lower/middle) |
| range.Orientation | text orientation (horizontal/vertical) |
| range.WrapText | text wrap |
| range.ColumnWidth | width of an entire column |
| range.RowHeight | height of an entire row |
| range.NumberFormat | string with number format |
| range.Style | string with style name |
| range.BorderAround style, weight | sets the entire border |
| range.Borders | reference to border object |
| range.Row | row number of the cell |
| range.Column | column number of the cell |
EAN: 2147483647
Pages: 134