The improved Update Sales macro

If we go back to the UpdateSales macro earlier in this chapter, you will recall that among other things, the month_no was incremented ready for the next month before ending the macro. The problem with the macro however, is that if it is used every month, then eventually it will contain the value 13, and this of course does not make sense as there are 12 months in a year. For this reason, this example will extend that macro so that whenever the month_no value exceeds 12, it is reset to 1. We can use the VBA If. Then statement to do this as can be seen on line 11. This checks to see If a condition is met - in this case month_no >12 - and if it is, Then the statement(s) will be executed. That is, the month_no value is reset to 1.

The full code is shown in Listing 3. 4. Notice the lines 11 to 13 have been included to implement this part. If Range("month_no") >12 Then checks to see if the month_no is greater than 12, and if it is the action in line 12 is taken, i.e. Range ("month_no") = 1. Line 13 will end the If block: this means the actions following the true condition are complete.

Note how the statements are indented between If Range ("month_no") >12 Then and End If.

Listing 3.4 The UpdateSales macro updated to include decision

1 Sub Updatesales ()

2 'Update Sales VBA program Version 1.0

3 'Written by Keith Darlington Date 12/1/03

5 Worksheets ("Weeklysales").select

6 ActiveSheet.Unprotect

7 Range ("End_month_sales").Copy

8 Range ("sales_to_date").PasteSpecial xlValues

9 Range ("Week_sales").ClearContents

10 Range ("month_no") = Range ("month_no") + 1

11 If Range (month_no"> 12 Then

12 Range ("month_no") =1 If the month_no is greater

13 End If than 12, month_no is set to 1

14 ActiveSheet.Protect __

15 End Sub

0 0

Post a comment