# Exercises

Vertex42 The Excel Nexus

Get Instant Access

Open the file SALESMAN.XLS. Write a recorded macro to make all data in the week_sales range of the weeklysales worksheet bold face and place a border around the week_sales range. Link the macro to a button named border to be placed on the right-hand side of the worksheet. Open the Excel workbook SALESMAN.XLS. Create a recorded macro calledprint_ main_data, which will select the range from A31 to I41. The macro should then print this area by using: File > Print Area > Set Print Area. Test the macro.

Create a button called Print Main Area and place it on the weeklysales worksheet. Its purpose should be to allow users to highlight a range of cells on the spreadsheet before clicking the button. By clicking the button, users will obtain a printout of the selected range.

Write a recorded macro called generate_random. The macro should generate random numbers between 0 and 100 and place them in the selected test data range of the week_sales_test_data worksheet of the SALESMAN workbook. Hint: to place a random number in a cell between 0 and 800, you can use the built-in Excel function Int(Rand()*801). This function works

 IE3 Microsoft Excel - SALESMAN jJnl. X » File Edit View Insert Format Tools Data Window Help y [IMS) "IArial • B / u œ a II! ■A | B I c I D E J F ■M | ± i Test Data r 2' I 37 21 11 534 3 _43 43 133 77 _ 4 78 63 432 683 5" 123 65 65 12 6" 47 53 88 71 — 61 32 43 55 TT 25 155 26 g 75 78 73 65 is A 105 5 44 611 11 12 13 14 IS 16 17 18 13 20 M 4 ► M [\ wei îkJysales ),week sales test data /~Sht | 4 \ 1 HI Ready Surn=3813

Figure 2.21 The test_data range in the week_sales_test_data sheet giving random numbers.

Figure 2.21 The test_data range in the week_sales_test_data sheet giving random numbers.

because Rand () will generate a random number between 0 and 1. Hence, when multiplied by 801 it turns it into a decimal number between 0 and 800. The Int() function will truncate the part after the decimal point giving a value between 0 and 800.

Open the Excel workbook SALESMAN.XLS. Create a recorded macro called copy_test_data. Its purpose is to copy the selected data from the test_data range of the week_sales_test_data (see Figure 2.21), and paste this data into the named range week_sales of the (see Figure 2.22) weeklysales worksheet. Run and test the macro. Select Tools > Macro > Macros to see the Macro dialog box. Highlight the copy_test_data macro and choose Edit to view the VBA program. Briefly study the program - in the next chapter we will be writing programs in the VBA language.

 [Ë Microsoft Encel - SALESMAN ^JSJül B] File Edit Vîe'/V Insert Format lools Data Window Helo -SX y fi m ^ 1 Arial - IC ' - B I u or « [5 I » -A B as 1 D I E F ¡ g - HTi W: Monthly Bonus: Each cell value in this is c alculate d by multiplying M onthly T otal by the B c | 23 Sales To Date: Each cell in this range is carried over from the End of Month Sales column: ,24 Month No: This cell value is incremented at the start of each new month 25 III!! 1 26 Sales liata: n 27 28 Bonus Rate: 29 Month No: 1 i ap Weekly Sales 31 Rep Name Soles To Date ! 2 I 3 4 I Monthly Tola! Monthly 32 Andy 135 ■ 54 5! 6 ! Hi 77I v33\ Bindi 459 32- 43 28 Oli 136 P HI 34 Katie 357 16 33 150 166 365 e i 35 Misba 540 32 77 52: pa 180 t 36 AH 489 44 54 0 591 137 £ 37 Amy 333 20 "56; 33 " PJ| 193 i 3B Pete 33Ú 2T jrH ■il .123 £ 39 Rhiannon 453- 33 54 402 BS| 557 IL 4D Gareth 330 27 44i '33 18.1 YÚ £ 41 TOTALS 34321 279 1 4661 i 719¡ 4-iú) ¡91Ü A2L ».ir H ^ ► H|\ weekly sales/ week sales test data / Sheetl | < | _J Ready 5um=19i0 â

Figure 2.22 The selected range weeksales of the weeklysales worksheet

Figure 2.22 The selected range weeksales of the weeklysales worksheet

Write a recorded macro to create the 3-D pie chart as shown in Figure 2.12. The pie chart should chart the proportions of weekly sales during a month.

3 Introduction to VBA

Excel and VBA 44

The VBA environment 45

Structured English pseudocode . . .50

Input and output in VBA 52

Running VBA modules 57

Tips for running VBA macros 59

Calculations in VBA 61

VBA for Excel Help 63

Creating and naming a module . . 65

Exercises 66