3 Part VBA Problem


This is probably really basic for some of you expert VBA coders out there and I was wondering if you could help me with this 3 part VBA Excel 2003 problem. Thanks in advance!

Randy Speakman

-Open Excel and create a blank Workbook. Before you do anything else, save it with the following filename format: Speakman-term.xls1
-Rename Sheet1 on the Workbook Speakman.
-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 excercise.
-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.
-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
-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 functions 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 (3.1415926)
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
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. Place a Command Button on the page to execute the subroutine GenerateNormals.

Part 3
-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 doesnt 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.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!