Making Decisions

VBA provides two main structures for making decisions and carrying out alternative processing, represented by the If and Select Case statements. If is the more flexible one, but Select Case is better when you are testing a single variable.

If Statements

If comes in three forms: the IIf function, the one-line If statement, and the block If structure. The following dTaxfunction uses the IIf (Immediate If) function:

Function dTax(dProfitBeforeTax As Double) As Double dTax = IIf(dProfitBeforeTax > 0, 0.3 * dProfitBeforeTax, 0) End Function

IIf is similar to the Excel worksheet IF function. It has three input arguments: the first is a logical test, the second is an expression that is evaluated if the test is true, and the third is an expression that is evaluated if the test is false.

In this example, the IIf function tests that the dProfitBeforeTax value is greater than 0. If the test is true, IIf calculates 30% of dProfitBeforeTax. If the test is false, IIf calculates 0. The calculated IIf value is then assigned to the return value of the Tax function. The Tax function can be rewritten using the single-line If statement as follows:

Function dTax(dProfitBeforeTax As Double) As Double

If dProfitBeforeTax > 0 Then dTax = 0.3 * dProfitBeforeTax Else dTax = 0 End Function

One difference between IIf and the single-line If is that the Else section of the single-line If is optional. The third parameter of the IIf function must be defined. In VBA, it is often useful to omit the Else:

If dProfitBeforeTax < 0 Then MsgBox "A Loss has occurred", , "Warning"

Another difference is that, whereas IIf can only return a value to a single variable, the single-line If can assign values to different variables:

If iJohnsScore > iMarysScore Then iJohn = iJohn + 1 Else iMary = iMary + 1

Block If

If you want to carry out more than one action when a test is true, you can use a block If structure, as follows:

If iJohnsScore > iMarysScore Then iJohn = iJohn + 1 iMary = iMary - 1 End If

Using a block If, you must not include any code after the Then, on the same line. You can have as many lines after the test as required, and you must terminate the scope of the block If with an End If statement. A block If can also have an Else section, as follows:

If iJohnsScore > iMarysScore Then iJohn = iJohn + 1 iMary = iMary - 1 Else iJohn = iJohn - 1 iMary = iMary + 1 End If

A block If can also have as many ElseIf sections as required:

If iJohnsScore > iMarysScore Then iJohn = iJohn + 1 iMary = iMary - 1 ElseIf iJohnsScore < iMarysScore Then iJohn = iJohn - 1 iMary = iMary + 1 Else iJohn = iJohn + 1 iMary = iMary + 1 End If

When you have a block If followed by one or more ElseIf tests, VBA keeps testing until it finds a true section. It executes the code for that section and then proceeds directly to the statement following the End If. If no test is true, the Else section is executed.

A block If does nothing when all tests are false and the Else section is missing. Block If tests can be nested, one inside the other. You should make use of indenting to show the scope of each block. This is vital—you can get into an awful muddle with the nesting of If blocks within other If blocks, and If blocks within Else blocks, and so on. If code is unindented, it isn't easy, in a long series of nested If tests, to match each End If with each If:

If Not ThisWorkbook.Saved Then

lAnswer = MsgBox("Do you want

to save your changes",

, vbQuestion + _

vbYesNo)

If lAnswer = vbYes Then

ThisWorkbook.Save

MsgBox ThisWorkbook.Name & 1

' has been saved"

End If

End If

This code uses the Saved property of the Workbook object containing the code to see if the workbook has been saved since changes were last made to it. If changes have not been saved, the user is asked if they want to save changes. If the answer is yes, the inner block If saves the workbook and informs the user.

Select Case

The following block If is testing the same variable value in each section:

Function vPrice(sProduct As String) As Variant

If sProduct = "Apples" Then

vPrice = 12.5

ElseIf sProduct

= "Oranges" Then

vPrice = 15

ElseIf sProduct

= "Pears" Then

vPrice = 1B

ElseIf sProduct

= "Mangoes" Then

vPrice = 25

Else

vPrice = CVErr(xlErrNA)

End If

End Function

If sProduct is not found, the vPrice function returns an Excel error value of #NA. Note that vPrice is declared as a Variant so it can handle the error value as well as numeric values. For a situation like this, Select Case is a more elegant construction. It looks like this:

Function vPrice(sProduct As String) As Variant Select Case sProduct Case "Apples"

vPrice = 12.5 Case "Oranges"

vPrice = 15 Case "Pears"

vPrice = 18 Case "Mangoes"

vPrice = 25 Case Else vPrice = CVErr(xlErrNA) End Select End Function

If you have only one statement per case, the following format works quite well. You can place multiple statements on a single line by placing a colon between statements:

Function vPrice(sProduct As

String) As Variant

Select Case sProduct

Case "Apples":

vPrice =

= 12.5

Case "Oranges"

: vPrice =

15

Case "Pears":

vPrice =

1B

Case "Mangoes"

: vPrice =

25

Case Else:

vPrice =

= CVErr(xlErrNA)

End Select

End Function

Select Case can also handle ranges of numbers or text, as well as comparisons using the keyword Is. The following example calculates a fare of 0 for infants up to 3 years old and anyone older than 65, with two ranges between. Negative ages generate an error:

Function vFare(iAge

As Integer) As Variant

Select Case iAge

Case 0 To 3, Is

> 65

vFare = 0

Case 4 To 15

vFare = 10

Case 16 To 65

vFare = 20

Case Else

vFare = CVErr(xlErrNA)

End Select

End Function

0 0

Post a comment