Part 1 – Easy (30 points)

ˆ Open Excel and create a blank Workbook. Before you do anything else, save it with the following filename format: [url removed, login to view]

ˆ Rename Sheet1 on the Workbook with YourLastName.

ˆ In cell A1, please insert the following text:

“I, , have completed this assignment on my own to the best of my ability.”

ˆ Go to the Visual Basic Editor, Select the VBA project with your new filename and then Insert a Module to store the macros and code that you will develop for this examination. If you accidentally insert the module before selecting your module there is no telling where things will be stored and then you will get a big fat ZERO for the examination.

ˆ Name your cell A1 on the first sheet “Message”.

ˆ Start the Macro Recorder, name the macro Font1 and change the Font to Arial, 14 point, Bold and then stop the Recorder. Make sure the macro is saved in the Workbook NOT your personal file.

ˆ Repeat the previous step with macro name Font2 but change the Font to Lucida 10 Point, Italic (not Bold) and then stop the Recorder. Again, make sure the macro is saved in the Workbook and NOT your personal file.

ˆ Now, edit the two macros to refer to the range “Message” and then clean up the macros so that all extraneous code is eliminated.

1 Obviously, wherever I have the expression YourLastName, you should insert your last name, e.g. “McDevitt”

DSS-2715-331 Decision Support Systems Modeling – Midterm Examination

Spring 2006 – Dr. Ira Yermish Page 2

ˆ Add two command buttons to the cover sheet that call the two macros you just created. You should be able to click those buttons and the font of your message should then change.

Part 2 – Not So Easy (30 points)

ˆ Change the name of Worksheet2 in your workbook to “RandomNormal”

ˆ Go to the Visual Basic Editor and create a new function that will generate Random Normal Deviates. These are random numbers with a given Mean and Standard Deviation. Remember your statistics? The function’s name should be “RandomNormal” and have two arguments, the Mean (double) and StdDev (double) and the function should return a Double as the value. The formula to create a random number with the normal (Guassian) distribution is the following:

R1 = Random Number between 0 and 1 (use the Rnd function)

R2 = Random Number between 0 and 1 (use the Rnd function)

S = Standard Deviation (input)

M = Mean (input)

Pi = Pi ([url removed, login to view])

X = Random Number you generate (output)

X = S * Sqr(-2 * Log(R1)) * Cos(2 * Pi * R2) + M

ˆ You can test your function in the Immediate Window to see that it works properly.

ˆ On the Worksheet “RandomNormal” put the label “Mean” in cell A1, and the label “StdDev” in cell A2. Name cell B1 “Mean” and name cell B2 “StdDev”. Put an outline around both of these cells as input and unprotect them.

ˆ On the Worksheet “RandomNormal” create a range from Cells D5:P15 and name this range “RandomNumbers”.

ˆ In cell C1 insert a formula the computes the actual Mean (Average) of the Range “RandomNumbers”. In Cell C2 insert a formula the computes the actual Standard Deviation of the range “RandomNumbers”. When you perform the next steps those outputs should tell you if you doing things right. These values should be close to (but not exactly equal to) the input Mean and StdDev.

ˆ Create a subroutine in the Visual Basic Editor called “GenerateNormals”. This Sub will examine the range “RandomNumbers” to determine the number of rows and columns in the range. Using a nested loop, fill this range with Random

DSS-2715-331 Decision Support Systems Modeling – Midterm Examination

Spring 2006 – Dr. Ira Yermish Page 3

Normal Numbers using the “Mean” and “StdDev” that you defined on that page. Note: I will change the definition of the range and fill in values for Mean and StdDev when I grade your problem. Place a Command Button on the page to execute the subroutine GenerateNormals.

ˆ Save your file, just in case!

Part 3 – Pretty hard if I do say so myself (40 points)

ˆ Modify the name of Worksheet3 to “Problem3”

ˆ In Cells A1:A5 insert the names of five cities and name this Range “Cities”

ˆ Create a Visual Basic Subroutine “CreateCitySheets” that creates worksheets for each of the cities that you have named in the Range “Cities”. If that worksheet already exists, erase all of the contents on that page. If it doesn’t exist create it.

ˆ For each of the city worksheets, the above subroutine should create the values “Jan”, “Feb”, etc. in cells A2:A13

ˆ For each of the city worksheets, the above subroutine should create a set of product headers in cells B2:B6 with the values (“ProductA”, “ProductB”, etc.)

ˆ Next create the results of Sales of ProductA, ProductB, etc. in each of the months with Random Uniform Numbers between 0 and 10,000.

ˆ The routine should be called from a button on the “Problem3” page.

ˆ Create a chart on the Problem3 worksheet page which shows the monthly sales for TWO selected Cities and ONE selected product. You can use whatever format you choose.

ˆ Create a subroutine that modifies the chart to show a user selected product and pair of cities via Input Boxes. This subroutine should be called from a command button on the Problem3 worksheet. The subroutine should modify the graph titles and legends to show the right city and product names. This routine should provide feedback to the user if the product or city is invalid.

ˆ FINALLY, create a subroutine that copies all of the values the sales from a user selected City into a two dimensional array Sales(Month, Product). From this array determine the product and month for the lowest value and the product and month for the highest value of sales and display the result in a message box.

Taidot: Visual Basic, Windows Desktop

Näytä lisää: value city, use case module, hard easy 2006, cover store, statistics problem, problem graph, problem statistics, part standard, pair points, outline systems, numbers function, problem part, next step systems, make graph chart, lowest range, graph problem, graph part, function numbers, formula it, five cover, first graph, excel user defined function, e value, edit feedback, dr first

About the Employer:
( 0 reviews ) Danville, United States

Projektin tunnus: #49374

2 freelanceria on tarjonnut keskimäärin 175 $ tähän työhön


Hi, Pls see PMB for more details. Thanks

150 $ USD 1 päivässä
(4 arvostelua)

Check pmb.also go through my reviews.

200 $ USD 1 päivässä
(0 arvostelua)