Excel Visual Basic (VBA) Tutorial - Part 3

Program Control and Loops

Our extended macro now makes use of a For… Next loop to cycle through and repeat the same operation a number of times. However this is not the only type of loop that Excel macros can use. Visual Basic also supports Do While loops and For Each Next loops.

For Next

The For Next loop is useful for repeating an operation a specified number of times. It uses a counter variable which can be incremented or decremented for each cycle of the loop. The increment or decrement doesn't have to be an integer, nor does it need to have a value of 1. By using the Step keyword we can move in any block size we want, and the sign of the step determines whether we increment or decrement. For example if we wanted to count down from 20 to 10 in steps of 2 we could write the following loop statement:

	For j = 20 to 10 Step -2
		'do something here
	Next j

The above example explicitly lists the counter variable with the 'Next' statement. Although this isn't a requirement it's good practice and makes it clear when debugging or looking at other people's code which loop is being referred to. In the case of nested loops the gain in readability is worth the extra typing.

Do Loop

The For Next loop is fine for those situations when you know how many times you wish to repeat an operation. There may be other times when you want to repeat an operation until a given condition has been met. The Do Loop is provided for this situation - it will repeat a block of code until a specific logical test is found to be True. There are two ways of using a Do… Loop, depending on whether you want to check if the condition is true before or after the first cycle. If you check for the logical condition before the first cycle then it could be that the block of code inside the loop is not executed at all. If you check at the end of the loop then you are guaranteed to have executed the block of code at least once.

To check the condition first the code would be like this:

	Do While j < 100
		'do something here
	Loop

To check at the end of the loop:

	Do
		'do somthing here
	Loop While j < 100

A variation of this type of loop structure uses the 'Until' keyword instead of 'While' keyword. In this form the code inside the loop is executed until a given test becomes logically True rather than being executed while it is True. As in the previous form of the loop the test for the condition can at the beginning or the end of the loop. To keep things short only the first condition will be shown:

	Do Until j = 100
		'do something here
	Loop

For Each Next

The For Each Next loop is used to execute a block of code on every object in a given collection. Remembering that sets of worksheets, ranges of cells and so on can be referenced as objects in a collection then it becomes immediately obvious that the For Each… Next loop can be a simple and elegant way of cycling through a workbook or worksheet performing a given set of operations on each object in turn.

For example, if we wanted to loop through a range of cells and bold those that had a negative value we could code the following:

	For Each c In Worksheets("Sheet1").Range("A1:A10").Cells
		If c.Value < 0 Then c.Font.Bold = True
  	Next

In this case the variable c contains a different cell reference in each iteration of the loop.

If Then Else

Aside from loops, Excel macros can include code to control program execution. The simplest of these is the familiar If… Then… Else construct, which basically says that if a given logical condition is true execute one block of code, otherwise execute a different block of code. This capability means that your macro can test your data for specific conditions and carry out operations accordingly.

The simplest kind of If… Then loop is one where you are only interested in carrying out a single operation if a logical test gives a True result - you are not going to do anything if the test gives a logical False result. For example, if you wanted to test a cell to see if it is empty and to enter the current date (using the Now function) if it is, then you could code a simple test as follows (assuming that we had previously set up the reference to the cell as variable cl):

	If IsEmpty(cl.Value) = True Then cl.Value=Now

While this is a relatively simple example, a more common requirement would be to execute more than one operation if a test returns a True result. If we wanted to extend the previous example so that it added in the current date and then bolded it to let the user know that it had been added in automatically, we would use the following type of If Then Else construct:

	If IsEmpty(cl.Value)=True Then
		cl.Value=Now
		cl.Font.Bold=True
	End If

Note that here the If statement is matched at the end by an End If statement, which the previous example didn't need.

And what if we need to perform some other operation on those cells where the test fails? If we extend our current example so that if the cell is not empty then the font is set to italics, we end up with the following code:

	If IsEmpty(cl.Value) = True Then
		cl.Value = Now
		cl.Font.Bold = True
	Else
		cl.Font.Italic = True
	End If

Note that the Else statement separates the block of code for the True result from the block for the False result, and that the entire thing is finished off with an End.. If statement.

There are also occasions where you want to cascade tests, so that if one test fails you want to try another one, and another and so on. At each stage you may have code that you want to execute for those cells that pass the test. It's feasible to code up a long sequence of If Then Else blocks, though it will obscure the logic of what you are attempting to do and will also make maintenance more difficult when you return to your code later (which you will, you will). A more elegant solution is to use an ElseIf statement to carry out the next test within your existing If Then Else block.

For example, if we have a column of numbers which represent exam results, we can go through them and award a result of 'Fail', 'Pass', 'Merit' or 'Distinction' depending on the score. We could program a macro to do this using If…ElseIf as shown below:

Sub ExamScore()
Dim Row As Integer
Dim Score As Double
Dim Award As String
  
  'Loop through 10 rows of scores
  'First column contains exam score, second to contain the award
  For Row = 1 To 10
    
    'First get the score
    Score = ActiveSheet.Cells(Row, 1)
    
    'Assign the award based on the score
    If Score < 40 Then
        Award = "Fail"
    ElseIf Score < 60 Then
        Award = "Pass"
    ElseIf Score < 80 Then
        Award = "Merit"
    Else
        'At this point score must be greater than 80%
        Award = "Distinction"
    End If
    
    'Write the award
    ActiveSheet.Cells(Row, 2) = Award
    
  Next Row

End Sub

The macro assumes that the exam scores are in the first ten rows of the active sheet. Note also that the ElseIf statements are used to whittle out the successively higher scores so that we can use a final Else statement to assume that everything that's left is higher than 80% and so can be awarded a Distinction.

Select Case

The previous If… ElseIf example carried out the same test for each ElseIf statement, with only the actual score being different each time. There is nothing to stop you coding different tests in the different clauses, and in certain circumstances that is precisely what is dictated by the data you are processing. However, where you do have the same test each time, another program control construct exists which is neater and easier to use than If… ElseIf. This is the Select… Case statement, which uses a logical test to pick out (or select…) which block of code to operate. For example our previous macro can easily be re-coded to use the Select… Case statement:

Sub ExamScore2()
Dim Row As Integer
Dim Score As Double
Dim Award As String
  
  'Loop through 10 rows of scores
  'First column contains exam score, second to contain the award
  For Row = 1 To 10
    
    'First get the score
    Score = ActiveSheet.Cells(Row, 1)
    
    Select Case Score
        Case Is < 40
            Award = "Fail"
        Case 40 To 59
            Award = "Pass"
        Case 60 To 79
            Award = "Merit"
        Case Else
            Award = "Distinction"
    End Select
        
    'Write the award
    ActiveSheet.Cells(Row, 2) = Award
    
  Next Row

End Sub

The macro is clearly easier to read and understand. The Case statements can even use tests such as '40 To 59' to code for a range of values. As in the previous example a final 'Case Else' statement is used to catch those codes that have slipped through, and although in this example we are assuming that they have a score greater than 80%, in most situations you would use the final Else statement to catch erroneous data or to flag some kind of warning message.

With End With

Although it's not a loop command, the With statement is extremely useful to know and can make your coding both simpler to understand and save on the drudgery of typing out long statements again and again. The With statement allows you to set up a reference to an object once and then to re-use that reference repeatedly without having to type it all in again. For example, if we want to address a particular cell on a worksheet we would have to refer to it as:

	ActiveSheet.Range("A1")

If we now wanted to set the value, the font type and font size for this cell we could use the With statement as shown below:

	With ActiveSheet.Range("A1")
		.Value=12
		.Font.Italic = True
		.Font.Size = 14
	End With

Given that references can become extremely verbose at times, the With End With statements should always be used to make your code easier to follow.

Return to home page