Excel VBA - City University Continuing Education
VBA for Microsoft Excel - Part 5
Page 4 of 5
Excel VBA - City University Continuing Education
Table of Contents
1 For Each Loops 1
1.1 For Each loops 1
1.2 Testing to see if a cell contains a number 2
1.3 Testing to see if a cell is blank 2
2 Using variables with loops 3
2.1 ‘Counter’ variables 3
2.2 Finding the highest / lowest value 3
2.3 The Locals window 3
3 Used Range 4
4 Using For Each with Object Collections 4
4.1 A macro that runs on every open workbook 4
4.2 A macro that runs on every worksheet 4
5 Shortcut selection keys 5
5.1 Code corresponding to keyboard shortcuts 5
1 For Each Loops
Rather than repeat code to perform repeated tasks, you should create a loop structure to repeat the task in hand. ‘Looping’ therefore means ‘repetition’.
Often you will need a statement for applying a task to a group of objects. If you want to change the colour of a range of cells to blue, or give every cell the value of "2", this can be done using Selection or Range e.g.,
Selection.Interior.Color = vbGreen
Range("B2:C6").Interior.Color = vbGreen
Commands that are applied to a group of cells present little problem - for example, "change the fill colour to red" or "change the value of the cells to say ‘Closed on Sundays’ “. The previous state of the cells is irrelevant as they are
overwritten. After the macro has been run, they uniformly have the colour or value set by the macro.
1.1 For Each loops
Sometimes, the existing values do matter as when you want to update a range of numbers by multiplying the values by 1.5 – for example, if all employees get a 50% pay increase. If the macro involves processing each cell individually, then the selection property is not adequate.
Selection = Selection * 1.5 does not work because each cell in a selection has its own individual Value property.
Rather than create repetitious code like:
Range("B2") = Range("B2") * 1.5
Range("B3") = Range("B3") * 1.5
Range("B4") = Range("B4") * 1.5
you can create a loop.
What you need is a construction that is equivalent to for each cell in the selection do some action. This allows you to treat each cell individually but only need to write one command for the action. For a selection of cells, do something for one cell and when that is done repeat the commands for the next cell. This loop continues until all cells are processed .
Here the calculation is different for each cell. If B2 contains 19, then the operation is 19 * 1.5; if cell B3 contains 26, the operation is 26 * 1.5, and so on.
This is where a For Each loop can be used. Here is the basic pattern:
For Each object in selection
-- do some action(s) on each object --
Next object
Where object means an object variable of some kind – on this occasion a Range variable representing a single cell within a selection. The code between the For Each and Next is repeated until the end of the selection is reached.
Here is an example: we assume that cells are selected before running the macro. The macro will fail if any cell contains non-numeric data
Sub LoopTest()
Dim myCell As Range
For Each myCell In Selection
myCell = myCell * 1.5
Next myCell
End Sub
A range variable called myCell is declared; the variable will be instantiated to each cell in the selection by the code
Any lines between the For Each and Next are repeated, i.e. cells are multiplied by 1.5 one by one within the selection.
You may find it helpful to compare the code with the flow diagram below.
The For Each … Next block contains the lines to be repeated, and can also contain other blocks like If statements
1.2 Testing to see if a cell contains a number
Use the IsNumeric() function for this. Example:
If IsNumeric(ActiveCell) Then
Msgbox “Current cell contains a number”
End If
1.3 Testing to see if a cell is blank
You can use the IsEmpty() function to test whether or not a cell is blank
Example:
If Not IsEmpty(ActiveCell) Then
Msgbox “Current cell contains data”
End If
Alternatively, if a cell is empty it contains the empty string”” (literally one double quote followed by another with nothing in between):
If ActiveCell > “” Then
Msgbox “Current cell contains data”
End If
In the next code note also how the IsNumeric() function prevents a run-time error should a cell contain non-numeric data
Sub Looptest2()
Dim mycell As Range
For Each myCell In Selection
If IsNumeric(myCell) And mycell >= 25 Then
myCell = myCell * 1.5
myCell.Interior.Color = vbCyan
ElseIf IsNumeric(myCell) And mycell 25 Then
myCell = myCell * 1.2
myCell.Interior.Color = vbMagenta
End if
Next myCell
End Sub
2 Using variables with loops
The loop examples up to now have been used to directly manipulate worksheet data. However you may want to accomplish additional tasks, which typically means creating one or more extra variables.
2.1 ‘Counter’ variables
For example if you carry out operations on a range of cells using a loop, you might want to keep a count of how many cells were affected. You would have to create an integer variable, and increment it by one each time the operation
happened. To increment a variable by one is very easy. If the variable is called i then the way to increment it is i = i + 1
For example, if you select a range containing hourly pay rates, and you want
upgrade any to a minimum of £10 and message how many cells were affected you could write:
Sub PayCalc()Dim rng As Range
Dim i As Integer
For Each rng In Selection
If rng.Value < 10 Then
rng.Value = 10
i = i + 1
End If
Next rng
MsgBox i & " pay rates increased"
End Sub
2.2 Finding the highest / lowest value
Again use a variable in a loop to store the value of the highest so far. Assign the first value in the selection to the variable, and as the loop runs it is compared with each successive value. If the first value is superseded then the higher value is assigned to variable, otherwise the highest remains as it is.
Sub FindMaxValue()Dim Highest As Single
Dim rng As Range
'initial setting of a max value
Highest = Selection.Cells(1).Value
For Each rng In Selection
If rng.Value > Highest Then
Highest = rng.Value
End If
Next rng
MsgBox "Highest value is " & Highest
End Sub
For this code to work you must have a range of numeric cells selected. Note that the statement Selection.Cells(1).Value (see sction 4 of these notes) is able to reference the first cell of a selection
2.3 The Locals window
The Locals window can help you debug problems in your code because it shows the current value of any variable while you step through a program. To display the Locals window, in the VB Editor, click View menu, Locals window.
To use the Locals window step through your program (function key F8). Carefully observe the value of variables as each line is executed.
3 Used Range
The worksheet object has a UsedRange property, which returns the range from the top left to the bottom right of cells that have been used on the worksheet. You must remember to include a reference to a worksheet e.g.,
ActiveSheet.UsedRange.Select or
Worksheets(1).UsedRange.Select
Worksheets(“Rates”).UsedRange.Columns(1).Select
UsedRange (or any other range) has an Address property, which can be useful, eg Debug.Print ActiveSheet.UsedRange.Address
The program below loops through the UsedRange and formats cells containing a value over 50.
Sub UsedDemo()Dim mycell As Range
For Each mycell In ActiveSheet.UsedRange
If IsNumeric(mycell) And mycell > 50 Then
mycell.Interior.Color = vbYellow
End If
Next mycell
End Sub
The limitation of Selection.CurrentRegion.Select is that you cannot use it to reference data that includes blank rows. In these circumstances you may be able to exploit UsedRange. The disadvantage of UsedRange is that includes blank cells that were previously formatted, which might spoil your intentions, so avoid UsedRange where the same sheet is continously recycled.
4 Using For Each with Object Collections
For Each is versatile, because it can be used to repeat statements for any collection of objects. It is not limited to ranges of cells. The code always takes the form
For Each objectvariable In collection
e.g.,
Dim w As Worksheet
For Each w In Worksheets
...
Next w
4.1 A macro that runs on every open workbook
This code saves all open workbooks and closes Excel.
Sub SaveThemAll()Dim w As Workbook
‘w is an object variable of type Workbook
For Each w In Workbooks
w.Save
Next w
Application.Quit
End Sub
4.2 A macro that runs on every worksheet
The next code writes some text to cell A1 on each worksheet by iterating through the worksheets collection.
Sub LoopWorksheets()
Dim w As Worksheet
For Each w In Worksheets
w.Range("A1") = "City University"
Next w
End Sub
It’s essential to remember to use the object variable in the code within the loop
w.Range("A1") = "City University"
otherwise the text will be written to the active sheet multiple times.
Admittedly the above program is not very useful. The next code uses the same technique to change the font name, font size and column width for every sheet in the workbook.
Sub LoopWorksheets2()
Dim w As Worksheet
For Each w In Worksheets
With w.Cells.Font
.Name = "Verdana"
.Size = 11
End With
w.Columns.ColumnWidth = 14
Next w
End Sub
5 Shortcut selection keys
A region is a range of cells, all containing text, numbers or formulas and bounded by white space (empty cells). The current region is where the currently selected cell is: it is the block of cells surrounding the current cell. We have already encountered Ctrl+Shift+8 or Ctrl + * (from numeric keypad) to select the current region.
You can use Ctrl + (left/right/up/down) arrow to select the cell on the edge of a region to the left of/right of/above/below the selected cell
Assuming the cell containing the value, “Current” is selected ,Ctrl + Up arrow has the effect illustrated below
You can use Ctrl + Shift + (left/right/up/down) arrow to select a range from the currently active cell to the leftmost/rightmost/topmost/bottommost cell in the current region
Similarly, assuming the cell containing the value, “Current” is selected, Ctrl-Shift-right arrow selects the following:
5.1 Code corresponding to keyboard shortcuts
Keystroke Corresponding code
Ctrl + Shift +8 Selection.CurrentRegion.Select
Ctrl + left Selection.End(xlToLeft).Select
Ctrl + right Selection.End(xlToRight).Select
Ctrl + up Selection.End(xlUp).Select
Ctrl + down Selection.End(xlDown).Select
Ctrl + Shift + left Range(Selection,Selection.End(xlToLeft)).Select
Ctrl+Shift+right Range(Selection,Selection.End(xlToRight)).Select
Ctrl + Shift + up Range(Selection,Selection.End(xlUp)).Select
Ctrl+Shift+down Range(Selection,Selection.End(xlDown)).Select
These references can be valuable for tasks such as selecting data prior to a loop or finding the first blank row of a worksheet.
Page 4 of 5