PDA

View Full Version : 3 Part VBA Excel 2003 Problem



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.

Zack Barresse
02-27-2006, 06:24 PM
Hi RWSpeakMan, welcome to VBAX!

Please read our FAQ on Homework (http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item). I realize this is extra credit. If you need specific help, please post back. Looks pretty straight forward though. How far did you get?

XLGibbs
02-27-2006, 06:26 PM
Since this is homework, you are better to post problems resolving some of the issues after your attempts to do so. Most of the free help you may got would not involve doing homework or extra credit for you.

However, we can help you understand things that may be hard to understand, or help identify why your code is not working.

We do want to help, but since this is homework, you will only gain any real value from the exercise by at least attemptingi to do it yourself.

The instructions in the first part are pretty basic and self explanatory, so you should be able to handle that. The second set identifies what functions to use and where to put them....

Most of this is not anything overwhelmingly difficult by any means, particular if it is a class related to using excel that you may be participating in.

Post back with problems you may have completing the various tasks and we will see what we can do to guide you along.

RWSpeakman
03-02-2006, 02:25 PM
Ok I've gotten through parts 1 and struggled through part 2. Part 3 is really difficult for me. If someone could just help me get started on part 3 to point me in the right direction I'd greatly appreciate it.

Randy

XLGibbs
03-02-2006, 03:03 PM
It seems fairly straightforward, which part is giving you trouble?

RWSpeakman
03-02-2006, 03:09 PM
I'm on a really really beginner level so a lot of this stuff is not straight forward for me. I appreciate all of youre help. These parts in particular are confusing to me.

�� 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.)

mdmackillop
03-02-2006, 03:23 PM
The first items can be created using the macro recorder, then edited to remove verbiage. Names etc. can be stored in arrays and accessed using a "For each x in array" routine and incrementing an integer variable to add data to cells and worksheet names. I confess I don't know what a "random uniform number" is, I guess they mean Integers.
I hope this helps with the first few items of Part 3.
Regards
MD