Exercises

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

£

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

0 0

Post a comment