DePaul University



LSP 121

Activity 13 - Using VBA with Excel

(30 points)

The purpose of this activity is to familiarize the student with simple programming code as used in VBA, Visual Basic for Applications. VBA is designed for most of MS Office applications. We will be working with Excel since you are already familiar with Excel. Note: for this activity, work in groups of less than 4. If you have 4 in your usual group, split into 2 groups of 2. Point value for this activity = 30.

We will try to illustrate graphically what is known as the ‘Law of Large Numbers’. If you flip a coin 5 times you might get 5 heads. If you flip a coin 100, 500 or 1000 times, the probability of heads will empirically get closer and closer to the theoretical 0.5. Your instructor will hand out the CODE needed for this activity. Code = lines of programming instructions (~algorithm).

1. Open an Excel worksheet and save it with activity13

2. Enter the VBA editor by pressing ALT+F11, then press F5 and name this macro ‘coin_flipper’. Notice at the task bar at the bottom of the screen you can see an Excel worksheet and the VBA editor tabs.

3. In the edit window enter the code that you receive on the handout. Carefully enter the code. You can omit the remarks but read them – the remarks are the lines of code that begin with an apostrophe. You don’t have to save the code. It is part of the Excel file. Be sure to type in the correct ‘first line’ of code.

4. If you make any mistakes in typing a KEYWORD, after you press enter, the VBA editor will point to the problem. Correct the problem or ask for help. Now switch to the Excel worksheet view.

5. After entering the code, create a macro RUN button:

a) First add the ‘developer’ tab to the ribbon if it is not already there: click on the ‘Office button’ (the colored thing in the upper left corner) then enable ‘Show Developer tab in Ribbon’, click OK.

b) Click on Developer tab, select Insert, and then select the first tool which is a ‘form control’ button. Draw a rectangle in the right half of your worksheet that is about 2 columns wide and 3 rows deep.

c) After the button is drawn, assign the macro ‘coin_flipper’ to the button

d) Rename the button to ‘Flip coin 100 times’. Carefully highlight the old name to do this, then rename it.

e) Close the toolbox window

6. Format column D to 3 decimal places.

7. You can now RUN the macro by clicking on the button

8. If you encounter any RUN errors, the editor will stop at the error line of code. Correct the error (probably a typo). Click on the RESET button in the toolbar before you try to run the macro again. The RESET button is a small dark ‘square’ button near the center of the tool bar.

9. If the macro runs and doesn’t stop (it can happen), press Esc and select STOP or DEBUG.

Save your work now using Save As and select for file type: Excel Macro-enabled Workbook. The resulting file should be named: activity13.xlsm

If the macro runs correctly, now add a chart, x-y scatterplot.

1. Select the 100 rows in column A and 100 rows in column D only

2. Click on insert tab, then Chart and select x-y scatter plot with data connected by smooth lines

3. Add the title ‘Plot of percent heads in 100 coin tosses’, x-axis (set range from 0 to 100) and y-axis labels (set range from 0 to 1). Set the scale of x/y axes by right-clicking a value in the x-axis or y-axis. Set min and max values as above.

4. Label x-axis ‘Toss Number’ and y-axis ‘Percent Heads (0-1)’. Remove ‘Series 1’ legend if it appears in the chart.

5. Position the chart so that it is near the top, not blocking any text or the macro run button and so that it is about as wide and tall as the display screen. The chart should be repositioned so that the y-axis shows values .100, .200, .300, etc. Stretch the chart if necessary to do this.

6. Before you test the macro by clicking the ‘button’, add the following to the worksheet:

a) in cell E1 type HEADS

b) in cell E2 type TAILS

c) in cell E3 type %HEADS

d) in cell F1 type =sum(B1:B100)

e) in cell F2 type =100 - F1

f) in cell F3 type =F1/100 (format as a percent with 1 decimal place)

Run the macro a few times to test, make corrections where necessary. Be sure the layout is readable. You would expect the scatter-plot to approach .5 as the coin flips approach 100 flips.

Try to click the RUN button 4 times quickly and see what happens.

Lastly: include comments in the Excel workbook below the chart. Select ‘Insert’ then select a ‘text box’. Draw a box below the chart and add comments. First, put GROUP MEMBER NAMES in this comment box. Also include a few short sentences about the assignment; what problems you had if any, did it work correctly the first time, was this a valid or helpful ‘programming’ assignment, and also what you think this animated Excel VBA chart was supposed to demonstrate (did it actually demonstrate what you expected?). What was the simulated probability that you expected?

IMPORTANT: save your work again. Then close the Excel file. The filename should still be activity13.xlsm. This is the file you should submit.

← Be sure group member names are included (in text box) and are visible on Excel worksheet.

Submit your activity13 file ending in .xlsm to D2L DropBox. Check with your instructor after you submit your work to be sure the proper file is received. It HAS to end in .xlsm

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download