12. Visual Basic If Statements and Do Loops

1. 12. Visual BasicIf Statements andDo Loops

2. Open 12b-datastart.xlsm

3. If statements • We have seen how to use IF statements in formulas in Excel • IF statements can also be used in Visual Basic but they have a different format • The basic format is: If a cell has a value greater than 10Then Make the font bold ElseIfcell has a value less than 10 Then Make the font italic EndIf • Begin with Macro Recorder and then edit

4. Select Sheet3 and cell D2

5. Click Record Macro

6. Type Macro name: HighlightCells Shortcut key: Ctrl+j

7. Click OK

8. Right-click with mouse and select Format Cells

9. Select Bold Font and click OK

10. Click Stop Recording

11. Click Visual Basic

12. Select Module 2

13. Excel produces code for all aspects of the fontWe are only concerned with .FontStyle = “Bold”

14. Just keep the following lines

15. We only want Excel to do this when the cell value is greater than 10

16. If ActiveCell.Value > 10 Then …Endif

17. Save and Close

18. Click on cell D3 and press Ctrl+j to run macro

19. Excel should evaluate the cell and give it a bold font as it has a value above 10

20. Click Visual Basic

21. Want to add another condition that if value is less than 10 give it Italic font

22. ElseIfActiveCell.Value < 10 Then

23. Repeat FontStyle code, but change it to Italic

24. Save and Close

25. Click Cell E2 and press Ctrl + j to run macro

26. Font should become Italic

27. Challenge • Edit the Visual Basic code so that there are three conditions • If ActiveCell.Value > 20 Then • Font style should be “Bold” • ElseIfActiveCell.Value > 15 Then • Font style should be “Italic” • ElseIfActiveCell.Value < 15 Then • Font style should be “Bold Italic” • EndIf

28. Do Loops • We can get Excel to do something repeatedly by setting up a Do … Loop Until … • Rather than evaluating one cell at a time we may want to work through all the cells in a row • We can tell Excel to evaluate the cell and move to the next cell • We then get Excel to repeat this until the next cell is blank

29. Click Visual Basic

30. After the If statement we want to select the cell in the next column

31. Type ActiveCell.Offset(0,1).Select

32. Click Save and Close

33. Select cell C4 and press Ctrl+j to run macro

34. Excel evaluates the cell and moves to the next column

35. Click Visual Basic

36. We want Excel to Do this repeatedly until the next cell is empty

37. Type Do before the If statement

38. Type Loop Until ActiveCell.Value = “”

39. Save and Close

40. Select cell C5 and press Ctrl+j to run macro

41. Excel repeats the command and evaluates each cell within the row

42. Excel stops running the command when it finds an empty cell

43. Challenge • Edit the Visual Basic code so that once the end of the row is reached, Excel moves to the beginning of the next row • After the Do Loop tell Excel to ActiveCell.Offset(1,-4).Select • Then set up another Do Loop so that Excel keeps doing this until the whole table has been evaluated

44. You now have a Do Loop within a Do Loop

45. Once the first Do Loop is finished it moves to the next row and keeps going until the next row is empty

46. Advice • Writing programs requires trial and error • Use the macro recorder to get most of the code • Then edit this code to make it do exactly what you want • Download a copy of all these notes (www.qubexcel.co.uk) and refer back to them when you have a particular task to perform • The only way to get really confident with Excel and VBA is to use them regularly