PDA

View Full Version : VBA old test question help



jgoods
04-15-2015, 07:44 PM
The Excel file contains data on a company’s outstanding accounts from different customers. Each row corresponds to a particular customer. It indicates the size of the customer (1 for Small, 2 for Medium, 3 for Large), the transaction date in 2013, the number of shirts ordered and the amount of the payment due. Develop a user form that has the usual OK and Cancel buttons, two sets of option buttons and a list box. The first set of option buttons allows the user to choose the size of the customer (using captions Small, Medium and Large), and the second set allows the user to choose the Number of shirts purchased or the Amount column to summarize. The list box lists the twelve months of the year and allows the user to select one or more months to be summarized. Then write code in a sub called summarize placed in a module that takes the choices made in the form and displays a message listing the appropriate information and average number or amount.

The sub summarize should be called from a button placed on the worksheet with the data. The sub will call the form as described above. The form should be called over and over until the cancel button on the form is clicked and the options selected in the form should be preserved between calls to the form. The form should have your full name in the title bar. Also the message boxes will have your full name in the title bar. The average for days should be reported with a number with two significant digits and the average for amount should be displayed as currency with two significant digits. The following illustrates the screen captures that you will need to submit. (To capture the images press <alt-PrtSrc> and to paste them in your document press <Ctrl-V>).


Note that the program should work for any range that starts in cell A4 and that the orders and dates do not have to be sorted by size for the program to work. Try to size your images so that they fit on one page printed on both sides. Bring your program in a flash drive to class on Thursday so that you can run it with different data.


(Hints: You can define dim strMth as Variant and then have an statement like strMth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") to populate an array with the months. The VBA function Month(date) returns a number between 1 and 12 for the month of January,…, December, respectively. Your code in the sub Summarize will have to go through each line of data and count and accumulate either the number or the amount for the selected size and the selected months. For the month, your code would have to cycle through all the selected months and see if the transaction date occurred in one of the selected months.)





All I have so far is
Sub Summarize()
With UserForm1.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = ""
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
UserForm1.Show
End Sub


I created the user form and have all of the options on it. I just don't know how to get it to calculate an average based on the options selected.

Yongle
04-16-2015, 02:08 AM
Welcome to the forum
As a new member you may not yet have got around to reading the informative forum FAQ which you will find here (http://www.vbaexpress.com/forum/faq.php)

Can I ask about my homework here?
Please don't ask us questions directly out of your coursework materials. We are happy to provide direction and guidance for those studying VBA and other software. Be open about the fact that it is coursework, and you'll likely find yourself with more resources than you could possible need.


You were open but we do not really want to wade through old exam questions - it reminds us too much of being at school! And soaks up too much time. So in future, try to summarise the key points to help us quickly identify what you need which I think comes down to the following

The average for days should be reported with a number with two significant digits and the average for amount should be displayed as currency with two significant digits...
....
I created the user form and have all of the options on it. I just don't know how to get it to calculate an average based on the options selected.

You could have just asked:
Can anyone help me write some vba to calculate an average for both a range with cell values with number of days and $amounts to 2 significant digits
To which my reply is:
to try the attached macro
put some values for days in sheet 1 cells B2 to B7
put some values for amounts in sheet 1 cells C2 to C7

the macro will
calculate the averages and place them in B8 and C8
B8 and C8 are then formatted as required

This should give you a starting point. It gives you the syntax. You will need to modify to make it fit your workbook.
If you need help in making your ranges dynamic etc then start a new thread and try to be concise in you explanation.



Sub Calculate_Averages()


Dim DaysAvg As Double, AmntAvg As Double


AmntAvg = Application.Average(Range("b2:b7"))
DaysAvg = Application.Average(Range("c2:c7"))
Sheets("sheet1").Range("B8") = AmntAvg
Sheets("sheet1").Range("B8").NumberFormat = "0.00"
Sheets("sheet1").Range("C8") = DaysAvg
Sheets("sheet1").Range("C8").NumberFormat = "$#,##0.00"


End Sub