RWSpeakman
02-27-2006, 05:46 PM
Hi,
I am in of some help for some extra credit in one of my classes. 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 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 (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 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.
I am in of some help for some extra credit in one of my classes. 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 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 (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 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.