VBA Control Structures

Here is a screenshot of one of the Excel files in the series together with the Visual Basic Editor showing the code behind the file. The control structure here is that of a nested "If...Then...End If" statement. In Excel 2003 only 6 nested "If" statements were allowed but with Excel 2007 there are now 64 allowed. To do the same as this VBA code we could click in cell "C7", then type the following into the formula bar in the worksheet: =IF(B7>=90,"A",IF(B7>=80,"B", IF(B7>=70,"C",IF(B7>=60,"D","F")))), which isn't too bad but can quickly become messy if anything out of the ordinary is required or if many more conditions need to be tested. for example, there are 24 national curriculum levels within the UK KS3 Maths curriculum going from 1L, 1M, 1H, 2L, 2M, 2H, ..., 8L, 8M and finally 8H. These could only be done via the worksheet formula bar in Excel 2007 and it would be very messy at that, but within VBA there is structure and flexibility and all Excel versions would allow such a construction within their VBA.

Lesson Text
Notice that at the end of this file is an appendix from which the VBA code may be highlighted, copied and pasted into the Visual Basic Editor (VBE) to save yourself some time.

Excel Files (1), (2), (3), (4), (5), (6), (7), (8), (9)

My advice is for you to download the files directly to your hard drive by right-clicking on the link and then selecting "Save Target As..." - otherwise the Excel files may not work fully in your browser window!