Excel courses

EXCEL VBA advanced PDF lessons


Télécharger EXCEL VBA advanced PDF lessons

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


EXCEL VBA advanced PDF lessons

...

REVIEW EXERCISE

AUTOMATING A WORKSHEET USING VBA BASICS

  1. Open the file, Daily Profit and Loss.
  2. Launch the VBA Editor and open the module sheet in the Daily Profit and Loss project

containing the CreateTable procedure.

  1. Add code to the module that:
  2. adds formulas in cells B4 and C4
  3. formats cells B1 to C2 with a bold font
  4. formats cells A2 to A4 with an italic font
  5. formats cells A4 to C4 with a grey fill (Tip: ColorIndex = 15)
  6. Create a button on Sheet1 of the workbook and assign the CreateTable macro to it.
  7. Use this button to run the macro and check that it runs correctly. It should result in the following.
  8. Correct any errors.
  9. Enter the following data into the newly created table.

USA Europe

Sales 35,000 42,000

Costs 25,000 25,000

  1. In the same module sheet there is already a procedure named TestProfit that tests cell B4 and makes the font bold if its value is 15000 or over, and formats it red if below 15000.

Edit the procedure with a loop so that after it tests cell B4, it also tests cell C4.

  1. Assign a keystroke of your choice to this macro and run it to check that it works correctly. The font in cell B4 should be made red and the font in cell C4 bold.
  2. Put right any errors and then save and close the file.

LESSON 1 - EXPLORING THE RANGE OBJECT

In this lesson, you will learn how to:

Refer to ranges

Use collections

Manipulate ranges with the Offset and Resize functions

REFERRING TO A RANGE

Discussion

Procedures will almost certainly need to work with ranges; these ranges will usually differ in size and position in a workbook and hence, there needs to be flexible ways of identifying and selecting them.

The Range Property

As a result, there are many ways of referring to a range. The first and most commonly seen after recording a macro is using the Range property:-

Range(reference)

  1. Range(“A1”)

Range(“B2 : D10”)

An alternative way of selecting a range of cells that can provide more flexibility, is to separate the first cell from the last one with a comma, eg.

Range("A1", "A10")

...refers to the range A1 : A10 and gives the same result as using Range(“A1:A10”).

Several (non contiguous) ranges can be referred to by typing them into the Range argument with commas separating them and quote marks (“) at the beginning and end, eg.

Range("A1:A10, C1:C10, E1:E10")

... refers to three ranges.

Another way of using referring to a range is from a currently selected cell to another fixed point in a worksheet, eg.

Range(ActiveCell, ActiveCell.Offset(10, 0))

This provides rather more flexibility because it will always refer to the range from the active cell to the cell 10 rows below it. So, for example, if the active cell is B4, the range B4 : B14 will be selected.

All the above methods refer to and identify a range object that can then have an appropriate method or property applied to it, eg.

Range("A1", "A10").Interior.ColorIndex = 15

(add a light grey fill colour to the range A1 : A10)

The Cells Property

The most significant difference between using the Cells property to the Range property, is that the Cells property identifies cells numerically (using what is called an Index) AND it can only identify single cells, not ranges.



For example, you cannot use Cells(“A1”) to refer to cell A1 as this would return an error.

A run-time error message

Cells(“A1:A10”) would return the same error.

The correct way of using the Cells property is to use an Index number in the brackets.

For example, cell A1 can be referred to in two ways:

Cells(1) or Cells(1, 1)

Cells(1) identifies it as cell number 1 of the worksheet, ie. A1. The Index counts along the row before returning to the beginning of the next row and resuming from where it left off. B1, therefore, would be Cells(2), C1 would be Cells(3)... etc.

In Excel 2000 – 03, the last cell in row 1 (IV1) would be referred to with an index of

256 - Cells(256). A2 would then have the index 257 - Cells(257).

In Excel 2007, however, there are 16,384 columns (unless you are running it in “compatibility mode”). The last cell in row 1 (XFD1), therefore, would be Cells(16384) and A2 would be Cells(16385)

It makes things easier, therefore, the use the cells property with TWO index numbers, the first being the row number and the second the column number. For example, A2 can be referred to as:

Cells(1, 1) - the cell that is in row 1 and column 1 of the worksheet (A1).

Cells(2, 1) - the cell that is in row 2 and column 1 of the worksheet (A2)

Cells can also be used on its own as a collection (see page 10). It would then refer to

ALL the cells on a worksheet or a selection, eg.

ActiveSheet.Cells.Clear or just Cells.Clear

...removes formatting and contents from every cell on the

current (active) sheet

Sheets(Sheet2”).Cells.Font.Name = “Calibri”

... formats all cells on Sheet2 to the Calibri font.

numCels = Selection.Cells.Count

... returns to the variable numCels the number of cells in the selected area

The Cells property is generally more powerful and flexible than the Range property, although Range is still best used when referring to specific (absolute) cells or ranges.

The Cells property and the Range property can be used together as follows:

Range(Cells(1, 1),Cells(3, 3))

This refers to the range A1 : C3, Cells(1, 1) being A1 and Cells(3, 3) being C3.

Procedures

  1. Launch or switch to the VB Editor.
  2. Identify in the Project Explorer pane, the workbook

(VBA project) that you want to add code to.

  1. Open the module sheet containing the code or, insert a new module.
  2. Position the cursor in the procedure where you want to write the code.
  3. Type the object that you want to refer to.
  4. Type a full stop.
  5. Type the method or property that you want to apply to the object.
  6. Press Enter.

COLLECTIONS

Discussion

It was explained in the previous topic that Cells may be used on its own to represent a “collection.” Relevant methods and properties can then be applied to a whole collection (or “type”) of object. For example:

Cells.Select

...select ALL the cells on the active worksheet

... ...

To identify and refer to a single item in a collection, you normally have to refer to it by its index number, or by its name (as a string). For example:

 WorkBooks(1) is the first workbook opened (assuming that several are open).

 WorkBooks(“Sales Data.xls”) is specifically the open file named Sales Data.

 Sheets(1) is the first sheet from the left of the workbook.

 Sheets(“January”) is specifically the sheet named January.

 Rows(1) refers to row 1 of the active worksheet.



 Columns(1) is the same as Columns(“A”) and refers to column A of the active worksheet.

To select a range of columns, for example from B through G, would require the code

Columns(“B : G”).Select. This is the only way to specify a range of columns within a collection, numbers (eg. Columns(2 : 7) are not allowed.

Procedures

  1. Launch or switch to the VB Editor.
  2. Identify in the Project Explorer pane, the workbook

(VBA project) that you want to add code to.

  1. Open the module sheet containing the code or, insert a new module.
  2. Position the cursor in the procedure where you want to write the code.
  3. Type the object that you want to refer to.
  4. Type a full stop.
  5. Type the method or property that you want to apply to the object.
  6. Press Enter.

THE CURRENTREGION PROPERTY

Discussion

Referring to a range using CurrentRegion can be very useful when you do not know what the range references are going to be, or how many rows and/or columns it will have.

The CurrentRegion property identifies a contiguous range on a worksheet (a range that is bounded by a combination of a blank column and a blank row).

A contiguous range (A3 : B22)

To achieve this, CurrentRegion needs a “starting point” (an expression that returns a range object). The starting point can be any cell on the range. The syntax is:

<expression>.CurrentRegion.<method or property>

In the picture above, the code for selecting the contiguous range A3 to B22 might be:

Range(“A3”).CurrentRegion.Select

If a cell is already active on the range, the code could be written as follows:

ActiveCell.CurrentRegion.Select

For example:

Range(“A1 : G200”).Select

Selection.Copy Destination:= Sheets(1).Range(“A2”)

... will always select the cells from A1 to G200, copy them and paste them into cell A2 on the second sheet of the workbook.

Nevertheless, if you want to run the same procedure on a different table that does not span from cells A1 to G200, then the procedure will not work correctly. Using the

CurrentRegion property, however, it would be successful, eg.

ActiveCell.CurrentRegion.Select

Selection.Copy Destination:= Sheets(1).Range(“A2”)

The example above would require a cell to be selected on the table first, but would succeed a table containing any number of rows and columns anywhere on the worksheet.

Another useful way of selecting cells CurrentRegion would be to use arguments for it. The following example would select the cell 2 Rows down and 2 Columns to the right, from the first cell in the current region.

ActiveCell.CurrentRegion(2, 2).Select

An alternative method would be to use the activate method. This would be useful if the current region was selected first and needed to be kept selected. Using activate will make a cell active without removing any cell selection.

ActiveCell.CurrentRegion.Select

ActiveCell.CurrentRegion(3, 6).Activate

Procedures

  1. Launch or switch to the VB Editor.
  2. Identify in the Project Explorer pane, the workbook

(VBA project) that you want to add code to.

  1. Open the module sheet containing the code or, insert a new module.
  2. Position the cursor in the procedure where you want to write the code.
  3. Type the object that you want to refer to.
  4. Type a full stop.
  5. Type the method or property that you want to apply to the object.
  6. Press Enter.

THE OFFSET & RESIZE PROPERTIES



Discussion

Offset

The Offset property is discussed in the Excel VBA Introduction booklet with respect to it identifying a single cell that is “away” from another one (see Excel VBA

Introduction (Student Edition), page 63) . For example:

ActiveCell.Offset(1,0).Select

... would select the cell that is 1 row and 0 columns away from the active cell. If the active cell was B10, therefore, Offset(1,0) would refer to B11 (one row down, same column).

The Offset property can also be used to offset an entire range of cells.

The following example moves a selected range 1 cell down and 1 column to the right:

Range(“A1”).CurrentRegion.Select

Selection.Offset(1,1).Select

This proves useful where a table has to be selected excluding the top row and left column.

Resize

Using the Resize function enables a selected range of cells to be made bigger or smaller by increasing or decreasing the number of rows and columns that it contains.

The new size of the range is “measured” from its top left corner. In the example below, a current region is selected and then resized to 15 rows and 4 columns.

ActiveCell.CurrentRegion.Select

Selection.Resize(15, 4).Select

The Resize property proves useful where a table has to be selected excluding the bottom row and right column. To achieve this, VBA has to do a bit of work for you! It has to count the number of rows and columns in the table so that a calculation can be performed to determine how many rows and columns to resize the table to.

The example below uses variables to count the number of rows and columns in a table (the selection), and then adds 1 to determine the resize parameters:

ActiveCell.CurrentRegion.Select

numRows = Selection.Rows.Count + 1

numCols = Selection.Columns.Count + 1

Selection.Resize(numRows, numCols).Select

The example above could also be written without the use of variables, although this may make the code more complicated to write and understand:

ActiveCell.CurrentRegion.Select

Selection.Resize(Selection.Rows.Count + 1, Selection.Columns.Count + 1).Select

The Offset and Resize properties work well together as in the following example. A range is offset by 1 row and 1 column and then resized to 1 row and 1 column less, so that it excludes the blank row and blank column at the bottom and right.

ActiveCell.CurrentRegion.Select

numRows = Selection.Rows.Count -1

numCols = Selection.Columns.Count -1

Selection.Offset(1, 1).Resize(numRows, numCols).Select

Procedures

  1. Launch or switch to the VB Editor.
  2. Identify in the Project Explorer pane, the workbook (VBA project) that you want to add code to.
  3. Open the module sheet containing the code or, insert a new module.
  4. Position the cursor in the procedure where you want to write the code.
  5. Type the object that you want to refer to.
  6. Type a full stop.
  7. Type the method or property that you want to apply to the object.
  8. Press Enter.



7